Oracle SQL时间范围人员移动追踪：如何获取起始位置？

要同时获取目标时段内的活动记录和每位人员在时段开始前的最后位置，可以通过合并两部分数据集实现：一是指定时间范围（8:00-20:00）内的所有活动，二是每个人员在该时段开始前的最新活动记录。以下是具体实现：

步骤说明 #

1. 定义目标时间范围：明确追踪的日期和时段边界（示例以2024-05-20为例，可按需替换）。
2. 获取时段内的活动记录：用WHERE子句直接筛选Timestamp在08:00:00到20:00:00之间的数据。
3. 获取时段前的起始位置：对每个人员，筛选出Timestamp早于08:00:00的记录，用ROW_NUMBER()窗口函数按时间倒序排序，取每条排序后的第一条（即最新的前置记录）。
4. 合并两部分数据：用UNION ALL将上述两组结果合并，最后按人员和时间排序。

完整SQL代码 #

WITH target_time AS (
    -- 定义目标日期和时间范围，可根据实际需求修改
    SELECT 
        TO_DATE('2024-05-20 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AS start_time,
        TO_DATE('2024-05-20 20:00:00', 'YYYY-MM-DD HH24:MI:SS') AS end_time
    FROM DUAL
),
pre_period_records AS (
    -- 获取每个人员在目标时段开始前的最新活动记录
    SELECT 
        Name,
        Timestamp,
        Activity
    FROM (
        SELECT 
            ar.Name,
            ar.Timestamp,
            ar.Activity,
            ROW_NUMBER() OVER (PARTITION BY ar.Name ORDER BY ar.Timestamp DESC) AS rn
        FROM activity_records ar
        CROSS JOIN target_time tt
        WHERE ar.Timestamp < tt.start_time
    )
    WHERE rn = 1
),
in_period_records AS (
    -- 获取目标时段内的所有活动记录
    SELECT 
        Name,
        Timestamp,
        Activity
    FROM activity_records ar
    CROSS JOIN target_time tt
    WHERE ar.Timestamp BETWEEN tt.start_time AND tt.end_time
)
-- 合并两部分数据并按人员和时间排序
SELECT Name, Timestamp, Activity
FROM pre_period_records
UNION ALL
SELECT Name, Timestamp, Activity
FROM in_period_records
ORDER BY Name, Timestamp;

关键细节说明 #

• target_time CTE：将时间范围参数化，方便后续修改日期或时段，避免硬编码。
• pre_period_records CTE：通过PARTITION BY Name按人员分组，ORDER BY Timestamp DESC确保取到每个人员在时段开始前的最后一条记录（即最接近8:00的位置），rn = 1筛选出这条记录。
• UNION ALL：保留所有记录（前置记录在时段前，时段内记录在时段中，时间不重叠），比UNION更高效，无需去重。
• 若部分人员在目标时段开始前无活动记录，可关联人员表补充默认值（如位置设为Unknown），示例代码如下：

WITH target_time AS (
    SELECT 
        TO_DATE('2024-05-20 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AS start_time,
        TO_DATE('2024-05-20 20:00:00', 'YYYY-MM-DD HH24:MI:SS') AS end_time
    FROM DUAL
),
pre_period_records AS (
    SELECT 
        e.Name,
        COALESCE(ar.Timestamp, tt.start_time - INTERVAL '1' MINUTE) AS Timestamp,
        COALESCE(ar.Activity, 'Unknown') AS Activity
    FROM employees e
    CROSS JOIN target_time tt
    LEFT JOIN (
        SELECT 
            Name,
            Timestamp,
            Activity,
            ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Timestamp DESC) AS rn
        FROM activity_records
        WHERE Timestamp < (SELECT start_time FROM target_time)
    ) ar ON e.Name = ar.Name AND ar.rn = 1
),
in_period_records AS (
    SELECT 
        Name,
        Timestamp,
        Activity
    FROM activity_records ar
    CROSS JOIN target_time tt
    WHERE ar.Timestamp BETWEEN tt.start_time AND tt.end_time
)
SELECT Name, Timestamp, Activity
FROM pre_period_records
UNION ALL
SELECT Name, Timestamp, Activity
FROM in_period_records
ORDER BY Name, Timestamp;

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