Java基础(23) | SQL 进阶语法：常用函数、CTE 与窗口函数

📚 本系列系统梳理了 Java 开发的详细知识点，从基础语法到工程实践层层递进，内容详实成体系，建议先收藏再慢慢阅读，方便日后随时回顾查阅。

前言

后端开发不只是写 Java——日常排查问题、给数据团队对账、写报表统计，都离不开 SQL。尤其是广告系统里"统计某个广告主最近 30 天的曝光转化率""按渠道排名找出 Top 10 投放计划"这类需求，光靠SELECT * FROM ... WHERE ...是不够的。这篇文章整理 MySQL 里常用的函数、CTE（公共表表达式）和窗口函数——后者是写统计类 SQL 最关键的工具。

1. 常用函数

几个容易用错或者用法不直观的，单独说明一下：

TIMEDIFF vs DATEDIFF

DATEDIFF(date1, date2)只算天数差，忽略时间部分；TIMEDIFF(time1, time2)算的是时间差（格式HH:MM:SS），两个参数必须是同类型（都是 time 或都是 datetime）：

SELECTDATEDIFF('2026-05-20','2026-05-18');-- 2（天）SELECTTIMEDIFF('2026-05-20 10:00:00','2026-05-20 08:30:00');-- 01:30:00

DATE_FORMAT：格式化占位符

SELECTDATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');-- 2026-05-20 14:30:00SELECTDATE_FORMAT(NOW(),'%Y年%m月');-- 2026年05月

STR_TO_DATE是反过来的操作——把字符串按指定格式解析成日期类型，常用于处理"日期被当成字符串存进表"的历史数据：

SELECTSTR_TO_DATE('2026/05/20','%Y/%m/%d');-- 2026-05-20

CONCAT vs CONCAT_WS

CONCAT直接拼接，任意一个参数是NULL，整个结果就是NULL；CONCAT_WS（WS = With Separator）会自动忽略NULL参数，并在非空参数之间插入分隔符：

SELECTCONCAT('a',NULL,'b');-- NULL（整体失效）SELECTCONCAT_WS('-','a',NULL,'b');-- a-b（自动跳过 NULL）

COALESCE：处理 NULL 的默认值

返回参数列表中第一个非NULL的值，常用于"给可能为空的字段一个默认值"：

-- nickname 为空时显示 username，username 也为空时显示 "匿名用户"SELECTCOALESCE(nickname,username,'匿名用户')ASdisplay_nameFROMusers;

CAST：类型转换

SELECTCAST('123'ASSIGNED);-- 123（字符串转整数）SELECTCAST(3.789ASDECIMAL(5,2));-- 3.79（保留 2 位小数）SELECTCAST('2026-05-20'ASDATE);-- 2026-05-20（字符串转日期）

DATE_ADD / DATE_SUB：日期加减

SELECTDATE_ADD('2026-05-20',INTERVAL7DAY);-- 2026-05-27SELECTDATE_SUB('2026-05-20',INTERVAL1MONTH);-- 2026-04-20SELECTDATE_ADD(NOW(),INTERVAL-30DAY);-- 30 天前（负数 = SUB）

2. CTE：用 WITH 语句拆分查询

复杂查询如果写成一层套一层的子查询，会很难读、很难调试。CTE（Common Table Expression，公共表表达式）用WITH ... AS (...)把每一步查询命名成一个临时结果集，后面的查询可以直接引用它，就像引用一张表：

WITHresultAS(SELECTMAX(DATE(start_time))ASmax_dateFROMtb_user_video_log),join_tableAS(SELECTtag,if_retweetFROMtb_user_video_logAStINNERJOINtb_video_infoASiONt.video_id=i.video_id,resultWHEREDATEDIFF(result.max_date,start_time)<30)SELECTtag,SUM(if_retweet)ASretweet_cnt,ROUND(SUM(if_retweet)/COUNT(*),3)ASretweet_rateFROMjoin_tableGROUPBYtagORDERBYretweet_rateDESC;

逐步拆解这个例子：

1. result：先算出整张日志表里最新的一天max_date——后面"最近 30 天"都以这一天为基准，而不是NOW()（因为测试数据可能不是今天的）。
2. join_table：把日志表和视频信息表 join 起来，拿到每条记录的tag（视频标签）和if_retweet（是否转发）。这里FROM ... , result用逗号把单行的result也接进FROM列表——因为result只有一行一列，这相当于把max_date这个值"广播"到每一行，方便在WHERE里用DATEDIFF(result.max_date, start_time) < 30筛选"最近 30 天"的数据。
3. 最外层查询：基于join_table，按tag分组，统计每个标签的转发数retweet_cnt和转发率retweet_rate。

好处：每个 CTE 都可以单独拿出来跑，看中间结果对不对——比如先单独执行result，确认max_date算对了，再执行join_table，逐步排查，比一整条嵌套子查询好调试得多。

3. CASE 与 IF

CASE：多分支条件

CASEif_followWHEN2THEN-1ELSEif_followEND

等价于"如果if_follow等于 2，结果为 -1，否则结果就是if_follow本身"。CASE还有不带表达式、直接写条件的形式：

CASEWHENscore>=90THEN'优秀'WHENscore>=60THEN'及格'ELSE'不及格'ENDASlevel

IF：单分支条件（MySQL 专有函数）

IF(condition,true_value,false_value)

IF是CASE WHEN ... THEN ... ELSE ... END的简写，只能写一个条件，适合简单的二选一场景：

SELECTIF(score>=60,'及格','不及格')ASresultFROMexams;

4. 窗口函数

参考：通俗易懂的学会：SQL窗口函数

GROUP BY分组后，每组只能输出一行——明细数据没了。但很多统计需求是"既要看每一行的明细，又要算出这一行在所在分组里排第几名 / 和上一行比变化了多少"。窗口函数就是为这类需求设计的：它不会减少行数，而是在每一行旁边多算一列"基于某个窗口范围的统计值"。

<窗口函数>OVER(PARTITIONBY<用于分组的列名>ORDERBY<用于排序的列名><ROWS/RANGE 子句>)

• PARTITION BY：把数据分成多个"窗口"（类似GROUP BY，但不会合并行）
• ORDER BY：决定窗口内行的顺序（影响排名、LAG/LEAD 等函数的结果）
• ROWS/RANGE子句：进一步限定"当前窗口"包含哪些行（默认是整个分区）

窗口函数原则上只能写在SELECT子句中。

4.1 专用窗口函数：排名类

SELECTname,score,ROW_NUMBER()OVER(ORDERBYscoreDESC)ASrow_num,RANK()OVER(ORDERBYscoreDESC)ASrnk,DENSE_RANK()OVER(ORDERBYscoreDESC)ASdense_rnkFROMexams;

4.2 前后行函数：LAG / LEAD

用于在当前行直接拿到"上一行"或"下一行"的值，常用来计算"环比"（比如本月销量 - 上月销量）：

SELECTproduct,month,sales,LAG(sales,1,0)OVER(PARTITIONBYproductORDERBYmonth)ASlast_month_sales,sales-LAG(sales,1,0)OVER(PARTITIONBYproductORDERBYmonth)ASdiffFROMmonthly_sales;

• offset是"往前/往后数几行"，最常用的是1（上一行/下一行）
• default是"如果取不到（比如第一行没有上一行）该返回什么"，这里传0表示第一个月的环比基准是 0

4.3 首尾值函数

SELECTname,dept,salary,FIRST_VALUE(salary)OVER(PARTITIONBYdeptORDERBYhire_date)ASfirst_hired_salary,NTH_VALUE(salary,3)OVER(PARTITIONBYdeptORDERBYsalaryDESC)ASthird_highest_salaryFROMemployees;

LAST_VALUE的坑：默认的窗口框架是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从分区第一行到当前行），所以LAST_VALUE默认取到的其实是"当前行"，不是整个分区的最后一行——想拿到分区真正的最后一行，必须显式指定ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（见 4.5 节）。

4.4 聚合窗口函数

SUM/AVG/COUNT/MIN/MAX加上OVER()就变成窗口函数——区别于GROUP BY里的聚合函数，它不会把多行合并成一行：

SELECTname,dept,salary,SUM(salary)OVER(PARTITIONBYdept)ASdept_total,AVG(salary)OVER(PARTITIONBYdept)ASdept_avg,salary/SUM(salary)OVER(PARTITIONBYdept)ASsalary_ratioFROMemployees;

每一行都能看到自己的明细（name、salary），同时又能看到所在部门的总和、平均值，以及自己占部门总额的比例——这是GROUP BY做不到的。

4.5 窗口框架规范：ROWS BETWEEN

ROWS BETWEEN ... AND ...进一步限定"当前窗口具体包含哪些行"：

例 1：累计求和（从分区第一行累加到当前行）

SELECTid,category,sales_volume,SUM(sales_volume)OVER(PARTITIONBYcategoryORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)ASrunning_totalFROMsales;

例 2：移动平均（前一行 + 当前行 + 后一行，共 3 行的平均值）

SELECTid,category,sales_volume,AVG(sales_volume)OVER(PARTITIONBYcategoryORDERBYidROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASmoving_avgFROMsales;

4.6 为什么有时必须用窗口函数代替 GROUP BY

下面这条 SQL 想算"每个商品的利润率"——profit_price = 1 - 成本价 / 加权平均售价：

-- 报错：in_price 既不在 GROUP BY 里，也没被聚合函数包裹product_profitAS(SELECTproduct_id,1-in_price/(SUM(price*cnt)/SUM(cnt))ASprofit_priceFROMjoin_tbGROUPBYproduct_id)

为什么报错：SQL 规定SELECT里出现的每一列，要么在GROUP BY里，要么被聚合函数（SUM/MAX/…）包裹。这里in_price两者都不满足——虽然同一个product_id对应的in_price实际上是同一个值（成本价不会按行变化），但数据库不知道这件事，仍然会报错。

解决方案：把聚合从"按组合并"换成"窗口聚合"，这样既能用到SUM(...) OVER(PARTITION BY product_id)算出整个分组的汇总值，又能保留in_price这种逐行字段，不需要把它也塞进GROUP BY：

product_profitAS(SELECTDISTINCTproduct_id,1-(in_price/(SUM(price*cnt)OVER(PARTITIONBYproduct_id)/SUM(cnt)OVER(PARTITIONBYproduct_id)))ASprofit_priceFROMjoin_tb)

SUM(...) OVER(PARTITION BY product_id)会把"该product_id分组内price*cnt的总和"算出来，贴在每一行上（不合并行），所以in_price这种逐行字段可以直接拿来用。算完之后每个product_id会有多行结果（一行对应原表的一行），值都一样，最后用SELECT DISTINCT去重成一个商品一行。

5. 小结

下一篇预告：MySQL 原理与优化——存储引擎、索引、锁与事务隔离级别

🎯 如果这篇文章对你有帮助，别忘了点赞、收藏、关注三连！关注我，让你在 Java 学习的道路上不迷路，持续为你带来成体系的 Java 干货~