91学AI - 大数据工程师

考察点

这道题出自字节跳动数据仓库工程师一面，是典型的"讲完就让你写"的手撕题。面试官要看两点：一是分类讲不讲得清，很多人只会用 row_number 说不出窗口函数有几大类；二是 SQL 写不写得对，重点看 over() 里 partition by 和 order by 的关系、rows between 帧的写法、rank 和 dense_rank 的区别这种细节。追问常往"取每组 top N 怎么写"、"rank 和 row_number 去重效果差在哪"、"rows 和 range 帧有什么区别"走。

参考答案

窗口函数的分类

窗口函数按功能分四类。第一类是排序函数：row_number、rank、dense_rank。三者的区别在于并列处理：row_number 不分青红皂白连续编号（1,2,3,4），rank 并列后跳号（1,1,3,4），dense_rank 并列后不跳号（1,1,2,3）。第二类是分布函数：percent_rank、cume_dist、ntile。percent_rank 计算当前行在分区内的相对排位（(rank-1)/(总行数-1)），cume_dist 是小于等于当前值的行占比，ntile(n) 把分区均匀切成 n 桶。第三类是取值函数：lag、lead、first_value、last_value、nth_value，用来取窗口内偏移位置的值，做环比、留存分析全靠它们。第四类是聚合函数作窗口用：sum、avg、count、max、min 配合 over()，算累计值、滑动平均。

和普通 group by 聚合的本质区别要一句点透：group by 把多行压成一行，窗口函数保留每一行，在每一行上"回头看"整个窗口计算一个值。这也是它适合做 TopN、去重、环比的原因。

手撕一：排序取 TopN

每个类目下取销售额 top 3 的商品：

SELECT category_id, product_id, amount, rn
FROM (
  SELECT category_id,
         product_id,
         amount,
         row_number() OVER (PARTITION BY category_id ORDER BY amount DESC) AS rn
  FROM dws_product_sales_di
) t
WHERE rn <= 3;

易错点有两个。一是面试官追问"销售额并列第三怎么办"，这时候要说明需求语义：严格只要 3 行就用 row_number；并列都要保留就换 rank（可能返回超过 3 行）；并列算同一名次且名次连续就用 dense_rank。二是提醒一句 Hive/Spark 里这个写法没问题，MySQL 8.0 之前没有窗口函数得用变量模拟——说明你知道不同引擎的差异。

手撕二：ntile 分桶

把用户按消费金额从高到低分成 10 个等份（十分位分层，做 RFM 或用户分层常用）：

SELECT user_id,
       total_amount,
       ntile(10) OVER (ORDER BY total_amount DESC) AS decile
FROM dws_user_trade_df;

ntile 的规则要交代：N 行分 n 桶，除不尽时前面的桶每桶多一行。比如 105 个用户分 10 桶，前 5 桶各 11 人、后 5 桶各 10 人。面试官可能顺手追问"想要按金额占比分桶而不是人数等分怎么办"——那就不是 ntile 的活，用累计 sum over 除以总额，再按占比区间 case when。

手撕三：分位数

算每个用户消费金额在全体用户中的分位（percent_rank），并找出前 10% 高消费用户：

SELECT user_id,
       total_amount,
       ROUND(percent_rank() OVER (ORDER BY total_amount), 4) AS pct_rk,
       ROUND(cume_dist() OVER (ORDER BY total_amount), 4) AS cume
FROM dws_user_trade_df;

percent_rank 和 cume_dist 的差异值得主动说：percent_rank 顶部是 0、底部趋近 1（第一名是 0），衡量"排在我前面的比例"；cume_dist 顶部是 1/N、底部恰好 1，衡量"不超过我的比例"。筛选前 10% 用 pct_rk <= 0.1 还是 cume <= 0.1 语义不同，面试里能说清这个差别会很加分。

补充：帧（frame）的用法

聚合窗口常考滑动窗口，比如每个用户近 3 次订单的累计金额：

SELECT user_id,
       order_id,
       amount,
       SUM(amount) OVER (
         PARTITION BY user_id
         ORDER BY order_time
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS last3_sum
FROM dwd_trade_order_detail;

注意 rows 和 range 的区别：rows 按物理行数取帧，range 按值范围取帧（比如 RANGE BETWEEN INTERVAL 7 DAY PRECEDING 在某些引擎里支持，Hive 支持有限）。默认帧是"起点到当前行"，写 last_value 时尤其要小心——不指定帧的话 last_value 取到的是当前行而不是分区最后一行，这是最经典的坑。

可能的追问

用窗口函数去重怎么做？row_number() over (partition by 业务主键 order by 更新时间 desc) = 1 取最新一条，是数仓 ODS 到 DWD 去重的标准写法。
窗口函数性能上要注意什么？partition by 的 key 倾斜会直接拖垮窗口计算；另外 order by 不同值很多时帧计算代价高，大表上先做局部聚合再开窗往往更快。
lag/lead 的默认值参数怎么用？lag(amount, 1, 0) 第三参数是越界时的默认值，不填是 null，算环比增长率时 null 会导致整行结果 null，一般要 coalesce 或直接给默认值。

【字节跳动】窗口函数分类与现场手写 SQL

考察点