SQLite查询中的求和/分组滞后()结果

Question

我在SQLite数据库中有一个带有增量计数器的表。一个简化的例子是：

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "counters"(date TEXT PRIMARY KEY, hours INT);
INSERT INTO counters VALUES('2022-09-25',10360);
INSERT INTO counters VALUES('2022-09-26',10368);
INSERT INTO counters VALUES('2022-09-27',10370);
INSERT INTO counters VALUES('2022-09-28',10374);
INSERT INTO counters VALUES('2022-09-29',10380);
INSERT INTO counters VALUES('2022-09-30',10387);
INSERT INTO counters VALUES('2022-10-01',10387);
INSERT INTO counters VALUES('2022-10-02',10390);
INSERT INTO counters VALUES('2022-10-03',10394);
INSERT INTO counters VALUES('2022-10-04',10399);
INSERT INTO counters VALUES('2022-10-05',10405);
COMMIT;

我可以使用LAG函数查询两个日期之间计数器的差异，例如：

SELECT
    STRFTIME('%Y-%m', DATE(date, 'localtime')),
    hours - LAG(hours, 1) OVER(ORDER BY date)
FROM counters
ORDER BY date
LIMIT -1 OFFSET 1;

这给了我，对于上面的例子，

2022-09|8
2022-09|2
2022-09|4
2022-09|6
2022-09|7
2022-10|0
2022-10|3
2022-10|4
2022-10|5
2022-10|6

但我很难总结出所有这些价值，比如每个月。如果我做了这样的事情：

SELECT
    STRFTIME('%Y-%m', DATE(date, 'localtime')) AS month,
    SUM(hours - LAG(hours, 1) OVER(ORDER BY date)) AS hours
FROM counters
GROUP BY month
ORDER BY date
LIMIT -1 OFFSET 1;

我得到了

Parse error: misuse of window function LAG()
ATE(date, 'localtime')) AS month,     SUM(hours - LAG(hours, 1) OVER(ORDER BY 
                                    error here ---^

怎么了？我该怎么做？

谢谢你的帮助！

Answer 1

不能使用嵌套聚合/窗口函数。

所以使用您的第一个查询作为CTE，并将其聚合到外部查询中。

WITH
cte AS (
    SELECT STRFTIME('%Y-%m', DATE(date, 'localtime')) month,
           hours - LAG(hours, 1) OVER(ORDER BY date) hours
    FROM counters
    ORDER BY date
    LIMIT -1 OFFSET 1
)
SELECT
    month,
    SUM(hours) AS hours
FROM cte
GROUP BY month
ORDER BY month;