– 建表
CREATE DATABASE IF NOT EXISTS TEST;
CREATE TABLE TEST.COSTITEM
( NAME STRING,
ORDERDATE DATE,
COST STRING);
– 数据加载
INSERT INTO TEST.COSTITEM VALUES ('jack','2020-01-01','10');
INSERT INTO TEST.COSTITEM VALUES ('tony','2020-01-02','15');
INSERT INTO TEST.COSTITEM VALUES ('jack','2020-02-03','23');
INSERT INTO TEST.COSTITEM VALUES ('tony','2020-01-04','29');
INSERT INTO TEST.COSTITEM VALUES ('jack','2020-01-05','46');
INSERT INTO TEST.COSTITEM VALUES ('jack','2020-04-06','42');
INSERT INTO TEST.COSTITEM VALUES ('tony','2020-01-07','50');
INSERT INTO TEST.COSTITEM VALUES ('jack','2020-01-08','55');
INSERT INTO TEST.COSTITEM VALUES ('mart','2020-04-08','62');
INSERT INTO TEST.COSTITEM VALUES ('mart','2020-04-09','68');
INSERT INTO TEST.COSTITEM VALUES ('neil','2020-05-10','12');
INSERT INTO TEST.COSTITEM VALUES ('mart','2020-04-11','75');
INSERT INTO TEST.COSTITEM VALUES ('neil','2020-06-12','80');
INSERT INTO TEST.COSTITEM VALUES ('mart','2020-04-13','94');
–查询结果:
select * from costitem;
–SQL实现:
SELECT
name,
orderdate,
cost,
sum(cost) OVER() as sample1, -- 所有行相加
sum(cost) over(PARTITION BY name) as sample2, -- 按name分组,组内数据求和
sum(cost) OVER(PARTITION BY name ORDER BY orderdate) as sample3, -- 按name分组,时间排序,组内数据逐个相加
sum(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 组内由起点到当前行的聚合
sum(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 组内当前行和前面一行做聚合
sum(cost) over(PARTITION BY name ORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 组内当前行和前一行和后一行聚合
sum(cost) over(PARTITION BY name ORDER BY orderdate ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 组内当前行和后面所有行
FROM
TEST.COSTITEM;
从上述结果看出:
sample3 和 sample4实际是等效的,只是语法表达不同。
sample2和sample3 语法表达有点像,实际上含义不同。