SQL CASE表达式与窗口函数

 CASE 表达式是一种通用的条件表达式，类似于其他编程语言中的if/else语句。

窗口函数类似于group by，但是不会改变记录行数，能扫描所有行，能对每一行执行聚合计算或其他复杂计算，并把结果填到每一行中。

1 CASE 表达式

CASE 表达式有简单CASE表达式和搜索CASE表达式两种写法：

-- 简单 CASE 表达式
CASE `status`
    WHEN 1 THEN '正常'
    WHEN 0 THEN '审核中'
    ELSE '锁定'
END

-- 搜索CASE表达式
CASE WHEN `status` = 1 THEN '正常'
     WHEN `status` = 0 THEN '审核中'
     ELSE '锁定'
END  

  1.1 在SELECT 与 GROUP BY 中同时使用

图 城市人口信息表t_city_info

需求：根据上表，统计对应省份的人口数。

图 统计出的对应人口数

SELECT
CASE city 
WHEN '九江' THEN '江西'
WHEN '赣州' THEN '江西' 
WHEN '南昌' THEN '江西'
WHEN '深圳' THEN '广东'
WHEN '广州' THEN '广东'
WHEN '韶关' THEN '广东'
WHEN '惠州' THEN '广东'
ELSE '其他' 
END AS '省份',
SUM(population) AS '人口'
FROM t_city_info
GROUP BY
(
CASE city 
WHEN '九江' THEN '江西'
WHEN '赣州' THEN '江西' 
WHEN '南昌' THEN '江西'
WHEN '深圳' THEN '广东'
WHEN '广州' THEN '广东'
WHEN '韶关' THEN '广东'
WHEN '惠州' THEN '广东'
ELSE '其他' 
END 
)

1.2 在聚合函数内使用CASE表达式

图 表城市男女人口数量表t_city_info,1 表示男性 0 表示女性

 需求，根据上表统计出各市男女数量，输出格式如下：

图 各市男女数量

SELECT city as '城市',
SUM(
CASE sex 
WHEN 1 THEN population
ELSE 0
END
) AS '男性',
SUM(
CASE sex 
WHEN 0 THEN population
ELSE 0
END
) AS '女性'
FROM t_city_info
GROUP BY city

1.3 在update里使用CASE

图 员工薪资信息表t_emplpyee及薪资调整

需求：工资25000以上的降薪10%，10000以下的涨薪2000。

UPDATE t_employee 
SET salary = 
CASE 
	 WHEN salary > 25000 THEN salary * 0.9
	 WHEN salary < 10000 THEN salary + 2000
	 ELSE salary 
END

1.4 在CASE里使用嵌套子查询

图 课程信息表t_course_info 与 开课情况t_course_open表

需求：统计各课程每月开课情况。

图 各课程每月开课情况

SELECT 
`name` AS '课程',
(
	CASE 
		 WHEN EXISTS  
				(SELECT `course_id`
				 FROM t_course_open
				 WHERE `month` = '202408'	AND course_id = id 
				) 
		 THEN 'YES'
		 ELSE 'no'
	END	 
) AS '8月',
(
	CASE 
		 WHEN EXISTS
				(SELECT `course_id`
				 FROM t_course_open
				 WHERE `month` = '202409'	AND course_id = id 
				) 
		 THEN 'YES'
		 ELSE 'no'
	END	 
) AS '9月',
(
	CASE 
		 WHEN EXISTS 
				(SELECT `course_id`
				 FROM t_course_open
				 WHERE `month` = '202410'	AND course_id = id 
				) 
		 THEN 'YES'
		 ELSE 'no'
	END	 
) AS '10月'
FROM t_course_info

1.5 在CASE中使用聚合函数

图 学生加入俱乐部情况t_student_club 表

学生加入俱乐部情况：1）1个学生可以加入多个俱乐部；2）如果加入了多个俱乐部，Y标志主俱乐部，只加入一个俱乐部标注N。

需求：1）列出学生参加的主俱乐部。2）如果学生只假如一个俱乐部，则也为主俱乐部。

SELECT student_id AS '学生',
CASE 
	WHEN COUNT(*) = 1 THEN club_name
	ELSE MAX(CASE WHEN main_flg = 'Y' THEN club_name ELSE NULL END) 
END AS '主俱乐部'
FROM t_student_club
GROUP BY student_id

2 窗口函数

窗口函数和聚合函数共同点在于它们也是对一组数据进行分析。但是窗口函数不是将一组数据汇总为单个结果，而是针对查询中的每一行数据，基于和它相关的一组数据计算出一个结果。语法如下：

window_fun (expr) over (

     partition by …

order by …

frame_clause

)

PARTITION BY 子句分隔记录集合，类似于group by

ORDER BY 子句对记录排序

frame_clause 串口大小，帧子句，定义以当前记录为中心的子集。

2.1 匿名窗口与命名窗口

图 服务器每日请求量t_service_load 表

需求：列出服务器每日请求量、前两日请求评价数。

SELECT `date`,`load`,
AVG(`load`) OVER (
	ORDER BY `date`
	ROWS 
	BETWEEN 2 PRECEDING AND 1 PRECEDING
) AS avgLoad
FROM t_service_load;

2.1.1 命名窗口

SELECT `date`,`load`,
AVG(`load`) OVER loadW AS avgLoad,
SUM(`load`) OVER loadW AS sumLoad
FROM t_service_load

WINDOW loadW AS (
	ORDER BY `date`
	ROWS 
	BETWEEN 2 PRECEDING AND 1 PRECEDING
)

2.2 帧子句

表 帧子句中可以使用的选项

需求：统计上表中，两日前的请求量。

SELECT `date`,`load`,
MAX(`load`) OVER (
	ORDER BY `date` ASC 
	RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY PRECEDING
) AS preLoad
FROM t_service_load;