【Hive】SQL | 窗口函数详解
最新回答
蔚蓝的心
2023-12-10 16:49:50
假设有一个贷款表（loan），包含贷款人ID、贷款日期与金额。
使用窗口函数over()可以对聚合函数如sum()、min()、max()与avg()进行分区计算。例如，第一句查询计算每个贷款人当前记录前三条贷款的总金额；第二句计算至下一笔贷款前，贷款人累计的贷款总额。
窗口函数over()的语法包含时间限定：ROWS BETWEEN 一个时间点 AND 一个时间点。时间点可以是n PRECEDING（前n行）、n FOLLOWING（后n行）或CURRENT ROW（当前行）。若无具体行数限制，可以使用UNBOUNDED（从起始到当前行）。
窗口函数over()与group by的区别在于，后者要求所有列都按照分区进行计算，而over()允许单个特征分区。
NTILE(), ROW_NUMBER(), RANK(), DENSE_RANK()等函数用于为数据集增加序列号。NTILE()将数据按名称切分至10个分区，并返回所属分区。ROW_NUMBER()返回连续序号；RANK()与DENSE_RANK()对相同值的处理不同，RANK()跳过下一个序号，而DENSE_RANK()不跳过。
LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()函数用于返回指定点的数据。LAG(n)向前错位n行，LEAD()向后错位n行；FIRST_VALUE()取当前分区的第一个值；LAST_VALUE()取当前分区的最后一个值。
GROUPING SET(),with CUBE, with ROLLUP对group by进行限制。GROUPING SET()生成的GROUPING_ID用于区分数据输出结果所属的group by。生成时，根据声明的顺序字段是否存在于当前group by中的二进制位组合数据。GROUPING SETS()需先执行GROUP BY操作。例如，(A,C)的group_id为二进制：101（即5）。解释器发现group by A,C 但select A,B,C时，会复制所有from表结果，将B置为null。
with CUBE返回的是group by字段的笛卡尔积，与GROUPING_SET()不同；with ROLLUP则不会产生额外的聚合结果，仅按substring(orderdate,1,7)展示。使用with ROLLUP时，注意group by后面字段的顺序。
热门标签
