如何用单条SQL按用户ID和7天周期动态分组查询多表数据

完全可以通过单条SQL实现该需求，核心思路是先通过递归CTE为每个用户生成专属的7天周期序列，再基于生成的周期关联业务表做统计即可，具体实现代码如下：

WITH RECURSIVE user_date_ranges AS (
    -- 初始化每个用户的第一个7天周期，起始为开户日
    SELECT
        id AS user_id,
        "onboardedAt" AS user_opened_account_at,
        "closedAt" AS user_closed_account_at,
        "onboardedAt"::date AS start_range_date,
        ("onboardedAt" + INTERVAL '7 days')::date AS end_range_date
    FROM "Users"
    UNION ALL
    -- 递归生成后续周期，直到周期起始超过销户日期停止
    SELECT
        udr.user_id,
        udr.user_opened_account_at,
        udr.user_closed_account_at,
        (udr.start_range_date + INTERVAL '7 days')::date,
        (udr.end_range_date + INTERVAL '7 days')::date
    FROM user_date_ranges udr
    WHERE udr.start_range_date < udr.user_closed_account_at::date
)
SELECT
    udr.user_id,
    udr.user_opened_account_at,
    udr.user_closed_account_at,
    udr.start_range_date,
    udr.end_range_date,
    COUNT(t.id) AS tx_count,
    -- 取当前周期内该用户的最后一次行为
    (
        SELECT action 
        FROM "UserActions" ua 
        WHERE ua."userId" = udr.user_id 
          AND ua."createdAt" >= udr.start_range_date 
          AND ua."createdAt" < udr.end_range_date 
        ORDER BY ua."createdAt" DESC 
        LIMIT 1
    ) AS last_user_action
FROM user_date_ranges udr
LEFT JOIN "Transactions" t 
    ON t."userId" = udr.user_id 
    AND t."createdAt" >= udr.start_range_date 
    AND t."createdAt" < udr.end_range_date
GROUP BY udr.user_id, udr.user_opened_account_at, udr.user_closed_account_at, udr.start_range_date, udr.end_range_date
ORDER BY udr.user_id, udr.start_range_date;

逻辑说明 #

• 递归CTE user_date_ranges 会为每个用户独立生成周期，起始为用户自己的开户时间，每向后递推7天生成一个新周期，直到周期起始时间超过用户销户时间为止，完全符合不同用户周期各不相同的规则。
• 交易统计、最后用户行为获取的逻辑和原有查询保持一致，只是把原来写死的固定时间范围替换成了动态生成的用户专属周期。
• 如果你使用的数据库不支持递归CTE，也可以提前构造一个数字辅助表，通过数字乘7天的方式批量生成周期，逻辑本质相同。

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