停车场15分钟时段在停车辆车牌提取需求及脚本求助

问题描述 #

我正在制作停车场占用情况报告，现有BigQuery脚本可按15分钟间隔统计累计在停车辆数（通过“时段到达车辆+上一时段未驶出车辆”计算）。现在需要提取每个15分钟时段内停车场的所有在停车辆车牌（比如某时段累计在停5辆，要获取这5辆车的具体车牌），但目前只能提取该时段到达的车牌，无法包含之前时段未驶出的车辆，求修改脚本实现需求。

原脚本如下：

WITH sitebay AS (
    SELECT DISTINCT 
        organization AS orgID, 
        parent AS region, 
        groupId AS site_id,  
        name AS SiteName,
        SAFE_CAST(JSON_VALUE(JSON_EXTRACT(json, '$.metadata.bayCount')) AS INT64) AS bay_count,
        timezone
    FROM `sc-neptune-production.group_actions.group_actions_scm`
    WHERE STRUCT (KEY, timestamp) IN (
        SELECT STRUCT (KEY, MAX(timestamp))
        FROM `my-table`
        WHERE type = 'site'
        GROUP BY KEY 
    )
    AND actiontype <> 'GroupDeleted'
    
 ),
time_intervals AS (
    SELECT 
        s.site_id,
        s.SiteName,
        COALESCE(s.bay_count, 0) AS bay_count,
        s.timezone,
       TIMESTAMP(DATETIME(timestamp_value,s.timezone)) AS time
     FROM sitebay s
        CROSS JOIN UNNEST(GENERATE_TIMESTAMP_ARRAY(
        TIMESTAMP(CONCAT(FORMAT_DATE('%Y-%m-%d', (PARSE_DATE('%Y%m%d', '20250101'))), ' 00:00:00'), s.timezone),
        TIMESTAMP(CONCAT(FORMAT_DATE('%Y-%m-%d', PARSE_DATE('%Y%m%d','20250129')), ' 23:59:59'), s.timezone),
        INTERVAL 15 MINUTE
    )) AS timestamp_value
),
base_data AS (
  
    select *,
    CASE
      WHEN StayDurationMinute <= 30 THEN '0-30 min'
      WHEN StayDurationMinute > 30 AND StayDurationMinute <= 60 THEN '30-60 min'
      WHEN StayDurationMinute > 60 AND StayDurationMinute <= 120 THEN '60-120 min'
      ELSE '120+ min'
    END AS stay_category
    FROM(     
    SELECT DISTINCT
        SPLIT(orsId, '#')[SAFE_OFFSET(0)] AS org_id,
        SPLIT(orsId, '#')[SAFE_OFFSET(1)] AS region_id,
        SPLIT(orsId, '#')[SAFE_OFFSET(2)] AS site_id,
        inPlate AS plate,
        arrivalTime,
        departureTime,
        DATETIME(arrivalTime, 'Pacific/Auckland') AS arrivalDate,
        DATETIME(departureTime, 'Pacific/Auckland') AS departureDate,
        CASE 
       WHEN arrivaltime IS NOT NULL AND departureTime IS NOT NULL 
       THEN ROUND((DATETIME_DIFF(DATETIME(departureTime,'Pacific/Auckland'),DATETIME(arrivalTime,'Pacific/Auckland'),MINUTE)),1) ELSE 0 END AS StayDurationMinute  
    FROM `my-table`
    WHERE 
        DATE(arrivalTime, 'Pacific/Auckland') BETWEEN 
            DATE(TIMESTAMP(PARSE_DATE('%Y%m%d', '20250101')), 'Pacific/Auckland') AND 
            DATE(TIMESTAMP(PARSE_DATE('%Y%m%d','20250129')), 'Pacific/Auckland')
)
),
aggregated_data AS (
    select site_id,time_intervals,plate,
        COUNTIF(direction = 'entry') AS entryCount,
        COUNTIF(direction = 'exit') AS exitCount from
        (
    SELECT DISTINCT
        site_id, plate,stay_category,
        timestamp(DATETIME(TIMESTAMP_SECONDS(DIV(UNIX_SECONDS(TIMESTAMP(arrivalTime)), 15 * 60) * 15 * 60), 'Pacific/Auckland')) AS time_intervals, 
        'entry' AS direction
    FROM base_data
    WHERE arrivalDate IS NOT NULL
    UNION ALL
    SELECT DISTINCT
        site_id, plate,stay_category,
        timestamp(DATETIME(TIMESTAMP_SECONDS(DIV(UNIX_SECONDS(TIMESTAMP(departureTime)), 15 * 60) * 15 * 60), 'Pacific/Auckland')) AS time_intervals, 
        'exit' AS direction
    FROM base_data 
    WHERE departureDate IS NOT NULL
        )
        GROUP BY site_id,time_intervals,plate
),
merged_data as(
    SELECT distinct
        ti.SiteName,
        ti.bay_count,
        ti.time,
        DATE(ti.time) AS date,
        FORMAT_TIME('%H:%M', TIME(time)) AS time_slot,
        plate,
        COALESCE(ad.entryCount, 0) AS entryCount,
        COALESCE(ad.exitCount, 0) AS exitCount,
              
    FROM time_intervals ti
    LEFT JOIN aggregated_data ad 
           ON ti.time = ad.time_intervals
           AND ti.site_id = ad.site_id
        
      
)
SELECT distinct
    SiteName,
    bay_count,    
    Date, 
    time,
    time_slot,
    plate,
    entryCount,
    exitCount,
    SUM(entryCount - exitCount) OVER (PARTITION BY date,SiteName ORDER BY time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS currentOccupancy,
    
FROM merged_data
ORDER BY  time ASC

解决方案 #

要获取每个时段的在停车辆车牌，核心是判断每辆车的停留时间段与15分钟时段是否有重叠。一辆车在某个15分钟时段内属于在停状态，需满足：

• 车辆到达时间 ≤ 该时段的结束时间
• 车辆离开时间 > 该时段的开始时间（或车辆尚未离开，departureTime为NULL）

基于这个逻辑，修改后的脚本如下：

WITH sitebay AS (
    SELECT DISTINCT 
        organization AS orgID, 
        parent AS region, 
        groupId AS site_id,  
        name AS SiteName,
        SAFE_CAST(JSON_VALUE(JSON_EXTRACT(json, '$.metadata.bayCount')) AS INT64) AS bay_count,
        timezone
    FROM `sc-neptune-production.group_actions.group_actions_scm`
    WHERE STRUCT (KEY, timestamp) IN (
        SELECT STRUCT (KEY, MAX(timestamp))
        FROM `my-table`
        WHERE type = 'site'
        GROUP BY KEY 
    )
    AND actiontype <> 'GroupDeleted'
    
 ),
time_intervals AS (
    SELECT 
        s.site_id,
        s.SiteName,
        COALESCE(s.bay_count, 0) AS bay_count,
        s.timezone,
        TIMESTAMP(DATETIME(timestamp_value,s.timezone)) AS interval_start,
        TIMESTAMP_ADD(TIMESTAMP(DATETIME(timestamp_value,s.timezone)), INTERVAL 15 MINUTE) AS interval_end
     FROM sitebay s
        CROSS JOIN UNNEST(GENERATE_TIMESTAMP_ARRAY(
        TIMESTAMP(CONCAT(FORMAT_DATE('%Y-%m-%d', (PARSE_DATE('%Y%m%d', '20250101'))), ' 00:00:00'), s.timezone),
        TIMESTAMP(CONCAT(FORMAT_DATE('%Y-%m-%d', PARSE_DATE('%Y%m%d','20250129')), ' 23:59:59'), s.timezone),
        INTERVAL 15 MINUTE
    )) AS timestamp_value
),
base_data AS (
    SELECT DISTINCT
        SPLIT(orsId, '#')[SAFE_OFFSET(2)] AS site_id,
        inPlate AS plate,
        arrivalTime,
        departureTime,
        DATETIME(arrivalTime, 'Pacific/Auckland') AS arrivalDate,
        DATETIME(departureTime, 'Pacific/Auckland') AS departureDate,
        CASE 
            WHEN arrivaltime IS NOT NULL AND departureTime IS NOT NULL 
            THEN ROUND((DATETIME_DIFF(DATETIME(departureTime,'Pacific/Auckland'),DATETIME(arrivalTime,'Pacific/Auckland'),MINUTE)),1) 
            ELSE 0 
        END AS StayDurationMinute,
        CASE
            WHEN StayDurationMinute <= 30 THEN '0-30 min'
            WHEN StayDurationMinute > 30 AND StayDurationMinute <= 60 THEN '30-60 min'
            WHEN StayDurationMinute > 60 AND StayDurationMinute <= 120 THEN '60-120 min'
            ELSE '120+ min'
        END AS stay_category
    FROM `my-table`
    WHERE 
        DATE(arrivalTime, 'Pacific/Auckland') BETWEEN 
            DATE(TIMESTAMP(PARSE_DATE('%Y%m%d', '20250101')), 'Pacific/Auckland') AND 
            DATE(TIMESTAMP(PARSE_DATE('%Y%m%d','20250129')), 'Pacific/Auckland')
),
-- 关联时段与在停车辆，判断停留时间是否与时段重叠
parked_vehicles_per_interval AS (
    SELECT
        ti.SiteName,
        ti.bay_count,
        ti.interval_start AS time,
        DATE(ti.interval_start) AS date,
        FORMAT_TIME('%H:%M', TIME(ti.interval_start)) AS time_slot,
        bd.plate,
        bd.arrivalTime,
        bd.departureTime,
        bd.stay_category
    FROM time_intervals ti
    LEFT JOIN base_data bd 
        ON ti.site_id = bd.site_id
        -- 判断车辆是否在当前时段内停留
        AND bd.arrivalTime <= ti.interval_end
        AND (bd.departureTime > ti.interval_start OR bd.departureTime IS NULL)
),
-- 可选：保留原有累计在停数量统计需求
occupancy_stats AS (
    SELECT
        SiteName,
        bay_count,
        date,
        time,
        time_slot,
        COUNT(DISTINCT plate) AS currentOccupancy,
        SUM(COUNT(DISTINCT plate)) OVER (PARTITION BY date, SiteName ORDER BY time ASC) AS cumulativeOccupancy
    FROM parked_vehicles_per_interval
    GROUP BY SiteName, bay_count, date, time, time_slot
)
-- 输出每个时段的在停车辆明细，如需统计数据可切换到occupancy_stats
SELECT
    SiteName,
    bay_count,
    date,
    time,
    time_slot,
    plate,
    arrivalTime,
    departureTime,
    stay_category
FROM parked_vehicles_per_interval
WHERE plate IS NOT NULL -- 过滤无车辆的时段（可选）
ORDER BY SiteName, date, time, plate

关键修改说明 #

1. time_intervals CTE：新增interval_end字段，明确每个15分钟时段的结束时间，简化后续车辆停留时段的判断逻辑。
2. parked_vehicles_per_interval CTE：通过关联条件筛选出所有与当前时段有重叠的停留车辆，覆盖了“之前时段到达未驶出”和“本时段到达”的所有在停车辆。
3. 保留原有统计需求：新增occupancy_stats CTE，可继续获取每个时段的在停数量及累计值，按需选择输出明细或统计数据。

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