SSAS中按日期与小时统计ID数量的实现方法咨询

核心思路 #

你的需求本质是统计每个小时区间内，有多少ID的时间范围（开始日期+开始时间 到 结束日期+结束时间）包含该小时，需要先补全日期维度的小时层级，再通过MDX度量值实现区间判断。

步骤1：扩展日期维度的小时层级 #

当前日期维度只有到日历日，需要新增小时层级：

• 在日期维度表中添加Hour字段（取值0-23），并构建完整层级结构：年度 -> 季度 -> 月份 -> 日历日 -> 小时
• 确保维度表中每个日历日对应24条小时记录（例如2024-05-20需生成24条记录，分别对应当日00:00到23:00的小时区间）

步骤2：创建MDX度量值 #

在事实表的度量值组中创建计算度量值，用以下MDX代码实现区间判断：

CREATE MEMBER CURRENTCUBE.[Measures].[每小时ID数量]
AS 
COUNT(
    FILTER(
        [事实表].[ID].[ID].Members,
        -- 判断当前小时是否被ID的时间范围覆盖
        (
            [事实表].[开始日期].CurrentMember.MemberValue + [事实表].[开始时间].CurrentMember.MemberValue 
            <= 
            [日期维度].[日历日-小时].CurrentMember.MemberValue
        )
        AND
        (
            [事实表].[结束日期].CurrentMember.MemberValue + [事实表].[结束时间].CurrentMember.MemberValue 
            >= 
            [日期维度].[日历日-小时].CurrentMember.MemberValue + TIME(1,0,0) -- 当前小时区间的结束时间（当前小时+1小时）
        )
    )
),
FORMAT_STRING = "#,##0",
VISIBLE = 1;

代码说明 #

• [日期维度].[日历日-小时].CurrentMember.MemberValue：获取当前选中的小时维度成员的起始时间值（如2024-05-20 10:00）
• 第一个条件：ID的开始时间不晚于当前小时的起始时间
• 第二个条件：ID的结束时间不早于当前小时的结束时间
• COUNT函数统计符合区间条件的ID总数

步骤3：验证与调整 #

• 将日期维度的小时层级拖到报表的行/列区域，搭配每小时ID数量度量值，即可得到按小时统计的结果
• 若事实表中开始/结束时间为单独字段，需确保日期与时间的拼接逻辑正确（可通过DATEADD或CAST转换为datetime类型）
• 对于跨天的ID（比如开始时间为2024-05-20 22:00、结束时间为2024-05-21 01:00），上述逻辑会自动将其统计到20日22、23点和21日0、1点的结果中

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