sql - 如何计算运行 SUM？

Question

我如何得到一个列是另一列前面值的总和？

Answer 1

从 SQLite 3.25.0 开始，自 2018 年 9 月 15 日起，window functions及其关键字 OVER 均受支持。您的问题的答案现在很简单:

SELECT Country, Gdp, SUM(Gdp) OVER (ROWS UNBOUNDED PRECEDING)
FROM CountryGdp;

这是执行您请求的最小查询，但它没有定义任何顺序，因此这是一种更合适的执行方式。

SELECT
    Country,
    Gdp,
    SUM(Gdp) OVER (
        ORDER BY Country -- Window ordering (not necessarily the same as result ordering!)
        ROWS BETWEEN -- Window for the SUM includes these rows:
            UNBOUNDED PRECEDING -- all rows before current one in window ordering
            AND CURRENT ROW -- up to and including current row.
        ) AS RunningTotal
FROM CountryGdp
ORDER BY Country;

无论如何，查询应该在 O(N) 时间内运行。