分组聚合 OVER PARTITION BY

CREATE TABLE default.test_over_partition (
  `fdate` Date,
  `year` Int,
  `month` Int,
  `category1` String,
  `category2` String,
  `income` Double
);

INSERT INTO hdp_fin_dash_ods.test_over_partition (`fdate`,`year`,`month`,`category1`,`category2`,`income`) VALUES
('2020-01-01',2020,1,'3C','电脑','1010'),
('2020-01-01',2020,1,'3C','手机','1011'),
('2020-02-01',2020,2,'3C','电脑','1012'),
('2020-02-01',2020,2,'3C','手机','1013'),
('2020-03-01',2020,3,'3C','电脑','1014'),
('2020-03-01',2020,3,'3C','手机','1015'),
('2021-04-01',2021,4,'3C','电脑','1016'),
('2021-04-01',2021,4,'3C','手机','1017'),
('2021-05-01',2021,5,'3C','电脑','1018'),
('2021-05-01',2021,5,'3C','手机','1019');

-- 查询每年每 category2 日累计 income

SELECT `fdate`,`year`,`month`,`category1`,`category2`,`income`
,SUM(income) OVER (PARTITION BY `year`,`category1`,`category2` ORDER BY fdate) AS ttl_year_income
FROM hdp_fin_dash_ods.test_over_partition;