PostgreSQL:如何在给定日期范围内每天为每个帐户选择最后余额

Question

我正在运行PostgreSQL 9.3，有一个看起来像这样的表：

     entry_date      | account_id | balance
---------------------+------------+---------
 2016-02-01 00:00:00 |        123 |     100
 2016-02-01 06:00:00 |        123 |     200
 2016-02-01 12:00:00 |        123 |     300
 2016-02-01 18:00:00 |        123 |     250
 2016-02-01 00:00:00 |        456 |     400
 2016-02-01 06:00:00 |        456 |     300
 2016-02-01 12:00:00 |        456 |     200
 2016-02-01 18:00:00 |        456 |     299
 2016-02-02 00:00:00 |        123 |     250
 2016-02-02 06:00:00 |        123 |     300
 2016-02-02 12:00:00 |        123 |     400
 2016-02-02 18:00:00 |        123 |     450
 2016-02-02 00:00:00 |        456 |     299
 2016-02-02 06:00:00 |        456 |     200
 2016-02-02 12:00:00 |        456 |     100
 2016-02-02 18:00:00 |        456 |       0
(16 rows)

我的目标是在给定日期范围内每天检索每个帐户的最终余额。所以我想要的结果是：

     entry_date      | account_id | balance
---------------------+------------+---------
 2016-02-01 18:00:00 |        123 |     250
 2016-02-01 18:00:00 |        456 |     299
 2016-02-02 18:00:00 |        123 |     450
 2016-02-02 18:00:00 |        456 |       0
(4 rows)

请注意，我示例中的时间戳比现实中的要整齐得多......我不能总是把18：00作为每天的最后一时间。

我将如何编写此 SQL 查询？

我尝试了这个的变体：

SELECT max(entry_date), account_id, max(balance)
FROM ledger
WHERE entry_date BETWEEN '2016-02-01'::timestamp AND '2016-02-02'::timestamp
GROUP BY account_id, entry_date;

下面是架构：

CREATE TABLE ledger (
  entry_date    timestamp(3),
  account_id    int,
  balance       int
);
INSERT INTO ledger VALUES ('2016-02-01T00:00:00.000Z', 123, 100);
INSERT INTO ledger VALUES ('2016-02-01T06:00:00.000Z', 123, 200);
INSERT INTO ledger VALUES ('2016-02-01T12:00:00.000Z', 123, 300);
INSERT INTO ledger VALUES ('2016-02-01T18:00:00.000Z', 123, 250);
INSERT INTO ledger VALUES ('2016-02-01T00:00:00.000Z', 456, 400);
INSERT INTO ledger VALUES ('2016-02-01T06:00:00.000Z', 456, 300);
INSERT INTO ledger VALUES ('2016-02-01T12:00:00.000Z', 456, 200);
INSERT INTO ledger VALUES ('2016-02-01T18:00:00.000Z', 456, 299);
INSERT INTO ledger VALUES ('2016-02-02T00:00:00.000Z', 123, 250);
INSERT INTO ledger VALUES ('2016-02-02T06:00:00.000Z', 123, 300);
INSERT INTO ledger VALUES ('2016-02-02T12:00:00.000Z', 123, 400);
INSERT INTO ledger VALUES ('2016-02-02T18:00:00.000Z', 123, 450);
INSERT INTO ledger VALUES ('2016-02-02T00:00:00.000Z', 456, 299);
INSERT INTO ledger VALUES ('2016-02-02T06:00:00.000Z', 456, 200);
INSERT INTO ledger VALUES ('2016-02-02T12:00:00.000Z', 456, 100);
INSERT INTO ledger VALUES ('2016-02-02T18:00:00.000Z', 456, 0);

这是一个SQL小提琴：http://sqlfiddle.com/#!15/56886

提前感谢！

Answer 1

您可以将

ROW_NUMBER与PARTITION BY一起使用：

SELECT entry_date, account_id, balance
FROM (
  SELECT entry_date, account_id, balance, 
         ROW_NUMBER() OVER (PARTITION BY account_id, entry_date::date 
                            ORDER BY entry_date DESC) AS rn
  FROM ledger
  WHERE entry_date BETWEEN '2016-02-01'::timestamp AND '2016-02-02'::timestamp) AS t
WHERE t.rn = 1

PARTITION BY每天创建 account_id 个值的切片，因为entry_date在转换为日期值后也在同一子句中使用。每个切片按entry_date降序排序，因此ROW_NUMBER = 1对应于当天的最后一条记录。

在这里演示

Answer 2

在Postgres中，我认为最简单的方法是distinct on：

SELECT DISTINCT ON (account_id) l.*
FROM ledger l
WHERE entry_date BETWEEN '2016-02-01'::timestamp AND '2016-02-02'::timestamp
ORDER BY account_id, entry_date DESC;

DISTINCT ON根据ORDER BY中的键对数据进行排序。 然后，它选择ON列表中键的唯一值，选择遇到的第一个值。

编辑：

完全相同的想法适用于一天的一条记录 - 我只是误读了原始要求：

SELECT DISTINCT ON (account_id, date_trunc('day', entry_date)) l.*
FROM ledger l
WHERE entry_date BETWEEN '2016-02-01'::timestamp AND '2016-02-02'::timestamp
ORDER BY account_id, date_trunc('day', entry_date), entry_date DESC;