首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >选择具有不同条件的多列

问选择具有不同条件的多列
EN

Stack Overflow用户
提问于 2016-08-03 14:37:56
回答 2查看 3.1K关注 0票数 3

包含以下字段的用户表。

代码语言:javascript
复制
id
agent_id
locality
total
building_type
price

我通过以下查询得到了不同的统计结果

代码语言:javascript
复制
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

但我希望在相同的查询中获得所有统计数据的结果,例如

代码语言:javascript
复制
SELECT agent_id, 
       COUNT(*) AS stat_1, 
       COUNT(*) AS stat_2, 
       COUNT(*) AS stat_3 
from users 
where <Conditions>

如何在一次查询中获得此结果?

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-08-03 14:42:39

您可以使用条件聚合:

代码语言:javascript
复制
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
票数 3
EN

Stack Overflow用户

发布于 2016-08-03 14:44:35

Postgres9.4以一种优雅的方式解决了这类问题,它添加了一个filter子句,允许您在聚合函数上应用条件:

代码语言:javascript
复制
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的属性:

代码语言:javascript
复制
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"
票数 4
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/38736105

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档