1 小时 SQL 极速入门
前面两篇我们从 SQL 的最基础语法讲起, 到表联结多表查询.
大家可以点击链接查看
1 小时 SQL 极速入门 (一)
1 小时 SQL 极速入门 (二)
今天我们讲一些在做报表和复杂计算时非常实用的分析函数. 由于各个数据库函数的实现不太一样, 本文基于 Oracle 12c .
ROW_NUMBER() 函数
这个函数在平时用的还是比较多的. 这个函数的作用是为分组内的每一行返回一个行号. 我们还是举例来说明.
假设我们有以下数据表:
共 8 个订单, 分为 A,B,C,D 四种类型, 后面两列是订单描述和订单数量.
假如我们现在想找到每个订单类型中数量最少的一行记录, 比如想找到 A 类型订单数量最少的, B 类型订单数量最少的...
我们要怎么写呢 ? 用 GROUP BY 可能会很麻烦. 这里用 ROW_NUMBER() 就很合适
- SELECT order_no,
- order_type,
- order_text,
- order_qty,
- row_number() OVER(PARTITION BY order_type order by order_qty) AS rowno
- FROM wip_order_test
结果:
可以看到, 每一行最后都有一个从低到高的编号, 有了这个编号我们就可以通过取编号为 1 的行来得到每个分组中订单数量最少的一行记录.
解释一下, ROW_NUMBER() 为每一行返回一个行号, partition by 表示分组, 这里表示根据 order_type 分组, 然后我们按照订单数量排序. 就会得到每个分组内的按照订单数量排序的行号.
SUM() OVER() 函数
假如我们现在要 查询每个类型的订单总数分别是多少, 要怎么做?
大家可能会想到 GROUP BY, 不过大家可以自己试试, 是否能得到和我同样的结果
- SELECT order_no,
- order_type,
- order_text,
- order_qty,
- sum(order_qty) OVER(PARTITION BY order_type) AS sum_qty
- FROM wip_order_test
结果:
看到后面多了一个数量列, 就是每个分组的订单总数量. 是不是很方便?
除了 SUM 函数, 其他几个计算函数如 AVG(),MAX(),MIN(),COUNT() 的使用方法和 SUM 一样.
窗口函数
窗口函数可以对一个结果集内的一定范围内值进行累积, 或者通过移动窗口进行累积. 还是看例子吧.
- SELECT order_no,
- order_type,
- order_text,
- order_qty,
- sum(order_qty) OVER
- (ORDER BY order_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
- AS cumulative_qty
- FROM wip_order_test;
解释一下: 还是用 SUM 来计算总和, 这里我们使用了新的语法, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义了窗口的起点和终点, UNBOUNDED PRECEDING 表示起点在第一行, CURRENT ROW 表示终点在当前行. 我们看一下上图的结果, 能看到最后一列的值是逐行累加的.
移动窗口
上面我们的窗口的起点是固定的, 终点逐渐往下移, 我们可以创建一个固定大小的窗口, 起点和终点同时往下移动. 只需要修改 UNBOUNDED 为一个固定的数字就可以了. 我们修改成 2, 和 3 分别看一下
- SELECT order_no,
- order_type,
- order_text,
- order_qty,
- SUM(order_qty) OVER (ORDER BY order_no ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS cumulative_qty2,
- SUM(order_qty) OVER (ORDER BY order_no ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_qty3
- FROM wip_order_test;
解释下: 倒数第二列我们修改窗口起点 2, 表示当前行与前两行之间的范围. 可以看到每一行的值都是当前行与它前面两行的值的累加. 而最后一列, 是当前行与它之前 3 行的值的累加. 每处理一行, 窗口的起点和终点都向下移动.
同理, SUM 也可以改为 AVG 求窗口的平均值
FIRST_VALUE() 和 LAST_VALUE() 可以获取窗口的第一行和最后一行, NTH_VALUE() 可以获取第 N 行. 看一下例子:
- SELECT order_no,
- order_type,
- order_text,
- order_qty,
- first_value(order_qty) OVER (ORDER BY order_no ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS first_value,
- last_value(order_qty) OVER (ORDER BY order_no ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS last_value,
- nth_value(order_qty,2) OVER (ORDER BY order_no ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS second_value
- FROM wip_order_test;
LISTAGG() 函数
这个函数很有用, 有时候在 GROUP BY 以后, 我们想让分组内的某一列的几个值显示在一行上, 比如:
- SELECT
- order_type,
- listagg(to_char(order_text),'-') WITHIN GROUP (ORDER BY order_type) AS text
- FROM wip_order_test
- GROUP BY order_type
结果:
看到, 通过 LISTAGG , 把每个分组中的订单描述字段连接起来. 第一个参数表示要合并的字段名字, 第二个参数表示分隔符.
TOP-N 查询
Oracle 12c 中新增了对 TOP-N 的支持.
- SELECT order_no,
- order_type,
- order_text,
- order_qty
- FROM wip_order_test
- FETCH FIRST 3 ROWS ONLY;
我们用 FETCH FIRST 3 取出了前 3 行数据, 这里也可以使用 FETCH FIRST 20 PERCENT ROWS ONLY 用百分比来取出前 20% 的数据.
还可以使用 OFFSET 关键字, 来表示从第几行开始取, 比如 OFFSET 5 ROWS FETCH NEXT 3 ROWS ONLY 就表示从第 5 行开始往下取 3 行.
中位数 PERCENTILE_CONT()
可以算一组值的中位数, 传入一个参数, 比如传入 0.5 表示 1/2 中位数, 0.75 表示 3/4 中位数
- SELECT order_type,
- percentile_cont(0.5) WITHIN GROUP (
- ORDER BY order_qty) AS A,
- percentile_cont(0.75) WITHIN GROUP (
- ORDER BY order_qty) AS b
- FROM wip_order_test
- GROUP BY order_type
我们根据订单类型分组后, 分别算出每种订单类型数量的 1/2 中位数和 3/4 中位数.
来源: https://www.cnblogs.com/injet/p/10122832.html