窗口函数的实现

窗口函数的实现

简介

窗口函数的实现 & MySQL 实现窗口函数功能
众所周知: MySQL从8.0版本开始支持窗口函数, 但是对于8.0版本之前的MySQL是无法使用窗口函数的, 我们可以通过一个类似嵌套子查询的方式, 来实现窗口函数的功能 ;

1. 问题

使用sql完成,且不能使用窗口函数
由数据A如下: 
id	time	name
1001	'2023-11-06'	a
1001	'2023-11-10'	a
1001	'2023-11-23'	b
1001	'2023-12-08'	b
1001	'2023-12-11'	a
1001	'2023-12-28'	a

得到数据B如下: 
id	mame	min_date	max_date
1001	a	'2023-11-06'	'2023-11-10'
1001	b	'2023-11-23'	'2023-12-08'
1001	a	'2023-12-11'	'2023-12-28'

注意: 时间中间有穿插的, 分开统计

2. 解决方法

-- 方法一: 使用窗口函数
WITH t_1 AS (
SELECT * FROM (
VALUES  
 (1001,	'2023-11-06',	'a')
,(1001,	'2023-11-10',	'a')
,(1001,	'2023-11-23',	'b')
,(1001,	'2023-12-08',	'b')
,(1001,	'2023-12-11',	'a')
,(1001,	'2023-12-28',	'a')
) AS table_name(id, time, name) 
)

SELECT id, name, MIN(time) AS min_date, MAX(time) AS max_date
FROM (
    SELECT id, name, time, 
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY time) -
           ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY time) AS grp
    FROM t_1
) AS temp
GROUP BY id, name, grp;

id	name	min_date	max_date
1001	a	2023-11-06	2023-11-10
1001	a	2023-12-11	2023-12-28
1001	b	2023-11-23	2023-12-08

-- 方法二: 实现窗口函数
WITH A AS (
SELECT * FROM (
VALUES  
 (1001,	'2023-11-06',	'a')
,(1001,	'2023-11-10',	'a')
,(1001,	'2023-11-23',	'b')
,(1001,	'2023-12-08',	'b')
,(1001,	'2023-12-11',	'a')
,(1001,	'2023-12-28',	'a')
) AS table_name(id, time, name) 
)

SELECT 
    id, name, MIN(time) AS min_date, MAX(time) AS max_date
FROM (
    SELECT
        id, name, time
        , (SELECT COUNT(*) FROM A AS A2 WHERE A2.id = A1.id AND A2.time <= A1.time ) 
        - (SELECT COUNT(*) FROM A AS A3 WHERE A3.id = A1.id AND A3.name = A1.name AND A3.time <= A1.time) AS rn
      FROM A AS A1
) t1
GROUP BY id, name, rn
;

id	name	min_date	max_date
1001	a	2023-11-06	2023-11-10
1001	a	2023-12-11	2023-12-28
1001	b	2023-11-23	2023-12-08

总结:

-- 利用 id 和 时间的关系, 统计每个id和时间到目前为止 id出现次数, 实现 对次id根据时间顺序进行排序的功能
(SELECT COUNT(*) FROM A AS A2 WHERE A2.id = A1.id AND A2.time <= A1.time ) 
-->
ROW_NUMBER() OVER (PARTITION BY id ORDER BY time)

-- 同理: 多维度分组的数据, 就对维度进行管理, 对排序数据进行比较
(SELECT COUNT(*) FROM A AS A3 WHERE A3.id = A1.id AND A3.name = A1.name AND A3.time <= A1.time)
ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY time)

end