窗口函数滚动求和

Question

我有一个窗口函数，它给我一个滚动和，如下所示：

SELECT start_terminal,
duration_seconds,
start_time,
sum(duration_seconds) OVER
(PARTITION BY start_terminal order by start_time)
AS running_total
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < '2012-01-08'

但是，当start_time列中的一些时间戳相等时，滚动和保持不变，这是有道理的，因为我是按开始时间排序的，但它没有正确地将duration_seconds添加到滚动和中，如下所示：我如何修复或解释这一点？

current output:
start_terminal duration_seconds start_time         running_total
31000          74              2012-01-01 15:32:00     74
31000          291             2012-01-02 12:40:00     365
31000          520             2012-01-02 19:15:00     885
31000          424             2012-01-03 07:22:00     1756
31000          447             2012-01-03 07:22:00     1756
31000         1422             2012-01-03 12:32:00     3178
31000         348             2012-01-04 17:36:00     3526

所需：

start_terminal duration_seconds start_time         running_total
31000          74              2012-01-01 15:32:00     74
31000          291             2012-01-02 12:40:00     365
31000          520             2012-01-02 19:15:00     885
31000          424             2012-01-03 07:22:00     1756
31000          447             2012-01-03 07:22:00     2203
31000         1422             2012-01-03 12:32:00     3625
31000         348             2012-01-04 17:36:00     3973

Answer 1

如果您将duration_seconds列添加到partition中的order by，则应该会得到您想要的内容。

SELECT start_terminal,
duration_seconds,
start_time,
sum(duration_seconds) OVER
(PARTITION BY start_terminal order by start_time, duration_seconds)
AS running_total
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < '2012-01-08'

Answer 2

目前尚不清楚如何解决平局。但是order by的默认窗口帧是range between。你似乎想要rows between:

SELECT start_terminal, duration_seconds, start_time,
sum(duration_seconds) OVER (
partition by start_terminal
order by start_time
rows between unbounded preceding and current row
) as running_total
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < '2012-01-08';

如果您想要duration_seconds上的辅助密钥，可以将其添加到order by中。但是，如果两行的两列值相同，则会出现同样的问题。如果您有一个id列或在创建，那么它可以用作平局决胜局。