包含以下字段的用户表。
id
agent_id
locality
total
building_type
price我通过以下查询得到了不同的统计结果
SELECT agent_id, COUNT(*) AS stat_1
FROM users
WHERE "building_type" = 'single'
AND ("price" BETWEEN 0 AND 200000)
GROUP BY "agent_id"
order by agent_id
SELECT agent_id, COUNT(*) AS stat_2
FROM users
WHERE "building_type" = 'single'
AND ("price" BETWEEN 200001 AND 350000)
GROUP BY "agent_id"
order by agent_id
SELECT agent_id, COUNT(*) AS stat_3
FROM users
WHERE "building_type" = 'single'
AND ("price" BETWEEN 3500001 AND 500000)
GROUP BY "agent_id"
order by agent_id但我希望在相同的查询中获得所有统计数据的结果,例如
SELECT agent_id,
COUNT(*) AS stat_1,
COUNT(*) AS stat_2,
COUNT(*) AS stat_3
from users
where <Conditions>如何在一次查询中获得此结果?
发布于 2016-08-03 14:42:39
您可以使用条件聚合:
SELECT agent_id,
COUNT(*) filter (where price BETWEEN 0 AND 200000) as stat_1,
COUNT(*) filter (where price BETWEEN 200001 AND 350000) AS stat_2,
COUNT(*) filter (where price BETWEEN 3500001 AND 500000) AS stat_3
from users
WHERE building_type = 'single'
GROUP BY agent_id
order by agent_id发布于 2016-08-03 14:44:35
Postgres9.4以一种优雅的方式解决了这类问题,它添加了一个filter子句,允许您在聚合函数上应用条件:
SELECT agent_id,
COUNT(*) FILTER (WHERE "price" BETWEEN 0 AND 200000) AS stat_1,
COUNT(*) FILTER (WHERE "price" BETWEEN 200001 AND 350000) AS stat_2,
COUNT(*) FILTER (WHERE "price" BETWEEN 3500001 AND 500000) AS stat_3
FROM users
WHERE "building_type" = 'single'
GROUP BY "agent_id"
ORDER BY "agent_id"对于不允许filter子句的早期版本,您可以通过对case表达式应用聚合函数来实现相同的行为。在这里,我们利用count忽略nulls的属性:
SELECT agent_id,
COUNT(CASE WHEN "price" BETWEEN 0 AND 200000 THEN 1 END) AS stat_1,
COUNT(CASE WHEN "price" BETWEEN 200001 AND 350000 THEN 1 END) AS stat_2,
COUNT(CASE WHEN "price" BETWEEN 3500001 AND 500000 THEN 1 END) AS stat_3
FROM users
WHERE "building_type" = 'single'
GROUP BY "agent_id"
ORDER BY "agent_id"https://stackoverflow.com/questions/38736105
复制相似问题