SQL统计两个不同时间段内重复出现的相同ID数量求助

需求说明 #

• 筛选单月满足「交易次数>1」规则的去重u_id
• 统计相邻两个月份中，同时满足规则的重叠u_id数量

现有实现逻辑 #

现有SQL可实现单月符合条件的ID总数统计，逻辑如下：

关联transactions、contacts及业务表，筛选指定月份的交易记录，按u_id分组后保留交易次数大于1的去重u_id，最终统计该类ID的总数量

现有代码：

with total as (
select distinct(transactions.u_id), count(*)
    from transactions 
    join contacts using (u_id) 
    join table using (contact_id)
    where transactions.when_created between '2020-06-01' AND '2020-06-30'
    group by transactions.u_id
    HAVING COUNT(*) > 1 
)
SELECT
   COUNT(*)
FROM
  total

实现方案 #

通用兼容写法 #

分别拉取两个月的符合规则ID集合，取交集计数即可，兼容所有主流SQL数据库：

WITH 
-- 6月符合规则的u_id集合
june_ids AS (
    SELECT DISTINCT transactions.u_id
    FROM transactions 
    JOIN contacts USING (u_id) 
    -- 注意：table是SQL保留关键字，此处请替换为实际业务表名
    JOIN your_business_table USING (contact_id)
    WHERE transactions.when_created BETWEEN '2020-06-01' AND '2020-06-30'
    GROUP BY transactions.u_id
    HAVING COUNT(*) > 1 
),
-- 7月符合规则的u_id集合
july_ids AS (
    SELECT DISTINCT transactions.u_id
    FROM transactions 
    JOIN contacts USING (u_id) 
    JOIN your_business_table USING (contact_id)
    WHERE transactions.when_created BETWEEN '2020-07-01' AND '2020-07-31'
    GROUP BY transactions.u_id
    HAVING COUNT(*) > 1 
)
-- 统计重叠ID数量
SELECT COUNT(*) AS overlap_id_count
FROM june_ids
WHERE u_id IN (SELECT u_id FROM july_ids);

INTERSECT简化写法 #

若使用的数据库支持INTERSECT语法，可使用更简洁的写法：

WITH 
june_ids AS (
    SELECT DISTINCT transactions.u_id
    FROM transactions 
    JOIN contacts USING (u_id) 
    JOIN your_business_table USING (contact_id)
    WHERE transactions.when_created BETWEEN '2020-06-01' AND '2020-06-30'
    GROUP BY transactions.u_id
    HAVING COUNT(*) > 1 
),
july_ids AS (
    SELECT DISTINCT transactions.u_id
    FROM transactions 
    JOIN contacts USING (u_id) 
    JOIN your_business_table USING (contact_id)
    WHERE transactions.when_created BETWEEN '2020-07-01' AND '2020-07-31'
    GROUP BY transactions.u_id
    HAVING COUNT(*) > 1 
)
SELECT COUNT(*) AS overlap_id_count
FROM (
    SELECT u_id FROM june_ids
    INTERSECT
    SELECT u_id FROM july_ids
) t;

使用说明 #

• 两种写法效果完全一致，核心逻辑和你原有单月统计逻辑对齐，仅新增了下一个月的ID集合拉取和交集计算
• 需要统计其他月份的重叠ID时，仅修改两个CTE中的时间筛选区间即可
• 执行前请将your_business_table替换为实际业务表的真实名称

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