Oracle SQL行转列优化：子查询性能问题与JOIN实现求助

背景与需求 #

我有一个Oracle SQL视图，包含ID、TIME_STAMP、LOCATION字段，以及一个COMMAND字段：

• 整数1代表“Time IN”（进入）
• 整数2代表“Time Requested”（请求）
• 整数3代表“Time Out”（离开）

原始数据示例 #

期望结果 #

需要将同一ID在同一LOCATION的每次到访对应的“进入、请求、离开”记录合并为一行，结果如下：

现有尝试与问题 #

嵌套子查询实现（性能差） #

我通过查询COMMAND=1（所有进入记录），用SELECT嵌套子查询的方式实现了需求，SQL语句如下：

SELECT 
O.ID AS "ID", 
O.TIME AS "TIMEIN", 

(SELECT 
    MIN(TIME)
    FROM VIEW I
    WHERE O.LOCATION = I.LOCATION AND COMMAND = ('2') AND O.ID = I.ID AND O.TIME < I.TIME) 
AS "TIMEREQ",

(SELECT 
    MIN(TIME)
    FROM VIEW I
    WHERE O.LOCATION = I.LOCATION AND COMMAND = ('3') AND O.ID = I.ID AND O.TIME < I.TIME) 
AS "TIMEOUT",

O.LOCATION AS "LOCATION" 

FROM VIEW O
WHERE 
LOCATION IN ('52','53','54') AND COMMAND IN ('1')
ORDER BY TIME DESC

该语句处理12000行数据耗时约11秒。当尝试关联另一张仅包含ID和Comment字段的表（数据如下）时，视图加载耗时超50秒，性能极差：

JOIN方式尝试（结果不正确） #

我尝试改用JOIN嵌套子查询优化性能，但无法得到正确结果，测试SQL（仅针对TIMEREQ字段，指定ID=2253）如下：

SELECT 
P.ID AS "ID",
P.TIME AS "TIMEIN",
TIMECOM2 AS "TIMEREQ",
P.LOCATION AS "LOCATION",
P.COMMAND AS "COMMAND"

FROM VIEW P
LEFT JOIN 
(SELECT 
MAX(C.ID) AS "REQID",
MIN(C.TIME) AS "TIMECOM2"
FROM VIEW C
WHERE C.COMMAND = 2 AND C.LOCATION IN (52, 53, 54) AND C.ID = '2253') 
ON (P.ID = REQID) AND TIMECOM2 > P.TIME

WHERE 
P.ID = '2253' AND
P.LOCATION IN (52, 53, 54) AND 
P.COMMAND = 1
ORDER BY P.TIME, TIMECOM2

得到的结果不符合预期，只有第一条进入记录匹配到了请求时间，其余均为null：

疑问 #

1. 嵌套子查询性能低下的原因是什么？
2. 如何用JOIN方式正确实现需求并优化性能？

解答 #

一、嵌套子查询性能低下的原因 #

1. 行级重复执行：SELECT子句中的两个子查询是关联子查询，主查询返回的每一条COMMAND=1记录都会触发两次子查询扫描视图，若主查询返回上千行，就会产生数千次重复扫描，直接拖慢性能。
2. 索引缺失：如果视图基础表没有针对ID、LOCATION、COMMAND、TIME_STAMP的组合索引，每次子查询都会执行全表扫描，进一步放大性能损耗。
3. 关联表后数据膨胀：关联备注表时，若关联逻辑未优化，可能导致临时数据集膨胀，叠加子查询的重复执行，直接让耗时飙升。

二、用JOIN方式正确实现并优化性能的方案 #

核心思路是先对每个ID+LOCATION的到访会话分组，再通过窗口函数或预聚合关联匹配对应时间，避免行级子查询的重复执行。

方案1：窗口函数分组聚合（Oracle 12c+推荐）

用累计求和标记每个到访会话，再聚合对应命令的时间：

WITH session_data AS (
    SELECT 
        ID,
        LOCATION,
        TIME_STAMP,
        COMMAND,
        -- 每次遇到COMMAND=1，会话编号+1，标记同一到访会话
        SUM(CASE WHEN COMMAND = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ID, LOCATION ORDER BY TIME_STAMP) AS session_id
    FROM VIEW
    WHERE LOCATION IN ('52','53','54')
)
SELECT 
    ID,
    MAX(CASE WHEN COMMAND = 1 THEN TIME_STAMP END) AS 进入时间,
    MAX(CASE WHEN COMMAND = 2 THEN TIME_STAMP END) AS 请求时间,
    MAX(CASE WHEN COMMAND = 3 THEN TIME_STAMP END) AS 离开时间,
    LOCATION,
    t.备注
FROM session_data
LEFT JOIN 备注表 t ON session_data.ID = t.ID
GROUP BY ID, LOCATION, session_id
ORDER BY 进入时间 DESC;

方案2：预聚合自关联

先拆分各命令的记录集，再通过时间范围匹配对应会话：

WITH in_records AS (
    SELECT 
        ID,
        LOCATION,
        TIME_STAMP AS 进入时间,
        -- 取当前进入记录的下一个进入时间，作为当前会话的时间上限
        LEAD(TIME_STAMP) OVER (PARTITION BY ID, LOCATION ORDER BY TIME_STAMP) AS next_in_time
    FROM VIEW
    WHERE COMMAND = 1 AND LOCATION IN ('52','53','54')
),
req_records AS (
    SELECT ID, LOCATION, TIME_STAMP AS 请求时间 FROM VIEW WHERE COMMAND = 2 AND LOCATION IN ('52','53','54')
),
out_records AS (
    SELECT ID, LOCATION, TIME_STAMP AS 离开时间 FROM VIEW WHERE COMMAND = 3 AND LOCATION IN ('52','53','54')
)
SELECT 
    i.ID,
    i.进入时间,
    r.请求时间,
    o.离开时间,
    i.LOCATION,
    t.备注
FROM in_records i
LEFT JOIN req_records r 
    ON i.ID = r.ID 
    AND i.LOCATION = r.LOCATION 
    AND r.请求时间 BETWEEN i.进入时间 AND NVL(i.next_in_time, TO_DATE('9999-12-31', 'YYYY-MM-DD'))
LEFT JOIN out_records o 
    ON i.ID = o.ID 
    AND i.LOCATION = o.LOCATION 
    AND o.离开时间 BETWEEN i.进入时间 AND NVL(i.next_in_time, TO_DATE('9999-12-31', 'YYYY-MM-DD'))
LEFT JOIN 备注表 t ON i.ID = t.ID
ORDER BY i.进入时间 DESC;

性能优化建议

1. 创建组合索引：在视图基础表上创建索引CREATE INDEX idx_view_session ON 基础表(ID, LOCATION, COMMAND, TIME_STAMP);，大幅提升分组、过滤和关联速度。
2. 避免隐式类型转换：COMMAND是整数类型，直接写COMMAND=2而非COMMAND=('2')，避免类型转换导致索引失效。
3. 添加时间过滤：若不需要全量数据，增加TIME_STAMP >= TO_DATE('2023-01-01', 'YYYY-MM-DD')这类条件，减少处理的数据量。

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