如何在可接受的时间窗口delta增量上构造一个窗口的聚合查询

Question

我将用户事件记录到一些 sqlite 表中。

目前，这些表正在计数1分钟的桶上的交互作用，并在插入时进行了更新。

表的架构就是这样：像

CREATE TABLE table1(
                window DATETIME,
                counter INTEGER NOT NULL DEFAULT 1,
                value INTEGER NOT NULL DEFAULT 0,
                file_id INTEGER NOT NULL,
                PRIMARY KEY(window,file_id)
            );

CREATE TABLE table2(
                window DATETIME,
                counter INTEGER NOT NULL DEFAULT 1,
                value INTEGER NOT NULL DEFAULT 0,
                file_id INTEGER NOT NULL,
                PRIMARY KEY(window,file_id)
            );

CREATE TABLE files(name TEXT PRIMARY KEY NOT NULL ON CONFLICT REPLACE DEFAULT 'NA', touched DATE DEFAULT (date('now')));

这样执行了表格上表的插入：

INSERT OR REPLACE INTO files(ROWID, name, touched)
    VALUES(
        (select ROWID FROM files WHERE name=IFNULL(?1,'NA') UNION SELECT max(ROWID) + 1 FROM files limit 1),
        IFNULL(?1,'NA'), 
        date()
    )

INSERT INTO table1(window,file_id,value)
        VALUES(
            datetime(strftime('%s', 'now') - (strftime('%s', 'now') % 60), 'unixepoch', 'localtime'),
            (SELECT rowid FROM files WHERE name=IFNULL(:fname,'NA')),
            :delta
        ) ON CONFLICT(window,file_id) DO
            UPDATE SET count = count + 1, value = value + :delta

一些示例数据看起来像这样：

select * from table1

window               file_id                 counter      value
-------------------  -------------------  -----------  ----------
2022-06-13 10:26:00  1                        29           3         
2022-06-13 10:27:00  2                        99           7
2022-06-13 10:30:00  3                        1            22
2022-06-13 10:31:00  4                        1            22
2022-06-13 10:34:00  1                        1            22
2022-06-13 10:39:00  1                        1            22
2022-06-13 10:40:00  1                        1            22
2022-06-13 10:53:00  1                        1            22
              

我想做的是基于活动会话汇总“计数器”和“值”列。只要先前的活动（行窗口）在当前活动的10分钟内（行窗口分隔少于10分钟），就可以将会话视为活动。

在此示例中，第1-7行属于同一会话，因为每个活动（行窗口）距离先前的活动不到10分钟。最后一行定义了一个新会话，因为是在上一项活动之后的13分钟（13分钟=（10:53-10：40）大于10分钟）。

此查询应该同时从表2汇总（它们是同时记录的，但反映了不同的数据），

我所需的输出应包括这样的数据（按照第1-7行和第8行），

session 1:
started at: 2022-06-13 10:26:00
ended at:  2022-06-13 10:40:00
counter_sum: 22 + 99 + 1 + 1 + 1 + ... +1 (the sum of count column)
value_sum: 3 + 7 + 22 + 22 .... + 22 (the sum of value column)

session 2:
start at: 2022-06-13 10:53:00
finished at: 2022-06-13 10:53:00 (since no data after this assume same time)
count_sum: 1
value_sum: 22

您将如何构造此类型的窗口查询？

Answer 1

您需要一列标记有效组的列，

SELECT *, SUM(flag) OVER (ORDER BY window) grp
FROM (
  SELECT *, strftime('%s', window) - COALESCE(strftime('%s', LAG(window) OVER (ORDER BY window)), 0) > 600 flag
  FROM table1
);

然后可以基于该列进行汇总，例如：

WITH cte AS (
  SELECT *, SUM(flag) OVER (ORDER BY window) grp
  FROM (
    SELECT *, strftime('%s', window) - COALESCE(strftime('%s', LAG(window) OVER (ORDER BY window)), 0) > 600 flag
    FROM table1
  )
)
SELECT grp,
       MIN(window) min_window,
       MAX(window) max_window,
       SUM(counter) sum_counter,
       SUM(value) sum_value
FROM cte
GROUP BY grp; 

   

请参阅 demo 。