SQL选择求和时的情况

Question

我想使用基于group子句和的CASE应用查询。

我的桌子看起来应该是：

|---------------------|------------------|
|      customerId     |     duration     |
|---------------------|------------------|
|        A12345       |         5        |
|---------------------|------------------|
|        A12345       |         10       |
|---------------------|------------------|
|        A65421       |         5        |
|---------------------|------------------|

并希望它能输出

|---------------------|------------------|
|      sum(duration)  | count(customerId)|
|---------------------|------------------|
|        0-5          |         1        |
|---------------------|------------------|
|        5-10         |         0        |
|---------------------|------------------|
|        10+          |         1        |
|---------------------|------------------|

我当前的SQL如下所示：

select
       case
            when sum(duration) between 0 and 5 then '0-5'
            when sum(duration) between 5 and 10 then '5-10'
            else '10+'
        end as bucket,
       count(customerId)
from table
group by 1, customerid

但这给了我

无效操作:不允许按条款分组集合；

任何帮助都是非常感谢的。

Answer 1

您需要嵌套聚合：

select -- then count the number of rows per bucket
   bucket,
   count(*)
from 
 ( -- aggregate the duration per customer first
   select customerid, 
          case
            when sum(duration) between 0 and 5 then '0-5'
            when sum(duration) between 5 and 10 then '5-10'
            else '10+'
        end as bucket
   from table
   group by customerid
 ) as dt
group by bucket

Answer 2

按1-> will Group按第一列分组，该列具有不允许的聚合函数sum()，因此查询应如下所示

Select Case when duration between 0 and 5 then '0-5'
        when duration between 0 and 5 then '5-10'
        Else '10+'
        end as bucket,
        count(customerId) customerIdCount
From
(
    select
          customerid, sum(duration) as duration
    from table1
    group by customerid
) Q
Group by Case when duration between 0 and 5 then '0-5'
    when duration between 0 and 5 then '5-10'
    Else '10+'
    end

如果您希望得到问题中所示的确切结果，则需要将所有这些桶值放到一个表中，并加入该表。

Select t1.bucket, coalesce(customerIdCount,0) from table2 t1
Left Join 
(
    Select Case when duration between 0 and 5 then '0-5'
            when duration between 0 and 5 then '5-10'
            Else '10+'
            end as bucket,
            count(customerId) customerIdCount
    From
    (
        select
              customerid, sum(duration) as duration
        from table1
        group by customerid
    ) Q
    Group by Case when duration between 0 and 5 then '0-5'
        when duration between 0 and 5 then '5-10'
        Else '10+'
        end
) r on t1.bucket = r.bucket
;

这是小提琴

Answer 3

你需要先按客户分组，就像dnoeth已经建议的那样。但考虑一种修改后的方法：

SELECT CASE bucket
         WHEN 0 THEN '0-5'  -- 5 excluded
         WHEN 1 THEN '5-10' -- 10 excluded
         ELSE        '10+'
       END AS sum_duration
     , count(*) AS customers
FROM  (
   SELECT customerid
        , trunc(sum(duration))::int / 5 AS bucket -- ①
   FROM   tbl
   GROUP  BY customerid
   ) sub
GROUP  BY bucket;

1因为您形成了5的桶，所以我们可以使用整数除法来获得整数桶。应该更短一些/更快一些(尤其是用更多的桶)。如果duration是integer类型，则可以简化：

...
        , sum(duration) / 5 AS bucket
..

无论哪种方式，5(确切地)结束在组"5-10“独占，等等-修复你原来的一个角落案例错误，这将是双重计数。经常被忽略的是，BETWEEN包括下限和上限，并且通常是仅适用于离散数据类型。