SQL: partition by / window function with the combination of 2 columns, separated by todays date-CodePudding

I am trying to create a materialized-view in postgres, where I sum 2 columns, depending on todays date within a window function.

In the below picture there is an example of the query I am trying to achieve:

If today is the '2022-06-06':

2022-06-05: 1 2 3 4 5
2022-06-06: 1 2 3 4 5 6 107
2022-06-07: 1 2 3 4 5 6 107 108

Here is a sample fiddle with date:

~~http://sqlfiddle.com/#!15/538ea7/1~~ Updated:

Edit: Updated Fiddle and Image

CodePudding user response：

If I understood you correctly, you need the following result

 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

Then you can use a query like this

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

Working demo

CodePudding user response：

I think the function you want is Cumulative Aggregation, but before doing so you can add a new column Before/After to get it to partition correctly.

Does this do what you expect?

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/14