组合GROUP BY和CASE表达式-标签问题

Question

我一直在努力在GROUP BY中使用CASE表达式。我不确定这是否是查询我想要什么的最佳方式，所以请告诉我是否有更好的方式，但这是我能找到的最接近的方式。我现在遇到的问题是给GROUP BY语句加标签。

ID      Crossing_Order     Barhab_Weighted
1             0                 120
2             0                 0
3             1                 200
4             1                 500
5             2                 200
6             3                 10
7             4                 500

我很想买一张这样的桌子：

Crossing_Order       Over_100            Under_100
zero                    1                   1   
one                     0                   2
more_than_1             1                   2

这个代码让我接近：

SELECT
count(distinct(case when a.barhab_weighted > 100 then site_id end)) as Over_100
,count(distinct(case when a.barhab_weighted <= 100 then site_id end)) as Under_100
FROM site_details as a
GROUP BY case
when a.crossing_order = 0 then 'zero'
when a.crossing_order = 1 then 'one'
when a.crossing_order > 1 then 'more_than_1' end

我得到的结果很接近，但我无法正确标记。如何显示"零"one_answers"一"值？

Over_100       Under_100
1           1               1
2           0               2
3           1               2

Answer 1

我不知道为什么要在唯一列上使用count(distinct)。

但是您需要SELECT中的CASE逻辑。如果使用横向连接定义一次，这是最简单的：

SELECT v.Crossing_Order,
count(distinct case when sd.barhab_weighted > 100 then sd.site_id end)) as Over_100,
count(distinct case when sd.barhab_weighted <= 100 then sd.site_id end)) as Under_100
FROM site_details sd CROSS APPLY
(VALUES (case when sd.crossing_order = 0 then 'zero'
when sd.crossing_order = 1 then 'one'
when sd.crossing_order > 1 then 'more_than_1'
end)
) v(Crossing_Order)
GROUP BY v.Crossing_Order;

注意，我还将表别名a更改为有意义的内容——sd——表的缩写。

Answer 2

您可以使用以下条件聚合：

select
case
when a.crossing_order = 0 then 'zero'
when a.crossing_order = 1 then 'one'
when a.crossing_order > 1 then 'more_than_1' 
end cat,
sum(case when barhab_weighted >= 100 then 1 else 0 end) over_100,
sum(case when barhab_weighted <  100 then 1 else 0 end) under_100
from site_details
group by
case
when a.crossing_order = 0 then 'zero'
when a.crossing_order = 1 then 'one'
when a.crossing_order > 1 then 'more_than_1' 
end

Answer 3

执行case以在派生表中对crossing_order进行分组。GROUP BY的结果：

select crossing_order_grouped,
count(distinct case when barhab_weighted > 100 then site_id end) as Over_100,
count(distinct case when barhab_weighted <= 100 then site_id end) as Under_100
from
(
SELECT case when crossing_order = 0 then 'zero'
when crossing_order = 1 then 'one'
when crossing_order > 1 then 'more_than_1'
end crossing_order_grouped,
Barhab_Weighted
from site_details
) dt
group by crossing_order_grouped

Answer 4

GROUP BY不向结果添加字段，您需要SELECT该大小写表达式，并在GROUP BY中重复它。

SELECT CASE 
WHEN a.crossing_order = 0 THEN 'zero'
WHEN a.crossing_order = 1 THEN 'one'
WHEN a.crossing_order > 1 THEN 'more_than_1' 
END AS crossing_order
, COUNT(DISTINCT CASE WHEN a.barhab_weighted > 100 THEN site_id END) AS Over_100
, COUNT(DISTINCT CASE WHEN a.barhab_weighted <= 100 THEN site_id END) AS Under_100
FROM site_details AS a
GROUP BY CASE WHEN a.crossing_order = 0 THEN 'zero'
WHEN a.crossing_order = 1 THEN 'one'
WHEN a.crossing_order > 1 THEN 'more_than_1' 
END

注意：如果你真的希望数据按照你提供的顺序，你可能需要另一个CASE表达式来将cross_order"when"转换为合适的值；否则"零"将排在最后。