SQL Server 2012中按动态7天窗口分组班次数据的技术问询

Got it, let's break down how to solve this problem. The key goal is to group shifts into "islands" where every shift in the group is within a 7-day window of at least one other shift in the same group, then calculate the total shifts and hours for each island. Your initial use of LAG()/LEAD() and DATEDIFF() was close—we just need to add a cumulative sum to create persistent group IDs for these islands.

Step 1: Preprocess Data to Combine Dates & Times #

First, we need to merge the separate date and time columns into full datetime values so we can accurately calculate time differences between shifts.

Step 2: Create Shift Group IDs #

Using window functions, we'll compare each shift to the previous one. If the gap between the current shift's start time and the previous shift's start time exceeds 7 days, we'll start a new group. A cumulative sum of these "new group" flags will give us a unique ID for each shift island.

Step 3: Aggregate by Group #

Finally, we'll group by employee and group ID to get the start/end dates, shift count, and total hours for each island.

Full SQL Query (MySQL Example) #

WITH processed_shifts AS (
    -- Merge date and time columns into proper datetime values
    SELECT
        员工ID,
        STR_TO_DATE(CONCAT(开始日期, ' ', 开始时间), '%d-%m-%y %h:%i %p') AS shift_start,
        STR_TO_DATE(CONCAT(结束日期, ' ', 结束时间), '%d-%m-%y %h:%i %p') AS shift_end,
        工时
    FROM your_shift_table -- Replace with your actual table name
),
shift_groups AS (
    -- Generate group IDs for shift islands
    SELECT
        员工ID,
        shift_start,
        shift_end,
        工时,
        SUM(
            CASE
                -- If current shift is more than 7 days after the previous shift, start a new group
                WHEN DATEDIFF(shift_start, LAG(shift_start) OVER (PARTITION BY 员工ID ORDER BY shift_start)) > 7
                THEN 1
                ELSE 0
            END
        ) OVER (PARTITION BY 员工ID ORDER BY shift_start) AS group_id
    FROM processed_shifts
)
-- Aggregate results by employee and group
SELECT
    员工ID,
    DATE_FORMAT(MIN(shift_start), '%d-%m-%y') AS 起始日期,
    DATE_FORMAT(MAX(shift_end), '%d-%m-%y') AS 结束日期,
    COUNT(*) AS 班次数量,
    ROUND(SUM(工时), 2) AS 总工时
FROM shift_groups
GROUP BY 员工ID, group_id
ORDER BY 员工ID, 起始日期;

Key Explanations #

1. Datetime Merging: The STR_TO_DATE() function combines the date and time strings into a single datetime value, which is essential for accurate gap calculations.
2. Group ID Generation:
   • LAG(shift_start) gets the start time of the previous shift for the same employee.
   • The CASE statement checks if the gap between the current and previous shift is over 7 days—if so, it returns 1 (new group), else 0.
   • The SUM() OVER() clause accumulates these 1s and 0s to create a unique ID for each contiguous shift island. The first shift will have no previous shift, so it starts with group ID 0.
3. Aggregation: Grouping by 员工ID and group_id lets us calculate the earliest start date, latest end date, total shifts, and sum of hours for each island.

Adjustments for Other SQL Dialects #

• SQL Server: Replace STR_TO_DATE() with CONVERT(datetime, CONCAT(开始日期, ' ', 开始时间), 3) (format 3 = dd/mm/yy), and use DATEDIFF(day, LAG(shift_start) OVER (...), shift_start) instead.
• PostgreSQL: Use TO_TIMESTAMP(CONCAT(开始日期, ' ', 开始时间), 'DD-MM-YY HH:MI AM') for datetime conversion, and DATE_PART('day', shift_start - LAG(shift_start) OVER (...)) to calculate days between shifts.

Why Your Initial Attempt Might Have Failed #

Using LAG()/LEAD() alone only compares individual adjacent shifts, but doesn't create a persistent group for all shifts in a contiguous 7-day window. The cumulative sum approach ties all related shifts together into a single group, even if there are multiple shifts spread across the 7-day period.

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