基于SQL实现连续休假模式统计及休假直方图生成的技术问题

Got it, let's tackle this problem step by step. The core challenge here is identifying consecutive leave periods first, then categorizing those periods into your defined ranges, and finally counting how many times each range occurs per month. Here's a robust SQL approach using window functions to make this happen:

Step-by-Step Explanation #

The key trick is using a window function to group consecutive leave dates. For any sequence of consecutive dates, subtracting a row number (incrementing by 1 each day) from the date will give you the same "group ID" for all days in the sequence. This lets us cluster consecutive leaves together easily.

Full SQL Implementation #

Assuming your table is named employee_leaves with columns Year, Month (formatted like '1-Jan'), and Leaves, here's the code:

WITH formatted_leave_dates AS (
    -- First: Convert raw date parts into a standard date, filter only leave days
    SELECT
        -- Combine Year and Month into a valid date (adjust function for your DB if needed)
        TO_DATE(CONCAT("Year", ' ', "Month"), 'YYYY DD-Mon') AS leave_date,
        "Leaves"
    FROM employee_leaves
    WHERE "Leaves" = 1 -- Only care about days where the employee was on leave
),
consecutive_leave_groups AS (
    -- Second: Assign a group ID to each consecutive leave sequence
    SELECT
        leave_date,
        -- Consecutive dates will share the same group_id
        leave_date - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY leave_date) AS group_id
    FROM formatted_leave_dates
),
categorized_leave_periods AS (
    -- Third: Calculate length of each consecutive period and categorize it
    SELECT
        DATE_TRUNC('month', leave_date) AS leave_month, -- Extract the month for grouping
        COUNT(*) AS consecutive_days,
        CASE
            WHEN COUNT(*) BETWEEN 1 AND 2 THEN '1-2'
            WHEN COUNT(*) BETWEEN 3 AND 5 THEN '3-5'
            WHEN COUNT(*) > 5 THEN '>5'
            ELSE 'Above 5' -- This case won't trigger, but included for safety
        END AS "Leave Occurence"
    FROM consecutive_leave_groups
    GROUP BY group_id, leave_month
)
-- Fourth: Count how many times each category occurs per month
SELECT
    TO_CHAR(leave_month, 'YYYY-MM') AS month,
    "Leave Occurence",
    COUNT(*) AS occurrence_count
FROM categorized_leave_periods
GROUP BY leave_month, "Leave Occurence"
ORDER BY leave_month, "Leave Occurence";

Adjustments for Different Databases #

If you're using MySQL instead of PostgreSQL, tweak the date functions:

• Replace TO_DATE(...) with STR_TO_DATE(CONCAT("Year", ' ', "Month"), '%Y %d-%b')
• Replace leave_date - INTERVAL '1 day' * ROW_NUMBER(...) with DATE_SUB(leave_date, INTERVAL ROW_NUMBER() OVER (ORDER BY leave_date) DAY)
• Replace DATE_TRUNC('month', ...) with DATE_FORMAT(leave_date, '%Y-%m-01')
• Replace TO_CHAR(...) with DATE_FORMAT(leave_month, '%Y-%m')

How It Works with Your Example Data #

Let's map this to your sample data:

1. 1-Jan to 2-Jan: Consecutive 2 days → categorized as '1-2'
2. 8-Jan to 11-Jan: Consecutive 4 days → categorized as '3-5'
3. 15-Jan to 20-Jan: Consecutive 6 days → categorized as '>5'
4. 23-Jan to 24-Jan: Consecutive 2 days → categorized as '1-2'

The final output will show:

Which matches exactly what you were looking for!

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