时间戳

首页 > 解决方案 > SUM OVER PARTITION ON 日期范围

postgresql - SUM OVER PARTITION ON 日期范围

问题描述

我试图对 Postgres 中的每一行在特定时间段内进行累积总和，例如：

|---------------------|------------------|------------------|
|      Date           |     Value        |     Employee     |
|---------------------|------------------|------------------|
|  25-01-1990         |         34       |      Aaron       |
|---------------------|------------------|------------------|
|  15-02-1990         |         4        |      Aaron       |
|---------------------|------------------|------------------|
|  02-03-1990         |         3        |      Aaron       |
|---------------------|------------------|------------------|
|  22-05-1990         |         7        |      Aaron       |
|---------------------|------------------|------------------|

预期结果，范围为 60 天：

|---------------------|------------------|------------------|
|      Date           |     Value        |    Employee      |
|---------------------|------------------|------------------|
|  25-01-1990         |         34       |      Aaron       |
|---------------------|------------------|------------------|
|  15-02-1990         |         38       |      Aaron       |
|---------------------|------------------|------------------|
|  02-03-1990         |         41       |      Aaron       |
|---------------------|------------------|------------------|
|  01-05-1990         |         10       |      Aaron       |
|---------------------|------------------|------------------|

我尝试了以下方法，但结果不正确：

WITH tab AS (SELECT * FROM table_with_values)
SELECT tab.Date, SUM(tab.Value) 
FILTER (WHERE tab.Date<=tab.Date AND tab.Date >=t.Date - INTERVAL '60 DAY')
OVER(PARTITION BY tab.Employee ORDER BY tab.Date ROWS BETWEEN UNBOUND PRECEDENT AND CURRENT ROW)
AS values_cumulative, tab.Employee
FROM tab

标签： postgresqlcumulative-sum

解决方案

尝试这个：

SELECT date, employee, sum(bvalue)
FROM (
     SELECT a.*, b.date as bdate, b.value as bvalue
     FROM testtable a
     LEFT JOIN testtable b ON
               a.employee = b.employee AND
               b.date <= a.date AND
               b.date >= a.date - integer '60') c
GROUP BY employee, date
ORDER BY date ASC;


    date    | employee | sum
------------+----------+-----
 1990-01-25 | Aaron    |  34
 1990-02-15 | Aaron    |  38
 1990-03-02 | Aaron    |  41
 1990-05-01 | Aaron    |  10
(4 Zeilen)

推荐阅读