本文详细介绍了SQL中的coalesce函数，以及window函数（包括LEAD,LAG,percent_rank），如何与时间函数（如DATE、TIMESTAMPDIFF、LAST_DAY）、USING用法和文本函数（length,char_length）配合使用。还涵盖了数据多维汇总统计和使用WITHROLLUP进行累积计算的方法。
coalesce函数
SQL——coalesce函数详解_sql coalesce-CSDN博客
窗口函数
窗口函数（三种排名对比）：SQL中的开窗函数(窗口函数)_Mysql_脚本之家
搭配窗口函数使用的函数：
SQL开窗函数之前后函数（LEAD、LAG）_sql lead-CSDN博客
MySQL8中的开窗函数_mysql8开窗函数-CSDN博客
分布类窗口函数percent_rank()：SQL 开窗函数，row_number, dense_rank, percent_rank_开窗函数row number-CSDN博客
using函数
时间函数
1.
2.
零基础自学SQL课程 | SQL中的日期函数大全_sql 轮训日期-CSDN博客：有代码演示结果。
3.timestampdiff：求两个时间的差额，指定时间单位（如秒、分、时、天等）。
TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
表示start_time-submit_time＜5分钟。
4.求月份天数 ——last_day()
https://wenku.csdn.net/answer/4d00d506de514e4daf21aa25cc616bb6
5.求两个时间的分钟差（SQL server使用datediff即可）
MySQL中如何计算时间相差的分钟数_mysql timediff-CSDN博客
文本函数
SQL 文本函数_文本函数sql-CSDN博客：有代码演示结果
SQL基础语法和文本处理常用函数_sql 函数写法-CSDN博客：函数列举较全
文本函数——组连接函数：group_concat、concat_ws
函数参考1：SQL中concat、concat_ws()、group_concat()的使用与区别_MsSql_脚本之家
函数参考2：concat() 、concat_ws()和group_concat()_group_ws用法-CSDN博客
示例（来自牛客网习题）
1）建表语句
drop table if exists examination_info;
CREATE TABLE examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
drop table if exists exam_record;
CREATE TABLE exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'),
(9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'),
(9003, '算法', 'medium', 80, '2020-08-02 10:00:00');
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);2）查询语句
注：
（1）未完成题目数的判断，应该是＜5。
（2）group_concat后的distinct应该去掉，同一天可能做多次同一套题目，无需去重。
SELECT uid,
COUNT(incomplete) AS incomplete_cnt,
COUNT(complete) AS complete_cnt,
GROUP_CONCAT(CONCAT_WS(':', DATE(start_time), tag) ORDER BY DATE(start_time) SEPARATOR ';') AS detail
FROM (
SELECT uid, tag, start_time,
IF(submit_time IS NULL, 1, NULL) AS incomplete,
IF(submit_time IS NULL, NULL, 1) AS complete
FROM exam_record
LEFT JOIN examination_info USING(exam_id)
WHERE YEAR(start_time)=2021
) AS exam_complete_rec
GROUP BY uid
HAVING complete_cnt >= 1 AND incomplete_cnt<5# BETWEEN 2 and 4
ORDER BY incomplete_cnt DESC
3）输出结构为detail
文本函数——length和char_length的区别
MySQL中length()、char_length()的区别_Mysql_脚本之家
数据多维汇总统计
函数参考
记录SQL汇总统计： 在SQL中使用WITH ROLLUP实现数据多维汇总_postgresql 数据库中 类似 mysql 中 with rollup 的用法-CSDN博客
示例 （来自牛客网练习，修改插入记录多加两行）
1）建表语句
drop table if exists practice_record;
CREATE TABLE practice_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
question_id int NOT NULL COMMENT '题目ID',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1003, 8002, '2021-08-01 19:38:01', 80),
(1003, 8001, '2022-08-02 19:48:01', 90),
(1004, 8002, '2022-10-05 19:38:01', 80);2）查询语句
#求年汇总、多年汇总
SELECT
COALESCE(DATE_FORMAT(s.submit_time,"%Y%m"),CONCAT(DATE_FORMAT(s.submit_time,"%Y"),'汇总'),'各年汇总') submit_month,
SUM(s.month_q_cnt) month_q_cnt,#记录数
SUM(DAY(LAST_DAY(s.submit_time))) days_m,#月份天数
SUM(s.month_q_cnt) / SUM(DAY(LAST_DAY(s.submit_time))) avg_day_q_cnt
FROM
(SELECT
#在这一步去重，下一步聚合sum月份天数就不会重复计算
DISTINCT y.year_and_month,COUNT(y.year_and_month) AS month_q_cnt,y.submit_time
FROM
(SELECT DATE_FORMAT(submit_time,'%Y%m') AS year_and_month,submit_time FROM practice_record) `y`
GROUP BY y.year_and_month) s
GROUP BY
DATE_FORMAT(submit_time,"%Y"),DATE_FORMAT(submit_time,"%Y%m") WITH ROLLUP;3）输出结果
求每个类别截止到当月的累计
类似于需要求某个品类的截止到当月累计
示例（来自牛客网） ：请输出每份试卷每月作答数和截止当月的作答总数。
1）建表语句
drop table if exists exam_record;
CREATE TABLE exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1002, 9001, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 89),
(1002, 9001, '2020-02-01 12:11:01', '2020-02-01 12:31:01', 83),
(1003, 9001, '2020-03-01 19:01:01', '2020-03-01 19:30:01', 75),
(1004, 9001, '2020-03-01 12:01:01', '2020-03-01 12:11:01', 60),
(1003, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
(1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1004, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1003, 9002, '2020-02-02 12:01:01', '2020-02-02 12:31:01', 68),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-03-02 12:11:01', null, null);2）查询语句
select#第2步：累计每种试卷截止到当月的作答数
exam_id,start_month,month_cnt,
sum(month_cnt) over(partition by exam_id order by start_month) as acc_month_cnt#直接sum即可累加
from
(select#第1步：获得每种试卷、每个月的作答数
exam_id,
date_format(start_time,'%Y%m') as start_month,
sum(count(date_format(start_time,'%Y%m'))) over(partition by exam_id,date_format(start_time,'%Y%m')) as month_cnt
#as accu_month_cnt
from exam_record
group by exam_id,date_format(start_time,'%Y%m')
) t1
;3）输出结果
660
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
