程序员乐源

表结构

image-20220516181239369

CREATE table t3 like t1;
INSERT into t3 values('2014', 'A', 3),('2014' ,'B' ,1 ),('2014' ,'C' ,2 ),('2015' ,'A' ,4),('2015', 'D' ,3);
SELECT * FROM t3;

1）按a分组，对b排序，对c进行累加

思路

sum(c) 开窗 partition a 然后order by b

SQL

SELECT a,
       b,
       c,
       SUM(c) over (PARTITION by a ORDER BY b) sum_c
FROM t3;

输出结果

image-20220516181258271

2)按a分组,对b排序，求平均值

思路

avg``(c) 开窗 partition a 然后order by b

SQL

SELECT a,
       b,
       c,
       AVG(c) over (PARTITION by a ORDER BY b) as avg_c
FROM t3;

输出结果

image-20220516181313296

3)按a分组对b排序，求对b进行累计排名的比例

思路

首先要知道什么是累计排名比例 累计排名比例 = 当前排名 /

SQL

SELECT a,
       b,
       c,
       round(ROW_NUMBER() over (PARTITION by a ORDER BY b) / COUNT(c) OVER (PARTITION BY a), 2) AS ratio_c
FROM t3
order by a, b;

输出结果

image-20220516181327349

4)按 a 分组按 b 字段排序，对 b 取累计求和比例

思路

累计求和 = sum(c) over (partition by a order by b) / sum(c) over (partition by a)

SQL

select a,
       b,
       c,
       round(sum(c) over (partition by a order by b) / (sum(c) over (partition by a)), 2) as ratio_c
from t3
order by a,
         b;

输出结果

image-20220516181338848