重复数据以获得保留率

Question

我想创造保留率，但我有一个问题要面对，如何重复的0日期作出一些除法。

我的数据是这样的

week_monday    users     week_diff
2021-11-01     560       0
2021-11-01     340       1
2021-11-08     600       0
2021-11-08     100       1
2021-11-08      60       2
2021-11-08      40       3

我想要的是将0 week_diff重复到每个组的日期，这样我就可以将它除以得到保留率。

week_monday    users     week_diff   users_2   retention_rate
2021-11-01     560       0           560       1
2021-11-01     340       1           560       0.6
2021-11-08     600       0           600       1
2021-11-08     100       1           600       0.16
2021-11-08      60       2           600       0.1
2021-11-08      40       3           600       0.06

Answer 1

试试这种方法：

SELECT week_monday, users, week_diff,
    MAX (users) OVER (PARTITION BY week_monday) AS users_2,
    users / max(users) OVER (PARTITION BY week_monday) AS retention_rate
FROM mydataset.mytable
ORDER BY week_monday, week_diff

输出：

​

​