乐趣区

关于大数据:大数据开发Sql涉及迭代数据的sql问题处理思路

jiezi

在后面一篇外面，算法 - 一个经典 sql 题和一个 Java 算法题 大数据开发 -Hive- 罕用日期函数 && 日期间断题 sql 套路有一道经典 sql 题目，解决间断问题，本文持续总结对于连续性的套路，来自于理论生产我的项目的问题，本文略去其余不重要信息字段，来从更深地档次解决问题，因为在生产中，经常是了解需要，转换需要，让需要拆解为更通用的解决形式，同时从解决难题中进步本人，解决这些问题，也是高兴的源泉之一????。

1. 生产问题原始形容

对于给用户打标签的问题，对于标签的数据源在理论环境下简直是笼罩全副门甚至全公司的，有的数据源自身是明细形式，有的是原始 ods 日志形式，有的是维度表，有的是名单表，对于需求方来说要的是疾速迭代上线标签，看需要或者数据仓库划分或者解决麻烦水平，往往会采纳不同的解决形式，另外对于标签的利用方来说肯定会有人查值，值查人，资产剖析等，本文的例子就一个名单表，什么是名单表呢，就是圈客的后果，比方满足某条件某条件的一堆 id，标签人造就是为满足圈客用的，然而原始数据它是圈客的后果，所以同时为了使得标签在业务方应用起来更不便，所以把名单表加工成标签，那么需要就确定了，从名单表外面加工出标签。

原始表如下 

# id  dt
1   10
2   10
2   11
3    11

解释，为了阐明更简略，假如日期类型都用数字来示意，每天会有一群去重 id，下一天的 id 更这一天的 id 必然是两个穿插圆的模式，即既有多进去的，又有少进去的，又有重叠的。

当初需要就是，

我要筛选任意时间段的用户，满足是这段时间首次无效，或者有效，或者存量无效，别离对应形容外面的多进去，少了重合的每天

至于无效的定义就是，前一天名单没有这个 id，下一天有这个 id，这就是首次无效，然而对于名单这种数据来说，肯定会有无效，有效，无效，有效，这种, 所以标签的每天分片外面会有昨天的 id 加明天的 id.

2. 生产问题剖析拆解

针对下面剖析到的问题，无效有效这种状态不能是最近一次，或者首次的无效，因为会有区段的概念，比方某历史一段时间，只有首次呈现在这段时间的，而且多天重合的。所以针对这个问题，我这边解决成每天绝对于前一天的无效状态，别离为 0，1，2，也即对应有效，首次无效，存量无效，另外对应每一段的信息。

所以这样问题就化解为，先求无效状态，而后再在无效状态的根底上，加工出最早工夫和最晚工夫，为了筛选不便，我另外加工出两个冗余子属性字段，是否首次无效和是否以后无效, 对于无效有效，如果用窗口函数，找到同一个 id 的前一个日期，如果是日期的前一天，即示意 2，存量无效，如果不为前一天则为增量无效，然而这种形式没法把有效的取出来，所以我前面想到的是用 full join 来实现，这样即能够把前一天后一天的数据都拿到。

3. 问题解决 

-- 对于无效有效的加工
with t3 as (
select id,dt,CASE
                  WHEN a.did IS NOT NULL
                          AND b.did IS NULL THEN 1
                       WHEN a.did IS NULL
                           AND b.did IS NOT NULL THEN 0
                     WHEN a.did IS NOT NULL
                           AND b.did IS NOT NULL THEN 2
                           end as is_valid
 from t1 a full join b on a.id = b.id
 )

-- 最早工夫 12，最晚工夫 14，是否首次无效，是否以后无效
select id,min(dt) as min_dt,max(dt) as max_dt, gid from (select id,dt,is_valid, (dt - row_number() over (partition by id order by dt)) gid from t3 where is_valid != 0
) tmp group by id,gid

4. 总结套路

4.1. 首先思考能够用到的日期函数

datediff, date_sub/date_add

4.2. 间断日期

间断问题都会用到一个排名函数，然而排名函数的值是数值，要与日期的连续性做到映射，才不便分组，比方能够把日期映射到间断数字，或者数字映射到间断日期，实现这两个的操作就是通过后面的 datedff 和 date_sub 组合，原理就是日期与日期相减即可失去间断整数，整数轻易与某个日期做相减即可失去间断的日期, 其中 date_sub 能够是反向排序失去间断日期。

4.3. 通过间断的排序日期或者排序 id 相减，而后分组 gid

即可解决此类问题

求间断状态工夫的开始和完结工夫

4.4 对于非凡问题，比方想晓得前后分区的数据

现有形式，一种是 lag 和 lead 窗口函数，另外一种是自表关联，效率应该窗口函数高一点，基于本题，因为名单表的非凡起因，如果再给全量 id 打标签，这样后果分区的数据和原分区的数据不同，是加上了前一天生效的用户，所以用的自表 fulljoin 关联。


吴邪，小三爷，混迹于后盾，大数据，人工智能畛域的小菜鸟。
更多请关注

退出移动版