"按聚合事例分组"语句，并显示事例列和计数

Question

我有一个查询，我需要修改它来显示Bucket字段中的所有值，并显示计数和METRIC_VALUE

select 
CASE
WHEN sum(e.Metric_Value) >1000 THEN '>1000'
WHEN sum(e.Metric_Value) >500  THEN '>500'
WHEN sum(e.Metric_Value) >100  THEN '>100'
WHEN sum(e.Metric_Value) >1 THEN '>1'
ELSE '<1' END AS "Bucket",
count(distinct i.INTEGRATORKEY) as Count,
SUM(e.Metric_Value) AS Metric_Value  
from "ANALYTICS_PROD"."ACCOUNT_SCORE"."AGG_AS_ENVELOPES" e
left join   "ANALYTICS_PROD"."BODM"."INTEGRATORLOOKUPVIEW"  i
ON e.KeyId = i.KeyId 
AND e.SourceKey = i.SourceKey

上述查询的结果仅显示'>1000'然而，我需要看到一桶其他度量值，如>1000，>500，>100，>1

Answer 1

通过应用聚合函数(SUM，COUNT(而不使用GROUP BY子句，可以将所有行聚合为一个结果行。显然，表中所有Metric_Value的总和都大于1000。

相反，你似乎想要的是每个桶的计数，所以按桶分组：

select 
case
when e.metric_value > 1000 then '>1000'
when e.metric_value >  500 then '>500'
when e.metric_value >  100 then '>100'
when e.metric_value >    1 then '>1'
else                            '<=1'
end as bucket,
count(distinct i.integratorkey) as count_of_integrators,
sum(e.metric_value) as sum_of_metric_values
from analytics_prod.account_score.agg_as_envelopes e
left join analytics_prod.bodm.integratorlookupview  i on e.keyid = i.keyid 
and e.sourcekey = i.sourcekey
group by
case
when e.metric_value > 1000 then '>1000'
when e.metric_value >  500 then '>500'
when e.metric_value >  100 then '>100'
when e.metric_value >    1 then '>1'
else                            '<=1'
end;