Java教程
数仓开发5大SQL分析函数
本文主要是介绍数仓开发5大SQL分析函数，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
基本语法
analytic_function_name([argument_list]) OVER ( [PARTITION BY partition_expression,…] [ORDER BY sort_expression, … [ASC|DESC]])
analytic_function_name: 函数名称 — 比如RANK(),SUM(),FIRST()等等partition_expression: 分区列sort_expression: 排序列
案例
数据准备
CREATE TABLE `orders` (
`order_num` String COMMENT '订单号',
`order_amount` DECIMAL ( 12, 2 ) COMMENT '订单金额',
`advance_amount` DECIMAL ( 12, 2 ) COMMENT '预付款',
`order_date` string COMMENT '订单日期',
`cust_code` string COMMENT '客户',
`agent_code` string COMMENT '代理商'
);
INSERT INTO orders VALUES('200100', '1000.00', '600.00', '2020-08-01', 'C00013', 'A003');
INSERT INTO orders VALUES('200110', '3000.00', '500.00', '2020-04-15', 'C00019', 'A010');
INSERT INTO orders VALUES('200107', '4500.00', '900.00', '2020-08-30', 'C00007', 'A010');
INSERT INTO orders VALUES('200112', '2000.00', '400.00', '2020-05-30', 'C00016', 'A007');
INSERT INTO orders VALUES('200113', '4000.00', '600.00', '2020-06-10', 'C00022', 'A002');
INSERT INTO orders VALUES('200102', '2000.00', '300.00', '2020-05-25', 'C00012', 'A012');
INSERT INTO orders VALUES('200114', '3500.00', '2000.00', '2020-08-15', 'C00002','A008');
INSERT INTO orders VALUES('200122', '2500.00', '400.00', '2020-09-16', 'C00003', 'A004');
INSERT INTO orders VALUES('200118', '500.00', '100.00', '2020-07-20', 'C00023', 'A006');
INSERT INTO orders VALUES('200119', '4000.00', '700.00', '2020-09-16', 'C00007', 'A010');
INSERT INTO orders VALUES('200121', '1500.00', '600.00', '2020-09-23', 'C00008', 'A004');
INSERT INTO orders VALUES('200130', '2500.00', '400.00', '2020-07-30', 'C00025', 'A011');
INSERT INTO orders VALUES('200134', '4200.00', '1800.00', '2020-09-25', 'C00004','A005');
INSERT INTO orders VALUES('200108', '4000.00', '600.00', '2020-02-15', 'C00008', 'A004');
INSERT INTO orders VALUES('200103', '1500.00', '700.00', '2020-05-15', 'C00021', 'A005');
INSERT INTO orders VALUES('200105', '2500.00', '500.00', '2020-07-18', 'C00025', 'A011');
INSERT INTO orders VALUES('200109', '3500.00', '800.00', '2020-07-30', 'C00011', 'A010');
INSERT INTO orders VALUES('200101', '3000.00', '1000.00', '2020-07-15', 'C00001','A008');
INSERT INTO orders VALUES('200111', '1000.00', '300.00', '2020-07-10', 'C00020', 'A008');
INSERT INTO orders VALUES('200104', '1500.00', '500.00', '2020-03-13', 'C00006', 'A004');
INSERT INTO orders VALUES('200106', '2500.00', '700.00', '2020-04-20', 'C00005', 'A002');
INSERT INTO orders VALUES('200125', '2000.00', '600.00', '2020-10-01', 'C00018', 'A005');
INSERT INTO orders VALUES('200117', '800.00', '200.00', '2020-10-20', 'C00014', 'A001');
INSERT INTO orders VALUES('200123', '500.00', '100.00', '2020-09-16', 'C00022', 'A002');
INSERT INTO orders VALUES('200120', '500.00', '100.00', '2020-07-20', 'C00009', 'A002');
INSERT INTO orders VALUES('200116', '500.00', '100.00', '2020-07-13', 'C00010', 'A009');
INSERT INTO orders VALUES('200124', '500.00', '100.00', '2020-06-20', 'C00017', 'A007');
INSERT INTO orders VALUES('200126', '500.00', '100.00', '2020-06-24', 'C00022', 'A002');
INSERT INTO orders VALUES('200129', '2500.00', '500.00', '2020-07-20', 'C00024', 'A006');
INSERT INTO orders VALUES('200127', '2500.00', '400.00', '2020-07-20', 'C00015', 'A003');
INSERT INTO orders VALUES('200128', '3500.00', '1500.00', '2020-07-20', 'C00009','A002');
INSERT INTO orders VALUES('200135', '2000.00', '800.00', '2020-09-16', 'C00007', 'A010');
INSERT INTO orders VALUES('200131', '900.00', '150.00', '2020-08-26', 'C00012', 'A012');
INSERT INTO orders VALUES('200133', '1200.00', '400.00', '2020-06-29', 'C00009', 'A002');
AVG() 和SUM()
需求描述：
第三季度每个代理商的移动平均收入和总收入
SELECT
agent_code,
order_date,
AVG( order_amount ) OVER ( PARTITION BY agent_code ORDER BY order_date) avg_rev,
SUM( order_amount ) OVER ( PARTITION BY agent_code ORDER BY order_date ) total_rev
FROM
orders
WHERE
order_date >= '2020-07-01'
AND order_date <= '2020-09-30';
结果输出
A002 2020-07-20 2000 4000 A002 2020-07-20 2000 4000 A002 2020-09-16 1500 4500 A003 2020-07-20 2500 2500 A003 2020-08-01 1750 3500 A004 2020-09-16 2500 2500 A004 2020-09-23 2000 4000 A005 2020-09-25 4200 4200 A006 2020-07-20 1500 3000 A006 2020-07-20 1500 3000 A008 2020-07-10 1000 1000 A008 2020-07-15 2000 4000 A008 2020-08-15 2500 7500 A009 2020-07-13 500 500 A010 2020-07-30 3500 3500 A010 2020-08-30 4000 8000 A010 2020-09-16 3500 14000 A010 2020-09-16 3500 14000 A011 2020-07-18 2500 2500 A011 2020-07-30 2500 5000 A012 2020-08-26 900 900
FIRST_VALUE()和 LAST_VALUE()
- first_value: 取分组内排序后，截止到当前行，第一个值
- last_value: 取分组内排序后，截止到当前行，最后一个值
需求描述
客户首次购买后多少天才进行下一次购买
SELECT
cust_code,
order_date,
datediff(order_date,FIRST_VALUE ( order_date ) OVER ( PARTITION BY cust_code ORDER BY order_date )) next_order_gap
FROM
orders
order by cust_code,next_order_gap
结果输出
C00001 2020-07-15 0 C00002 2020-08-15 0 C00003 2020-09-16 0 C00004 2020-09-25 0 C00005 2020-04-20 0 C00006 2020-03-13 0 C00007 2020-08-30 0 C00007 2020-09-16 17 C00007 2020-09-16 17 C00008 2020-02-15 0 C00008 2020-09-23 221 C00009 2020-06-29 0 C00009 2020-07-20 21 C00009 2020-07-20 21 C00010 2020-07-13 0 C00011 2020-07-30 0 C00012 2020-05-25 0 C00012 2020-08-26 93 C00013 2020-08-01 0 C00014 2020-10-20 0 C00015 2020-07-20 0 C00016 2020-05-30 0 C00017 2020-06-20 0 C00018 2020-10-01 0 C00019 2020-04-15 0 C00020 2020-07-10 0 C00021 2020-05-15 0 C00022 2020-06-10 0 C00022 2020-06-24 14 C00022 2020-09-16 98 C00023 2020-07-20 0 C00024 2020-07-20 0 C00025 2020-07-18 0 C00025 2020-07-30 12
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）
需求描述
代理商最近一次出售的最高订单金额是多少？
SELECT agent_code, order_amount, LAG ( order_amount, 1 ) OVER ( PARTITION BY agent_code ORDER BY order_amount DESC ) last_highest_amount FROM orders ORDER BY agent_code, order_amount DESC;
结果输出
A001 800 NULL A002 4000 NULL A002 3500 4000 A002 2500 3500 A002 1200 2500 A002 500 1200 A002 500 500 A002 500 500 A003 2500 NULL A003 1000 2500 A004 4000 NULL A004 2500 4000 A004 1500 2500 A004 1500 1500 A005 4200 NULL A005 2000 4200 A005 1500 2000 A006 2500 NULL A006 500 2500 A007 2000 NULL A007 500 2000 A008 3500 NULL A008 3000 3500 A008 1000 3000 A009 500 NULL A010 4500 NULL A010 4000 4500 A010 3500 4000 A010 3000 3500 A010 2000 3000 A011 2500 NULL A011 2500 2500 A012 2000 NULL A012 900 2000
RANK() 和DENSE_RANK()
rank：对组中的数据进行排名，如果名次相同，则排名也相同，但是下一个名次的排名序号会出现不连续。比如查找具体条件的topN行。RANK() 排序为 (1,2,2,4)
dense_rank：dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。当出现名次相同时，则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。
DENSE_RANK() 排序为 (1,2,2,3)
需求描述
每月第二高的订单金额是多少？
SELECT order_num, order_date, order_amount, order_month FROM ( SELECT order_num, order_date, order_amount, DATE_FORMAT( order_date, 'YYYY-MM' ) AS order_month, DENSE_RANK ( ) OVER ( PARTITION BY DATE_FORMAT( order_date, 'YYYY-MM' ) ORDER BY order_amount DESC ) order_rank FROM orders ) t WHERE order_rank = 2 ORDER BY order_date;
结果输出
200106 2020-04-20 2500 2020-04 200103 2020-05-15 1500 2020-05 200133 2020-06-29 1200 2020-06 200101 2020-07-15 3000 2020-07 200114 2020-08-15 3500 2020-08 200119 2020-09-16 4000 2020-09 200117 2020-10-20 800 2020-10
CUME_DIST()
cume_dist:如果按升序排列，则统计：小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）。如果是降序排列，则统计：大于等于当前值的行数/总行数。比如，统计小于等于当前工资的人数占总人数的比例 ，用于累计统计。
需求描述
8月和9月每个订单的收入百分比
先查看一下8月和9月的数据，按订单金额排序
SELECT order_num, order_amount, order_date, agent_code FROM orders WHERE order_date >= '2020-08-01' AND order_date <= '2020-09-30' ORDER BY date_format( order_date, "YYYY-MM" ), order_amount;
其结果为：
SELECT DATE_FORMAT( order_date, 'YYYY-MM' ) AS order_month, agent_code, order_amount, CUME_DIST ( ) OVER ( PARTITION BY DATE_FORMAT( order_date, 'YYYY-MM' ) ORDER BY order_amount ) FROM orders WHERE order_date >= '2020-08-01' AND order_date <= '2020-09-30';
结果输出
2020-08 A012 900 0.25 2020-08 A003 1000 0.5 2020-08 A008 3500 0.75 2020-08 A010 4500 1.0 2020-09 A002 500 0.16666666666666666 2020-09 A004 1500 0.3333333333333333 2020-09 A010 2000 0.5 2020-09 A004 2500 0.6666666666666666 2020-09 A010 4000 0.8333333333333334 2020-09 A005 4200 1.0
这篇关于数仓开发5大SQL分析函数的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
Java中定时任务实现方式及源码剖析11-24
-
Java中定时任务实现方式及源码剖析11-24
-
鸿蒙原生开发手记：03-元服务开发全流程（开发元服务，只需要看这一篇文章）11-24
-
细说敏捷：敏捷四会之每日站会11-24
-
Springboot应用的多环境打包入门11-23
-
Springboot应用的生产发布入门教程11-23
-
Python编程入门指南11-23
-
Java创业入门：从零开始的编程之旅11-23
-
Java创业入门：新手必读的Java编程与创业指南11-23
-
Java对接阿里云智能语音服务入门详解11-23
-
Java对接阿里云智能语音服务入门教程11-23
-
JAVA对接阿里云智能语音服务入门教程11-23
-
Java副业入门：初学者的简单教程11-23
-
JAVA副业入门：初学者的实战指南11-23
-
JAVA项目部署入门：新手必读指南11-23
栏目导航
