0

Using an analytical function, I want that the column 'sum' contains the cumulative of the previous column.

But my code gets the total sum of all encounters.

Here is both table and data for testing:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name CHARACTER VARYING(50)
);

CREATE TABLE orders_catalog (
    order_code INT PRIMARY KEY,
    order_desc CHARACTER VARYING(50) NOT NULL,
    cost REAL NOT NULL
);

CREATE TABLE encounter (
    encounter_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    encounter_type CHARACTER VARYING(50) NOT NULL,

    CONSTRAINT FK_encounter FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_code INT NOT NULL,
    encounter_id INT NOT NULL,
    created_dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT FK_orders_catalog FOREIGN KEY (order_code) REFERENCES orders_catalog (order_code),
    CONSTRAINT FK_orders_encounter FOREIGN KEY (encounter_id) REFERENCES encounter(encounter_id)
);
--

INSERT INTO users(user_id, name) VALUES(1, 'Peter');
INSERT INTO users(user_id, name) VALUES(2, 'Charles');
INSERT INTO users(user_id, name) VALUES(3, 'Eva') ;
INSERT INTO users(user_id, name) VALUES(4, 'John');
INSERT INTO users(user_id, name) VALUES(5, 'Helene');

INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10000, 'Painting', 100.34);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10001, 'Painting', 214.11);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10002, 'Painting', 214.11);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10003, 'Spare part', 181.03);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10004, 'Sheet metal', 168.18);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10005, 'Sheet metal', 240.02);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10006, 'Sheet metal', 240.02);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10007, 'Electricity', 146.85);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10008, 'Spare part', 162.13);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10009, 'Electricity', 140.02);
INSERT INTO orders_catalog(order_code, order_desc, cost) VALUES (10010, 'Electricity', 180.02);

INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(100,1,'appointment');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(101,2,'appointment');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(102,3,'appointment');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(103,4,'urgent');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(104,5,'urgent');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(105,1,'appointment');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(106,2,'appointment');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(107,3,'waiting');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(108,4,'urgent');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(109,5,'waiting');
INSERT INTO encounter(encounter_id, user_id, encounter_type) VALUES(110,1,'waiting');

INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1000,10000,100,'2009-06-16 09:12');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1001,10001,101,'2009-06-16 09:12');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1002,10002,102,'2009-06-16 09:12');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1003,10003,103,'2009-12-03 09:50');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1004,10004,104,'2010-02-24 12:21');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1005,10005,105,'2010-03-27 23:54');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1006,10006,106,'2010-03-22 12:43');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1007,10007,107,'2010-02-24 12:21');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1008,10008,108,'2010-03-04 08:55');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1009,10009,109,'2010-03-06 09:25');
INSERT INTO orders(order_id, order_code, encounter_id, created_dt) VALUES (1010,10010,110,'2010-03-22 11:18');

And here is the query that I tried:

SELECT
  u.user_id,
  name,
  ROW_NUMBER() OVER (
    PARTITION BY u.user_id ORDER BY o.created_dt ASC
  ) AS position,
  SUM (c.cost) OVER (
    PARTITION BY o.encounter_id
  ) AS cost,
  SUM (cost) OVER (
    PARTITION BY u.user_id
  ) AS sum
FROM
  users u
INNER JOIN
  encounter e USING (user_id)
INNER JOIN
  orders o USING (encounter_id)
INNER JOIN
  orders_catalog c USING (order_code)
ORDER BY name, position;

But the output is not what I expect:

user_id name position cost sum
2 Charles 1 214.11 454.13
2 Charles 2 240.02 454.13
3 Eva 1 214.11 360.96002
3 Eva 2 146.85 360.96002
5 Helene 1 168.18 308.2
5 Helene 2 140.02 308.2
4 John 1 181.03 343.16
4 John 2 162.13 343.16
1 Peter 1 100.34 520.38
1 Peter 2 180.02 520.38
1 Peter 3 240.02 520.38

Since I would want something like:

user_id name position cost sum
... ... ... ... ...
1 Peter 1 100.34 100.34
1 Peter 2 180.02 280.36
1 Peter 3 240.02 520.38

Fiddle

Improve this question
0

1 Answer 1

Reset to default
1

The sum definition lacks an important component, the key detail that turns a regular window aggregate into a running total, or cumulative aggregate if you will – the ORDER BY clause. In this case, the ORDER BY criterion should be the same as the one used in the position definition, i.e. o.created_dt ASC.

Therefore, your complete query should look like this:

SELECT
  u.user_id,
  name,
  ROW_NUMBER() OVER (
    PARTITION BY u.user_id ORDER BY o.created_dt ASC
  ) AS position,
  SUM (c.cost) OVER (
    PARTITION BY o.encounter_id
  ) AS cost,
  SUM (cost) OVER (
    PARTITION BY u.user_id ORDER BY o.created_dt ASC
  ) AS sum
FROM
  users u
INNER JOIN
  encounter e USING (user_id)
INNER JOIN
  orders o USING (encounter_id)
INNER JOIN
  orders_catalog c USING (order_code)
ORDER BY name, position;

Output:

user_id name position cost sum
2 Charles 1 214.11 214.11
2 Charles 2 240.02 454.13
3 Eva 1 214.11 214.11
3 Eva 2 146.85 360.96
5 Helene 1 168.18 168.18
5 Helene 2 140.02 308.2
4 John 1 181.03 181.03
4 John 2 162.13 343.16
1 Peter 1 100.34 100.34
1 Peter 2 180.02 280.36
1 Peter 3 240.02 520.38

This solution can be tested and played with in a live demo at db<>fiddle.

Improve this answer
5
  • You should add ROWS UNBOUNDED PRECEDING because the default is RANGE
    – Charlieface
    Jun 2, 2021 at 20:50
  • @Charlieface: It probably wouldn't hurt if I did, though I'm not entirely convinced that I should.
    – Andriy M
    Jun 3, 2021 at 5:45
  • If one user had two orders at the same time then you would get incorrect results, because created_dt is not unique. See modified fiddle, Peter gets a different result (I have changed the last order) dbfiddle.uk/…
    – Charlieface
    Jun 3, 2021 at 8:40
  • @Charlieface: That issue has to do with the ORDER BY not being deterministic enough. My understanding is the OP's ORDER BY shouldn't produce ties in practice (two distinct orders involving the same user aren't supposed to have exactly the same timestamp), though it can in theory, as you've demonstrated. And this theoretical edge case would be better resolved, if needed, with a better ORDER BY, IMHO. For instance, you could change it to ORDER BY o.created_dt ASC, o.encounter_id ASC (in both cases), demo
    – Andriy M
    Jun 3, 2021 at 9:36
  • That may be, but the uniqueness issue is not obvious, and it's probably better to be explicit about the calculation. And I don't know about Postgres, but on SQL Server ROWS is always more efficient than RANGE
    – Charlieface
    Jun 3, 2021 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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