Oracle门禁系统记录按建筑透视生成多趟行程报表需求

原始门禁数据 #

表字段约束 #

CARDNBR   NOT NULL VARCHAR2(50)
TIMESTAMP NOT NULL DATE
BUILDING  NOT NULL VARCHAR2(20)
DOOR      NOT NULL VARCHAR2(4)

需求报表样式 #

园区访客需按A-F顺序通行各建筑后离开，需生成如下格式的报表，列出卡号及对应每趟行程中各建筑的刷卡时间（无需门号）：

注：若某建筑无刷卡记录（如漏刷或跟随他人进入），对应单元格留空；部分访客当日可能多次往返，每次仍按顺序通行各检查点。

技术实现方案（Oracle SQL） #

步骤1：数据预处理与行程分组 #

通过窗口函数识别同一张卡的不同行程：当访客完成一趟通行后再次刷卡进入A建筑时，视为新行程的开始。同时去重同一行程同一建筑的重复刷卡记录。

WITH pre_processed AS (
    SELECT 
        cardnbr,
        timestamp,
        building,
        -- 给建筑分配顺序值，用于判断行程逻辑
        CASE building 
            WHEN 'A' THEN 1
            WHEN 'B' THEN 2
            WHEN 'C' THEN 3
            WHEN 'D' THEN 4
            WHEN 'E' THEN 5
            WHEN 'F' THEN 6
        END AS building_order,
        -- 标记新行程起点：回到A且之前的建筑顺序不小于当前（说明完成过至少部分行程）
        CASE 
            WHEN building = 'A' 
                 AND LAG(building_order, 1, 0) OVER (PARTITION BY cardnbr ORDER BY timestamp) >= building_order
            THEN 1
            ELSE 0
        END AS trip_start
    FROM access_log
),
trip_grouped AS (
    SELECT 
        cardnbr,
        timestamp,
        building,
        -- 累计行程起点标记，生成唯一行程ID
        SUM(trip_start) OVER (PARTITION BY cardnbr ORDER BY timestamp) AS trip_id
    FROM pre_processed
),
deduped AS (
    SELECT 
        cardnbr,
        trip_id,
        building,
        -- 同一行程同一建筑取最早刷卡时间
        MIN(timestamp) AS first_swipe_time
    FROM trip_grouped
    GROUP BY cardnbr, trip_id, building
)

步骤2：行转列生成报表格式 #

使用Oracle原生PIVOT函数将行数据转为报表要求的列格式，空值自动留空：

SELECT 
    cardnbr AS "卡号(CardNbr)",
    TO_CHAR(A, 'MM-DD-RR HH24:MI:SS') AS "A",
    TO_CHAR(B, 'MM-DD-RR HH24:MI:SS') AS "B",
    TO_CHAR(C, 'MM-DD-RR HH24:MI:SS') AS "C",
    TO_CHAR(D, 'MM-DD-RR HH24:MI:SS') AS "D",
    TO_CHAR(E, 'MM-DD-RR HH24:MI:SS') AS "E",
    TO_CHAR(F, 'MM-DD-RR HH24:MI:SS') AS "F"
FROM deduped
PIVOT (
    MAX(first_swipe_time)
    FOR building IN ('A' AS A, 'B' AS B, 'C' AS C, 'D' AS D, 'E' AS E, 'F' AS F)
)
ORDER BY cardnbr, trip_id;

方案说明 #

1. 行程分组：通过建筑顺序的变化识别新行程，确保同一张卡的多次往返被拆分为独立趟次。
2. 去重处理：同一趟行程同一建筑的重复刷卡记录只保留最早时间，避免数据冗余。
3. 行转列：PIVOT函数高效实现格式转换，空值直接留空，符合报表要求。
4. 时间格式化：用TO_CHAR将DATE类型转为报表指定的时间格式。

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