使用SQL查询实现每日单条card_number记录去重

核心需求是忽略transit_date的时间部分，对每个card_number按日期去重，最终统计月度内每日打卡人数。以下是不同数据库环境下的实现方案，兼顾性能和准确性：

分数据库实现代码 #

MySQL/MariaDB #

如果只需要去重后的每日打卡记录：

SELECT DISTINCT
    card_number,
    DATE(transit_date) AS punch_date
FROM
    your_table_name
WHERE
    transit_date BETWEEN '2024-01-01' AND '2024-01-31'; -- 替换为目标月份的起止日期

数据量较大时，GROUP BY方式性能更稳定：

SELECT
    card_number,
    DATE(transit_date) AS punch_date
FROM
    your_table_name
WHERE
    transit_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY
    card_number, DATE(transit_date);

SQL Server #

注意用< 次月第一天的方式避免包含次月0点的无效记录：

SELECT DISTINCT
    card_number,
    CAST(transit_date AS DATE) AS punch_date
FROM
    your_table_name
WHERE
    transit_date >= '2024-01-01' AND transit_date < '2024-02-01';

GROUP BY版本：

SELECT
    card_number,
    CAST(transit_date AS DATE) AS punch_date
FROM
    your_table_name
WHERE
    transit_date >= '2024-01-01' AND transit_date < '2024-02-01'
GROUP BY
    card_number, CAST(transit_date AS DATE);

PostgreSQL #

用类型转换提取日期部分：

SELECT DISTINCT
    card_number,
    transit_date::DATE AS punch_date
FROM
    your_table_name
WHERE
    transit_date >= '2024-01-01' AND transit_date < '2024-02-01';

GROUP BY版本：

SELECT
    card_number,
    transit_date::DATE AS punch_date
FROM
    your_table_name
WHERE
    transit_date >= '2024-01-01' AND transit_date < '2024-02-01'
GROUP BY
    card_number, transit_date::DATE;

直接统计每日打卡人数 #

如果不需要单独的去重记录，直接统计每日人数可以一步完成：
以MySQL为例：

SELECT
    DATE(transit_date) AS punch_date,
    COUNT(DISTINCT card_number) AS daily_punch_count
FROM
    your_table_name
WHERE
    transit_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY
    DATE(transit_date)
ORDER BY
    punch_date;

性能优化建议 #

针对每月30万+的数据量，建议：

• 给transit_date字段建立普通索引，能大幅加速日期范围的过滤
• 尽量避免在WHERE条件里对transit_date做函数运算（上面的写法已经规避了这个问题）

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