合并列中连续相同值行及考勤系统签到签出重构方案咨询

Hey there! Let's break down your two requirements step by step—both are super common when working with attendance system data, so I’ve got some practical SQL-based solutions for you.

To group consecutive rows with the same status into a single entry, window functions are your best friend here. The core idea is to first identify "groups" of consecutive same-status records, then aggregate those groups into one row.

Here’s a concrete example using SQL (assuming your table is named attendance with columns employee_id, record_time, and status):

WITH grouped_records AS (
    SELECT 
        employee_id,
        record_time,
        status,
        -- Create a group ID: increment when the current status differs from the previous row
        SUM(CASE WHEN LAG(status) OVER (PARTITION BY employee_id ORDER BY record_time) != status THEN 1 ELSE 0 END) 
            OVER (PARTITION BY employee_id ORDER BY record_time) AS group_id
    FROM attendance
)
SELECT 
    employee_id,
    MIN(record_time) AS first_record_time, -- Earliest time in the consecutive group
    MAX(record_time) AS last_record_time,  -- Latest time in the consecutive group
    status
FROM grouped_records
GROUP BY employee_id, group_id, status
ORDER BY employee_id, first_record_time;

How this works: #

• The LAG() function grabs the status of the previous row for the same employee. We compare it to the current row’s status to detect when a new group starts.
• The SUM() window function accumulates these "group start" signals to create a unique ID for each consecutive group.
• Finally, we group by employee, group ID, and status, then aggregate the earliest and latest times for each group.

This is a bit more nuanced, but we can build on the logic from the first requirement to get the job done. The goal is to:

1. Collapse consecutive check-ins into the earliest record
2. Collapse consecutive check-outs into the latest record
3. Pair each check-in with the next valid check-out

Here’s the step-by-step SQL solution:

-- Step 1: Group consecutive same-status records
WITH preprocessed AS (
    SELECT 
        employee_id,
        record_time,
        status,
        SUM(CASE WHEN LAG(status) OVER (PARTITION BY employee_id ORDER BY record_time) != status THEN 1 ELSE 0 END) 
            OVER (PARTITION BY employee_id ORDER BY record_time) AS group_id
    FROM attendance
),
-- Step 2: Merge consecutive records (keep earliest check-in, latest check-out)
merged_records AS (
    SELECT 
        employee_id,
        CASE 
            WHEN status = 'check-in' THEN MIN(record_time) 
            WHEN status = 'check-out' THEN MAX(record_time) 
        END AS record_time,
        status
    FROM preprocessed
    GROUP BY employee_id, group_id, status
    ORDER BY employee_id, record_time
),
-- Step 3: Assign row numbers to check-ins and check-outs separately
numbered_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY employee_id, status ORDER BY record_time) AS rn
    FROM merged_records
)
-- Step 4: Pair check-ins with corresponding check-outs
SELECT 
    c.employee_id,
    c.record_time AS check_in_time,
    o.record_time AS check_out_time
FROM numbered_records c
LEFT JOIN numbered_records o 
    ON c.employee_id = o.employee_id 
    AND c.rn = o.rn 
    AND c.status = 'check-in' 
    AND o.status = 'check-out'
ORDER BY c.employee_id, c.record_time;

Key details: #

• Preprocessing: We first group consecutive records just like in requirement 1.
• Merging: For check-ins, we keep the earliest time in the consecutive group; for check-outs, we keep the latest. This directly addresses your rule of "keep earlier consecutive check-ins, later consecutive check-outs."
• Pairing: By assigning row numbers to check-ins and check-outs separately, we can match the first check-in with the first check-out, the second check-in with the second check-out, etc. The LEFT JOIN ensures we don’t lose check-ins that don’t have a corresponding check-out (like an employee who forgot to sign out).

A quick tip: If your attendance table has a lot of data, adding indexes on employee_id and record_time will speed up these window functions significantly.

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