mariadb - MariaDB 总共运行 N 并且行不包括在其计算中

Question

我有一个表，其中包含amt和created（时间戳）。

1. 我正在尝试计算amt高达N
2. 获取所有未包含在计算中的行，导致总和为N

我正在代码中执行此操作，但想知道是否有一种方法可以使用 SQL 获取这些，最好是在一个查询中。

环顾四周，很容易找到计算运行总数的示例，例如 https://stackoverflow.com/a/1290936/400048，但找到运行总数的例子较少N，然后实际上只返回不参与计算 N 的行。

标签： mariadbh2cumulative-sum

Answer 1

您可以使用SUM聚合函数的窗口版本来获取每行的运行总计。

CREATE TABLE TEST (ID BIGINT PRIMARY KEY, AMT INT, CREATED TIMESTAMP);

INSERT INTO TEST VALUES
(1, 1, TIMESTAMP '2000-01-01 00:00:00'),
(2, 2, TIMESTAMP '2000-01-02 00:00:00'),
(3, 1, TIMESTAMP '2000-01-03 00:00:00'),
(4, 3, TIMESTAMP '2000-01-04 00:00:00'),
(5, 5, TIMESTAMP '2000-01-05 00:00:00'),
(6, 1, TIMESTAMP '2000-01-07 00:00:00');

SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST ORDER BY CREATED;

> ID AMT RT CREATED
> -- --- -- -------------------
> 1  1   1  2000-01-01 00:00:00
> 2  2   3  2000-01-02 00:00:00
> 3  1   4  2000-01-03 00:00:00
> 4  3   7  2000-01-04 00:00:00
> 5  5   12 2000-01-05 00:00:00
> 6  1   13 2000-01-07 00:00:00

然后，您可以使用 H2 中的非标准QUALIFY子句或子查询（在 MariaDB 和 H2 中）来过滤掉低于限制的行。

如果N是运行总限制，并且“计算中未包含的行数”是指超出限制的行数，则查询将如下所示：

-- Simple non-standard query for H2
SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST
    QUALIFY RT > 10 ORDER BY CREATED;
-- Equivalent standard query with subquery for MariaDB, H2, and many others
SELECT * FROM (
    SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST
) T WHERE RT > 10 ORDER BY CREATED;

> ID AMT RT CREATED
> -- --- -- -------------------
> 5  5   12 2000-01-05 00:00:00
> 6  1   13 2000-01-07 00:00:00

RT - AMT在第一行中，这里是所有先前行的总和。如果您愿意，可以单独选择它：

-- Non-standard query for H2
SELECT SUM(AMT) OVER (ORDER BY CREATED) RT FROM TEST
    QUALIFY RT < 10 ORDER BY CREATED DESC FETCH FIRST ROW ONLY;
-- Non-standard query for MariaDB or H2
SELECT RT FROM (
    SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST
) T WHERE RT < 10 ORDER BY CREATED DESC LIMIT 1;
-- Standard query for H2 and others (but not for MariaDB)
SELECT RT FROM (
    SELECT ID, AMT, SUM(AMT) OVER (ORDER BY CREATED) RT, CREATED FROM TEST
) T WHERE RT < 10 ORDER BY CREATED DESC FETCH FIRST ROW ONLY;

> RT
> --
> 7

如果你的意思是别的，QUALIFY或WHERE标准会有所不同。