查询活跃业务sql

最新推荐文章于 2024-08-30 14:08:50 发布

什么阳

最新推荐文章于 2024-08-30 14:08:50 发布

阅读量54

点赞数

分类专栏： sql训练文章标签： sql 数据库 mysql

原文链接：https://leetcode.cn/problems/active-businesses

版权

sql训练专栏收录该内容

24 篇文章 0 订阅

订阅专栏

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