一、力扣链接
LeetCode_1126
二、题目描述
事件表：Events
+---------------+---------+ | Column Name | Type | +---------------+---------+ | business_id | int | | event_type | varchar | | occurrences | int | +---------------+---------+ (business_id, event_type) 是这个表的主键（具有唯一值的列的组合）。 表中的每一行记录了某种类型的事件在某些业务中多次发生的信息。
平均活动 是指有特定 event_type 的具有该事件的所有公司的 occurrences 的均值。
活跃业务 是指具有 多个 event_type 的业务，它们的 occurrences 严格大于 该事件的平均活动次数。
写一个解决方案，找到所有 活跃业务。
三、目标拆解
四、建表语句
Create table If Not Exists Events (business_id int, event_type varchar(10), occurrences int)
Truncate table Events
insert into Events (business_id, event_type, occurrences) values ('1', 'reviews', '7')
insert into Events (business_id, event_type, occurrences) values ('3', 'reviews', '3')
insert into Events (business_id, event_type, occurrences) values ('1', 'ads', '11')
insert into Events (business_id, event_type, occurrences) values ('2', 'ads', '7')
insert into Events (business_id, event_type, occurrences) values ('3', 'ads', '6')
insert into Events (business_id, event_type, occurrences) values ('1', 'page views', '3')
insert into Events (business_id, event_type, occurrences) values ('2', 'page views', '12')五、过程分析
1、窗口函数计算平均活动次数，不影响原字段
2、筛选有多种类型大于平均活动次数的记录
六、代码实现
with t1 as(
select business_id, event_type, occurrences, avg(occurrences) over(partition by event_type) avg_occurrences
from Events
)
select business_id
from t1 where occurrences > avg_occurrences
group by business_id having count(business_id) > 1七、结果验证
八、小结
1、CTE表达式 + 窗口函数 + group by
2、关键句：有 多个 event_type 的业务
