解析函式：對上一列的累積求和

使用分析函式，我希望列“總和”包含上一列的累積值。

但是我的程式碼得到了所有遭遇的總和。

這是用於測試的表格和數據：

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');

這是我嘗試過的查詢：

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;

但輸出不是我所期望的：

因為我想要類似的東西：

小提琴

該sum定義缺少一個重要組成部分，即將正常視窗聚合轉換為執行總計或累積聚合（如果您願意）的關鍵細節 -ORDER BY子句。在這種情況下，ORDER BY標準應該與position定義中使用的標準相同，即o.created_dt ASC。

因此，您的完整查詢應如下所示：

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;

輸出：

該解決方案可以在 db<>fiddle的現場展示中進行測試和使用。

引用自：https://dba.stackexchange.com/questions/292652