1. 这道题到底在考什么分时数据算累计听起来像是一道SQL练习题但真正做过离线数仓的人都知道这背后考的是三件事窗口函数的边界控制、时间轴对齐的思维、以及空值补全的工程化处理。前两件事决定了你能不能把数算对第三件事决定了你的报表能不能看。先把场景说清楚。假设我们有一张交易流水表记录了每个小时或者每五分钟、每十分钟的成交金额。业务方要的不是每个时间点的增量而是一个从当天零点开始、逐时段累加的曲线。比如时间段增量金额00:00-01:0010001:00-02:0015002:00-03:0080业务方要看到的是时间段增量金额累计金额00:00-01:0010010001:00-02:0015025002:00-03:0080330这个转换本身不复杂SUM() OVER一行就搞定了。但面试官不会只考这一层。他会接着问如果02:00-03:00这个时段没有任何交易你的数据里根本没有这一行那累计曲线是不是就断了业务方看到的报表上02点到03点直接消失了03点的累计值变成了从04点重新开始算——这就是分时累计数据为空时补全的问题。所以这道题的核心考点拆开来看是这样的窗口函数的正确使用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW和RANGE BETWEEN的区别以及为什么在分时累计场景下必须用ROWS。时间轴对齐增量数据里缺失的时间段在累计结果里必须补上且累计值要延续上一个有效时段的值。空值补全的两种思路一种是在计算累计之前就把时间轴补齐另一种是先算累计再对结果做补全。两种思路的代价和适用场景完全不同。数据倾斜与性能当天的数据量如果很大SUM() OVER的全窗口排序会不会成为瓶颈怎么优化。我带过几个做数仓的同学这道题他们第一次遇到的时候十有八九会写出一个能跑但结果不对的SQL。问题基本都出在“以为数据里每个小时都有记录”这个假设上。实际生产中尤其是凌晨低峰期连续几个小时没有交易是常态。你不做时间轴补全报表就是残缺的。下面我按实际做项目的思路从表结构设计开始一步步把这道题拆干净。2. 表结构与数据准备2.1 增量流水表的设计先定义一张最基础的分时增量表。在实际数仓里这张表可能是从ODS层清洗过来的也可能是从实时链路落下来的微批数据。为了聚焦核心逻辑我用最简结构CREATE TABLE dwd_trade_hourly ( stat_hour STRING COMMENT 统计时段格式 yyyy-MM-dd HH:00:00, trade_amt DECIMAL(18,2) COMMENT 该时段增量交易金额 ) PARTITIONED BY (dt STRING) STORED AS ORC;这里有个细节值得说stat_hour我用的是字符串格式的整点时间而不是INT类型的小时数。为什么因为跨天的时候INT类型的0-23没法区分是哪一天。如果你只算当天用INT也行但一旦业务方要查“最近7天每天的分时累计”INT就会让你在关联日期的时候多写一层逻辑。用完整的yyyy-MM-dd HH:00:00字符串虽然存储上多占几个字节但后续所有时间轴操作都省心。提示如果你的源数据是yyyyMMddHH这种紧凑格式建议在DWD层就统一转成标准格式。别等到ADS层再转那时候你已经被各种substr和concat绕晕了。2.2 构造测试数据为了把“空时段”这个场景复现出来我故意在数据里留几个断档INSERT INTO dwd_trade_hourly PARTITION (dt2025-01-15) VALUES (2025-01-15 00:00:00, 100.00), (2025-01-15 01:00:00, 150.00), (2025-01-15 02:00:00, 80.00), -- 03:00 缺失 -- 04:00 缺失 (2025-01-15 05:00:00, 200.00), (2025-01-15 06:00:00, 120.00), -- 07:00 缺失 (2025-01-15 08:00:00, 90.00), (2025-01-15 09:00:00, 60.00);注意看03点、04点、07点这三个时段在增量表里是不存在的。如果你直接对这个表做SUM() OVER得到的结果只有6行03点和04点的累计值直接跳过了07点也是断的。业务方看到的就是一条有缺口的曲线。这就是面试官想看你处理的地方。3. 核心解法先补时间轴再算累计3.1 为什么不能直接算累计再补很多人第一反应是我先用窗口函数把累计算出来然后拿一张完整的时间维度表去左连接缺失的时段用前一个有效值填充。这个思路逻辑上没问题但实现起来有一个坑累计值的填充不能用简单的LAST_VALUE或LAG因为缺失可能连续多个时段。比如03点和04点都缺失你左连接之后03点的累计值应该是02点的累计值即33004点的累计值也应该是330。如果你只用LAG(累计值, 1)03点能填对04点填的就是03点的值——但03点本身是NULL所以04点还是NULL。你需要的是“取最近一个非空值”这在Hive里要用LAST_VALUE配合IGNORE NULLS或者用窗口内MAX的技巧。而且先算累计再补全意味着你的窗口函数是在一个不完整的时间轴上执行的。虽然SUM() OVER (ORDER BY stat_hour)本身不要求时间连续但一旦后续要做同比、环比、或者按固定时段数做滑动窗口不连续的时间轴就会让所有基于行号的逻辑全部失效。所以我的建议是先把时间轴补全再在完整时间轴上算累计。这样累计逻辑最简单后续扩展也最稳。3.2 生成完整时间轴的几种方式补时间轴的核心是对于当天00:00到23:00这24个整点不管增量表里有没有数据都要有一行。生成这24行有几种常见做法。方式一用时间维度表关联。如果你数仓里有一张dim_date_hour或者dim_time表直接按日期过滤出24行然后左连接增量表。这是最规范的做法但前提是你得有这张表。方式二用posexplode或explode生成序列。Hive里可以用split(space(23), )配合posexplode生成0到23的数字再拼成时间字符串。这个技巧很实用不需要依赖外部维度表SELECT date_format(date_add(2025-01-15, 0), yyyy-MM-dd) AS dt, lpad(cast(pos AS STRING), 2, 0) AS hour_str FROM ( SELECT posexplode(split(space(23), )) AS (pos, val) ) t;这段SQL会生成0到23共24行hour_str从00到23。然后拼成2025-01-15 00:00:00这种格式就得到了完整时间轴。方式三用sequenceexplodeHive 2.2。如果你的Hive版本支持sequence函数可以更简洁SELECT explode(sequence( to_date(2025-01-15), to_date(2025-01-15), interval 1 hour )) AS stat_hour;但注意sequence生成的是timestamp类型格式上可能需要再转一次。而且不同Hive版本对interval的支持程度不一样生产环境用之前最好先测一下。我个人的习惯是用方式二因为spacesplitposexplode这套组合在几乎所有Hive版本上都能跑兼容性最好。3.3 左连接补全增量数据有了完整时间轴接下来就是左连接增量表WITH time_axis AS ( SELECT concat(2025-01-15, , lpad(cast(pos AS STRING), 2, 0), :00:00) AS stat_hour FROM ( SELECT posexplode(split(space(23), )) AS (pos, val) ) t ), hourly_data AS ( SELECT stat_hour, trade_amt FROM dwd_trade_hourly WHERE dt 2025-01-15 ) SELECT t.stat_hour, COALESCE(h.trade_amt, 0) AS trade_amt FROM time_axis t LEFT JOIN hourly_data h ON t.stat_hour h.stat_hour ORDER BY t.stat_hour;这里有一个关键决策缺失时段的增量值填0还是填NULL从累计的角度看填0和填NULL在SUM() OVER里的效果是一样的——SUM会忽略NULL填0则直接加0最终累计值相同。但从数据语义上看填0更合适。因为“该时段没有交易”和“该时段交易金额未知”是两回事。在离线数仓里没有记录通常意味着没有发生交易增量就是0。填0之后后续如果有人要算“有交易的小时数”也不会被NULL干扰。注意如果你的业务场景里缺失可能是因为数据延迟或丢数那填0就会掩盖问题。这种情况下应该填NULL并且在数据质量监控里加一条“分时增量空值率”的告警。填0还是填NULL取决于你对缺失原因的判断没有绝对的对错。3.4 在完整时间轴上计算累计时间轴补齐之后累计就是一行窗口函数的事SELECT stat_hour, trade_amt, SUM(trade_amt) OVER ( ORDER BY stat_hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amt FROM ( SELECT t.stat_hour, COALESCE(h.trade_amt, 0) AS trade_amt FROM time_axis t LEFT JOIN hourly_data h ON t.stat_hour h.stat_hour ) combined ORDER BY stat_hour;跑出来的结果就是完整的24行每个小时的累计值都是从00点累加到当前小时。03点和04点的增量是0累计值保持330不变07点增量0累计值保持650不变。曲线连续业务方看着舒服。这里重点说一下ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW这个窗口帧。很多人写SUM() OVER (ORDER BY stat_hour)就结束了默认的窗口帧在大多数情况下确实是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW但RANGE和ROWS在遇到重复排序值的时候行为不同。RANGE会把所有相同stat_hour的行一起算进去而ROWS是严格按行号逐行累加。在分时累计场景下stat_hour是唯一的两者结果一样但显式写ROWS更安全也更能体现你对窗口帧的理解。4. 如果非要在累计结果上补全怎么做虽然我推荐先补时间轴再算累计但面试官有时候会追问如果我就是想先算累计然后对累计结果做补全你能实现吗这时候你得能答上来因为实际工作中确实会遇到这种需求——比如累计逻辑很复杂不想在补全后的时间轴上重算一遍。4.1 用 LAST_VALUE IGNORE NULLS 填充Hive从2.1版本开始支持IGNORE NULLS这让“取最近一个非空值”变得很简单SELECT stat_hour, cumulative_amt, LAST_VALUE(cumulative_amt, TRUE) OVER ( ORDER BY stat_hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS filled_cumulative FROM ( SELECT t.stat_hour, SUM(h.trade_amt) OVER ( ORDER BY t.stat_hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amt FROM time_axis t LEFT JOIN hourly_data h ON t.stat_hour h.stat_hour ) calc ORDER BY stat_hour;LAST_VALUE(cumulative_amt, TRUE)里的TRUE就是IGNORE NULLS的意思。它会沿着窗口从第一行走到当前行取最后一个非NULL的值。这样即使连续多个时段缺失填充值也会一直延续下去。但这里有个坑LAST_VALUE的默认窗口帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW如果你不显式写ROWS在某些数据分布下可能会出问题。我建议永远显式写窗口帧别偷懒。4.2 用 MAX 窗口做填充的兼容方案如果你的Hive版本不支持IGNORE NULLS可以用一个经典的技巧给每个非空值打一个分组标记然后按分组取最大值。SELECT stat_hour, MAX(cumulative_amt) OVER ( PARTITION BY grp ORDER BY stat_hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS filled_cumulative FROM ( SELECT stat_hour, cumulative_amt, COUNT(cumulative_amt) OVER ( ORDER BY stat_hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS grp FROM calc ) marked ORDER BY stat_hour;思路是这样的COUNT(cumulative_amt)在遇到NULL时不计数所以每个非空值会把grp加1。这样同一个grp内的行要么是那个非空值本身要么是它后面跟着的NULL。然后在每个grp内取MAX(cumulative_amt)因为累计值是单调递增的所以MAX就是那个非空值。这个方案不依赖IGNORE NULLS兼容性极好是我在旧版本Hive上常用的兜底手段。4.3 两种补全时机的对比对比维度先补时间轴再算累计先算累计再补全实现复杂度低逻辑清晰中需要处理连续NULL累计逻辑复用性高完整时间轴上算低不完整时间轴上算后续扩展滑动窗口、同比容易困难行号不连续性能多一次左连接少一次连接但多一次窗口适用场景大多数离线报表累计逻辑极复杂、不想重算我个人的选择很明确只要时间轴补全的成本可控一律先补时间轴。因为离线数仓里时间维度表的生成成本几乎可以忽略而后续所有基于时间序列的计算都会因为时间轴完整而变得简单。5. 跨天场景与性能优化5.1 跨天累计怎么处理上面的例子都是单天的。如果业务方要的是“从本月1号零点开始一直累加到当前时刻”的累计曲线时间轴就要跨天生成。这时候posexplode(split(space(23), ))就不够用了你需要生成从月初到当天的所有小时。一种做法是先用日期维度表生成所有日期再和24小时序列做笛卡尔积WITH date_axis AS ( SELECT date_str FROM dim_date WHERE date_str BETWEEN 2025-01-01 AND 2025-01-15 ), hour_axis AS ( SELECT lpad(cast(pos AS STRING), 2, 0) AS hour_str FROM (SELECT posexplode(split(space(23), )) AS (pos, val)) t ), full_axis AS ( SELECT concat(d.date_str, , h.hour_str, :00:00) AS stat_hour FROM date_axis d CROSS JOIN hour_axis h ) SELECT ...这样生成的时间轴行数是天数 × 24。如果跨月比如从1月1号到1月31号就是744行。这个量级对Hive来说毫无压力。但要注意跨天累计的窗口范围会变得很大。SUM() OVER (ORDER BY stat_hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)会对744行做全窗口累加。如果数据量再大一个数量级比如按分钟累计一个月就是43200行窗口排序的开销就不能忽略了。5.2 窗口函数的性能瓶颈在哪SUM() OVER的本质是一次排序加一次扫描。在Hive里窗口函数会触发一次Reduce阶段的排序如果ORDER BY的字段基数很大排序开销会很明显。对于分时累计这种场景stat_hour的基数最多就是几百到几万排序本身不是问题。真正的问题在于数据倾斜。假设你的增量表里某些小时的数据量特别大比如晚高峰而某些小时几乎没有数据。在SUM() OVER执行时所有数据会被 shuffle 到一个 Reducer 里做全局排序。如果某一天的总数据量是几千万行单个 Reducer 处理起来就会很慢。优化的思路有两个思路一先聚合再开窗。如果你的增量表是明细流水不要直接对明细开窗。先在子查询里按stat_hour做一次GROUP BY把每个小时的增量汇总成一行然后再对汇总结果开窗。这样进入窗口函数的数据量从几千万行降到24行性能提升是数量级的。WITH hourly_agg AS ( SELECT stat_hour, SUM(trade_amt) AS trade_amt FROM dwd_trade_detail WHERE dt 2025-01-15 GROUP BY stat_hour ) SELECT t.stat_hour, COALESCE(h.trade_amt, 0) AS trade_amt, SUM(COALESCE(h.trade_amt, 0)) OVER ( ORDER BY t.stat_hour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amt FROM time_axis t LEFT JOIN hourly_agg h ON t.stat_hour h.stat_hour;思路二如果必须对明细开窗考虑用DISTRIBUTE BY控制并发。但分时累计本身要求全局有序DISTRIBUTE BY会打散数据反而破坏累计逻辑。所以这个场景下思路一是唯一正确的优化方向。提示我见过有人在分时累计的SQL里用SORT BY代替ORDER BY觉得这样能并行排序更快。但SORT BY只保证每个 Reducer 内部有序不保证全局有序。累计值会算错。这个坑踩一次就记住了。6. 常见问题与排查清单6.1 累计值突然归零或跳变这是最常遇到的问题。表现是某个小时的累计值比上个小时还小或者直接变成0。原因通常有三个时间轴没有补全窗口函数在缺失的时间点上重新开始。检查你的time_axis是否覆盖了所有需要的小时。ORDER BY的字段类型不一致。比如时间轴生成的是字符串2025-01-15 09:00:00而增量表里是timestamp类型。Hive在比较时会做隐式转换但排序结果可能不符合预期。统一转成字符串再排序。窗口帧写成了RANGE且存在重复时间点。如果你的数据里同一个小时有多行比如明细未聚合RANGE会把它们一起算进去导致累计值跳变。显式写ROWS可以避免。6.2 补全后的累计值在缺失时段没有延续如果你用的是“先算累计再补全”的方案检查LAST_VALUE的窗口帧是否写对了。默认的RANGE帧在遇到NULL时行为可能不符合预期。另外IGNORE NULLS在部分Hive版本上对LAST_VALUE的支持不完整建议用MAXCOUNT分组标记的方案兜底。6.3 跨天累计时每天的第一个小时累计值不对跨天累计时时间轴是按天拼接的。如果你在生成时间轴时用了date_format或to_date注意时区问题。Hive的date_format默认使用服务器时区如果服务器是UTC而业务数据是东八区跨天的那一个小时就会错位。统一在SQL里用字符串拼接不要依赖日期函数的时区转换。6.4 性能排查速查表现象可能原因排查方法解决方向任务跑得慢明细数据直接开窗看Map/Reduce阶段的数据量先GROUP BY聚合再开窗累计值错误时间轴不完整检查time_axis行数是否为24的倍数补全时间轴累计值跳变窗口帧用了RANGE检查SQL里是否显式写了ROWS改为ROWS BETWEEN补全失败IGNORE NULLS不支持看Hive版本和报错信息改用MAXCOUNT方案跨天错位时区转换问题对比时间轴和增量表的时间格式统一用字符串拼接7. 几个我踩过的坑和实操心得第一个坑是关于COALESCE的位置。我早期写SQL的时候习惯在最后输出的时候才做COALESCE结果窗口函数里用的还是NULL累计值算出来全是NULL。后来才明白COALESCE必须放在窗口函数之前让进入SUM的值本身就是0。这个顺序问题看起来很小但排查起来很费时间因为SQL不报错只是结果不对。第二个坑是关于时间轴的生成效率。用posexplode(split(space(23), ))生成24行在单天场景下没问题。但如果你的时间轴要跨好几个月space函数生成的字符串会很长split的开销也会上来。这时候更好的做法是用一张静态的时间维度表或者用sequence函数直接生成。我现在的习惯是单天用posexplode跨天用维度表跨月用sequence。第三个坑是关于数据倾斜的。有一次我处理一张日增上亿行的流水表直接对明细开窗算分时累计任务跑了两个多小时还没出来。后来改成先按小时聚合再对24行开窗整个任务三分钟就跑完了。这个优化带来的性能提升是几百倍的而且逻辑完全一样。所以我现在写任何窗口函数之前都会先问自己一句进入窗口的数据量能不能先降下来第四个坑是关于LAST_VALUE的。Hive的LAST_VALUE默认窗口帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW这个帧在遇到相同排序值时会把所有相同值的行都包含进来。如果你的时间轴里有重复的小时比如数据质量问题导致同一小时出现两次LAST_VALUE取到的可能不是你期望的那一行。显式写ROWS可以规避这个问题但更重要的是在数据质量层面保证时间轴的唯一性。最后一个心得是关于面试的。这道题如果面试官只问“怎么算累计”你答SUM() OVER就够了。但他如果追问“数据为空怎么办”他想听的不是一个函数名而是你对时间轴完整性这个问题的理解。你要能说清楚为什么时间轴不完整会导致累计错误为什么先补时间轴比先算累计再补全更好以及两种补全方案各自的适用场景。把这些讲清楚这道题就稳了。实际工作中分时累计只是时间序列计算的一个起点。同样的思路可以扩展到分时同比、分时环比、滑动平均、累计去重计数等等。核心永远是那三件事时间轴对齐、窗口帧控制、空值处理。把这三件事吃透后面遇到什么变体都不慌。