I am trying to sum over partition with distinct values from GameID column. It should be partitioned over CasinoID column, but it shouldn't sum up amounts with same GameID.
This is what I am aiming for
Date | CasinoID | GameID | Amount | TARGETAMOUNT |
---|---|---|---|---|
2020-01-01 | 1 | 1 | 1000 | 1000 |
2020-01-01 | 1 | 4 | 2000 | 3000 |
2020-01-01 | 1 | 2 | 300 | 3300 |
2020-01-01 | 1 | 3 | 1000 | 4300 |
2023-01-07 | 1 | 3 | 500 | 3800 |
2023-01-10 | 1 | 1 | 500 | 3300 |
Below are results.
Sum(amount) over (partition by CasinoID order by cast(date as date)) as TARGETAMOUNT
Date | CasinoID | GameID | Amount | TARGETAMOUNT |
---|---|---|---|---|
2020-01-01 | 1 | 1 | 1000 | 4300 |
2020-01-01 | 1 | 4 | 2000 | 4300 |
2020-01-01 | 1 | 2 | 300 | 4300 |
2020-01-01 | 1 | 3 | 1000 | 4300 |
2023-01-07 | 1 | 3 | 500 | 4800 |
2023-01-10 | 1 | 1 | 500 | 5300 |
Since those first four are from same day the TARGETAMOUNT is correct this way but I just showed at the above example to make it clearer what I was aiming for. But like it is shown below it sums bottom 2 rows to TARGETAMOUNT instead of replacing the amount value from the same GameID.
Sum(amount) over (partition by CasinoID, GameID order by cast(date as date)) as TARGETAMOUNT
This doesn't produce wanted end result either:
Date | CasinoID | GameID | Amount | TARGETAMOUNT |
---|---|---|---|---|
2020-01-01 | 1 | 1 | 1000 | 1000 |
2020-01-01 | 1 | 4 | 2000 | 2000 |
2020-01-01 | 1 | 2 | 300 | 300 |
2020-01-01 | 1 | 3 | 1000 | 1000 |
2023-01-07 | 1 | 3 | 500 | 1500 |
2023-01-10 | 1 | 1 | 500 | 1500 |
An alternative way of looking at it is that for each row you want to add the new amount, but also take away that game's previous amount.
You can get the previous amount with LAG() and then use SUM(amount - previous_amount).
CREATE TABLE moneys (
game_date DATE,
casino_id INT,
game_id INT,
amount INT
)
INSERT INTO
moneys
VALUES
('2024-01-01', 1, 1, 1000),
('2024-01-01', 1, 4, 2000),
('2024-01-01', 1, 2, 300),
('2024-01-01', 1, 3, 1000),
('2024-01-07', 1, 3, 500),
('2024-01-10', 1, 1, 500),
('2024-01-11', 1, 1, 100)
7 rows affected
WITH
lagged AS
(
SELECT
*,
LAG(amount, 1, 0) OVER (
PARTITION BY casino_id, game_id
ORDER BY game_date
)
AS previous_amount
FROM
moneys
)
SELECT
*,
SUM(amount - previous_amount) OVER (
PARTITION BY casino_id
ORDER BY game_date, game_id
)
FROM
lagged
game_date | casino_id | game_id | amount | previous_amount | (No column name) |
---|---|---|---|---|---|
2024-01-01 | 1 | 1 | 1000 | 0 | 1000 |
2024-01-01 | 1 | 2 | 300 | 0 | 1300 |
2024-01-01 | 1 | 3 | 1000 | 0 | 2300 |
2024-01-01 | 1 | 4 | 2000 | 0 | 4300 |
2024-01-07 | 1 | 3 | 500 | 1000 | 3800 |
2024-01-10 | 1 | 1 | 500 | 1000 | 3300 |
2024-01-11 | 1 | 1 | 100 | 500 | 2900 |