在条件小于分组列的单击房屋上按分组

Question

我有一部分sql代码：

select   
,inst_date
,country
,count (distinct (case when (event_day - inst_date) <= 0 then id end)) as event_0
,count (distinct (case when (event_day - inst_date) <= 1 then id end)) as event_1
,count (distinct (case when (event_day - inst_date) <= 2 then id end)) as event_2
,count (distinct (case when (event_day - inst_date) <= 3 then id end)) as event_3
,count (distinct (case when (event_day - inst_date) <= 4 then id end)) as event_4
,count (distinct (case when (event_day - inst_date) <= 5 then id end)) as event_5
,count (distinct (case when (event_day - inst_date) <= 6 then id end)) as event_6
,count (distinct (case when (event_day - inst_date) <= 7 then id end)) as event_7
from t1
all left join 
(
select distinct id
, toDate(event_date) as event_day
, 1 as event
from events
) as events_
using id
where (event_day - inst_date) between 0 and 7
group by inst_date
,country ;

我看到了我的数据：

事件_0>事件_2><1th>事件_5>事件_6<2th>事件_7<11>>>td>13<14>14

inst_date	国家	事件_1	事件_3	事件_4
2020-12-01	我们	10	11	12	14

使用group by:

select inst_date, country, 
(event_day - inst_date) as date_difference,
count(*) as events_count
from t1
all left join (
select distinct id, toDate(event_date) as event_day, 1 as event
from events
) as events_ using id
where (event_day - inst_date) between 0 and 7
group by inst_date, country, (event_day - inst_date)
order by inst_date, country, date_difference;

我强烈建议在查询中的所有列前面加上它们所属的表，这样查询就可以清楚地了解底层数据结构。