选择最新可用的 SQL 条目状态

Question

考虑这个 DDL：

CREATE TABLE cash_depot_state 
(
    id INTEGER NOT NULL PRIMARY KEY,
    date DATE,
    amount REAL,
    cash_depot_id INTEGER
);
    
INSERT INTO cash_depot_state (date, amount, cash_depot_id) 
VALUES (DATE('2022-03-02'), 382489, 5);
INSERT INTO cash_depot_state (date, amount, cash_depot_id) 
VALUES (DATE('2022-03-03'), 750, 2);
INSERT INTO cash_depot_state (date, amount, cash_depot_id) 
VALUES (DATE('2022-03-04'), 750, 3);
INSERT INTO cash_depot_state (date, amount, cash_depot_id) 
VALUES (DATE('2022-03-05'), 0, 5);

对于一系列日期，我需要选择所有现金仓库实际金额的总和：

1. 2022-03-01 - 无可用数据 - 预计 0
2. 2022-03-02 - 现金仓库 #5 已将其值更改为 382489 - 预计 382489
3. 2022-03-03 - 现金仓库 #2 已将其价值更改为 750 - 预计382489 + 750
4. 2022-03-03 - 现金仓库 #3 已将其值更改为 750 - 预期 382489 + 750 + 750
5. 2022-03-04 - 现金仓库 #5 已将其值更改为 0 - 预期 0 + 750 + 750

我的最佳尝试：http://sqlfiddle.com/#!5/94ad0d/1

但我不能弄清楚如何选出子组的获胜者

Answer 1

当您按cash_depot_id划分记录时，您可以将每个现金仓库的最新金额定义为行号为1的记录，并按日期降序排列它们：

SELECT 
    id,
    cash_depot_id,
    date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY cash_depot_id ORDER BY date DESC) rn
FROM
    cash_depot_state

这将突出显示表中的最新数据 - 所有相关行都将包含 rn = 1：

id	cash_depot_id	date	amount	rn
2	2	2022-03-03	750.0	1
3	3	2022-03-04	750.0	1
4	5	2022-03-05	0.0	1
1	5	2022-03-02	382489.0	2

现在您可以使用 WHERE 子句来过滤特定日期的记录，例如 WHERE 数据 <= “2022-03-05”：

SELECT
    SUM(amount) sum_amount
FROM
    (
        SELECT amount, ROW_NUMBER() OVER (PARTITION BY cash_depot_id ORDER BY date DESC) rn
        FROM   cash_depot_state 
        WHERE  date <= '2022-03-05'
    ) latest
WHERE
    rn = 1;

将返回 1500。

---

解决此问题的更传统方法是相关子查询：

SELECT
    SUM(amount) sum_amount
FROM
    cash_depot_state s
WHERE
    date = (
        SELECT MAX(date)
        FROM   cash_depot_state
        WHERE  date <= '2022-03-05' AND cash_depot_id = s.cash_depot_id
    )

或针对具体化子查询的联接：

SELECT
    SUM(amount) sum_amount
FROM
    cash_depot_state s
    INNER JOIN (
        SELECT   MAX(date) date, cash_depot_id
        FROM     cash_depot_state
        WHERE    date <= '2022-03-05' 
        GROUP BY cash_depot_id
    ) latest ON latest.cash_depot_id = s.cash_depot_id AND latest.date = s.date

在大型表中，这些可能比 ROW_NUMBER() 变体更快。 YMMV，进行测量。

---

涵盖 date、cash_depot_id 和 amount 的索引可帮助所有显示的方法：

CREATE INDEX ix_latest_cash ON cash_depot_state (date DESC, cash_depot_id ASC, amount);

---

要针对生成日历的 CTE 运行，可以使用上述任何方法可以作为子查询进行关联，

WITH RECURSIVE dates(date) AS (
  SELECT '2022-03-01'
  UNION ALL
  SELECT date(date, '+1 day') FROM dates WHERE date < DATE('now')
)
SELECT
    date,
    IFNULL(
        (
            -- any of the above approaches with `WHERE date <= dates.date`
        ), 0
    ) balance
FROM
    dates;

例如http://sqlfiddle.com/#!5/94ad0d/12