1126. 查询活跃业务（难度：中等）

题目描述

（通过次数10,948 | 提交次数15,932，通过率68.72%）

事件表：Events
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| business_id   | int     |
| event_type    | varchar |
| occurences    | int     | 
+---------------+---------+
此表的主键是 (business_id, event_type)。
表中的每一行记录了某种类型的事件在某些业务中多次发生的信息。

写一段 SQL 来查询所有活跃的业务。
如果一个业务的某个事件类型的发生次数大于此事件类型在所有业务中的平均发生次数，并且该业务至少有两个这样的事件类型，那么该业务就可被看做是活跃业务。

查询结果格式如下所示：
Events table:
+-------------+------------+------------+
| business_id | event_type | occurences |
+-------------+------------+------------+
| 1           | reviews    | 7          |
| 3           | reviews    | 3          |
| 1           | ads        | 11         |
| 2           | ads        | 7          |
| 3           | ads        | 6          |
| 1           | page views | 3          |
| 2           | page views | 12         |
+-------------+------------+------------+

结果表
+-------------+
| business_id |
+-------------+
| 1           |
+-------------+ 
'reviews'、 'ads' 和 'page views' 的总平均发生次数分别是 (7+3)/2=5, (11+7+6)/3=8, (3+12)/2=7.5。
id 为 1 的业务有 7 个 'reviews' 事件（大于 5）和 11 个 'ads' 事件（大于 8），所以它是活跃业务。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/active-businesses

//测试数据
Create table If Not Exists Events (business_id int, event_type varchar(10), occurences int);
insert into Events (business_id, event_type, occurences) values ('1', 'reviews', '7');
insert into Events (business_id, event_type, occurences) values ('3', 'reviews', '3');
insert into Events (business_id, event_type, occurences) values ('1', 'ads', '11');
insert into Events (business_id, event_type, occurences) values ('2', 'ads', '7');
insert into Events (business_id, event_type, occurences) values ('3', 'ads', '6');
insert into Events (business_id, event_type, occurences) values ('1', 'page views', '3');
insert into Events (business_id, event_type, occurences) values ('2', 'page views', '12');

解题思路

解题思路：

这道题逻辑稍微有点复杂，但解题难度并不高。

只要理解了业务需求，应该很容易就能做出来。这一点从通过率上就能看得出来。

一般来说，可以通过如下步骤来实现：

**第一步**：计算出每个event_type的平均发生次数；

**第二步**：将每个业务中每个event_type与该event_type的平均发生次数相对比，取出高于平均发生次数的event_type；

**第三步**：对业务进行分组统计，计算出高于平均发生次数的event_type的个数，并过滤出个数大于等于2的业务；

那么，第一步中，如何取出每个event_type的平均发生次数呢？

最常规的处理方法，就是对event_type进行分组统计。比如，下面参考SQL中的方法一。

不过，也可以使用窗口函数得到同样的结果，并且因为窗口函数可以在每一行中得出开窗后的平均值，方便了第二步的比较，使用起来更加方便。比如，下面参考SQL中的方法二。

可是，强哥（公众号：跟强哥学SQL）觉得，抛开以上的思路，本题还可以通过自关联来实现。这是我在力扣大量的题解中没有看到的思路。

通过自关联，将每一条数据都发散到跟它event_type相同的行上去，然后通过最大值与平均值的比较，即可得出满足条件的行。

最后，再完成第三步即可。比如，下面参考SQL中的方法三。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

#方法一：使用子查询
with 
tmp1 as (
select
        a.event_type,
avg(a.occurences) avg_occurences
from Events a
group by a.event_type
)
select
    b.business_id
from Events b
inner join tmp1 c
on b.event_type = c.event_type
and b.occurences > c.avg_occurences
group by b.business_id
having count(1)>=2;

#方法二：使用分析函数
select b.business_id
from 
(
select
    a.business_id,
    a.occurences,
avg(a.occurences) over(partition by a.event_type) as avg_occurences
from events a
)b
where b.occurences > b.avg_occurences
group by 1
having count(1) > 1;

#方法三：使用自关联
SELECT
    c.business_id
from
(
SELECT
    a.business_id,
    a.event_type
FROM Events a
inner join Events b
on a.event_type = b.event_type
group by a.business_id,a.event_type
having max(a.occurences) > avg(b.occurences)
)c
group by c.business_id
having count(1)>=2;