按动物分组，基于10天间隔生成免疫日期分组排名的技术咨询

Got it, let's work through this problem. You need to bucket each animal's immunization dates into groups where:

• The first group starts at the animal's earliest date and includes all dates within 10 days of that start
• Any date outside the current group's window starts a new group, with that date as the new anchor
• Each group gets an incrementing rank per animal

Below are working implementations for major SQL dialects, using your sample data:

Sample Data Setup #

First, create a test table (skip this if your table already exists):

CREATE TABLE immunizations (
    Animal VARCHAR(10),
    Immunization_Date DATE
);

INSERT INTO immunizations VALUES
('Cat', '2017-01-18'),
('Cat', '2017-01-27'),
('Cat', '2017-05-07'),
('Cat', '2017-05-12'),
('Dog', '2017-01-01'),
('Dog', '2017-01-05'),
('Dog', '2017-01-07'),
('Dog', '2017-03-25'),
('Dog', '2017-04-18');

Implementation 1: PostgreSQL (Recursive CTE) #

Recursive CTEs are perfect here because they let us track the current group's start date as we iterate through sorted dates:

WITH ranked_dates AS (
    SELECT 
        Animal,
        Immunization_Date,
        ROW_NUMBER() OVER (PARTITION BY Animal ORDER BY Immunization_Date) AS rn
    FROM immunizations
),
recursive_groups AS (
    -- Anchor: first date per animal = group 1
    SELECT 
        Animal,
        Immunization_Date,
        rn,
        1 AS group_rank,
        Immunization_Date AS group_start
    FROM ranked_dates
    WHERE rn = 1
    
    UNION ALL
    
    -- Recursive step: check if current date fits in the last group's 10-day window
    SELECT 
        rd.Animal,
        rd.Immunization_Date,
        rd.rn,
        CASE 
            WHEN rd.Immunization_Date <= rg.group_start + INTERVAL '10 days' THEN rg.group_rank
            ELSE rg.group_rank + 1
        END AS group_rank,
        CASE 
            WHEN rd.Immunization_Date <= rg.group_start + INTERVAL '10 days' THEN rg.group_start
            ELSE rd.Immunization_Date
        END AS group_start
    FROM ranked_dates rd
    JOIN recursive_groups rg ON rd.Animal = rg.Animal AND rd.rn = rg.rn + 1
)
SELECT Animal, Immunization_Date, group_rank
FROM recursive_groups
ORDER BY Animal, Immunization_Date;

Implementation 2: MySQL 8.0+ (Recursive CTE) #

MySQL 8.0 supports recursive CTEs—only adjust the date interval syntax:

WITH ranked_dates AS (
    SELECT 
        Animal,
        Immunization_Date,
        ROW_NUMBER() OVER (PARTITION BY Animal ORDER BY Immunization_Date) AS rn
    FROM immunizations
),
recursive_groups AS (
    SELECT 
        Animal,
        Immunization_Date,
        rn,
        1 AS group_rank,
        Immunization_Date AS group_start
    FROM ranked_dates
    WHERE rn = 1
    
    UNION ALL
    
    SELECT 
        rd.Animal,
        rd.Immunization_Date,
        rd.rn,
        CASE 
            WHEN rd.Immunization_Date <= DATE_ADD(rg.group_start, INTERVAL 10 DAY) THEN rg.group_rank
            ELSE rg.group_rank + 1
        END AS group_rank,
        CASE 
            WHEN rd.Immunization_Date <= DATE_ADD(rg.group_start, INTERVAL 10 DAY) THEN rg.group_start
            ELSE rd.Immunization_Date
        END AS group_start
    FROM ranked_dates rd
    JOIN recursive_groups rg ON rd.Animal = rg.Animal AND rd.rn = rg.rn + 1
)
SELECT Animal, Immunization_Date, group_rank
FROM recursive_groups
ORDER BY Animal, Immunization_Date;

Implementation 3: SQL Server (Recursive CTE) #

Use DATEADD for interval calculations in SQL Server:

WITH ranked_dates AS (
    SELECT 
        Animal,
        Immunization_Date,
        ROW_NUMBER() OVER (PARTITION BY Animal ORDER BY Immunization_Date) AS rn
    FROM immunizations
),
recursive_groups AS (
    SELECT 
        Animal,
        Immunization_Date,
        rn,
        1 AS group_rank,
        Immunization_Date AS group_start
    FROM ranked_dates
    WHERE rn = 1
    
    UNION ALL
    
    SELECT 
        rd.Animal,
        rd.Immunization_Date,
        rd.rn,
        CASE 
            WHEN rd.Immunization_Date <= DATEADD(DAY, 10, rg.group_start) THEN rg.group_rank
            ELSE rg.group_rank + 1
        END AS group_rank,
        CASE 
            WHEN rd.Immunization_Date <= DATEADD(DAY, 10, rg.group_start) THEN rg.group_start
            ELSE rd.Immunization_Date
        END AS group_start
    FROM ranked_dates rd
    JOIN recursive_groups rg ON rd.Animal = rg.Animal AND rd.rn = rg.rn + 1
)
SELECT Animal, Immunization_Date, group_rank
FROM recursive_groups
ORDER BY Animal, Immunization_Date;

Expected Output #

All queries will return this result:

Key Notes #

• The recursive approach correctly tracks group starts, even with large gaps between dates
• ROW_NUMBER() ensures we process dates in strict chronological order per animal
• The CASE statement checks if the current date fits in the existing group's window; if not, it starts a new group

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