各种深挖 -— 这个我就不细写了，前面有很多给出了具体问题的简历深挖，大家可以去看看

1. dwm表与dwd表的粒度
2. 事实表的创建
3. 表数据的体量:dwd表:几千条、dwm表:曝光 几万条，旺季几十万条
4. 看板加载时长缩短做了什么
5. SQL:各直播间最大同时在线人数

1. DWM表与DWD表的粒度

考察知识点

• 数仓分层中“粒度”的核心定义（数据细化程度）
• DWD与DWM层的定位差异对粒度的影响
• 粒度设计与数据体量、业务需求的匹配逻辑

参考回答

数据粒度指数据的“细化程度”（如一条记录代表“一笔订单”还是“一个用户的日订单汇总”），DWD表与DWM表的粒度差异由两层定位决定，结合“DWD表几千条、DWM表几万至几十万条”的体量特征，具体区别如下：

补充注意要点

• 粒度“细→粗”的转化逻辑：DWM表粒度是DWD表的“聚合升级”，需明确聚合维度（如按“用户+时间”“业务实体+周期”），避免无目的聚合导致粒度混乱；
• 体量差异的合理性：若DWM表体量远大于DWD表（如DWD几千条→DWM几十万条），需确认是否存在“一对多”聚合（如一个DWD明细对应多个DWM维度，如“一笔订单关联多个商品，按‘订单+商品’聚合”），避免因冗余导致体量异常；
• 与业务规模匹配：几千条DWD表通常对应“小众业务”（如某内部管理系统数据），DWM表的体量增长需与业务旺季的实际需求匹配（如直播业务旺季用户行为频次提升，导致每小时聚合记录数增加）。

2. 事实表的创建

考察知识点

• 事实表的核心特征（度量值、关联维度、时间戳）
• 事实表创建的全流程（需求分析→表结构设计→数据加载）
• 不同类型事实表（事务/快照/累积）的创建差异

参考回答

事实表是数仓中存储“业务过程度量值”（如订单金额、点击次数）的核心表，创建需围绕“业务过程、粒度、度量值”三大核心，步骤如下：

（1）创建前的核心准备

1. 明确业务过程与表类型：
   • 事务事实表（记录单次业务事件）：如“订单创建事实表”“用户点击事实表”，适合业务过程离散（如下单、点击）；
   • 周期快照事实表（按固定周期记录状态）：如“每日直播间用户活跃事实表”，适合监控实体状态变化（如每日活跃用户数）；
   • 累积快照事实表（记录业务全生命周期）：如“订单从创建到签收的事实表”，适合追踪跨周期业务（如订单履约全流程）。
2. 定义粒度：确定单条记录代表的业务单元（如事务事实表按“order_id（订单ID）”粒度，周期快照表按“room_id+date（直播间+日）”粒度）。
3. 梳理度量值与维度关联：
   • 度量值：可量化的业务指标（如订单金额amount、点击次数click_cnt、在线人数online_num）；
   • 关联维度：需关联的维度标识（如user_id（用户维度）、room_id（直播间维度）、date（时间维度）），暂不关联维度表（仅保留id）。

（2）表结构设计（以Hive为例，直播点击事务事实表）

-- 1. 建表语句（事务事实表，DWD层，粒度：单次点击）
CREATE TABLE dwd_live_click (
    click_id STRING COMMENT '点击ID（唯一标识，如UUID）',
    room_id STRING COMMENT '直播间ID（关联直播间维度表）',
    user_id STRING COMMENT '用户ID（关联用户维度表）',
    click_time DATETIME COMMENT '点击时间（精确到秒）',
    click_position STRING COMMENT '点击位置（如“商品卡片”“评论区”）',
    device_type STRING COMMENT '设备类型（如“iOS”“Android”）',
    dt STRING COMMENT '分区字段（日期，格式：yyyyMMdd）'
)
COMMENT '直播点击明细事实表（DWD层）'
PARTITIONED BY (dt STRING)  -- 按日期分区，便于按时间查询
STORED AS ORC  -- 列存格式，支持压缩和列裁剪
TBLPROPERTIES (
    'orc.compress' = 'snappy',  -- Snappy压缩，平衡压缩率和速度
    'transient_lastDdlTime' = '1690000000'
);

-- 2. 周期快照事实表（DWM层，粒度：直播间+小时）
CREATE TABLE dwm_live_hourly_click (
    room_id STRING COMMENT '直播间ID',
    hour STRING COMMENT '小时（格式：HH，如“20”代表20点-21点）',
    click_cnt BIGINT COMMENT '每小时点击次数（度量值）',
    user_cnt BIGINT COMMENT '每小时点击用户数（去重，度量值）',
    ios_click_cnt BIGINT COMMENT 'iOS设备点击次数（细分度量值）',
    android_click_cnt BIGINT COMMENT 'Android设备点击次数（细分度量值）',
    dt STRING COMMENT '分区字段（日期：yyyyMMdd）'
)
COMMENT '直播点击小时快照事实表（DWM层）'
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress' = 'snappy'
);

（3）数据加载逻辑

DWM层加载（从DWD层轻度聚合）：

INSERT OVERWRITE TABLE dwm_live_hourly_click PARTITION (dt='20240901')
SELECT
    room_id,
    DATE_FORMAT(click_time, 'HH') AS hour,  -- 按小时聚合
    COUNT(click_id) AS click_cnt,  -- 每小时点击次数
    COUNT(DISTINCT user_id) AS user_cnt,  -- 每小时点击用户数（去重）
    COUNT(CASE WHEN device_type='iOS' THEN click_id END) AS ios_click_cnt,  -- iOS设备点击数
    COUNT(CASE WHEN device_type='Android' THEN click_id END) AS android_click_cnt  -- Android设备点击数
FROM dwd_live_click
WHERE dt='20240901'
GROUP BY room_id, DATE_FORMAT(click_time, 'HH');  -- 按“直播间+小时”聚合（DWM粒度）

DWD层加载（从ODS层同步清洗）：

INSERT OVERWRITE TABLE dwd_live_click PARTITION (dt='20240901')
SELECT
    click_id,
    room_id,
    user_id,
    FROM_UNIXTIME(unix_timestamp(click_time_str, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd HH:mm:ss') AS click_time,  -- 时间格式标准化
    click_position,
    device_type
FROM ods_live_click  -- ODS层原始点击日志
WHERE dt='20240901'
  AND click_id IS NOT NULL  -- 过滤无效数据（点击ID为空）
  AND room_id IS NOT NULL;  -- 过滤无直播间ID的记录

补充注意要点

• 度量值需“可累加”：事实表的核心是度量值（如click_cnt“amount”），需选择可聚合的数值型字段，避免存储非度量值（如用户昵称，应放在维度表）；
• 分区设计必选：事实表数据量随时间增长，需按时间（dt）分区，避免全表扫描；
• 与维度表关联时机：创建事实表时仅保留维度id（如user_id），不直接关联维度表（如dim_user），关联操作留到查询时（如DWS层聚合或看板查询），保证事实表的通用性。

3. 看板加载时长缩短做了什么

考察知识点

• 看板加载慢的核心瓶颈（数据量、SQL效率、存储格式）
• 优化手段与数仓分层（DWD/DWM/DWS）的结合
• 针对“DWD几千条、DWM几万至几十万条”的体量优化逻辑

参考回答

看板加载慢的核心原因是“查询数据量过大”“SQL逻辑冗余”“存储格式低效”，结合业务数据体量（DWD几千条、DWM几万至几十万条，属中小规模数据），通过以下4点优化可显著缩短加载时长：

（1）基于中间层预聚合，减少查询计算量

核心逻辑：将看板高频使用的指标（如“直播间小时点击数”“用户日下单金额”）在DWM/DWS层预聚合，避免直接查询DWD明细数据。

• 原方案：看板直接查询DWD层dwd_live_click（几千条），实时计算“每小时点击数”（需GROUP BY room_id, hour），每次查询耗时5-8秒；
• 优化后：提前在DWM层创建dwm_live_hourly_click（几万条），预计算“room_id+hour”粒度的点击数、用户数，看板直接查询该表，无需聚合，加载时长缩短至1-2秒；
• 旺季适配：DWM表旺季达几十万条，仍比实时聚合DWD明细（若业务增长导致DWD达几万条）效率高3-5倍，因预聚合已完成大部分计算。

（2）优化SQL逻辑，避免冗余计算

1. 字段裁剪与分区过滤：
   • 原SQL：SELECT * FROM dwd_live_click WHERE dt BETWEEN '20240825' AND '20240901'（查询所有字段，时间范围过大）；
   • 优化SQL：SELECT room_id, hour, click_cnt FROM dwm_live_hourly_click WHERE dt='20240901'（仅查必要字段，限定当日分区），数据扫描量减少80%；
2. 替换低效算子：
   • 避免COUNT(DISTINCT)：DWM层预计算user_cnt（去重用户数），看板直接引用，替代实时COUNT(DISTINCT user_id)（效率提升10倍）；
   • 用JOIN替代子查询：如关联直播间维度表时，用LEFT JOIN dim_room ON a.room_id = b.room_id替代WHERE room_id IN (SELECT room_id FROM dim_room)。

（3）优化存储格式与压缩

1. 更换列存格式：将DWM/DWS表从Text/CSV转为ORC/Parquet（列存格式），支持“列裁剪”（仅读取必要字段）和“ predicate pushdown ”（过滤条件下推至存储层）；
   • 效果：DWM表用ORC格式后，数据体积减少60%，看板查询时仅加载“room_id”“click_cnt”等3个字段，IO时间从3秒缩短至0.5秒；
2. 启用Snappy压缩：ORC/Parquet表启用Snappy压缩（压缩率高且解压快），平衡存储成本和读取速度，避免用Gzip（解压慢）或不压缩（体积大）。

（4）优化看板工具与资源配置

1. 选择轻量查询引擎：若原使用Hive（批处理引擎）查询，切换至ClickHouse/Doris（OLAP引擎），针对聚合查询优化，响应时间缩短50%以上；
2. 配置缓存策略：对高频查询（如“当日直播间TOP10点击榜”），在看板工具（如Superset/Tableau）中设置缓存（缓存有效期10分钟），重复查询直接读取缓存，无需重新计算；
3. 分配独立资源：若使用Spark查询，为看板查询任务分配独立队列（如2个Executor，4G内存），避免与其他ETL任务争抢资源，高峰期加载时间稳定在2秒内。

补充注意要点

• 优先“预聚合”：中小规模数据（DWM几十万条）的优化核心是“减少实时计算”，DWM层预聚合性价比最高，无需过度依赖硬件升级；
• 避免“过度优化”：DWD表仅几千条时，无需复杂优化（如分桶），聚焦DWM层即可；旺季数据增长时，重点监控DWM表聚合任务的执行效率，避免延迟影响看板加载；
• 监控与迭代：通过看板工具的“查询耗时统计”功能，定期分析慢查询，针对性优化（如某SQL耗时超5秒，检查是否未用DWM表或缺少分区过滤）。

4. SQL：各直播间最大同时在线人数

考察知识点

• 实时/准实时数据中“同时在线人数”的计算逻辑（基于时间窗口的去重计数）
• 窗口函数（如LAG/LEAD）或会话分析函数的应用
• 数据粒度（如用户进入/退出日志）对计算结果的影响

参考回答

计算“各直播间最大同时在线人数”的核心是“统计某一时刻直播间内的用户数，取最大值”，需基于“用户进入/退出直播间的行为日志”（粒度：单次用户进入/退出事件），通过“时间线展开+用户状态标记”实现，具体SQL如下：

（1）前提条件与数据模型

假设存在DWD层用户直播间行为日志表dwd_live_user_behavior，记录用户进入（action='enter'）和退出（action='exit'）直播间的事件，表结构如下：

（2）SQL实现逻辑（基于时间窗口的状态计算）

WITH
-- 步骤1：标记用户进入/退出的状态（进入+1，退出-1）
user_status AS (
    SELECT
        room_id,
        user_id,
        action_time,
        CASE
            WHEN action = 'enter' THEN 1  -- 进入：在线人数+1
            WHEN action = 'exit' THEN -1  -- 退出：在线人数-1
        END AS status_change
    FROM dwd_live_user_behavior
    WHERE dt = '20240901'  -- 限定查询日期
      AND action IN ('enter', 'exit')  -- 仅保留进入/退出行为
),

-- 步骤2：按直播间和时间排序，计算累计在线人数（实时在线人数）
live_online AS (
    SELECT
        room_id,
        action_time,
        -- 按直播间分组，按时间排序，累计状态变化值（即当前在线人数）
        SUM(status_change) OVER (
            PARTITION BY room_id
            ORDER BY action_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS current_online
    FROM user_status
    -- 处理异常：同一用户同一时间的重复行为（如重复点击进入，去重保留第一条）
    QUALIFY ROW_NUMBER() OVER (PARTITION BY room_id, user_id, action_time ORDER BY behavior_id) = 1
)

-- 步骤3：按直播间分组，取最大在线人数
SELECT
    room_id,
    MAX(current_online) AS max_simultaneous_online  -- 各直播间最大同时在线人数
FROM live_online
GROUP BY room_id
ORDER BY max_simultaneous_online DESC;

（3）关键逻辑说明

1. 状态标记（步骤1）：将“进入”行为标记为+1（在线人数增加），“退出”行为标记为1（在线人数减少），为后续累计计算做准备；
2. 累计在线人数计算（步骤2）：
   • 用窗口函数SUM(status_change) OVER (...)，按room_id分组（区分直播间）、action_time排序（按时间先后），累计状态变化值，得到“每个时间点的实时在线人数”；
   • 用QUALIFY过滤重复行为（如用户同一秒内重复点击“进入”，避免重复计数）；
3. 取最大值（步骤3）：按room_id分组，取current_online的最大值，即该直播间当天的“最大同时在线人数”。

（4）特殊场景处理

1. 用户未主动退出（如直接关闭页面）：
3. 时间粒度细化（如精确到毫秒）：
   • 若action_time精确到毫秒（如2024-09-01 20:00:00.123），排序时会更精准，避免同一秒内的行为顺序错乱（不影响最终最大值，仅影响中间过程）。

若日志表无exit行为记录（仅enter），需补充“会话超时退出”逻辑（如用户30分钟无操作视为退出）：

-- 补充超时退出记录
UNION ALL
SELECT
    room_id,
    user_id,
    DATE_ADD(action_time, INTERVAL 30 MINUTE) AS action_time,  -- 进入后30分钟视为退出
    -1 AS status_change
FROM user_status
WHERE action = 'enter'
  -- 过滤已存在退出记录的用户
  AND NOT EXISTS (
      SELECT 1 FROM user_status u2
      WHERE u2.room_id = u1.room_id
        AND u2.user_id = u1.user_id
        AND u2.action = 'exit'
  )

补充注意要点

• 数据粒度是关键：需基于“用户进入/退出”的细粒度日志（DWD层），若仅用“用户在线快照”（如每5分钟记录一次在线用户），会导致计算结果偏差（如快照间隔内的峰值未被捕捉）；
• 性能优化：若数据量较大（如单日百万级行为日志），可先在DWM层按“room_id+minute”预聚合“分钟级在线人数”，再基于DWM表计算最大值（减少SQL计算量）；
• 结果验证：可通过“人工抽查”验证（如某直播间已知峰值200人，SQL结果应接近该值），避免因异常数据（如测试用户重复进入）导致结果失真。