如何按日期统计Jail/Prison两类羁押场所的在押人员数量？

我正在处理羁押人员数据，需要计算任意日期下不同场所的在押人员数量。现有数据规则：

• 一行对应一名羁押人员的一次场所记录，一个Booking_ID对应同一人员
• Release Date是该人员彻底离开羁押系统的日期，若为null表示尚未释放

以Booking_ID 123为例：该人员2022年1月1日入Jail，2月4日转Prison，3月6日转回Jail，4月4日再转Prison，6月2日彻底释放。

原始数据表格 #

需求 #

生成从数据中最早日期到当前日期的每日记录表格，统计当日Jail/Prison两类场所的在押人数。例如：

• 2022年1月30日Jail在押人数为2
• 2022年2月20日Prison在押人数为2

已完成代码片段 #

from UNNEST(
        GENERATE_DATE_ARRAY(
            (select min(date) from base),
            current_date(),
            INTERVAL 1 DAY
        )
    ) as dt -- 生成日期范围内的每日一行数据

预期输出表格 #

解决方案（BigQuery SQL） #

核心思路是先确定每个人员各阶段场所的生效时间段，再将日期与场所做全组合匹配，最终统计对应日期的在押人数：

WITH date_range AS (
    -- 生成从最早记录日期到当前日期的所有日期
    SELECT dt FROM UNNEST(GENERATE_DATE_ARRAY((SELECT MIN(Date) FROM base), CURRENT_DATE(), INTERVAL 1 DAY)) AS dt
),
-- 为每个Booking_ID的场所记录确定生效时间段
booking_periods AS (
    SELECT 
        Booking_ID,
        Type,
        Date AS start_date,
        -- 下一条转场日期作为当前场所结束时间，无后续记录则用释放日期（未释放取当前日期）
        COALESCE(LEAD(Date) OVER (PARTITION BY Booking_ID ORDER BY Date), 
                 COALESCE(Release_Date, CURRENT_DATE())) AS end_date
    FROM base
),
-- 生成日期与场所的全组合，确保每个日期都有两类场所的记录
date_type_combinations AS (
    SELECT dt, type 
    FROM date_range
    CROSS JOIN (SELECT DISTINCT Type FROM base) types
)
-- 统计每个日期-场所的在押人数
SELECT 
    dc.dt AS Date,
    dc.type AS Type,
    COUNT(DISTINCT bp.Booking_ID) AS Count
FROM date_type_combinations dc
LEFT JOIN booking_periods bp 
    ON dc.type = bp.type
    AND dc.dt BETWEEN bp.start_date AND bp.end_date
GROUP BY dc.dt, dc.type
ORDER BY dc.dt, dc.type;

代码说明 #

1. date_range：复用你已实现的日期范围生成逻辑。
2. booking_periods：用LEAD()函数获取同一人员下一次转场的日期，作为当前场所的结束时间；如果是最后一条记录，则用释放日期（未释放则取当前日期）。
3. date_type_combinations：通过笛卡尔积让每个日期都对应Jail和Prison两种类型，避免出现人数为0时无记录的情况。
4. 最终通过左关联匹配日期落在生效区间内的人员，统计去重后的Booking_ID数量，确保同一人员不会被重复统计。

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