SQL时间序列移位查询：获取相邻时间戳实体并统计组合出现次数

你可以用SQL标准窗口函数LEAD()实现该需求，该函数的作用和pandas的shift(-1)效果一致，可以直接获取排序后当前行下一行的字段值。

假设你的表名为entity_records，完整查询语句如下：

WITH ranked_entity AS (
    SELECT
        entity_id,
        -- 按timestamp升序排序，取当前行下一行的entity_id
        LEAD(entity_id, 1) OVER (ORDER BY timestamp ASC) AS following_entity_id
    FROM entity_records
)
SELECT
    entity_id,
    following_entity_id,
    COUNT(*) AS count
FROM ranked_entity
-- 过滤掉没有下一行的最后一条记录
WHERE following_entity_id IS NOT NULL
GROUP BY entity_id, following_entity_id
ORDER BY entity_id, following_entity_id;

说明 #

• 该写法为标准SQL语法，所有支持窗口函数的主流数据库都可直接运行，包括MySQL 8.0+、PostgreSQL 9.4+、SQL Server、Oracle、SQLite 3.25+。
• 如果你的表名和示例不同，替换FROM后的表名即可。

内容的提问来源于stack exchange，提问作者heyarne