数仓开发需要了解的5大SQL分析函数

基本语法

: 函数名称 — 比如 , , 等等

: 分区列

: 排序列

案例

数据准备

AVG() 和SUM()

需求描述：

第三季度每个代理商的移动平均收入和总收入

结果输出

FIRST_VALUE()和 LAST_VALUE()

first_value: 取分组内排序后，截止到当前行，第一个值

last_value: 取分组内排序后，截止到当前行，最后一个值

需求描述

客户首次购买后多少天才进行下一次购买

结果输出

LEAD() 和 LAG()

lead(value_expr[,offset[,default]])：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL

lag(value_expr[,offset[,default]]): 与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

需求描述

代理商最近一次出售的最高订单金额是多少？

结果输出

RANK() 和DENSE_RANK()

rank：对组中的数据进行排名，如果名次相同，则排名也相同，但是下一个名次的排名序号会出现不连续。比如查找具体条件的topN行。 排序为 (1,2,2,4)

dense_rank：dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。当出现名次相同时，则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。

排序为 (1,2,2,3)

需求描述

每月第二高的订单金额是多少？

结果输出

CUME_DIST()

cume_dist:如果按升序排列，则统计：小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）。如果是降序排列，则统计：大于等于当前值的行数/总行数。比如，统计小于等于当前工资的人数占总人数的比例 ，用于累计统计。

需求描述

8月和9月每个订单的收入百分比

先查看一下8月和9月的数据，按订单金额排序

其结果为：

结果输出