Oracle 11g中用PL/SQL计算各产品在各工位的停留天数

现有日志表数据如下：

需求为计算每个产品在每个工位的总停留天数（以天为单位），规则如下：

• 产品每条记录的停留时间 = 该产品下一条记录的date_in - 当前记录的date_in
• 产品最后一条记录的停留时间 = 固定日期2022-09-13（替代sysdate） - 当前记录的date_in
• 日志表仅能按date_in排序，工位无固定顺序
• 最终结果按产品汇总各工位累计停留天数，未停留的工位显示0，格式示例：

| prod_id | s1 | s2 | s3 | s4 | s5 | s6 |...
| -------- | -- | -- | -- | -- | -- | -- |...
| p1 | 1 | 1 | 3 | 4 | 2 | 0 |...
| p2 | 1 | 4 | 0 | 0 | 0 | 0 |...
| p3 | 11 | 0 | 0 | 0 | 0 | 0 |...

当前使用Oracle 11g，如何用PL/SQL实现该需求？

Oracle 11g中需要结合分析函数计算停留时间，再通过动态SQL实现动态列转置（因为工位不固定），具体步骤如下：

步骤1：计算每条记录的停留天数 #

先用LEAD()分析函数获取每个产品的下一条记录时间，再计算停留天数（用TRUNC()对日期差取整）：

WITH prod_stay AS (
    SELECT 
        prod_id,
        station_id,
        date_in,
        -- 获取当前产品的下一条记录时间，无后续记录则用指定日期
        LEAD(date_in, 1, TO_DATE('2022-09-13', 'YYYY-MM-DD')) 
            OVER (PARTITION BY prod_id ORDER BY date_in) AS next_date
    FROM log_table
),
stay_days AS (
    SELECT 
        prod_id,
        station_id,
        -- 计算停留天数并取整
        TRUNC(next_date - date_in) AS days_stayed
    FROM prod_stay
)
SELECT * FROM stay_days;

这段代码会得到每个产品在每个工位的单次停留天数，为后续汇总转置做准备。

步骤2：动态生成工位列的转置SQL #

由于工位数量不固定，需先查询所有存在的工位，动态构建PIVOT语句的列：

DECLARE
    v_cols VARCHAR2(4000);
    v_sql VARCHAR2(4000);
BEGIN
    -- 收集所有工位，拼接成PIVOT所需的列格式
    SELECT LISTAGG('''' || station_id || ''' AS ' || station_id, ', ')
        WITHIN GROUP (ORDER BY station_id)
    INTO v_cols
    FROM (SELECT DISTINCT station_id FROM log_table);

    -- 构建完整的动态SQL
    v_sql := '
        WITH prod_stay AS (
            SELECT 
                prod_id,
                station_id,
                date_in,
                LEAD(date_in, 1, TO_DATE(''2022-09-13'', ''YYYY-MM-DD'')) 
                    OVER (PARTITION BY prod_id ORDER BY date_in) AS next_date
            FROM log_table
        ),
        stay_days AS (
            SELECT 
                prod_id,
                station_id,
                TRUNC(next_date - date_in) AS days_stayed
            FROM prod_stay
        )
        SELECT *
        FROM (
            SELECT prod_id, station_id, days_stayed FROM stay_days
        )
        PIVOT (
            SUM(days_stayed) FOR station_id IN (' || v_cols || ')
        )
        ORDER BY prod_id';

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_sql;
    -- 若需输出结果到屏幕，可添加DBMS_OUTPUT相关逻辑；若需保存结果，可插入到临时表
END;
/

补充说明 #

1. 若要使用系统当前日期sysdate替代固定日期，只需把TO_DATE('2022-09-13', 'YYYY-MM-DD')替换为SYSDATE即可。
2. 若工位数量较多（超过LISTAGG的长度限制），可改用XMLAGG拼接列名：

SELECT RTRIM(XMLAGG(XMLELEMENT(E, '''' || station_id || ''' AS ' || station_id, ', ') ORDER BY station_id).EXTRACT('//text()'), ', ')
INTO v_cols
FROM (SELECT DISTINCT station_id FROM log_table);

3. 若需要强制显示所有工位（包括未出现的工位如s6），需先维护一个工位维度表，关联后再进行转置。

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