Hive SQL 与 Pandas 面试复习手册

这份手册按“先掌握规则，再练完整题”的顺序整理。示例以 Hive SQL 为主；遇到日期函数、时间间隔和非等值 Join 时，应先确认题目使用的 SQL 方言与版本。

一、查询基础

SQL 逻辑执行顺序

FROM → JOIN / ON → WHERE → GROUP BY → HAVING
→ SELECT → DISTINCT → ORDER BY → LIMIT

• WHERE 执行时，SELECT 中定义的别名通常还不存在。
• 窗口函数一般在分组聚合之后、最终排序之前计算。
• 窗口函数不能直接用于 WHERE，应先放入 CTE 或子查询，再在外层筛选。

COUNT 与去重

• COUNT(*)：统计全部行。
• COUNT(col)：统计该列非 NULL 的行。
• COUNT(DISTINCT col)：统计非 NULL 的去重值。
• COUNT(1)：在多数现代数据库中与 COUNT(*) 等价。

单纯去重时优先使用 DISTINCT；需要同时计算聚合指标时使用 GROUP BY。

SELECT DISTINCT
    user_id,
    TO_DATE(login_time) AS login_date
FROM login_logs;

SELECT
    user_id,
    TO_DATE(login_time) AS login_date,
    COUNT(*) AS login_count
FROM login_logs
GROUP BY user_id, TO_DATE(login_time);

二、分位数与排名

$PERCENT_RANK$ 的公式是 (当前排名 - 1) / (总行数 - 1)。并列成绩共享排名，因此筛出的行数不一定严格等于总人数的 20%。

WITH ranked AS (
    SELECT
        student_id,
        score,
        PERCENT_RANK() OVER (ORDER BY score ASC) AS pct_rank
    FROM exam
    WHERE subject = '语文'
)
SELECT student_id, score
FROM ranked
WHERE pct_rank <= 0.20;

• $PERCENT_RANK$：每一行的排名百分位。
• $CUME_DIST$：小于等于当前值的累计行数比例。
• NTILE(5)：按人数尽量分成五组，可能拆开同分学生。
• $PERCENTILE_APPROX$：返回具体的分位点数值。

SELECT
    PERCENTILE_APPROX(score, 0.50) AS p50,
    PERCENTILE_APPROX(score, 0.90) AS p90
FROM exam;

三、日期与时间

Unix 时间戳

-- 正常时间转秒级时间戳
UNIX_TIMESTAMP('2026-09-21 15:30:00', 'yyyy-MM-dd HH:mm:ss')

-- 秒级时间戳转正常时间
FROM_UNIXTIME(ts, 'yyyy-MM-dd HH:mm:ss')

-- 毫秒时间戳转正常时间
FROM_UNIXTIME(CAST(ts_ms / 1000 AS BIGINT))

-- 计算时间差，单位为秒
UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)

格式字符串中，MM 表示月份，mm 表示分钟；HH 表示 24 小时制，hh 表示 12 小时制。

UTC 与本地时间

-- UTC 转北京时间
FROM_UTC_TIMESTAMP(CAST(utc_time AS TIMESTAMP), 'Asia/Shanghai')

-- 北京时间转 UTC
TO_UTC_TIMESTAMP(CAST(local_time AS TIMESTAMP), 'Asia/Shanghai')

优先使用标准时区名称，以正确处理夏令时。只有题目明确给出固定时差时，才使用 $UNIX_TIMESTAMP(utc_time) + offset * 3600$。

月份截断

-- PostgreSQL 等数据库
DATE_TRUNC('month', order_time)

-- Hive 常见写法
TRUNC(order_time, 'MM')
DATE_FORMAT(order_time, 'yyyy-MM')

不要默认所有 Hive 版本都支持 $DATE_TRUNC$。面试作答前先说明使用的 SQL 方言。

四、窗口函数

LAG 与 LEAD

SELECT
    user_id,
    month,
    amount,
    LAG(amount) OVER (
        PARTITION BY user_id ORDER BY month
    ) AS previous_amount,
    LEAD(amount) OVER (
        PARTITION BY user_id ORDER BY month
    ) AS next_amount
FROM monthly_orders;

LAG(amount, 2, 0) 表示读取前两行，不存在时返回 0。通常建议保留 NULL：NULL 表示没有上一期，0 表示上一期真实值为零。

计算月环比

WITH monthly AS (
    SELECT
        user_id,
        DATE_FORMAT(order_time, 'yyyy-MM') AS month,
        SUM(amount) AS monthly_amount
    FROM orders
    GROUP BY user_id, DATE_FORMAT(order_time, 'yyyy-MM')
),
with_previous AS (
    SELECT
        *,
        LAG(monthly_amount) OVER (
            PARTITION BY user_id ORDER BY month
        ) AS previous_amount
    FROM monthly
)
SELECT
    *,
    monthly_amount * 1.0 / NULLIF(previous_amount, 0) - 1 AS mom_rate
FROM with_previous;

LAG 获取的是上一条记录，并不保证它属于上一自然月。日期可能缺失时应先补齐日历；排序值并列时，应加入 $event_id$ 等唯一字段。

ROWS 与 RANGE

SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY month, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_amount

• PARTITION BY 定义独立计算的分区，ORDER BY 决定行的先后顺序。
• ROWS 按物理行定义窗口；RANGE 会把相同排序值的 peer rows 一起纳入。
• 唯一排序键解决“谁先谁后”，ROWS 解决“窗口包含哪些行”。

滚动 7 日

SUM(daily_amount) OVER (
    PARTITION BY city_id
    ORDER BY dt
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_amount

正确步骤是先按天聚合，再用日历表补齐缺失日期、将缺失金额填为 0，最后计算当前行与之前 6 行的合计。ROWS 表示 7 行，并不天然等于 7 个自然日。

五、Join 的规则与陷阱

先确认表的粒度

如果 A 表中某用户有 2 行，B 表中同一用户有 3 行，只按 $user_id$ 连接会得到 $2 × 3 = 6$ 行。Join 前应先确认一行代表什么，必要时先聚合或去重。

ON 中不宜直接使用的表达式

• 聚合函数：先在 CTE 中完成 GROUP BY。
• 窗口函数：先在 CTE 中排名，再使用 rn 连接或筛选。
• 本层 SELECT 新建的别名：ON 早于 SELECT 执行。
• 复杂相关子查询：改写为显式 Join 或 LEFT SEMI JOIN。
• 高风险 OR：容易重复匹配且难以优化，通常拆分后再 UNION。

非等值条件

ON  a.city_id = b.city_id
AND a.event_time >= b.effective_start_time
AND a.event_time <  b.effective_end_time

现代 Hive 通常支持等值键加范围条件；纯非等值 Join 可能非常昂贵，并且受版本限制。

LEFT JOIN 的过滤位置

SELECT
    u.*,
    o.order_id
FROM users u
LEFT JOIN orders o
    ON  u.user_id = o.user_id
    AND o.status = 'success';

如果把 $o.status = 'success'$ 放进 WHERE，没有匹配订单的用户会被过滤，LEFT JOIN 会退化成类似 INNER JOIN 的结果。

LEFT SEMI JOIN

• 只判断右表是否存在匹配。
• 只能返回左表字段。
• 右表多条匹配不会复制左表。
• 不会消除左表自身的重复行。
• 逻辑上类似 WHERE EXISTS。

SELECT
    u.user_id,
    u.user_name
FROM users u
LEFT SEMI JOIN orders o
    ON  u.user_id = o.user_id
    AND o.status = 'success';

查找右表不存在的记录，可以使用 LEFT ANTI JOIN，或在 LEFT JOIN 后筛选右表键 IS NULL。

六、Hive 复杂类型

COLLECT_LIST

$COLLECT_LIST$ 将分组内多行收集为数组，并保留重复值；$COLLECT_SET$ 会去重。两者默认都不保证顺序。

SELECT
    user_id,
    COLLECT_LIST(product_id) AS product_list
FROM user_product
GROUP BY user_id;

NAMED_STRUCT 与结构体数组

SELECT
    user_id,
    COLLECT_LIST(
        NAMED_STRUCT(
            'order_id', order_id,
            'order_time', order_time,
            'amount', amount
        )
    ) AS order_list
FROM orders
GROUP BY user_id;

返回类型为 $array<struct<...>>$，字段名和字段值必须成对出现。

SELECT
    user_id,
    order_info.order_id,
    order_info.amount
FROM user_order_array
LATERAL VIEW EXPLODE(order_list) exploded AS order_info;

需要数组位置时使用 POSEXPLODE。需要排序时可根据 Hive 版本尝试 $SORT_ARRAY$，但结构体排序能力取决于具体版本。

七、Pandas 对照写法

分组窗口均值

# SQL: AVG(a) OVER (PARTITION BY b)
df['c'] = df.groupby('b')['a'].transform('mean')

transform 返回与原 DataFrame 等长的 Series；agg 会将每个分组压缩为一行。

累计和、LAG 与环比

df = df.sort_values(['user_id', 'month', 'order_id'])

df['cumulative_amount'] = (
    df.groupby('user_id')['amount'].cumsum()
)

df['previous_amount'] = (
    df.groupby('user_id')['amount'].shift(1)
)

df['growth_rate'] = (
    df['amount']
    / df['previous_amount'].replace(0, float('nan'))
    - 1
)

分组百分位排名

df['pct_rank'] = (
    df.groupby('city')['amount']
      .rank(pct=True, method='min')
)

使用 shift 或 cumsum 之前，应先按分组键和时间字段排序。

八、SQL 实战题

下面的题目统一使用“口径 → 解法 → SQL → 易错点”的顺序。正式作答时，不要只写代码；先说明一行代表什么、统计窗口是什么、缺失值如何处理。

题 1：计算严格 D7 留存率

严格 D7 指用户在注册后的第 7 个自然日下单；注册后 1 至 7 日内下单属于窗口留存，不是严格 D7。如果题目要求按城市统计，但 users 表中没有城市字段，应主动指出字段缺失。

SELECT
    u.city,
    COUNT(DISTINCT CASE
        WHEN TO_DATE(o.order_date) = DATE_ADD(TO_DATE(u.register_date), 7)
        THEN u.user_id
    END) * 1.0
    / COUNT(DISTINCT u.user_id) AS d7_retention
FROM users u
LEFT JOIN orders o
    ON u.user_id = o.user_id
GROUP BY u.city
ORDER BY d7_retention DESC;

题 2：计算每个用户的最长连续登录天数

先按用户和日期去重，再使用“日期减行号”将连续日期映射到同一分组。

WITH login_days AS (
    SELECT
        user_id,
        TO_DATE(login_time) AS login_date
    FROM login_logs
    GROUP BY user_id, TO_DATE(login_time)
),
numbered AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY login_date
        ) AS rn
    FROM login_days
),
grouped AS (
    SELECT
        user_id,
        DATE_SUB(login_date, CAST(rn AS INT)) AS streak_group
    FROM numbered
),
streaks AS (
    SELECT
        user_id,
        streak_group,
        COUNT(*) AS streak_days
    FROM grouped
    GROUP BY user_id, streak_group
)
SELECT
    user_id,
    MAX(streak_days) AS max_streak
FROM streaks
GROUP BY user_id;

题 3：筛选连续 30 天销售额增长的店铺

先确认缺失日期的业务口径：

• 如果缺失日期直接打断连续增长，不需要补全日期，但必须同时判断日期相邻和销售额增长。
• 如果缺失日期要按销售额 0 参与比较，就必须使用日历表、递归日期序列或数字表补出日期。

以下 SQL 采用“缺失日期直接打断”的口径。

WITH daily AS (
    SELECT
        shop_id,
        TO_DATE(order_time) AS sale_date,
        SUM(amount) AS sales_amount
    FROM orders
    GROUP BY shop_id, TO_DATE(order_time)
),
previous_day AS (
    SELECT
        *,
        LAG(sale_date) OVER (
            PARTITION BY shop_id ORDER BY sale_date
        ) AS prev_date,
        LAG(sales_amount) OVER (
            PARTITION BY shop_id ORDER BY sale_date
        ) AS prev_amount
    FROM daily
),
marked AS (
    SELECT
        *,
        CASE
            WHEN DATEDIFF(sale_date, prev_date) = 1
             AND sales_amount > prev_amount
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM previous_day
),
grouped AS (
    SELECT
        *,
        SUM(is_new_group) OVER (
            PARTITION BY shop_id
            ORDER BY sale_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS streak_group
    FROM marked
)
SELECT
    shop_id,
    MIN(sale_date) AS start_date,
    MAX(sale_date) AS end_date,
    COUNT(*) AS consecutive_days
FROM grouped
GROUP BY shop_id, streak_group
HAVING COUNT(*) >= 30;

$streak_group$ 是人为生成的“连续增长区间编号”，不是原始表字段。

先标记新分组，再对标记累计求和：

is_new_group: 1 0 0 1 0 1 0
累计求和:     1 1 1 2 2 3 3

累计值相同的记录属于同一个连续增长区间。最后按 $shop_id, streak_group$ 分组，并使用 $HAVING COUNT(*) >= 30$ 筛选持续至少 30 天的区间。

如果缺失的第一天要按销售额 0 参与比较，例如 01-01 无订单、01-02 销售额为 10、01-03 为 20，那么不生成 01-01 这条记录就会少算一天。这种口径下必须先补全日期。

题 4：计算首次支付与最终支付成功率

先说明假设：一行代表一次支付尝试，$order_id$ 标识订单，$attempt_time$ 标识尝试时间，$status = 'success'$ 代表成功。

WITH attempts AS (
    SELECT
        order_id,
        country,
        payment_method,
        status,
        attempt_time,
        ROW_NUMBER() OVER (
            PARTITION BY order_id ORDER BY attempt_time
        ) AS attempt_no,
        MAX(CASE WHEN status = 'success' THEN 1 ELSE 0 END)
            OVER (PARTITION BY order_id) AS final_success
    FROM payment_attempts
    WHERE attempt_time >= DATE_SUB(CURRENT_DATE, 7)
),
order_level AS (
    SELECT
        order_id,
        country,
        payment_method,
        MAX(CASE
            WHEN attempt_no = 1 AND status = 'success' THEN 1
            ELSE 0
        END) AS first_success,
        MAX(final_success) AS final_success
    FROM attempts
    GROUP BY order_id, country, payment_method
)
SELECT
    country,
    payment_method,
    COUNT(*) AS order_count,
    AVG(first_success * 1.0) AS first_success_rate,
    AVG(final_success * 1.0) AS final_success_rate
FROM order_level
GROUP BY country, payment_method;

如果同一订单可能切换支付方式，还要先确定最终成功率归属于首次方式、最终方式，还是订单整体。否则 SQL 即使可以运行，业务口径仍可能错误。

题 5：找出支付成功率连续三天下降的国家

WITH daily AS (
    SELECT
        country,
        TO_DATE(attempt_time) AS dt,
        AVG(CASE
            WHEN status = 'success' THEN 1.0
            ELSE 0.0
        END) AS success_rate
    FROM payment_attempts
    GROUP BY country, TO_DATE(attempt_time)
),
compared AS (
    SELECT
        country,
        dt,
        success_rate,
        LAG(dt, 1) OVER (
            PARTITION BY country ORDER BY dt
        ) AS date_d1,
        LAG(dt, 2) OVER (
            PARTITION BY country ORDER BY dt
        ) AS date_d2,
        LAG(success_rate, 1) OVER (
            PARTITION BY country ORDER BY dt
        ) AS rate_d1,
        LAG(success_rate, 2) OVER (
            PARTITION BY country ORDER BY dt
        ) AS rate_d2
    FROM daily
)
SELECT
    country,
    dt,
    success_rate,
    rate_d1,
    rate_d2
FROM compared
WHERE DATEDIFF(dt, date_d1) = 1
  AND DATEDIFF(date_d1, date_d2) = 1
  AND success_rate < rate_d1
  AND rate_d1 < rate_d2;

实际分析时还应补齐连续日期，并加入最小支付量条件，避免把数据缺失或小样本波动误判为连续下降。

题 6：渠道重复回调如何去重

不要直接使用 DISTINCT，因为重复回调可能包含不同状态。应先确定业务主键，例如 $payment_id + event_type$，再按渠道事件时间、版本号或内部接收时间排序，保留最新的有效记录。

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY payment_id, event_type
            ORDER BY
                channel_event_time DESC,
                received_time DESC
        ) AS rn
    FROM payment_callback
)
SELECT *
FROM ranked
WHERE rn = 1;

如果状态可能从“处理中”变为“成功”，还必须结合状态机判断哪些变化合法。去重的本质不是删除相同行，而是根据业务规则确定哪条记录代表最终事实。

九、面试检查清单

• 先说明 SQL 方言和 Hive 版本。
• 确认一行代表用户、订单、事件，还是“用户 × 日期”。
• 澄清自然日、24 小时窗口、UTC 与本地时间。
• 解释 PARTITION BY、ORDER BY 和窗口范围各自的职责。
• Join 前检查两侧键是否唯一，防止多对多膨胀。
• LEFT JOIN 的右表过滤通常放在 ON；放入 WHERE 前先检查 NULL。
• LAG 和 ROWS 看的是相邻记录与物理行，并不天然代表自然日。
• 比率要处理分母为 0，时间要处理异常负值，排名要说明并列规则。
• 使用 COUNT(*)、COUNT(DISTINCT key) 和金额汇总进行结果对账。