SQL基于GroupBy条件新增列：判断单月内事件是否重复并标记布尔值0/1

核心逻辑为按事件名称、事件对应的自然月两个维度分组统计出现次数，次数大于1则标记为1，否则标记为0。

修正后测试数据构建SQL #

原测试数据存在列名不统一的问题，修正后的建表及数据插入语句如下：

-- 创建测试表
CREATE TABLE test (
    Event VARCHAR(20),
    Event_date DATE
);

-- 插入测试数据
INSERT INTO test (Event, Event_date)
VALUES
('Turn','2021-01-24'),
('Turn','2021-01-25'),
('Turn','2021-03-14'),
('Turn','2021-04-21'),
('Stop','2020-05-20'),
('Stop','2021-05-20'),
('Stop','2021-05-24'),
('Start','2021-05-23'),
('Start','2021-05-24'),
('Start','2021-05-24');

功能实现查询SQL #

通用版本（适配SQL Server、PostgreSQL等数据库） #

使用窗口函数按维度分组计数后做判断即可得到结果：

SELECT 
    Event,
    Event_date,
    CASE WHEN COUNT(*) OVER (PARTITION BY Event, DATE_TRUNC('month', Event_date)) > 1 
         THEN 1 
         ELSE 0 
    END AS "Returning?"
FROM test;

MySQL适配版本 #

MySQL环境下将年月提取逻辑替换为DATE_FORMAT函数即可：

SELECT 
    Event,
    Event_date,
    CASE WHEN COUNT(*) OVER (PARTITION BY Event, DATE_FORMAT(Event_date, '%Y-%m')) > 1 
         THEN 1 
         ELSE 0 
    END AS `Returning?`
FROM test;

执行上述代码后即可得到需要的输出结果，同一事件同一自然月下的所有行都会统一标记重复状态。

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