数据模型转变问题总结与思考

背景：

目前在做数字化审计时候某业务需要在Oracle中跑的长SQl，需要弄到hive中跑。原本Oracle中又不注意表设计。业务又复杂，所以SQL就很长。而且Hive 中很多Oracle函数中还跑不起来。 

业务简述：

我先把业务描述下。就是计算某个步骤(APP_NO)的5个核心子步骤（process_id）的耗时。

比如：

表结构大概:

1个APP_NO 对应多个 process_id

难点：

难点1：子步骤不固定

不知道哪些process_id 为一组。有的甚至是有20多个 process_id 为一组。

 

有可能是 23333到23336 是一组算步骤1， 也有可能23333到23334算步骤1。 也有可能本身 23333就算步骤1.

甚至有的没有步骤1  

且数据也存在后期填补可能， 也就是你可能遇到步骤1 只有开始时间， 没有结束时间。因为数据可能没有采集呢。

难点2：节假日不固定

目前接手时候SQl大概为。

select app_no, 

步骤1开始时间，

步骤1结束时间，

（select count(1) from 日期表 where   bewteen  步骤1开始时间 and 步骤1结束 ），

步骤2开始时间，

步骤2结束时间，

（select count(1) from 日期表 where   bewteen  步骤2开始时间 and 步骤2结束 ）............

 

有人说可以根据开始时间，结束时间。算两个时间相隔多少天。但是此处不可行。 因为是算工作日天数的。 每年的节假日都不同。

 

难点3：标量子查询改造

我们把输出列中嵌套select语句的叫标量子查询。

 

也有说使用left join 改造SQL。

单纯用left join 改造的话， 难点又有了，因为用left join 后数据量会翻几十甚至几百倍。

比如两个时间相隔20天。而且又是5个标量啊。 5个left join ???

比如： A left join B on   bewteen  and…..  A表2个时间， B表一个时间。而且是不等式关联。 大家可以百度下 不等式标量子查询看看就知道了。

虽然可以说用group by但是在关联时候就已经翻翻了。只不过最后group by 重新聚合了而已。  而且不要忘记有5个标量子查询。

解决方案：

我大概想了2种解决方案。这里说最终采用的

 

 

第一步： 子步骤识别

首先解决 哪几个process_id 为子步骤。 我采用的是制造一列。

 

第二步 子步骤合并：

当然了涉及到数据填补，填补结束时间啊， 因为有的数据就没有结束时间。

也就是说结果需要有开始时间，必须有结束时间。  

 

第三步 获取多少工作日

 

把所有步骤合并后，只要left join 日期表一次就可以取出工作日。 当然这个涉及到效率问题。可以理解为 1：N：N转化为1：N。

SQL我记得大概

Select t.*, count(t2) from t left join t_日期表t2 on

 t1.开始时间>= t2时间 and t1.结束时间<= t2时间  and isworkday=true

group by  t.*;

第四步  行列转换获取需要的数据

基于以上结果表

Select  APP_NO,

max( case when type = ‘步骤1’ then 开始时间 else null end )  步骤1开始时间，

max( case when type = ‘步骤1’ then 开始时间 else null end )  步骤1结束时间

max( case when type = ‘步骤1’ then 工作日天数 else null end )  步骤1工作日天数

…………

 

结果类似：

 

至此，功能完成。测试过了结果 2万多条数据，在1分钟左右出结果。效率还会更快，后期在说把……

 

总结：

接到这个业务的时候，基表数据非常不整齐，缺数据，少数据更是常见的。 大数据的基表数据可能来自文件数据，结构化的数据。 因此数据非常的不整洁。往往大数据项目组必然有数据清洗。  正是数据不整齐，大数据项目对业务模型，数据模型的要求是更高的。

 

如果我前期没有做数据整理， 基本上这个业务不好做， 而且本身大数据项目数据量大， 如果我不改造取日期计算， 将会是1:N:N:N:N:N.  因为是5个核心步骤。关联后数据量无法想象。估计不论多少节点估计暴内存。   可以透露下 process_id 基表数据量应该在 1个亿左右。