-- ---------------------- 窗口函数
-- ---------------------- 公用表表达式
-- ---------------------- 窗口函数
函数 OVER([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
/函数 OVER 窗口名 ... WINDOW 窗口名 AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
SELECT
ROW_NUMBER() -- 函数名称
OVER(PARTITION BY category_id ORDER BY price DESC) -- 固定格式 OVER(PARTITION BY .. ORDER BY ...ASC)
AS row_num, -- row_num 多出一列
id, category_id, category, NAME, price, stock -- 正常 筛选
FROM goods;
-- 描述 以 category_id 划分 第一排序 再以 价格 划分第二排序 成为表（基于以上）
-- ROW_NUMBER() 基于以上 多出一列 给 category_id 做 1.2.3.4 序号
-- RANK() 基于以上 多出一列 给 category_id 做 1.2.3.4 序号 的同时
-- category_id price 一样 则 序号 一样 占用序号位 向上兼容 1，2，2，4
-- DENSE_RANK() 基于以上 多出一列 给 category_id 做 1.2.3.4 序号 的同时
-- category_id price 一样 则 序号 一样 不占用序号位 向上兼容1，2，2，3
-- PERCENT_RANK() 基于以上 多出一列 给 category_id 根据 RANK()函数
-- 显示 百分比值 计算公式为 (rank - 1) / (rows - 1) rank = RANK()函数显示的值
-- CUME_DIST() 基于以上 多出一列 给 category_id 比较难理解
-- 查询小于或等于某个值的比例
-- LAG(expr,n)函数 前行的前n行的expr的值 （price）
-- LEAD(expr,n)函数 当前行的后n行的expr的值（price）
-- FIRST_VALUE(expr)函数 返回第一个expr的值
-- LAST_VALUE(expr)函数 最后一个expr的值
-- NTH_VALUE(expr,n)函数 回第n个expr的值
-- NTILE(n)函数 有序数据分为n个桶 ，记录桶编号 （有序重上到下，分成 N 组）6个数据 n=4 112234 / n=5 112345
-- ---------------------- 公用表表达式
-- 范式
WITH CTE名称
AS (子查询) SELECT|DELETE|UPDATE 语句;
-- 定义
WITH emp_dept_id
AS (SELECT DISTINCT department_id FROM employees)
-- 使用
SELECT *
FROM departments d JOIN emp_dept_id e
ON d.department_id = e.department_id;
递归公用表表达式
-- 范式
WITH RECURSIVE
CTE名称 AS (子查询) SELECT|DELETE|UPDATE 语句;
针对于我们常用的employees表，包含employee_id，last_name和manager_id三个字段。如果a是b 的管理者，那么，我们可以把b叫做a的下属，如果同时b又是c的管理者，那么c就是b的下属，是a的下下 属。
下面我们尝试用查询语句列出所有具有下下属身份的人员信息。
WITH RECURSIVE cte
AS
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100 -- 种子查询，找到第一代领导
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte ON (a.manager_id = cte.employee_id) -- 递归查询，找出以递归公用表表达式的人为领导的人 )
SELECT employee_id,last_name FROM cte WHERE n >= 3;
网友评论