5

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using Postgresql.

I have price data per day.

CREATE TEMP TABLE Price (id,Day, Price) AS 
VALUES
  (1, 1, 40),
  (2, 1, 20),
  (3, 1, 50),
  (4, 1, 10),
  (5, 1, 20),
  (6, 1, 60),
  (7, 2, 10),
  (8, 2, 40),
  (9, 2, 10),
  (10,2, 20),
  (11,2, 10);

I want to assign numbers (1, 2, 3...) to the price data based on the day and the sum of the prices. Every time when the sum > 60, the sum calculation starts again + every time when a new day is reached, the sum calculation starts again. So for example:

Row 1 [day 1, price 40] = 1. Then for row 2 [day 1, price 20] the price sum is 20 + 40 < 61, therefore row 2 also gets assigned to 1. Then for row 3 [day 1, price 50] the price sum is 20 + 40 + 50 > 60, therefore the counting of the sum has to restart and number 2 is assigned to row 3. The result will look like:

enter image description here

Does anyone know if this is achievable and how? I understand how to take the SUM(Price) OVER (PARTITION BY Day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), but do not know how to restart the sum counting when the condition is met. Thank you in advance for the help!

  • 1
    how is your table sorted do you have an id or timestamp? – Evan Carroll Jul 13 '18 at 21:51
  • 1
    It's sorted based on a 'timestamp without timezone' column. I have a column named date_dt which saves date in format "y-m-d H:M:S" (an example "2017-12-01 09:54:34") and from this I obtained the Day value. – Nina Jul 13 '18 at 21:57
  • You should always include that stuff, because it's not there it's hard to create it and all your answers are likely to create something different. – Evan Carroll Jul 13 '18 at 22:00
  • Sorry, thank you for pointing it out! It's the first time I am asking a question online, will remember it for the next time – Nina Jul 13 '18 at 22:08
  • np, that's what we're here for. I added an ID column because without that it simply won't work, but just change that when you apply it. (or update the question with a ts column if you wish). – Evan Carroll Jul 13 '18 at 22:14
1

You want something like this,

SELECT *, lag(cumsum,1,0::bigint) OVER (PARTITION BY day ORDER BY id)/60 AS grp
FROM (
  SELECT
    id,
    day,
    price,
    sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
  FROM price
) AS t;

Working from the inside out we calculate the cumulative sum with sum() in a window function,

SELECT
  id,
  day,
  price,
  sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
FROM price;

 id | day | price | cumsum 
----+-----+-------+--------
  1 |   1 |    40 |     40
  2 |   1 |    20 |     60
  3 |   1 |    50 |    110
  4 |   1 |    10 |    120
  5 |   1 |    20 |    140
  6 |   1 |    60 |    200
  7 |   2 |    10 |     10
  8 |   2 |    40 |     50
  9 |   2 |    10 |     60
 10 |   2 |    20 |     80
 11 |   2 |    10 |     90
(11 rows)

Notice our cumsum resets on a new day, but it persists past your 60. We need to fix that, for that we simply look at the prior row, and divide by 60. We don't want to start a new group, unless the last row exceeds a multiple of 60.

SELECT *, lag(cumsum,1,0::bigint) OVER (PARTITION BY day ORDER BY id)/60 AS grp
FROM (
  SELECT
    id,
    day,
    price,
    sum(price) OVER (PARTITION BY day ORDER BY id) AS cumsum
  FROM price
) AS t;
 id | day | price | cumsum | grp 
----+-----+-------+--------+-----
  1 |   1 |    40 |     40 |   0
  2 |   1 |    20 |     60 |   0
  3 |   1 |    50 |    110 |   1
  4 |   1 |    10 |    120 |   1
  5 |   1 |    20 |    140 |   2
  6 |   1 |    60 |    200 |   2
  7 |   2 |    10 |     10 |   0
  8 |   2 |    40 |     50 |   0
  9 |   2 |    10 |     60 |   0
 10 |   2 |    20 |     80 |   1
 11 |   2 |    10 |     90 |   1
(11 rows)

And you're done.

  • 1
    This is amazing! Was not even aware that /60 would be possible, thank you so much for the help and for guiding me through the steps! – Nina Jul 13 '18 at 22:27
  • How robust is this solution, though? – Andriy M Jul 14 '18 at 8:27
  • That answer is wrong, you have rows with id=5 and 6 in the same grp 2, while their prices are 20 and 60 which greater than 60. You can see that the picture in the question expects them to be in different groups, 3 and 4. – Grief May 11 at 17:16
  • Nice answer, but what if I wanted cumsum to reset each time it reached >60 rather than resetting each day? In the above example the prices always nicely add up to 60 so nothing gets carried over each time. This is the question I am trying to answer here: here – BStone May 17 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.