如何正确加入以在同一列上进行两次聚合?

简单编码

该表的纹理类似于state_id | county_id | city_id | eventtype其中eventtype是二进制的;它等于1或2。我想对所有3列进行分组,并查看等于1和2的事件类型的总和。如何正确执行此操作?

当我做一个内部连接

select *
from 
(state_id, county_id, city_id, sum(eventtype) as views
select 
poptable
where eventtype = 1 
group by state_id, county_id, city_id) l
INNER JOIN
(state_id, county_id, city_id, sum(eventtype) as passes
select 
poptable
where eventtype = 2
group by state_id, county_id, city_id) r
ON l.state_id = r.state_id
and l.county_id = r.county_id
and l.city_id = r.city_id

我得到大约500行。但是,如果我执行完全外部联接,则将获得约3000行。我知道会有缺失的组合,那么如何使它们一起出现呢?

戈登·利诺夫(Gordon Linoff)

我认为您只需要条件聚合:

select state_id, county_id, city_id,
       sum(case when eventtype = 1 then 1 else 0 end) as views_1,
       sum(case when eventtype = 2 then 1 else 0 end) as views_2
from poptable
group by state_id, county_id, city_id;

我不确定你为什么这么做sum(eventtype)您希望在第一种情况下将“ 1”相加,在第二种情况下将“ 2”相加似乎很奇怪。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章