如何在MySQL中按日历双周统计指定日期范围内的重复记录

Absolutely, you can handle this entirely in SQL—no need to export to Excel and mess with pivot tables! Let’s break down how to implement this based on your definition of duplicate records: records where name, med, and directions are identical, and their rec_date values are within 14 days of each other.

Option 1: Count duplicates within fixed 14-day (biweekly) intervals #

This approach groups records into 14-day calendar periods (starting from a fixed date like the start of the year) and counts how many times each name/med/directions combination appears in each interval. Since records in the same 14-day window naturally have dates within 14 days of each other, this fits your duplicate criteria perfectly.

Here’s the SQL (tailored for SQL Server, matching your existing syntax):

WITH biweek_intervals AS (
    SELECT
        rec_date,
        name,
        med,
        directions,
        -- Calculate the start of the 14-day interval for each record
        DATEADD(day, (DATEDIFF(day, '2022-01-01', rec_date) / 14) * 14, '2022-01-01') AS biweek_start,
        -- Calculate the end of the interval for clarity
        DATEADD(day, 13, DATEADD(day, (DATEDIFF(day, '2022-01-01', rec_date) / 14) * 14, '2022-01-01')) AS biweek_end
    FROM datatable
    WHERE rec_date >= {d '2022-01-20'} AND rec_date <= {d '2022-06-22'}
)
SELECT
    biweek_start,
    biweek_end,
    name,
    med,
    directions,
    COUNT(*) AS duplicate_count
FROM biweek_intervals
GROUP BY biweek_start, biweek_end, name, med, directions
HAVING COUNT(*) >= 2
ORDER BY biweek_start, name, med;

What this returns for your sample data:

• A row for mr blogs/paracetamol/ONE tablet FOUR times a day in the interval 2022-06-20 to 2022-07-03 with a duplicate_count of 2.
• A row for mr blogs/paracetamol/TWO tablets FOUR times a day in the interval 2022-02-01 to 2022-02-14 with a duplicate_count of 2.

If you want a summary of total duplicate records per biweek (instead of per group), wrap the query in another CTE:

WITH biweek_intervals AS (
    SELECT
        rec_date,
        name,
        med,
        directions,
        DATEADD(day, (DATEDIFF(day, '2022-01-01', rec_date) / 14) * 14, '2022-01-01') AS biweek_start,
        DATEADD(day, 13, DATEADD(day, (DATEDIFF(day, '2022-01-01', rec_date) / 14) * 14, '2022-01-01')) AS biweek_end
    FROM datatable
    WHERE rec_date >= {d '2022-01-20'} AND rec_date <= {d '2022-06-22'}
),
duplicate_groups AS (
    SELECT
        biweek_start,
        biweek_end,
        COUNT(*) AS group_record_count
    FROM biweek_intervals
    GROUP BY biweek_start, biweek_end, name, med, directions
    HAVING COUNT(*) >= 2
)
SELECT
    biweek_start,
    biweek_end,
    SUM(group_record_count) AS total_duplicate_records
FROM duplicate_groups
GROUP BY biweek_start, biweek_end
ORDER BY biweek_start;

Option 2: Account for duplicates that cross biweekly intervals #

If you need to include pairs of records that are within 14 days but fall into different 14-day intervals (e.g., one on day 14 of an interval, another on day 1 of the next), use a self-join to identify these cross-interval duplicates, then group them by interval.

WITH duplicate_record_pairs AS (
    SELECT
        da1.rec_date AS record_date,
        da1.name,
        da1.med,
        da1.directions,
        -- Group by the interval of the first record in the pair
        DATEADD(day, (DATEDIFF(day, '2022-01-01', da1.rec_date) / 14) * 14, '2022-01-01') AS biweek_start
    FROM datatable da1
    JOIN datatable da2
        ON da1.name = da2.name
        AND da1.med = da2.med
        AND da1.directions = da2.directions
        AND da1.rec_date < da2.rec_date -- Avoid duplicate pairs (a-b vs b-a)
        AND DATEDIFF(day, da1.rec_date, da2.rec_date) <= 14
    WHERE da1.rec_date >= {d '2022-01-20'} AND da1.rec_date <= {d '2022-06-22'}
),
biweek_groups AS (
    SELECT
        biweek_start,
        DATEADD(day,13,biweek_start) AS biweek_end,
        name,
        med,
        directions,
        COUNT(DISTINCT record_date) AS unique_duplicate_records
    FROM duplicate_record_pairs
    GROUP BY biweek_start, name, med, directions
)
SELECT * FROM biweek_groups ORDER BY biweek_start;

This query captures all valid duplicate pairs, even those crossing interval boundaries, and counts how many unique records are part of duplicates in each biweek.

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