该表的纹理类似于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行。我知道会有缺失的组合,那么如何使它们一起出现呢?
我认为您只需要条件聚合:
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] 删除。
我来说两句