SQL：按 /窗口函数与2列的组合分区，由今天分开

Question

我正在尝试在Postgres中创建一个实现的视图，其中I总和2列，具体取决于今天的日期。

在下图中，有一个我要实现的查询的示例：

如果今天是“ 2022-06-06”：

• 2022-06-05：1+2+2+3+3+4+4+5
• 2022-06-06： 1+2+3+4+5+6+6+107
• 2022-06-07：1+2+3+3+4+4+5+6+107+108

​

http://sqlfiddle.com/#！15/538EA7/1 更新： http://sqlfiddle.com/#！15/bef30/3

非常感谢任何帮助！

旧图像：

编辑：更新的小提琴和图像

Answer 1

如果我正确理解了您，则需要以下结果

 user_id | date       | actual | scheduled | sum
 ------: | :--------- | -----: | --------: | --:
       1 | 2022-06-01 |      1 |       100 |   1
       1 | 2022-06-02 |      2 |       101 |   3
       1 | 2022-06-03 |      3 |       103 |   6
       1 | 2022-06-04 |      4 |       104 |  10
       1 | 2022-06-05 |      5 |       105 |  15
       2 | 2022-06-06 |      6 |       106 |  21
       2 | 2022-06-07 |      7 |       107 | 128
       2 | 2022-06-08 |      8 |       108 | 236
       2 | 2022-06-09 |      9 |       109 | 345
       2 | 2022-06-10 |     10 |       110 | 455

，然后可以使用此类查询

SELECT
    user_id,
    date,
    actual,
    scheduled,
    SUM(CASE WHEN date <= '2022-06-6' THEN actual ELSE scheduled END)
        OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM table_1    

Answer 2

我认为您想要的功能是累积聚合，但是在这样做之前，您可以在/之后添加新列以正确分区。

这会做您期望的吗？

WITH CTE_PART AS (
  SELECT 
    user_id, 
    date, 
    actual, 
    scheduled, 
    CASE WHEN date <= '2022-06-06' THEN 'Before' ELSE 'After' END as BeforeAfterPartitionId 
  FROM 
    table_1
), 
CTE_CUMULATIVE AS (
  SELECT 
    *, 
    SUM(actual) OVER(
      PARTITION BY user_id, 
      BeforeAfterPartitionId 
      ORDER BY 
        date ROWS BETWEEN UNBOUNDED PRECEDING 
        AND CURRENT ROW
    ) as actual_sum, 
    SUM(scheduled) OVER(
      PARTITION BY user_id, 
      BeforeAfterPartitionId 
      ORDER BY 
        date ROWS BETWEEN UNBOUNDED PRECEDING 
        AND CURRENT ROW
    ) as sched_sum 
  FROM 
    CTE_PART
), 
CTE_PRIORS AS (
  SELECT 
    user_id, 
    date, 
    SUM(actual_sum) as PRIORS 
  FROM 
    CTE_CUMULATIVE 
  WHERE 
    date = '2022-06-06' 
  GROUP BY 
    user_id, 
    date
) 
SELECT 
  A.user_id, 
  A.date, 
  A.actual, 
  A.scheduled, 
  case when A.beforeafterpartitionId = 'Before' THEN A.actual_sum ELSE A.sched_sum + coalesce(B.PRIORS, 0) END as want 
FROM 
  CTE_CUMULATIVE A 
  LEFT OUTER JOIN CTE_PRIORS B ON A.user_id = B.user_id 
  AND A.date >= B.date 
ORDER BY 
  user_id, 
  date;

http://sqlfiddle.com/#！15/bef30/bef30/14