1 小时 SQL 极速入门(三)——分析函数

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中位数。

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐