从字节跳动SQL面试题看数据思维：如何用中间表设计优雅解决亿级数据问题

-- ❌ 没有人会在生产环境这么写
SELECT COUNT(DISTINCT a.uid)
FROM visit_log a
JOIN visit_log b ON a.uid = b.uid
WHERE a.date = 'T' AND b.date BETWEEN 'T+1' AND 'T+7'

-- 每日创建用户状态快照表（T+1凌晨运行）
CREATE TABLE dws_user_daily_snapshot (
    uid BIGINT COMMENT '用户ID',
    date STRING COMMENT '快照日期',

    -- 核心状态字段
    first_visit_date STRING COMMENT '首次访问日期',
    last_visit_date STRING COMMENT '最后访问日期',
    total_visit_days INT COMMENT '累计访问天数',

    -- 近期活跃度指标（这些字段直接支撑业务查询）
    visits_last_1d INT COMMENT '近1天访问天数',
    visits_last_7d INT COMMENT '近7天访问天数',
    visits_last_30d INT COMMENT '近30天访问天数',
    visits_last_365d INT COMMENT '近365天访问天数',

    -- 访问间隔特征
    last_visit_gap_hours INT COMMENT '最近两次访问间隔小时数',
    is_frequent_user INT COMMENT '是否24小时内多次访问用户',

    -- 留存标记（预计算）
    is_new_user INT COMMENT '是否新用户',
    is_retained_1d INT COMMENT '是否次日留存',
    is_retained_7d INT COMMENT '是否7日留存',
    is_retained_30d INT COMMENT '是否30日留存'
)
PARTITIONED BY (date STRING)
STORED AS PARQUET;

-- 问题1：7日留存率（直接查询，秒级返回）
SELECT
    COUNT(CASE WHEN is_new_user = 1 THEN uid END) as new_users,
    COUNT(CASE WHEN is_retained_7d = 1 THEN uid END) as retained_users,
    COUNT(CASE WHEN is_retained_7d = 1 THEN uid END) * 100.0 /
    COUNT(CASE WHEN is_new_user = 1 THEN uid END) as retention_rate_7d
FROM dws_user_daily_snapshot
WHERE date = DATE_SUB(CURRENT_DATE, 7);  -- T+7查询T日留存

-- 问题2：用户访问天数（直接读取）
SELECT
    uid,
    visits_last_1d,
    visits_last_7d,
    visits_last_30d,
    visits_last_365d
FROM dws_user_daily_snapshot
WHERE date = CURRENT_DATE;

-- 问题3：24小时内访问用户（已预计算）
SELECT uid
FROM dws_user_daily_snapshot
WHERE date = CURRENT_DATE
  AND is_frequent_user = 1;

-- ODS层：原始数据，保持原貌
CREATE TABLE ods_visit_log LIKE visit_log;

-- DWD层：清洗后的明细数据
CREATE TABLE dwd_visit_detail (
    uid BIGINT,
    visit_time TIMESTAMP,
    session_id STRING,
    -- 清洗：去重、过滤异常
) PARTITIONED BY (date STRING);

-- DWS层：轻度汇总表（用户粒度）
CREATE TABLE dws_user_daily_agg (
    uid BIGINT,
    date STRING,
    visit_count INT,
    total_duration INT
) PARTITIONED BY (date STRING);

-- ADS层：应用层指标表
CREATE TABLE ads_retention_metrics (
    date STRING,
    new_users INT,
    retained_1d INT,
    retained_7d INT,
    retention_rate_1d FLOAT,
    retention_rate_7d FLOAT
) PARTITIONED BY (date STRING);

-- 万能模板：能应付80%的场景
WITH temp AS (
    SELECT uid, date, [其他字段]
    FROM visit_log
    WHERE date = '条件'  -- 记得分区裁剪
    GROUP BY uid, date   -- 记得去重
)
SELECT
    COUNT(DISTINCT uid),
    其他聚合函数
FROM temp;