如何在第二个查询中将查询的输出用作where参数

Question

我有这个查询来列出top_10导联段：

select segment as "Segment", count(*) as "Qty" from table
where id >=1
group by segment
order by 2 desc
limit 10

现在，我想使用上面查询中的第一个片段(限制1 desc(在where子句中使用，以列出该特定片段中的所有角色。假设第一个查询中的top_1段是航空。

然后我想这样查询：

select role, count(*) from table
where segment = 'aviation'
group by role
order by 2 desc

我该怎么做？

Answer 1

您可以使用分析计数函数来获取每个分段的计数，然后使用first_value获取顶部分段，然后进行筛选。参见代码中的注释：

select role, count(*)
from
(
select segment, role,
--Get Top segment
first_value(segment) over(order by segment_cnt desc rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING ) as top_segment
from
(
select --calculate segment counts
segment, role, 
count(case when id>=1 then 1 else null end) over (partition by segment) as segment_cnt
from table
)s
)s
WHERE segment=top_segment --filter only top 1 segment
GROUP BY role
ORDER BY 2 desc