如何统计一天内各小时段去重在住房间数，无需重复编写24次SQL

实现方案 #

核心逻辑是先生成全量的时间窗口序列，再通过一次关联聚合完成所有时段的统计，同时天然支持自定义时间间隔调整。

通用SQL实现（适配Sigma对接的多数SQL数据源，比如Snowflake/PostgreSQL/BigQuery等） #

你可以直接使用以下SQL，仅需调整time_interval参数即可切换统计粒度：

-- 定义统计粒度，小时设为'1 hour'，半小时设为'30 minutes'，分钟级设为'1 minute'
with params as (
    select '1 hour'::interval as time_interval,
           -- 统计的日期范围，可按需调整
           '2021-10-01'::date as stat_date
),
-- 自动生成所有时间窗口
time_windows as (
    select
        stat_date + (generate_series(0, 24*3600 / extract(epoch from time_interval)::int - 1) * time_interval) as window_start,
        stat_date + ((generate_series(0, 24*3600 / extract(epoch from time_interval)::int - 1) +1 ) * time_interval) as window_end
    from params
)
select
    -- 格式化输出时段字符串，比如09:00-10:00
    to_char(t.window_start, 'HH24:MI') || '-' || to_char(t.window_end, 'HH24:MI') as "Time period",
    count(distinct d.Room_ID) as "Number of rooms"
from time_windows t
cross join data d
-- 沿用你已验证有效的在住判断逻辑
where d.Check_out_time > t.window_start
  and d.Check_in_time < t.window_end
group by t.window_start, t.window_end
order by t.window_start

Sigma可视化操作适配方案 #

如果你不想写原生SQL，也可以在Sigma中通过低代码方式实现：

1. 先创建一个时间维度公共数据集，提前预置好所有你可能用到的时间间隔的窗口数据（小时/半小时/分钟级）
2. 将酒店入住数据表和时间维度数据集做交叉关联
3. 添加过滤条件：Check_out_time > 窗口起始时间 且 Check_in_time < 窗口结束时间
4. 按时间窗口分组，添加count(distinct Room_ID)的聚合计算字段即可
5. 后续切换统计粒度仅需筛选时间维度数据集的对应间隔字段即可

兼容方案 #

如果你的数据源不支持generate_series函数，也可以手动构造一次全量窗口（后续复用即可），示例如下：

time_windows as (
    select '2021-10-01 00:00:00' as window_start, '2021-10-01 01:00:00' as window_end union all
    select '2021-10-01 01:00:00' as window_start, '2021-10-01 02:00:00' as window_end union all
    -- 剩余时段以此类推填充即可
    ...
)

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