sql开窗函数语法糖1

在翻mysql文档看见的开窗函数的语法糖，感觉很有意思，之前都没见过

-- 准备表数据
CREATE TABLE LOGIN_LOG(UID VARCHAR(10),LOG_DATE DATE);
INSERT INTO LOGIN_LOG VALUES(1,'2024-01-01'),
(1,'2024-01-02)'
(1,'2024-01-03'),
(1,'2024-01-04'),
(1,'2024-01-05'),
(1,'2024-01-06'),
(1,'2024-01-07'),
(2,'2024-01-03'),
(3,'2024-01-06'),
(4,'2024-01-02'),
(1,'2024-01-21'),
(8,'2024-01-01'),
(3,'2024-01-23'),
(4,'2024-01-12'),
(5,'2024-01-11'),
(1,'2024-01-22'),
(1,'2024-01-23'),
(5,'2024-01-23'),
(5,'2024-01-24'),
(5,'2024-01-25'),
(5,'2024-01-26'),
(5,'2024-01-27'),
(5,'2024-01-28'),
(5,'2024-01-29')

-- 分组查询 WINDOW win_name AS (PARTITION BY .. ORDER BY ..)
SELECT UID,LOG_DATE,RANK() OVER(WIN_NAME) RO FROM LOGIN_LOG WINDOW WIN_NAME AS (PARTITION BY UID ORDER BY LOG_DATE);
-- 将over部分语句放在表屁股后面形成一个公共表达式，这样有相同window的时候直接用，不需要重复写了
-- 目前测试mysql8/pgsql16/orcle21都能用，需要注意的是这个语法糖不能用在子查询里


-- 过滤器 FILTER (WHERE ...)
SELECT COUNT(UID) FILTER (WHERE UID='1') FROM LOGIN_LOG;
-- 按条件过滤后聚合



-- 查找连续登录用户
SELECT *
FROM (
    SELECT uid,
       log_date,
       lead(ld,5,'2099-12-12') OVER (PARTITION BY uid ORDER BY log_date) lld
    FROM (
        SELECT uid,
               log_date,
               LEAD(log_date, 1, '2099-12-12') OVER (PARTITION BY uid ORDER BY log_date) ld
            FROM tmp.LOGIN_LOG) a
            WHERE ld - log_date = 1) b
WHERE lld - log_date = 6;
-- 连续7天登录用户