Rockset SQL查询：关联lastSession=11行与同地点最新lastSession=1的createdAt

数据集 #

需求 #

筛选出lastSession=11的行，将这些行的createdAt列替换为同一地点下lastSession=1的记录中早于当前lastSession=11记录createdAt日期的最大createdAt值。

预期结果 #

当前尝试的SQL #

SELECT
    f.id,
    CAST(createdAt AS DATE) as createdAt,
    CAST(createdAt AS DATE) as completedAt,
    f.location
FROM
    mytest f
        INNER JOIN (
        SELECT
            id,
            MAX( createdAt ) mid
        FROM mytest
        GROUP BY
            id
    ) g ON f.id = g.id
WHERE
        f.userId = 1
  AND f.lastSession = 11
  AND f.createdAt between CAST("2020-01-01" AS DATE) AND CAST("2023-12-31" AS DATE)

问题说明 #

当前查询未得到正确结果，返回的createdAt并非目标值。尝试过LEFT JOIN、子查询等变体仍未解决。数据包含多用户，需指定userId和createdAt日期范围，确保数据来自指定用户且在日期范围内。

背景：每条记录代表用户完成的一个活动，构成某地点的会话序列（会话从1开始到11结束），用户可能重置会话，产生多个lastSession=1的记录，需获取对应lastSession=11之前最近的lastSession=1的createdAt。使用Rockset SQL。

解决方案 #

方案一：关联子查询实现 #

SELECT
    f.id,
    f.userId,
    f.lastSession,
    f.location,
    -- 子查询获取符合条件的最大createdAt
    (SELECT MAX(CAST(m.createdAt AS DATE))
     FROM mytest m
     WHERE m.userId = f.userId
       AND m.location = f.location
       AND m.lastSession = 1
       AND CAST(m.createdAt AS DATE) < CAST(f.createdAt AS DATE)) AS createdAt,
    CAST(f.completedAt AS DATE) AS completedAt
FROM mytest f
WHERE f.userId = 1
  AND f.lastSession = 11
  AND CAST(f.createdAt AS DATE) BETWEEN CAST('2020-01-01' AS DATE) AND CAST('2023-12-31' AS DATE);

思路解释

1. 主查询筛选出目标用户（userId=1）、lastSession=11且在指定日期范围内的记录。
2. 关联子查询针对每条主查询的记录，匹配同一用户、同一地点的lastSession=1记录，并且要求这些记录的createdAt早于当前lastSession=11记录的createdAt，取其中最大的日期作为新的createdAt。
3. 保留原记录的completedAt值，其他字段直接沿用主查询的内容。

方案二：窗口函数+CTE实现（适合大数据量场景） #

WITH session1_data AS (
    SELECT
        userId,
        location,
        CAST(createdAt AS DATE) AS session1_date,
        -- 按用户、地点分组，取每个分组内的session1日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY userId, location ORDER BY CAST(createdAt AS DATE) DESC) AS rn
    FROM mytest
    WHERE lastSession = 1
)
SELECT
    f.id,
    f.userId,
    f.lastSession,
    f.location,
    s.session1_date AS createdAt,
    CAST(f.completedAt AS DATE) AS completedAt
FROM mytest f
JOIN session1_data s
    ON f.userId = s.userId
    AND f.location = s.location
    AND s.session1_date < CAST(f.createdAt AS DATE)
WHERE f.userId = 1
  AND f.lastSession = 11
  AND CAST(f.createdAt AS DATE) BETWEEN CAST('2020-01-01' AS DATE) AND CAST('2023-12-31' AS DATE)
-- 取每个lastSession=11记录对应的最近session1日期
QUALIFY ROW_NUMBER() OVER (PARTITION BY f.id ORDER BY s.session1_date DESC) = 1;

思路解释

1. 用CTE预处理所有lastSession=1的记录，按用户和地点分组并按日期倒序排名，便于快速定位最新的符合条件的日期。
2. 将预处理后的session1数据与lastSession=11的记录关联，筛选出日期早于目标记录的session1数据。
3. 通过QUALIFY子句为每个lastSession=11的记录筛选出最新的符合条件的session1日期。

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