Analytical functions: enumerate, order

All we need is an easy explanation of the problem, so here it is.

I have added 2 columns through analytical functions with the next information:

  1. A column -position- that enumerates the encounters of each user, ordered chronologically.

  2. A column -cost- containing the sum of the costs of the different services created for that encounter.

But I could not solve the next issues:

  • The column ‘cost’ is not sorted chronologically for each user.
  • At column ‘position’, it shows the total number of encounters, but I would
    that it were from 1 until the total.

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,
  COUNT (e.encounter_type) OVER (
    PARTITION BY u.user_id
  ) AS position,
  SUM (c.cost) OVER (
    PARTITION BY o.encounter_id ORDER BY o.created_dt DESC
  ) AS cost
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 user_id;

But the output is not what I expect:

1   "Peter" 3   180.02
1   "Peter" 3   240.02
1   "Peter" 3   100.34
2   "Charles"   2   240.02
2   "Charles"   2   214.11
3   "Eva"   2   214.11
3   "Eva"   2   146.85
4   "John"  2   181.03
4   "John"  2   162.13
5   "Helene"    2   168.18
5   "Helene"    2   140.02

Since I would want something like:

1   "Peter" 1   100.34
1   "Peter" 2   240.02
1   "Peter" 3   180.02
...

FIDDLE

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

ROW_NUMBER is a btter choice, for what you want

SELECT
  u.user_id,
  name,
  ROW_NUMBER() OVER (
    PARTITION BY u.user_id
  ) AS position,
  SUM (c.cost) OVER (
    PARTITION BY o.encounter_id ORDER BY o.created_dt DESC
  ) AS cost
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 user_id;
user_id | name    | position | cost  
------: | :------ | -------: | :-----
      1 | Peter   |        1 | 180.02
      1 | Peter   |        2 | 240.02
      1 | Peter   |        3 | 100.34
      2 | Charles |        1 | 240.02
      2 | Charles |        2 | 214.11
      3 | Eva     |        1 | 214.11
      3 | Eva     |        2 | 146.85
      4 | John    |        1 | 181.03
      4 | John    |        2 | 162.13
      5 | Helene  |        1 | 168.18
      5 | Helene  |        2 | 140.02

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply