云名@-CSDN博客

原创 SQL09：有效值追溯

其实问题并不难，常见想法是将有效值过滤出来，然后利用窗口排序函数比如row_number取最近一次的成本值即可。这里想要介绍另一种更简便的方法，需要引用first_value或者last_value开窗函数，这两个函数顾名思义是用来取开窗内第一条和最后一条数据，具体函数说明可参考。假设有一张商品入库表，包括商品id、商品成本和入库日期3个字段，由于某些原因，导致部分商品的成本缺失（为0或者没有值都是缺失）。所以现在要把缺失的商品进价补充完整，补充的依据是使。用相同商品的最近一次有效成本作为当前商品的成本。

2024-02-22 16:39:41 383 1

原创 SQL08：找出恶意取消用户

下面是某电商网站的订单数据，包括order_id,user_id,order_status和operate_time四个字段，我们需要找出所有恶意购买的用户。恶意购买的用户定义是：同一个用户，在任意半小时内（含），取消订单次数>=3次的就被视为恶意买家。range与rows不同的是，range每一行的开窗值的范围是固定的，但行数是不固定的。的问题，其实是在滑动窗口内做计算的问题。针对这个例子，窗口大小就是半小时，然后按每条行为数据进行滑动，在窗口内判断该条数据对应的用户是否是恶意用户。

2024-02-22 11:28:04 413 1

原创 SQL07：实现分钟级的累加计算

提到了补全日期实现求和计算，如果当前需要实现到分钟的求和计算，该如何实现呢？假设原表：包含字段(trade_id，pay_time(格式是2020-08-05 10:30:28)，pay_gmv)。希望用sql实现分钟级的0点到当前分钟的GMV。想要得到从0点到每分钟的累计交易额，包含字段(minute_rn(分钟顺序),pay_gmv_td(每分钟的交易额，都是0点到当前分钟的累加值))。核心就是使用sql生成分钟级的序列，然后关联原表，做开窗累加即可。

2024-02-21 10:23:08 418

原创 SQL06：补全日期做求和计算

现在由于销售记录有缺失，他虽然在某天没有销售记录，但是他是有当月累计销售额的，且当月累计销售额与前一天的累计销售额一样，这种依然需要统计出来。所以我们首先考虑将每个人每天的销售记录补齐，当天没有销售记录的那么销售额给0，然后就可以开窗计算当月截至到每天的累计销售额了。得到月份的开始日期后，我们可以用开始日期加上对应日期的索引，得到新的一列包含所有日期的数据。假设有一张员工的销售记录表，记录了员工在23年10月份每天的销售金额，现在需要统计每个员工在当月截止到每天的累计销售额。

2024-02-20 17:05:54 681 1

原创 SQL05：计算相互关注的用户

找到互相关注的人的规律，当他们是互相关注时，那么将from_user和to_user其中一个顺序调换位置后，from_user和to_user就一定会出现两条数据(源表提前已经去重)，所有出现两条数据的人就是有互相关注的。这种方式不会导致数据膨胀。假设存在一张tmp表，记录了用户id和该用户关注的用户id，我们希望筛选出相互关注的用户，如何用sql求解？直接用原表做关联判断，简单直接，但是存在数据膨胀的风险。

2024-02-20 16:26:33 438 1

原创 SQL04：自定义业务周

但有时候随着业务运营策略的不同，我们可能并不会按自然周去看，会有一个业务周的定义，比如上周五到本周四为一个业务周。比如这里因为要以当前日期所在周的周一为周id，所以可以利用next_day得到当前日期的下一个周一的日期，然后利用。先说简单的，对于自然周，我们把自然周的周一定为该周的周id。那么同样的我们需要知道给定日期所在的业务周的周id，假设以业务周的开始日期作为周id，那么周id至周id+6则为一个业务周的日期范围。对于任意一个日期，求解其所在的业务周范围，实际上是在求解其对应的周id。

2024-02-01 11:29:50 460 1

原创数据分析02：指标异动归因补充

我们要对指标进行尽可能的拆解、提取相关驱动指标，这类指标拆解通常有明确的产品路径带来因果关系或者存在隐形的业务逻辑，我们可以将目标指标的异动归因到其驱动指标，对指标的变化进一步归因到业务环节的问题；（1）对于复杂指标或者可用业务逻辑拆解的指标，可以先对指标拆解看看是否驱动指标出现问题（利用乘法拆解计算各驱动指标的贡献度）。（2）如果无法对指标进行业务逻辑拆解，就进行维度细分，计算各个群体指标变化对整体的贡献度。我们要根据业务逻辑列举主要影响的维度，对各驱动指标进行维度下钻。绝对值指标和比率型指标。

2024-01-30 15:55:42 1926 1

原创 SQL03：数据倾斜问题

由于数据的分布不平衡导致某些reducer节点被分配的key值特别多，而其他reducer节点被分配的特别少，最终在处理数据时，数据量小的节点都在等这个量大的节点完成任务。因为其处理的数据量和其他reduce差异过大，单一reduce的记录数与平均记录数差异过大，最长时长远大于平均时长。执行一个非常简单的SQL语句，但任务的进度条长时间卡在99%，不确定还需多久才能结束，其实就说明我们遇到了数据倾斜问题。那么hive遇到数据倾斜，可能的原因是什么？空值分配随机的key值。（2）SQL语句优化。

2024-01-30 15:21:54 1191 1

原创 SQL02：求解连续计算问题

日常工作中可能会遇到两种求解连续的问题，第一种是计算连续出现的次数，第二种是和日期相关，比如用户最大连续登录天数等。既然下来我们分别讲解这两种问题的解题方法。

2024-01-28 13:56:40 1003

原创 SQL01：求解最大在线人数

观看记录表实际上记录了每个用户每次的观看行为和观看时长，用户每次进出视频是一段时间范围，在这段时间范围内可能同时有其他用户进出。极端举例：假设一个视频总共只有两个用户观看，A用户在12:00:00进入，在12:00:08离开，B用户在12:00:05进入，在12:00:10离开。假设存在一张用户-视频观看记录表，分别记录了用户id user_id，视频id video_id，用户进入时间enter_time、用户离开时间left_time，现在想要计算每个视频同时在线的最大人数，如何用sql求解？

2024-01-26 16:34:07 655 1