英文:
Calculating a share value group by in Postgres
问题
Sure, here's a script to calculate the percentage of listens to each track by an artist relative to the total listens by that artist for each date:
SELECT
id,
date,
artist,
track,
ROUND((listenings::NUMERIC / SUM(listenings) OVER (PARTITION BY date, artist))::NUMERIC, 3) AS percentage
FROM your_table_name
ORDER BY date, artist, id;
Replace your_table_name with the actual name of your table. This SQL query will give you the desired output with the percentage calculated for each track by artist and date.
英文:
I want to calculate percentage of value in Group by. My table data:
| 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 |
I need to calculate the share of listens to the track from listenings to all tracks of one artist for each date for each of the artists. The output table must be like
| 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 |
and so on. Please help me to write this script.
答案1
得分: 1
使用子查询来查找每位艺术家每日的总收听次数：
select t.*, round(cast(t.listenings as numeric)/cast((select sum(t1.listenings)
from tracks t1
where t1.artist = t.artist and t.date = t1.date) as numeric), 3)
from tracks t
英文:
Using a subquery to find the total number of listens per artist, per date:
select t.*, round(cast(t.listenings as numeric)/cast((select sum(t1.listenings)
from tracks t1
where t1.artist = t.artist and t.date = t1.date) as numeric), 3)
from tracks t
答案2
得分: 1
只使用窗口函数！
选择 t.*，
听众::数值
/ 求和(听众) over(按艺术家, 日期分区) 百分比
从音轨中获取
英文:
Just use window functions!
select t.*,
listenings::numeric
/ sum(listenings) over(partition by artist, date) pct
from tracks
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论