Create table If Not Exists Events0302 (business_id int, event_type varchar(10), occurences int) tablespace etc; Truncate table Events0302; insert into Events0302 (business_id, event_type, occurences) values ('1', 'reviews', '7'); insert into Events0302 (business_id, event_type, occurences) values ('3', 'reviews', '3'); insert into Events0302 (business_id, event_type, occurences) values ('1', 'ads', '11'); insert into Events0302 (business_id, event_type, occurences) values ('2', 'ads', '7'); insert into Events0302 (business_id, event_type, occurences) values ('3', 'ads', '6'); insert into Events0302 (business_id, event_type, occurences) values ('1', 'page views', '3'); insert into Events0302 (business_id, event_type, occurences) values ('2', 'page views', '12');
写一段 SQL 来查询所有活跃的业务。
如果一个业务的某个事件类型的发生次数大于此事件类型在所有业务中的平均发生次数,并且该业务至少有两个这样的事件类型,那么该业务就可被看做是活跃业务。
SELECT business_id
FROM Events AS e
JOIN (
SELECT event_type, AVG(occurences) AS eventAvg
FROM Events
GROUP BY event_type
) AS e1
ON e.event_type = e1.event_type
WHERE e.occurences > e1.eventAvg
GROUP BY business_id
HAVING COUNT(*) >= 2