leetcode:534. 游戏玩法分析III---知识点 sum over partition by 的用法

 

在刷leetcode534. 游戏玩法分析 III 时再解完题后发现还有另一种解法，用到的关键字是 sum over partition by 函数

# method 1

SELECT a1.player_id, a1.event_date, sum(a2.games_played) AS games_played_so_far

FROM Activity a1

LEFT JOIN Activity a2

ON a1.player_id = a2.player_id

AND a1.event_date >= a2.event_date

GROUP BY a1.player_id, a1.event_date

ORDER BY a1.player_id, a1.event_date ASC

# method 2

SELECT player_id, event_date, sum(games_played) OVER (PARTITION BY player_id ORDER BY event_date) AS games_played_so_far

FROM Activity

ORDER BY player_id, event_date DESC

 

 

 

0、select * from wmg_test; ---测试数据

1、select v1,v2,sum(v2) over(order by v2) as sum --按照 v2排序，累计n+n-1+....+1

from wmg_test;

 

 

 

 

2、select v1,v2,sum(v2) over(partition by v1 order by v2) as sum --先分组，组内在进行 1 中的操作

from wmg_test;

 

 

 

 

3、select v1,v2,sum(v2) over(partition by v1 order by v1) as sum ---稳定排序

from wmg_test;

 

 

 

 

4、select v1,v2,sum(v2) over(partition by v1) as sum --相同key的进行回填处理

from wmg_test;

 

 

 

 

5、select distinct v1,sum_01 --取一条

from (

select v1,sum(v2) over(partition by v1) as sum_01

from wmg_test

) a;

 

 

 

 

6、当然也可以逆序累加，只需order by desc 即可

总结区别：group by 和partition by的区别
group 单纯分组

partition 也能分组，但还具备累计的功能