SQL | 窗口函数 | 连续登录、连续得分

最新推荐文章于 2024-06-25 10:53:41 发布

惜木兮

最新推荐文章于 2024-06-25 10:53:41 发布

阅读量428

点赞数

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/weixin_46023346/article/details/116456164

版权

SQL 专栏收录该内容

39 篇文章 2 订阅

订阅专栏

排序函数：
ROW_NUMBER() OVER() 按照从上至下的顺序，连续排序，没有并列名次，1,2,3,4…
RANK() OVER() 有并列名次，但是名次是不连续的，1,2,2,4…
DENSE_RANK() OVER() 有并列名次，名次是连续的，1,2,2,3…

一、连续7天登录的总人数

1、创建表

CREATE TABLE IF NOT EXISTS login(
uid INT,
dt DATE,
login_status INT
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、插入数据

INSERT INTO login VALUES(1,'2019-07-11',1);
INSERT INTO login VALUES(1,'2019-07-12',1);
INSERT INTO login VALUES(1,'2019-07-13',1);
INSERT INTO login VALUES(1,'2019-07-14',1);
INSERT INTO login VALUES(1,'2019-07-15',1);
INSERT INTO login VALUES(1,'2019-07-16',1);
INSERT INTO login VALUES(1,'2019-07-17',1);
INSERT INTO login VALUES(1,'2019-07-18',1);
INSERT INTO login VALUES(2,'2019-07-11',1);
INSERT INTO login VALUES(2,'2019-07-12',1);
INSERT INTO login VALUES(2,'2019-07-13',0);
INSERT INTO login VALUES(2,'2019-07-14',1);
INSERT INTO login VALUES(2,'2019-07-15',1);
INSERT INTO login VALUES(2,'2019-07-16',0);
INSERT INTO login VALUES(2,'2019-07-17',1);
INSERT INTO login VALUES(2,'2019-07-18',0);
INSERT INTO login VALUES(3,'2019-07-11',1);
INSERT INTO login VALUES(3,'2019-07-12',1);
INSERT INTO login VALUES(3,'2019-07-13',1);
INSERT INTO login VALUES(3,'2019-07-14',1);
INSERT INTO login VALUES(3,'2019-07-15',1);
INSERT INTO login VALUES(3,'2019-07-16',1);
INSERT INTO login VALUES(3,'2019-07-17',1);
INSERT INTO login VALUES(3,'2019-07-18',1);
INSERT INTO login VALUES(3,'2019-07-18',0);
INSERT INTO login VALUES(3,'2019-07-18',1);

3、逻辑SQL
（1）利用ROW_NUMBER() OVER()根据用户分组排序（无并列名次，按顺序排1,2,3,4…）
（2）日期与排序求差dev

mysql> SELECT uid,dt,(dt-ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt)) AS dt_no
    -> FROM login
    -> WHERE login_status=1;
+------+------------+----------+
| uid  | dt         | dt_no    |
+------+------------+----------+
|    1 | 2019-07-11 | 20190710 |
|    1 | 2019-07-12 | 20190710 |
|    1 | 2019-07-13 | 20190710 |
|    1 | 2019-07-14 | 20190710 |
|    1 | 2019-07-15 | 20190710 |
|    1 | 2019-07-16 | 20190710 |
|    1 | 2019-07-17 | 20190710 |
|    1 | 2019-07-18 | 20190710 |
|    2 | 2019-07-11 | 20190710 |
|    2 | 2019-07-12 | 20190710 |
|    2 | 2019-07-14 | 20190711 |
|    2 | 2019-07-15 | 20190711 |
|    2 | 2019-07-17 | 20190712 |
|    3 | 2019-07-11 | 20190710 |
|    3 | 2019-07-12 | 20190710 |
|    3 | 2019-07-13 | 20190710 |
|    3 | 2019-07-14 | 20190710 |
|    3 | 2019-07-15 | 20190710 |
|    3 | 2019-07-16 | 20190710 |
|    3 | 2019-07-17 | 20190710 |
|    3 | 2019-07-18 | 20190710 |
+------+------------+----------+
21 rows in set (0.00 sec)

（3）如果连续登录差值相同，根据差值进行分组计数COUNT()，筛选出COUNT()值>7的数据

mysql> SELECT uid,COUNT(1)
    -> FROM(SELECT uid,dt,(dt-ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt)) AS dt_no
    ->      FROM login
    ->      WHERE login_status=1) t1
    -> GROUP BY uid,dt_no
    -> HAVING COUNT(uid)>=7;
+------+----------+
| uid  | COUNT(1) |
+------+----------+
|    1 |        8 |
|    3 |        8 |
+------+----------+
2 rows in set (0.00 sec)
# 最后计数
mysql> SELECT COUNT(1) AS '连续7天登陆总人数'
    -> FROM (SELECT uid,COUNT(1)
    ->       FROM(SELECT uid,dt,(dt-ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt)) AS dt_no
    ->            FROM login
    ->            WHERE login_status=1) t1
    ->       GROUP BY uid,dt_no
    ->       HAVING COUNT(uid)>=7 ) t2;
+---------------------------+
| 连续7天登陆总人数         |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.00 sec)

二、每个用户连续登陆的最大天数

1、创建表
沿用login表
2、插入数据
沿用login表
3、逻辑SQL
上一题在求出每个用户连续登录的天数之后，使用MAX()，按用户分组，求出最大的登录天数

mysql> SELECT uid,MAX(co)
    -> FROM (SELECT uid,COUNT(1) AS co
    ->       FROM (SELECT uid,dt,dt-ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt) AS dev
    ->    		   FROM login
    ->             WHERE login_status=1) t1
    ->       GROUP BY uid,dev) t2
    -> GROUP BY uid;
+------+---------+
| uid  | max(co) |
+------+---------+
|    1 |       8 |
|    2 |       2 |
|    3 |       8 |
+------+---------+
3 rows in set (0.00 sec)

三、table_a 是一个用户登录时间记录表，当月每次登录一次会记录一条记录，需计算每个用户本月最大连续登录天数

（1）创建表

CREATE TABLE IF NOT EXISTS table_a(
log_time DATETIME,
uid INT
);

（2）插入数据

INSERT INTO table_a VALUES('2018-10-01 18:00:00',123);
INSERT INTO table_a VALUES('2018-10-02 18:00:00',123);
INSERT INTO table_a VALUES('2018-10-02 19:00:00',456);
INSERT INTO table_a VALUES('2018-10-04 18:00:00',123);
INSERT INTO table_a VALUES('2018-10-04 18:00:00',456);
INSERT INTO table_a VALUES('2018-10-05 18:00:00',123);
INSERT INTO table_a VALUES('2018-10-06 18:00:00',123);
INSERT INTO table_a VALUES('2018-11-01 18:00:00',123);
INSERT INTO table_a VALUES('2018-11-02 18:00:00',123);
INSERT INTO table_a VALUES('2018-11-02 19:00:00',456);
INSERT INTO table_a VALUES('2018-11-04 18:00:00',123);
INSERT INTO table_a VALUES('2018-11-04 18:00:00',456);
INSERT INTO table_a VALUES('2018-11-05 18:00:00',456);
INSERT INTO table_a VALUES('2018-11-06 18:00:00',123);

（3）逻辑SQL
与上一题不同之处：按照月份和uid两项进行分组

mysql> SELECT ym,uid,max(co)
    -> FROM (SELECT ym,dev,uid,COUNT(1) AS co
    -> 		 FROM (SELECT EXTRACT(YEAR_MONTH FROM log_time) ym,uid,DATE(log_time)-ROW_NUMBER() OVER(PARTITION BY EXTRACT(YEAR_MONTH FROM log_time),uid ORDER BY log_time) AS dev
    -> 			   FROM table_a) t1
    -> 		 GROUP BY ym,uid,dev) t2
    -> GROUP BY ym,uid;
+--------+------+---------+
| ym     | uid  | max(co) |
+--------+------+---------+
| 201810 |  123 |       3 |
| 201810 |  456 |       1 |
| 201811 |  123 |       2 |
| 201811 |  456 |       2 |
+--------+------+---------+
4 rows in set (0.01 sec)

四、连续三次（及以上）为球队得分的球员名单

两只篮球队进行了激烈的比赛，比分交替上升。比赛结束后，得到一张分数表
该表记录了球队、球员号码、球员姓名、得分分数以及得分时间。求连续三次（及以上）为球队得分的球员名单
1、创建表

CREATE TABLE IF NOT EXISTS ball_score(
team VARCHAR(10),
player_id INT,
player_name VARCHAR(10),
score INT,
datetime1 DATETIME
);

2、插入数据

LOAD DATA LOCAL INFILE 'C:\\Users\\85171\\Desktop\\ball_score.csv' INTO TABLE ball_score fields terminated by ',';

3、逻辑SQL
第一种：
子表增加两列，分别是该球员后面2次得分的球员姓名
如果3个球员姓名相同，说明示同一人连续得分
如下SQL没有考虑连续得分大于3次的情况，如果有，play_name会重复，去重即可。

mysql> SELECT player_name
    -> FROM (SELECT *,LEAD(player_name,1) OVER() AS lead_name1,LEAD(player_name,2) OVER() AS lead_name2
    ->   	 FROM ball_score) t1
    -> WHERE player_name=lead_name1
    -> AND lead_name1=lead_name2;
+-------------+
| player_name |
+-------------+
| A1          |
+-------------+
1 row in set (0.00 sec)

第二种：
子表增加一列id，利用多表连接

mysql> WITH b AS (SELECT *,ROW_NUMBER() OVER(ORDER BY datetime1) AS id FROM ball_score ORDER BY datetime1)
    -> SELECT b1.player_name
    -> FROM b b1 LEFT JOIN b b2 ON b1.id=b2.id+1 LEFT JOIN b b3 ON b2.id=b3.id+1
    -> WHERE b1.player_name=b2.player_name AND b2.player_name=b3.player_name;
+-------------+
| player_name |
+-------------+
| A1          |
+-------------+
1 row in set (0.01 sec)

第三种：
利用ROW_NUMBER() OVER()构造类似于Oracle的伪列，与RANK() OVER()求差值
（类似于前面题目的日期列与rank求差）

mysql> SELECT player_name,dev,COUNT(*)
    -> FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY datetime1) rn,
    ->  	RANK() OVER(PARTITION BY player_name ORDER BY datetime1) rk,   # rn和rk单独拎出来用于观察数据，可以不要
    ->  	(ROW_NUMBER() OVER(ORDER BY datetime1)-RANK() OVER(PARTITION BY player_name ORDER BY datetime1)) dev
    -> 		FROM ball_score
    -> 		ORDER BY datetime1) t1
    -> GROUP BY player_name,dev
    -> HAVING COUNT(*)>=3;
+-------------+-----+----------+
| player_name | dev | COUNT(*) |
+-------------+-----+----------+
| A1          |   9 |        3 |
+-------------+-----+----------+
1 row in set (0.01 sec)

惜木兮

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SQL | 窗口函数 | 连续登录、连续得分

排序函数：ROW_NUMBER() OVER() 按照从上至下的顺序，连续排序，没有并列名次，1,2,3,4…RANK() OVER() 有并列名次，但是名次是不连续的，1,2,2,4…DENSE_RANK() OVER() 有并列名次，名次是连续的，1,2,2,3…一、连续7天登录的总人数1、创建表CREATE TABLE IF NOT EXISTS login(uid INT,dt DATE,login_status INT)ENGINE=InnoDB DEFAULT CHARSET
复制链接

扫一扫