我可以在使用数据库后激活 PITR 吗？

Question

PakLui

Asked: 2021-06-03 00:20:43 +0800 CST2021-06-03 00:20:43 +0800 CST 2021-06-03 00:20:43 +0800 CST

解析函数：对上一列的累积求和

772

使用分析函数，我希望列“总和”包含上一列的累积值。

但是我的代码得到了所有遭遇的总和。

这是用于测试的表格和数据：

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;

但输出不是我所期望的：

用户身份	姓名	位置	成本	和
2	查尔斯	1	214.11	454.13
2	查尔斯	2	240.02	454.13
3	伊娃	1	214.11	360.96002
3	伊娃	2	146.85	360.96002
5	海伦	1	168.18	308.2
5	海伦	2	140.02	308.2
4	约翰	1	181.03	343.16
4	约翰	2	162.13	343.16
1	彼得	1	100.34	520.38
1	彼得	2	180.02	520.38
1	彼得	3	240.02	520.38

因为我想要类似的东西：

用户身份	姓名	位置	成本	和
...	...	...	...	...
1	彼得	1	100.34	100.34
1	彼得	2	180.02	280.36
1	彼得	3	240.02	520.38

小提琴

1 个回答

Voted

Andriy M · Answer 1 · 2021-06-03T01:29:20+08:00

该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;

输出：

用户身份	姓名	位置	成本	和
2	查尔斯	1	214.11	214.11
2	查尔斯	2	240.02	454.13
3	伊娃	1	214.11	214.11
3	伊娃	2	146.85	360.96
5	海伦	1	168.18	168.18
5	海伦	2	140.02	308.2
4	约翰	1	181.03	181.03
4	约翰	2	162.13	343.16
1	彼得	1	100.34	100.34
1	彼得	2	180.02	280.36
1	彼得	3	240.02	520.38

该解决方案可以在 db<>fiddle的现场演示中进行测试和使用。

解析函数：对上一列的累积求和

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

解析函数：对上一列的累积求和

1 个回答

相关问题