蚂蚁集团大数据面试SQL-余额宝七日年化收益率计算

⚠️ 待修正

一、题目背景

这道题来自蚂蚁集团余额宝事业部的数据分析岗面试。七日年化收益率是货币基金最核心的展示指标——用户在余额宝首页看到的就是这个数字。它基于最近7天的万份收益滚动计算得出，每天更新一次。

业务场景：金融产品经理每天都要盯七日年化收益率的走势，如果持续下行可能引发用户资金流出。这道SQL正是收益率计算引擎的核心逻辑——用滑动窗口聚合最近7天数据，确保每天展示的都是最新7天的平均值。

二、题目

现有一张余额宝每日万份收益表 t3_yuebao_daily，记录了每日的万份收益。请计算每日的七日年化收益率。

七日年化收益率 = 最近7天每万份收益之和 / 10000 × 365 / 7 × 100%

余额宝万份收益表 t3_yuebao_daily：

+-------------+------------------+
| trade_date  | daily_return_10k |
+-------------+------------------+
| 2023-03-01  | 0.5231           |
| 2023-03-02  | 0.5187           |
| 2023-03-03  | 0.5215           |
| 2023-03-04  | 0.5198           |
| 2023-03-05  | 0.5202           |
| 2023-03-06  | 0.5246           |
| 2023-03-07  | 0.5223           |
| 2023-03-08  | 0.5179           |
| 2023-03-09  | 0.5164           |
| 2023-03-10  | 0.5201           |
+-------------+------------------+

三、思路分析

核心是滚动窗口聚合：

滑动窗口：SUM() OVER (ORDER BY trade_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 取当日+前6天共7天的收益和
数据完整性校验：COUNT() OVER 同样范围的窗口，确保前6天数据不足时不参与计算
年化公式：7天收益总和 / 10000 × 365 / 7 × 100%

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：计算滚动7日万份收益和

使用 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 滚动窗口，同时用 COUNT 记录窗口内实际行数。

执行SQL

select trade_date,
       daily_return_10k,
       sum(daily_return_10k) over (order by trade_date rows between 6 preceding and current row) as sum_7d_return,
       count(1) over (order by trade_date rows between 6 preceding and current row) as cnt_7d
from t3_yuebao_daily

执行结果

+-------------+------------------+---------------+--------+
| trade_date  | daily_return_10k | sum_7d_return | cnt_7d |
+-------------+------------------+---------------+--------+
| 2023-03-01  | 0.5231           | 0.5231        | 1      |
| 2023-03-02  | 0.5187           | 1.0418        | 2      |
| ...         | ...              | ...           | ...    |
| 2023-03-07  | 0.5223           | 3.6502        | 7      |
| 2023-03-08  | 0.5179           | 3.6450        | 7      |
| 2023-03-09  | 0.5164           | 3.6427        | 7      |
| 2023-03-10  | 0.5201           | 3.6413        | 7      |
+-------------+------------------+---------------+--------+

前6天 cnt_7d < 7，说明数据不足7天。从 03-07 开始窗口满7天，可以计算年化收益率。

步骤2：计算七日年化收益率（仅保留完整7天数据）

执行SQL

select trade_date,
       daily_return_10k,
       sum_7d_return,
       round(sum_7d_return / 10000 * 365 / 7 * 100, 4) as annualized_return_7d
from (
    select trade_date,
           daily_return_10k,
           sum(daily_return_10k) over (order by trade_date rows between 6 preceding and current row) as sum_7d_return,
           count(1) over (order by trade_date rows between 6 preceding and current row)  as cnt_7d
    from t3_yuebao_daily
) t
where cnt_7d = 7

执行结果

+-------------+------------------+---------------+---------------------+
| trade_date  | daily_return_10k | sum_7d_return | annualized_return_7d |
+-------------+------------------+---------------+---------------------+
| 2023-03-07  | 0.5223           | 3.6502        | 1.9033              |
| 2023-03-08  | 0.5179           | 3.6450        | 1.9006              |
| 2023-03-09  | 0.5164           | 3.6427        | 1.8994              |
| 2023-03-10  | 0.5201           | 3.6413        | 1.8987              |
+-------------+------------------+---------------+---------------------+

七日年化收益率稳定在 1.90% 左右，呈微降趋势。货币基金的收益率波动通常很小（日变化在0.001%量级）。

五、常见坑点

坑1：ROWS BETWEEN 和 RANGE BETWEEN 的区别

ROWS BETWEEN 6 PRECEDING 是物理行偏移（前6行），RANGE BETWEEN 6 PRECEDING 是逻辑值偏移（前6天的值范围）。如果某天缺少数据（如节假日），ROWS 仍然取前6行但可能跨了更多天，RANGE 会正确处理缺失日期。本题按日期排序且每天有数据，两方案结果相同。

坑2：前6天数据不足的处理

如果不过滤 cnt_7d < 7 的记录，前6天的"收益率"是基于少于7天数据算出的，数字会失真。必须 WHERE cnt_7d = 7 确保数据完整性。

坑3：年化公式的理解

有人会写成 sum / 7 * 365 / 10000 和 sum / 10000 / 7 * 365。两者数学上等价，但建议用 / 10000 * 365 / 7 * 100 的顺序——先归一化到每元收益、再年化、再转百分比，逻辑与业务公式一一对应，便于code review。

六、举一反三

30日年化收益率：把窗口改为 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW，其他公式不变
万份收益的移动平均：AVG(daily_return_10k) OVER (ORDER BY trade_date ROWS 6 PRECEDING) 直接算7日均值，用于平滑日波动
环比变化：LAG(annualized_return_7d) OVER (ORDER BY trade_date) 获取昨日收益率，计算日环比变动
多产品对比：如果表中有 fund_id 字段，加上 PARTITION BY fund_id，同时监控多只货币基金的收益率走势

七、知识点总结

考点	说明
ROWS BETWEEN ... PRECEDING	滚动窗口取前N行，适合时间序列滑动计算
SUM + COUNT 双窗口	SUM 算累计值，COUNT 校验窗口数据完整性
WHERE cnt_7d = 7	排除窗口不完整的记录，保证计算结果准确
年化公式	sum/10000 × 365/7 × 100%，理解业务含义比记公式更重要

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE t3_yuebao_daily (
    trade_date       string COMMENT '交易日期',
    daily_return_10k decimal(10,4) COMMENT '每万份日收益（元）'
) COMMENT '余额宝每日万份收益表';

INSERT INTO t3_yuebao_daily VALUES
('2023-03-01', 0.5231), ('2023-03-02', 0.5187),
('2023-03-03', 0.5215), ('2023-03-04', 0.5198),
('2023-03-05', 0.5202), ('2023-03-06', 0.5246),
('2023-03-07', 0.5223), ('2023-03-08', 0.5179),
('2023-03-09', 0.5164), ('2023-03-10', 0.5201);

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题

一、题目背景​

二、题目​

三、思路分析​

四、逐步推导​

步骤1：计算滚动7日万份收益和​

步骤2：计算七日年化收益率（仅保留完整7天数据）​

五、常见坑点​

六、举一反三​

七、知识点总结​

八、建表语句和数据插入​

你可能还想看