如何用单语句统计2023年1-6月存续ID的ctc总和并查询半年前活跃ID

问题1：查询六个月前的活跃ID #

如果“活跃ID”指在六个月前的日期有记录的ID，可根据目标日期筛选并去重：

-- 以2023年6月1日为基准，查询六个月前（2023年1月1日）的活跃ID
SELECT DISTINCT ID
FROM your_table
WHERE date = '2023-01-01';

-- 若需动态获取当前日期的六个月前记录（以MySQL为例）
SELECT DISTINCT ID
FROM your_table
WHERE date = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

问题2：统计2023年1月1日和6月1日都存在的ID的ctc总和 #

可通过两种方案实现，以下是具体语句：

方案1：自连接 #

SELECT SUM(t.value) AS ctc_total
FROM your_table t
-- 关联同一ID在2023-06-01的记录，筛选出同时存在的ID
JOIN your_table t6 ON t.ID = t6.ID
WHERE t.date = '2023-01-01'
  AND t6.date = '2023-06-01';

方案2：子查询筛选 #

SELECT SUM(value) AS ctc_total
FROM your_table
WHERE date = '2023-01-01'
  -- 仅保留在2023-06-01也有记录的ID
  AND ID IN (
    SELECT ID
    FROM your_table
    WHERE date = '2023-06-01'
  );

你的尝试语句问题分析 #

1. 第一条语句：

   • 存在聚合函数嵌套（SUM内嵌套SUM），违反SQL语法规范。
   • LAG(ID) OVER (PARTITION BY GP ORDER BY Date- 5 MONTH)逻辑错误：分区字段GP与ID追踪需求无关，且Date- 5 MONTH不符合标准SQL日期运算语法（不同数据库需用对应函数，如DATE_SUB(date, INTERVAL 5 MONTH)），无法正确关联六个月前后的记录。

2. 第二条语句：

   • 未定义字段eDate，直接导致语法错误。
   • 分区逻辑partition by id, case when date = eDate- 5 MONTH then 1 else 0 end无实际意义，无法将同一ID的两个日期记录关联，自然无法得到正确结果。

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