oracle多条数据合并成一条_用数据模型计算模型分析优化hiveSQL

背景：

记得以前有朋友(她也是我佩服的朋友之一)问，数据库系统中SQL优化，我知道你会优化的，hive SQl 你会优化吗？我就感觉很奇怪，便回答到，数据库系统中也好，大数据平台中也好，都是数据计算或处理。那么在数据库系统中执行计划，表，索引总归也可以归纳成数据模型/计算模型来说道说道，既然是数据模型，计算模型，那么在大数据平台中也可以通过改变数据模型，计算模型来优化系统，优化业务计算逻辑吧，本案例也是我操刀的优化最成功的案例之一。

案例：

这是做“数字化审计时候”某业务的长SQl，在hive中根本跑不出结果。 

业务简述：

我先把业务描述下。就是计算某个步骤(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：节假日不固定

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

难点3：hive 中不支持标量子查询

因为项目数据同时在Oracle，hive中都有，Oracle中采用的是标量子查询方式比如：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结束... ) 但是在hive 中不支持标量子查询。

有人说可以用left join 改造SQl。

但是用left join 改造的话,难点又有了,因为用left join 后数据量会翻几十甚至几百倍，而且又是5子步骤也就是5个left join.比如两个时间相隔20天。外加5个标量，关联时候就数据量已经翻了上百倍，只不过最后group by 重新聚合了，结果被聚合了，但是关联之时数据量超大。

解决方案：

第一步：子步骤识别

首先解决 哪几个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万多条数据，在10分钟之内出的结果。效率还会更快，后期再说说如何进一步优化吧，总的来说跑不出来的业务SQl，优化到10分钟之内，效果比较明显。

总结：

本次优化分析到原来的逻辑计算模型非常不好，因为原先关联计算中存在1：N:N... 也就是类似“小笛卡尔积”的结果，数据量可想而知。经过逻辑计算模型优化后(当然也涉及到数据模型)由1:N:N....优化成1:N.规避了“小笛卡尔积”的结果。因此效率提升很明显。

结束语：

有时候我常常在日常工作中，思考如果继续优化或者改造怎么办？现在的数据模型，业务模型会有什么问题？是否还没有暴露出隐患。其原理又是什么？是否有结果已经不重要了。同样数据库系统也好， 大数据系统也好。总的来说是根据业务模型，处理业务逻辑。跳出框架，优化数据模型，计算模型。那么又会是另外一层风景。