一、每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
1、创建表
CREATE TABLE IF NOT EXISTS visits(
userid VARCHAR(10),
datetime1 DATETIME,
visits INT)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、插入数据
INSERT INTO visits VALUES('A','2021-01-03 14:59:43',5);
INSERT INTO visits VALUES('A','2021-01-03 14:59:43',15);
INSERT INTO visits VALUES('B','2021-01-03 14:59:43',5);
INSERT INTO visits VALUES('A','2021-01-03 14:59:43',8);
INSERT INTO visits VALUES('B','2021-01-03 14:59:43',25);
INSERT INTO visits VALUES('A','2021-01-03 14:59:43',5);
INSERT INTO visits VALUES('A','2021-02-03 14:59:43',4);
INSERT INTO visits VALUES('A','2021-02-03 14:59:43',6);
INSERT INTO visits VALUES('B','2021-02-03 14:59:43',10);
INSERT INTO visits VALUES('B','2021-02-03 14:59:43',5);
INSERT INTO visits VALUES('A',NOW(),16);
INSERT INTO visits VALUES('A',NOW(),22);
INSERT INTO visits VALUES('B',NOW(),23);
INSERT INTO visits VALUES('B',NOW(),10);
INSERT INTO visits VALUES('B',NOW(),1);
3、逻辑SQL
（1）求每个用户每月的访问次数
首先从datetime中取出年月
EXTRACT(YEAR_MONTH FROM datetime1)
,
根据用户和月份分组，求出每个用户、每月总访问次数
mysql> SELECT userid,month, SUM(visits) sum_vistits
-> FROM (SELECT userid,EXTRACT(YEAR_MONTH FROM datetime1) AS month ,visits
-> FROM visits) a
-> GROUP BY userid,month;
+--------+--------+-------------+
| userid | month | sum_vistits |
+--------+--------+-------------+
| A | 202101 | 33 |
| B | 202101 | 30 |
| A | 202102 | 10 |
| B | 202102 | 15 |
| A | 202104 | 38 |
| B | 202104 | 34 |
+--------+--------+-------------+
6 rows in set (0.01 sec)
（2）利用窗口函数MAX() OVER()、SUM() OVER()，求截止该月最大和累计访问量
mysql> SELECT userid,month,sum_vistits AS 'user当月访问次数',MAX(sum_vistits) OVER(PARTITION BY userid ORDER BY month) AS 'user截止当月最大访问次数' ,SUM(sum_vistits) OVER(PARTITION BY userid ORDER BY month) AS 'user累计访问次数'
-> FROM (SELECT userid,month, SUM(visits) sum_vistits
-> FROM (SELECT userid,EXTRACT(YEAR_MONTH FROM datetime1) AS month ,visits
-> FROM visits) a
-> GROUP BY userid,month) A;
+--------+--------+----------------+------------------------+------------------+
| userid | month | user当月访问次数 | user截止当月最大访问次数 | user累计访问次数 |
+--------+--------+----------------+-------------------------+-----------------+
| A | 202101 | 33 | 33 | 33 |
| A | 202102 | 10 | 33 | 43 |
| A | 202104 | 38 | 38 | 81 |
| B | 202101 | 30 | 30 | 30 |
| B | 202102 | 15 | 30 | 45 |
| B | 202104 | 34 | 34 | 79 |
+--------+--------+----------------+-------------------------+------------------+
6 rows in set (0.00 sec)
二、求出每个栏目的被观看次数及累计观看时长
1、创建表
CREATE TABLE IF NOT EXISTS vedio(
uid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
channl VARCHAR(20),
min INT
)AUTO_INCREMENT=1,
ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、插入数据
INSERT INTO vedio (channl,min) VALUES ('1',23);
INSERT INTO vedio (channl,min) VALUES ('1',12);
INSERT INTO vedio (channl,min) VALUES ('1',12);
INSERT INTO vedio (channl,min) VALUES ('1',32);
INSERT INTO vedio (channl,min) VALUES ('1',342);
INSERT INTO vedio (channl,min) VALUES ('2',13);
INSERT INTO vedio (channl,min) VALUES ('2',34);
INSERT INTO vedio (channl,min) VALUES ('2',13);
INSERT INTO vedio (channl,min) VALUES ('2',134);
3、逻辑SQL
mysql> SELECT channl,COUNT(1) AS '观看次数' ,SUM(min) AS '累计观看时长'
-> FROM vedio
-> GROUP BY channl;
+--------+--------------+--------------------+
| channl | 观看次数 | 累计观看时长 |
+--------+--------------+--------------------+
| 1 | 5 | 421 |
| 2 | 4 | 194 |
+--------+--------------+--------------------+
2 rows in set (0.00 sec)
三、每个店铺的当月销售额和累计到当月的总销售额
1、创建表
CREATE TABLE IF NOT EXISTS t1_v(
store VARCHAR(10),
month VARCHAR(10),
sales_amount FLOAT
);
2、插入数据
INSERT INTO t1_v VALUES('a',01,150);
INSERT INTO t1_v VALUES('a',01,200);
INSERT INTO t1_v VALUES('b',01,1000);
INSERT INTO t1_v VALUES('b',01,800);
INSERT INTO t1_v VALUES('c',01,250);
INSERT INTO t1_v VALUES('c',01,220);
INSERT INTO t1_v VALUES('b',01,6000);
INSERT INTO t1_v VALUES('a',02,2000);
INSERT INTO t1_v VALUES('a',02,3000);
INSERT INTO t1_v VALUES('b',02,1000);
INSERT INTO t1_v VALUES('b',02,1500);
INSERT INTO t1_v VALUES('c',02,350);
INSERT INTO t1_v VALUES('c',02,280);
INSERT INTO t1_v VALUES('a',03,350);
INSERT INTO t1_v VALUES('a',03,250);
3、逻辑SQL
（1）求出每个店铺每月的销售额
SELECT store,month,sum(sales_amount) sum
FROM t1_v
GROUP BY store,month；
（2）利用窗口函数SUM() OVER()求累计当月的销售额
mysql> SELECT store,month,sum AS '当月销售额' ,SUM(sum) OVER(PARTITION BY store ORDER BY month) as '累积销售额'
-> FROM (SELECT store,month,sum(sales_amount) sum
-> FROM t1_v
-> GROUP BY store,month) t1;
+-------+-------+-----------------+-----------------+
| store | month | 当月销售额 | 累积销售额 |
+-------+-------+-----------------+-----------------+
| a | 1 | 350 | 350 |
| a | 2 | 5000 | 5350 |
| a | 3 | 600 | 5950 |
| b | 1 | 7800 | 7800 |
| b | 2 | 2500 | 10300 |
| c | 1 | 470 | 470 |
| c | 2 | 630 | 1100 |
+-------+-------+-----------------+-----------------+
7 rows in set (0.01 sec)
四、两只篮球队进行了激烈的比赛，比分交替上升。比赛结束后，得到一张分数表，该表记录了球队、球员号码、球员姓名、得分分数以及得分时间。现在球队要对比赛中表现突出的球员做出奖励。统计比赛中帮助各自球队反超比分的球员姓名以及对应时间
列：球队、球员号码、球员姓名、得分分数、得分时间
team、player_id、player_name、score、datetime1
1、创建表
CREATE TABLE IF NOT EXISTS ball_score(
team VARCHAR(10),
player_id INT,
player_name VARCHAR(10),
score INT,
datetime1 DATETIME
);
2、插入数据
INSERT INTO ball_score VALUES('A',1,'A1',1,'2021/03/02 10:01:00');
INSERT INTO ball_score VALUES('A',5,'A5',1,'2021/03/02 10:02:00');
INSERT INTO ball_score VALUES('B',4,'B4',3,'2021/03/02 10:03:00');
INSERT INTO ball_score VALUES('A',4,'A4',3,'2021/03/02 10:04:00');
INSERT INTO ball_score VALUES('B',1,'B1',3,'2021/03/02 10:05:00');
INSERT INTO ball_score VALUES('A',3,'A3',3,'2021/03/02 10:06:00');
INSERT INTO ball_score VALUES('A',4,'A4',3,'2021/03/02 10:07:00');
INSERT INTO ball_score VALUES('B',1,'B1',2,'2021/03/02 10:08:00');
INSERT INTO ball_score VALUES('B',2,'B2',2,'2021/03/02 10:09:00');
3、逻辑SQL
刚开始逻辑错误，写了如下SQL，此SQL仅是筛选本场累计分数高的队，但不一定是反超，很可能之前分数就已经高了
SELECT player_name,datetime1
FROM (SELECT *,LAG(team) OVER() AS lag_team,LAG(sum) OVER() AS lag_sum
FROM (SELECT *,SUM(score) OVER(PARTITION BY team ORDER BY datetime1) AS sum
FROM ball_score
ORDER BY datetime1) t1) t2
WHERE team<>lag_team
AND sum>lag_sum;
调整后的思路：
（1）按照时间，分别求出AB队的累积得分，如果A队得分B对记为0，B队得分A队记为0
mysql> SELECT *,
-> (CASE WHEN team='A' THEN score ELSE 0 END) AS 'a_score',
-> (CASE WHEN team='B' THEN score ELSE 0 END) AS 'b_score'
-> FROM ball_score;
+------+-----------+-------------+-------+---------------------+---------+---------+
| team | player_id | player_name | score | datetime1 | a_score | b_score |
+------+-----------+-------------+-------+---------------------+---------+---------+
| A | 1 | A1 | 1 | 2021-03-02 10:01:00 | 1 | 0 |
| A | 5 | A5 | 1 | 2021-03-02 10:02:00 | 1 | 0 |
| B | 4 | B4 | 3 | 2021-03-02 10:03:00 | 0 | 3 |
| A | 4 | A4 | 3 | 2021-03-02 10:04:00 | 3 | 0 |
| B | 1 | B1 | 3 | 2021-03-02 10:05:00 | 0 | 3 |
| A | 3 | A3 | 3 | 2021-03-02 10:06:00 | 3 | 0 |
| A | 4 | A4 | 3 | 2021-03-02 10:07:00 | 3 | 0 |
| B | 1 | B1 | 2 | 2021-03-02 10:08:00 | 0 | 2 |
| B | 2 | B2 | 2 | 2021-03-02 10:09:00 | 0 | 2 |
+------+-----------+-------------+-------+---------------------+---------+---------+
9 rows in set (0.00 sec)
（2）在上表基础上求出截止每个时间点的累计得分
此处注意SUM() OVER()中不能有
PARTITION BY team
，因此处是按时间累积求和，不用按组分
否则，当B队得分时，A队的累计分数为0，不是我们想要的结果。
mysql> SELECT *,SUM(a_score) OVER(ORDER BY datetime1) AS a_sum_score,SUM(b_score) OVER(ORDER BY datetime1) AS b_sum_score
-> FROM (SELECT *,
-> (CASE WHEN team='A' THEN score ELSE 0 END) AS 'a_score',
-> (CASE WHEN team='B' THEN score ELSE 0 END) AS 'b_score'
-> FROM ball_score) t1
-> ORDER BY datetime1;
+------+-----------+-------------+-------+---------------------+---------+---------+-------------+-------------+
| team | player_id | player_name | score | datetime1 | a_score | b_score | a_sum_score | b_sum_score |
+------+-----------+-------------+-------+---------------------+---------+---------+-------------+-------------+
| A | 1 | A1 | 1 | 2021-03-02 10:01:00 | 1 | 0 | 1 | 0 |
| A | 5 | A5 | 1 | 2021-03-02 10:02:00 | 1 | 0 | 2 | 0 |
| B | 4 | B4 | 3 | 2021-03-02 10:03:00 | 0 | 3 | 2 | 3 |
| A | 4 | A4 | 3 | 2021-03-02 10:04:00 | 3 | 0 | 5 | 3 |
| B | 1 | B1 | 3 | 2021-03-02 10:05:00 | 0 | 3 | 5 | 6 |
| A | 3 | A3 | 3 | 2021-03-02 10:06:00 | 3 | 0 | 8 | 6 |
| A | 4 | A4 | 3 | 2021-03-02 10:07:00 | 3 | 0 | 11 | 6 |
| B | 1 | B1 | 2 | 2021-03-02 10:08:00 | 0 | 2 | 11 | 8 |
| B | 2 | B2 | 2 | 2021-03-02 10:09:00 | 0 | 2 | 11 | 10 |
+------+-----------+-------------+-------+---------------------+---------+---------+-------------+-------------+
9 rows in set (0.00 sec)
（3）反超有两种定义情况
(a)截止上场累计得分A队>B队，截止本场累计得分A队<B队，算B队反超
截止上场累计得分A队<B队，截止本场累计得分A队>B队，算A队反超
以上本场与上场差值必定正负不同，相乘小于0
(b)截止上上场累计得分A队<B队,上场累计得分A队=B队，截止本场累计得分A队>B队，算A队反超
截止上上场累计得分A队>B队,上场累计得分A队=B队，截止本场累计得分A队<B队，算B队反超
以上本场与上场差值为0，上上场与本场差值正负不同，相乘小于0
商标基础上在增加三列，A队B队本场差值，及上场差值，上上场差值
mysql> SELECT *,(a_sum_score-b_sum_score) AS sum_dev,LAG(a_sum_score-b_sum_score,1) OVER() AS sum_dev1,LAG(a_sum_score-b_sum_score,2) OVER() AS sum_dev2
-> FROM (SELECT *,SUM(a_score) OVER(ORDER BY datetime1) AS a_sum_score,SUM(b_score) OVER(ORDER BY datetime1) AS b_sum_score
-> FROM (SELECT *,
-> (CASE WHEN team='A' THEN score ELSE 0 END) AS 'a_score',
-> (CASE WHEN team='B' THEN score ELSE 0 END) AS 'b_score'
-> FROM ball_score) t1
-> ORDER BY datetime1) t2;
+------+-----------+-------------+-------+---------------------+---------+---------+-------------+-------------+---------+----------+----------+
| team | player_id | player_name | score | datetime1 | a_score | b_score | a_sum_score | b_sum_score | sum_dev | sum_dev1 | sum_dev2 |
+------+-----------+-------------+-------+---------------------+---------+---------+-------------+-------------+---------+----------+----------+
| A | 1 | A1 | 1 | 2021-03-02 10:01:00 | 1 | 0 | 1 | 0 | 1 | NULL | NULL |
| A | 5 | A5 | 1 | 2021-03-02 10:02:00 | 1 | 0 | 2 | 0 | 2 | 1 | NULL |
| B | 4 | B4 | 3 | 2021-03-02 10:03:00 | 0 | 3 | 2 | 3 | -1 | 2 | 1 |
| A | 4 | A4 | 3 | 2021-03-02 10:04:00 | 3 | 0 | 5 | 3 | 2 | -1 | 2 |
| B | 1 | B1 | 3 | 2021-03-02 10:05:00 | 0 | 3 | 5 | 6 | -1 | 2 | -1 |
| A | 3 | A3 | 3 | 2021-03-02 10:06:00 | 3 | 0 | 8 | 6 | 2 | -1 | 2 |
| A | 4 | A4 | 3 | 2021-03-02 10:07:00 | 3 | 0 | 11 | 6 | 5 | 2 | -1 |
| B | 1 | B1 | 2 | 2021-03-02 10:08:00 | 0 | 2 | 11 | 8 | 3 | 5 | 2 |
| B | 2 | B2 | 2 | 2021-03-02 10:09:00 | 0 | 2 | 11 | 10 | 1 | 3 | 5 |
+------+-----------+-------------+-------+---------------------+---------+---------+-------------+-------------+---------+----------+----------+
9 rows in set (0.00 sec)
添加WHERE条件，取出球员姓名和得分时间
mysql> SELECT player_name AS '球员姓名',datetime1 AS '得分时间'
-> FROM (SELECT *,(a_sum_score-b_sum_score) AS sum_dev,LAG(a_sum_score-b_sum_score,1) OVER() AS sum_dev1,LAG(a_sum_score-b_sum_score,2) OVER() AS sum_dev2
-> FROM (SELECT *,SUM(a_score) OVER(ORDER BY datetime1) AS a_sum_score,SUM(b_score) OVER(ORDER BY datetime1) AS b_sum_score
-> FROM (SELECT *,
-> (CASE WHEN team='A' THEN score ELSE 0 END) AS 'a_score',
-> (CASE WHEN team='B' THEN score ELSE 0 END) AS 'b_score'
-> FROM ball_score) t1
-> ORDER BY datetime1) t2)t3
-> WHERE sum_dev*sum_dev1<0
-> OR (sum_dev*sum_dev1=0 AND sum_dev*sum_dev2<0);
+--------------+---------------------+
| 球员姓名 | 得分时间 |
+--------------+---------------------+
| B4 | 2021-03-02 10:03:00 |
| A4 | 2021-03-02 10:04:00 |
| B1 | 2021-03-02 10:05:00 |
| A3 | 2021-03-02 10:06:00 |
+--------------+---------------------+
4 rows in set (0.00 sec)
版权声明：本文为weixin_46023346原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。