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;

如果你真的想采用并集方法，那么下面的语法可能会起作用，每个限制子查询都在一个单独的闭包中：

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)