求助：编写SQL查询按季度计算账户的增减及留存状态

假设你的业务数据表名为business_data，包含核心字段：

• account_id：唯一标识账户的ID
• record_date：业务记录的日期（用于确定账户所在季度）

核心思路 #

要判断每个账户在当前季度的状态，需要对比当前季度和一年前的同季度（即当前季度往前推4个季度）的账户存在情况：

• 新增（1）：当前季度账户存在，且一年前的同季度不存在
• 留存（0）：当前季度账户存在，且一年前的同季度也存在
• 流失（-1）：当前季度账户不存在，但一年前的同季度存在

分步实现SQL #

1. 提取每个账户的所有存在季度

先整理出每个账户曾经出现过的所有季度，去重后得到账户-季度的唯一记录：

WITH account_quarters AS (
    SELECT DISTINCT
        account_id,
        DATE_TRUNC('quarter', record_date) AS quarter_start
    FROM business_data
),

2. 生成所有需要统计的季度范围

为了覆盖流失的情况（账户之前存在但后续季度消失），需要生成业务周期内的所有季度：

all_quarters AS (
    SELECT GENERATE_SERIES(
        (SELECT MIN(DATE_TRUNC('quarter', record_date)) FROM business_data),
        (SELECT MAX(DATE_TRUNC('quarter', record_date)) FROM business_data),
        INTERVAL '3 months'
    ) AS quarter_start
),

3. 生成所有账户-季度的组合（包括流失情况）

将所有账户和所有季度做笛卡尔积，再关联实际存在的记录，标记账户在该季度是否存在：

account_quarter_combinations AS (
    SELECT
        aq.account_id,
        q.quarter_start,
        CASE WHEN aq_q.account_id IS NOT NULL THEN 1 ELSE 0 END AS exists_in_quarter
    FROM (SELECT DISTINCT account_id FROM business_data) aq
    CROSS JOIN all_quarters q
    LEFT JOIN account_quarters aq_q
        ON aq.account_id = aq_q.account_id
        AND q.quarter_start = aq_q.quarter_start
),

4. 关联一年前的季度数据，计算状态

最后对比当前季度和一年前季度的存在状态，生成account_direction：

account_quarter_status AS (
    SELECT
        account_id,
        quarter_start,
        exists_in_quarter,
        LAG(exists_in_quarter, 4) OVER (PARTITION BY account_id ORDER BY quarter_start) AS exists_one_year_ago,
        CASE
            -- 新增：当前存在，一年前不存在
            WHEN exists_in_quarter = 1 AND (exists_one_year_ago IS NULL OR exists_one_year_ago = 0) THEN 1
            -- 留存：当前存在，一年前也存在
            WHEN exists_in_quarter = 1 AND exists_one_year_ago = 1 THEN 0
            -- 流失：当前不存在，一年前存在
            WHEN exists_in_quarter = 0 AND exists_one_year_ago = 1 THEN -1
            -- 其他情况（比如账户从未出现过，或连续多个季度不存在）：可根据需求调整，这里设为NULL
            ELSE NULL
        END AS account_direction
    FROM account_quarter_combinations
)

5. 最终查询结果

可以根据需求筛选或格式化输出：

SELECT
    account_id,
    TO_CHAR(quarter_start, 'YYYY-Q') AS quarter,
    CASE account_direction
        WHEN 1 THEN '新增'
        WHEN 0 THEN '留存'
        WHEN -1 THEN '流失'
        ELSE '无状态'
    END AS status,
    account_direction
FROM account_quarter_status
-- 可选：只筛选有状态的记录
WHERE account_direction IS NOT NULL
ORDER BY account_id, quarter_start;

不同数据库的适配说明 #

• MySQL：没有DATE_TRUNC和GENERATE_SERIES，可以用DATE_FORMAT(record_date, '%Y-%m-01')来获取季度起始，生成季度范围可以用递归CTE：

  WITH RECURSIVE all_quarters AS (
      SELECT MIN(DATE_FORMAT(record_date, '%Y-%m-01')) AS quarter_start FROM business_data
      UNION ALL
      SELECT DATE_ADD(quarter_start, INTERVAL 3 MONTH) FROM all_quarters
      WHERE quarter_start < (SELECT MAX(DATE_FORMAT(record_date, '%Y-%m-01')) FROM business_data)
  )
  

• SQL Server：用DATEADD(QUARTER, DATEDIFF(QUARTER, 0, record_date), 0)代替DATE_TRUNC，生成季度范围用递归CTE。

注意事项 #

• 如果你的表中每个账户每个季度有多条记录，DISTINCT是必须的，确保每个账户每个季度只统计一次存在状态。
• 对于业务周期第一年的季度，因为没有一年前的数据，这些季度的新增账户会被标记为1，符合“一年前不存在”的规则；而流失状态不会出现在第一年，因为没有更早的季度数据。
• 可以根据实际业务需求调整CASE语句中的逻辑，比如是否需要处理连续多个季度不存在的情况。

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