Presto - Union & Limit

Question

目前，我有一个CTE，它正在构建一个数据列表和一个随机行号

我想要做的是根据一些条件输出几个联合在一起的查询。使用联合查询可以很好地执行查询，但是当我向任何查询添加限制时，它就不能工作了。

有没有一种方法可以运行我的查询并获得不同的子集？

示例：

with selection as (
select account, address, type, random(1000)
from details
)

select 
  account,
  address
from details
where type = 'a'
order by random 
limit 50

union all

select 
  account,
  address
from details
where type = 'b'
order by random 
limit 50

union all

select 
  account,
  address
from details
where type = 'c'
order by random 
limit 50

Answer 1

实际上，这里根本不需要工会：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY type ORDER BY random) rn
    FROM details
    WHERE type IN ('a', 'b', 'c')
)

SELECT account, address
FROM cte
WHERE rn <= 50;

如果您真的想使用联合方法，那么以下语法可能会起作用，将每个limit子查询放在一个单独的闭包中：

SELECT account, address
FROM
(SELECT account, address
 FROM details
 WHERE type = 'a'
 ORDER BY random)
UNION ALL
(SELECT account, address
 FROM details
 WHERE type = 'b'
 ORDER BY random)
UNION ALL
(SELECT account, address
 FROM details
 WHERE type = 'c'
 ORDER BY random)