我有一个大表,每个表都有一个位置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/