1 小时 SQL 极速入门(三)

1 小时 SQL 极速入门(三)

1 小时 SQL 极速入门

前面两篇我们从 SQL 的最基础语法讲起,到表联结多表查询。大家可以点击链接查看

1 小时 SQL 极速入门(一)

1 小时 SQL 极速入门(二)

今天我们讲一些在做报表和复杂计算时非常实用的分析函数。由于各个数据库函数的实现不太一样,本文基于 Oracle 12c 。

  1. 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 rownoFROM wip_order_test

结果:

可以看到,每一行最后都有一个从低到高的编号,有了这个编号我们就可以通过取编号为 1 的行来得到每个分组中订单数量最少的一行记录。

解释一下,ROW_NUMBER() 为每一行返回一个行号, partition by 表示分组,这里表示根据 order_type 分组,然后我们按照订单数量排序。就会得到每个分组内的按照订单数量排序的行号。

  1. SUM() OVER()函数

假如我们现在要 查询每个类型的订单总数分别是多少,要怎么做?大家可能会想到 GROUP BY,不过大家可以自己试试,是否能得到和我同样的结果

SELECT order_no,  order_type,  order_text,  order_qty,  sum(order_qty) OVER(PARTITION BY order_type) AS sum_qtyFROM wip_order_test

结果:

看到后面多了一个数量列,就是每个分组的订单总数量。是不是很方便?

除了 SUM 函数,其他几个计算函数如 AVG(),MAX(),MIN(),COUNT()的使用方法和 SUM 一样。

  1. 窗口函数

窗口函数可以对一个结果集内的一定范围内值进行累积,或者通过移动窗口进行累积。还是看例子吧。

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_qtyFROM wip_order_test;

解释一下:还是用 SUM 来计算总和,这里我们使用了新的语法, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义了窗口的起点和终点,UNBOUNDED PRECEDING表示起点在第一行,CURRENT ROW 表示终点在当前行。我们看一下上图的结果,能看到最后一列的值是逐行累加的。

  1. 移动窗口

上面我们的窗口的起点是固定的,终点逐渐往下移,我们可以创建一个固定大小的窗口,起点和终点同时往下移动。只需要修改 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_qty3FROM 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_valueFROM wip_order_test;
  1. LISTAGG() 函数

这个函数很有用,有时候在 GROUP BY 以后,我们想让分组内的某一列的几个值显示在一行上,比如:

SELECT   order_type,  listagg(to_char(order_text),'-') WITHIN GROUP (ORDER BY order_type) AS textFROM wip_order_testGROUP BY order_type

结果:

看到,通过 LISTAGG ,把每个分组中的订单描述字段连接起来。第一个参数表示要合并的字段名字,第二个参数表示分隔符。

  1. TOP-N 查询

Oracle 12c中新增了对 TOP-N的支持。

SELECT order_no,  order_type,  order_text,  order_qtyFROM wip_order_testFETCH 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 行。

  1. 中位数 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 bFROM wip_order_testGROUP BY order_type

我们根据订单类型分组后,分别算出每种订单类型数量的 1/2 中位数和 3/4中位数。

推荐阅读

    excel怎么用乘法函数

    excel怎么用乘法函数,乘法,函数,哪个,excel乘法函数怎么用?1、首先用鼠标选中要计算的单元格。2、然后选中单元格后点击左上方工具栏的fx公

    计算机主板BIOS设置详细-BIOS知识

    计算机主板BIOS设置详细-BIOS知识,,什么是电脑BIOS,一般电脑主板已经设置完毕后,电脑就开始按del键进入BIOS。系统启动BIOS,即微机的基本输入

    计算机蓝屏故障的计算机蓝屏解决方案

    计算机蓝屏故障的计算机蓝屏解决方案,,电脑蓝屏电脑故障经常使用电脑的朋友经常遇到,因为电脑蓝屏是一个非常普遍的现象,所以很难预测,什么时

    excel中乘法函数是什么?

    excel中乘法函数是什么?,乘法,函数,什么,打开表格,在C1单元格中输入“=A1*B1”乘法公式。以此类推到多个单元。1、A1*B1=C1的Excel乘法公式

    计算机自动关机的原因是什么

    计算机自动关机的原因是什么,,计算机(计算机),通常称为计算机,是一种用于高速计算的电子计算机。它可以进行数值计算和逻辑计算,还具有存储记忆

    电脑功率计算|电脑功率计算公式

    电脑功率计算|电脑功率计算公式,,电脑功率计算公式  从设计角度出发一般取300w/台基本都可以满足要求,可以从以下几个方面分析一下电脑功

    标准差excel用什么函数?

    标准差excel用什么函数?,函数,标准,什么,在数据单元格的下方输入l标准差公式函数公式“=STDEVPA(C2:C6)”。按下回车,求出标准公差值。详细

    如何设置计算机视图视图的统一视图

    如何设置计算机视图视图的统一视图,,不知道你是否有这样的使用电脑经验,电脑在不同的文件夹打开,有时这个文件夹是用来查看列表的方式,但是当

    的故障_计算机解决无法打印文档

    的故障_计算机解决无法打印文档,,核心提示:最近,打印机出现了一个奇怪的现象,在打印正常之前,打印机不能打印最近的突然,提示发送打印作业,计算