MySQL高级进阶

MySQL高级进阶

---

(1)MySQL的开窗函数

1. ROW_NUMBER:从 1 开始，按照顺序生成组内的记录编号；
2. RANK:从 1 开始，按照顺序，相同会重复，名次会留下空的位置，生成组内的记录编号；
3. DENSE_RANK:从 1 开始，按照顺序生成组内的记录编号，相同会重复，名次不会留下空的位置；

(2)语法结构

• 函数 over (partition by XXX order by XXX)
• over():以谁进行开窗【table】
• partition by: 以谁进行分组【group by column】
• order by: 以谁进行排序【column】

开窗函数区别如下图所示：

(3)内置窗口函数

1. FIRST_VALUE:取窗口第一个值
2. LAST_VALUE:取窗口最后一个值

==注意：==
这里也是一个动态的当第n行时，如果不做特殊指定，当到达第n行时，每组的窗口范围是
从第1行到第n行，因此第一组的窗口的FIRST_VALUE都是第1行的值，LAST_VALUE都是当前行的值

MySQL中的三种条件判断函数

(1)IF()

IF ( EXPR , V1 , V2 )

执行逻辑：若 expr 为 ture 则 V1 否则 V2

(2)IFNULL()

IFNULL(字段，0)

执行逻辑：如果字段为 null 的话，就用 0 进行填充

(3)CASE WHER :类似于java中的if...else...函数

-- 如果字段值等于预期值，则返回结果 1 ，否则返回结果2

CASE 字段 WHEN 预期值 THEN 1 ELSE 2 END

注意

CASE WHEN 后面可以借多个 when 进行更加复杂的条件判断

(4)sum() over(order by date_time rows 6 preceding

求从 date_time 开始往前推 6 天的总和；

题目：求80，和90 后信用卡余额

两张表如下

代码如下：

SELECT
	k.NIAN_LING AS '年龄段',
	k.XYKYE AS '信用卡余额' 
FROM
	(
	SELECT
	CASE
			
		WHEN
			z.y >= '1980' 
			AND z.y < '1990' THEN '80 后' WHEN z.y >= '1990' 
				AND z.y < '2000' THEN
					'90 后' 
					END AS NIAN_LING,
				sum( z.CREDIT ) AS XYKYE 
			FROM
				(
				SELECT YEAR
					( a.BIRTH ) AS y,
					b.CREDIT 
				FROM
					TableA a,
					TableB b 
				WHERE
					a.NO = b.NO 
				) AS z 
			GROUP BY
				NIAN_LING 
			) AS k

结果如下：

题目：每篇文章在同一时刻的最大观看人数

一直以来，都觉得这种球最大的同时在线/阅读人数很难，但是在参考大家的思路后，感觉很清晰，一下子就通透了。具体的步骤是：
①将用户的进入时间单独拎出来，同时记为1；离开时间单独拎出来，同时记为-1，这样就聚合这两个表，按照时间排序，意思就是：进去一个加1，离开一个减1。
②然后利用窗口函数对计数(1或者-1)求累计和，因为题目规定：同一时间有就有出的话先算进来的后算出去的，所以排序的时候就要看好了先按时间排序，再按计数排序！
③然后再在每个分组里面去求最大的累积和就是最多同时在线的人数了！