SQL練習00015

需求:查詢活躍業務
事件表：Events

Column Nam	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	int	11
2	ads	7
3	ads	6
1	page views	3
2	page views	12

result table:

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），所以它是活躍業務。

--建表語句
Create table If Not Exists Events (business_id int, event_type varchar(10), occurences int);
Truncate table Events;
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');

--答案1
with tmp as
  (
  select
    event_type,
    avg(occurences) as mk
  from
    Events group by event_type
  )

select
  business_id
from 
  Events e join 
  tmp on 
  e.event_type=tmp.event_type where 
  occurences>mk group by 
  business_id having 
  count(1)>=2 

--答案2
select
  business_id
from
  (
  select
    *,
    if(occurences>avg(occurences) over(partition by    event_type),1,0) as mk
  from
    Events 
  ) 
  a group by 
  business_id having 
  sum(mk)>1