题 :如果有500多个事件,每天都有可能发生变化,想统计出一年内每一天不同事件的集合,一条sql写出
思路:
1 判断 通过单一字段识别当天变化还是通过联合字段识别 (打标签识别)
2 设计每天增量表记录变动事件
3 根据全年事件group by , 再用 collect_list 集合起来。
两种写法,1 sql 2 pyspark(书写更简单点)
plan A(伪代码)
– log_label (可以是组合字段:识别相同事件的不同变化)
– log_event
select log_event
,collect_list (‘,’,concat_ws(log_label))
from
(select ta.log_event
,ta.log_label
,case when ta.log_label!=tb.log_event then 1 else 0 end as label
,ta.dayno
from
(select log_event, log_label ,dayno
from table_a
where dayno >‘2020-12-31’ and dayno<=‘2021-12-31’
group by dayno )ta
left join
(select log_event, log_label ,date_add(dayno,1) as dayno1
from table_a
where dayno >=‘2020-12-31’ and dayno<‘2021-12-31’
group by dayno )tb
on ta.log_event = tb.log_event and ta.dayno=tb.dayno1
group by
ta.log_event
,ta.log_label
,case when ta.log_label!=tb.log_event then 1 else 0 end
,ta.dayno
)sa
where sa.label=1
group by log_event