PostgreSQL中按分组计算曲目播放量占比的SQL脚本求助
2026-7-22
PostgreSQL按分组计算播放量占比的SQL实现
需要计算每个日期下，单首曲目播放量占所属艺人当日总播放量的百分比，输出包含id、date、artist、track、percentage字段的结果集。
原始数据表
| id | date | artist | track | listenings |
|---|---|---|---|---|
| 1 | 2023-01-01 | C | track_1 | 2016 |
| 2 | 2023-01-01 | C | track_2 | 3800 |
| 3 | 2023-01-01 | B | track_3 | 2311 |
| 4 | 2023-01-01 | A | track_4 | 4180 |
| 5 | 2023-01-01 | C | track_5 | 2013 |
| 6 | 2023-01-01 | A | track_6 | 2227 |
| 7 | 2023-01-01 | B | track_7 | 1006 |
| 8 | 2023-01-01 | B | track_8 | 720 |
| 9 | 2023-01-01 | A | track_9 | 2438 |
| 10 | 2023-01-01 | A | track_10 | 2654 |
| 11 | 2023-01-02 | C | track_1 | 3347 |
| 12 | 2023-01-02 | C | track_2 | 3100 |
| 13 | 2023-01-02 | B | track_3 | 2436 |
| 14 | 2023-01-02 | A | track_4 | 4821 |
| 15 | 2023-01-02 | C | track_5 | 1485 |
| 16 | 2023-01-02 | A | track_6 | 3157 |
| 17 | 2023-01-02 | B | track_7 | 1993 |
| 18 | 2023-01-02 | B | track_8 | 2953 |
| 19 | 2023-01-02 | A | track_9 | 1651 |
| 20 | 2023-01-02 | A | track_10 | 1260 |
目标输出示例
| id | date | artist | track | percentage |
|---|---|---|---|---|
| 1 | 2023-01-01 | C | track_1 | 0.257 |
| 2 | 2023-01-01 | C | track_2 | 0.486 |
| 3 | 2023-01-01 | B | track_3 | 0.572 |
| 4 | 2023-01-01 | A | track_4 | 0.364 |
| 5 | 2023-01-01 | C | track_5 | 0.257 |
| 11 | 2023-01-02 | C | track_1 | 0.422 |
SQL脚本
SELECT id, date, artist, track, ROUND(listenings::NUMERIC / SUM(listenings) OVER (PARTITION BY date, artist), 3) AS percentage FROM your_table_name;
脚本说明
- 用
SUM(listenings) OVER (PARTITION BY date, artist)窗口函数，直接计算每个艺人对应日期的总播放量，无需额外子查询或关联操作。 - 将
listenings转为NUMERIC类型，避免整数除法导致的精度丢失。 - 通过
ROUND()函数将结果保留三位小数，匹配目标输出的格式要求。
内容的提问来源于stack exchange，提问作者Михаил Воронин
相关产品推荐
相关产品推荐
