当前位置：首页 > news >正文

【MySQL】MySQL 8+版本使用窗口函数可以减少一次连表操作（额外Avg函数和Using函数使用，Using关键字参考里自行了解）

news 来源：原创 2024/5/20 12:06:21

力扣题

1、题目地址

1126. 查询活跃业务

2、模拟表

事件表：Events

Column Name	Type
business_id	int
event_type	varchar
occurences	int

(business_id, event_type) 是这个表的主键（具有唯一值的列的组合）。
表中的每一行记录了某种类型的事件在某些业务中多次发生的信息。

3、要求

平均活动是指有特定 event_type 的具有该事件的所有公司的 occurences 的均值。

活跃业务是指具有多个 event_type 的业务，它们的 occurences 严格大于该事件的平均活动次数。

写一个解决方案，找到所有活跃业务。

以任意顺序返回结果表。

结果格式如下所示。

示例 1：

输入：
Events 表：

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’: (7+3)/2 = 5
‘ads’: (11+7+6)/3 = 8
‘page views’: (3+12)/2 = 7.5
id=1 的业务有 7 个 ‘reviews’ 事件（多于 5 个）和 11 个 ‘ads’ 事件（多于 8 个），所以它是一个活跃的业务。

4、代码编写

要求分析

1、occurences 大于平均活动次数，求每种活动的平均活动次数
2、多个 event_type 的业务，所以是有两个或以上就是活跃的业务

知识点

Avg 函数（有很多种情况，这里只演示一种，参考里面有多种）

可以借鉴下我下面写的代码

SELECT event_type, SUM(occurences)/COUNT(*) AS num
FROM Events
GROUP BY event_type

可以换成

SELECT event_type, AVG(occurences) AS num
FROM Events
GROUP BY event_type

效果 AVG(occurences) = SUM(occurences)/COUNT(*)
AVG函数 与 GROUP BY子句 一起计算表中每组行的平均值

参考：MySQL avg()函数

Using 函数

using() 用于两张表的 join 查询，要求 using() 指定的列在两个表中均存在，并使用之用于 join 的条件

示例：select a.*, b.* from a left join b using(colA);
等同于：select a.*, b.* from a left join b on a.colA = b.colA;

参考：MySQL USING关键词 / USING()函数的使用

我的代码（Using函数使用）

SELECT business_id
FROM Events oneLEFT JOIN (SELECT event_type, SUM(occurences)/COUNT(*) AS numFROM EventsGROUP BY event_type) AS two USING(event_type)
WHERE one.occurences > two.num 
GROUP BY one.business_id
HAVING COUNT(one.business_id) >= 2

网友代码（使用窗口函数，简洁）

SELECT business_id
FROM (SELECT *, AVG(occurences) OVER (PARTITION BY event_type) avg_ocFROM Events
) t1
WHERE occurences > avg_oc
GROUP BY business_id
HAVING COUNT(distinct event_type) >= 2

代码解析

SELECT event_type, SUM(occurences)/COUNT(*) AS num
FROM Events
GROUP BY event_type

| event_type | num |
| ---------- | --- |
| reviews    | 5   |
| ads        | 8   |
| page views | 7.5 |

SELECT *, AVG(occurences) OVER (PARTITION BY event_type) avg_oc
FROM Events

| business_id | event_type | occurences | avg_oc |
| ----------- | ---------- | ---------- | ------ |
| 1           | ads        | 11         | 8      |
| 2           | ads        | 7          | 8      |
| 3           | ads        | 6          | 8      |
| 1           | page views | 3          | 7.5    |
| 2           | page views | 12         | 7.5    |
| 1           | reviews    | 7          | 5      |
| 3           | reviews    | 3          | 5      |