我有一个大表,每个表都有一个位置id(location_id)。在location_id列下面有多个相同值的实例。我要做的是计算每个值出现的次数,然后将该值放入一个“bucket”
我试过了,但最后一切都在“500+”桶里。

SELECT
 CASE WHEN count(location_id) > 1 AND count(location_id) <= 25 THEN '1-25'
 WHEN count(location_id) > 25 AND count(location_id) <= 500 THEN '26-500'
 WHEN count(location_id) > 500  THEN '500+'
 ELSE 'nothing'
 end as bucket,
 count(*) as Column1
FROM myTable

我在这里做错什么了?

最佳答案

您需要两个级别的聚合:

SELECT (CASE WHEN cnt <= 25 THEN '1-25'
             WHEN cnt <= 500 THEN '26-500'
             ELSE '500+'
        END) as bucket, COUNT(*) as numlocations, SUM(cnt) as numTotal
FROM (SELECT location_id, count(*) as cnt
      FROM myTable
      GROUP BY location_id
     ) l
GROUP BY bucket
ORDER BY MIN(cnt);

关于sql - Postgres COUNT个实例并放入“存储桶”,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43527730/

10-11 08:56
查看更多