改写的力量--分析函数不光只有row_number()

还是某客户的经营分析系统，下面这个SQL，执行了1.2小时：

SQL 代码简化如下：

INSERT /*+ append*/

INTO MD_KPI_ACT_EMU_PRODUCT_MON_01 nologging

(month_number,

day1,

......

day31)

SELECT /*+parallel(t, 8) parallel(t2, 8) use_hash(t, t2) full(t) full(t2)*/

t.month_number,

TO_NUMBER (t2.day1) day1,

......

TO_NUMBER (t2.day31) day31

FROM

(SELECT /*+parallel(t, 8) full(t)*/

t.month_number,

......

t.town_id

FROM

(SELECT /*+parallel(t, 8) full(t)*/

t.month_number,

......

t.town_id,

ROW_NUMBER () OVER (PARTITION BY t.msisdn

ORDER BY t.stat_date DESC) row_id

FROM interim_cp_emu_02 t

) t WHERE t.row_id = 1

) t,

( SELECT /*+parallel(t, 8) full(t)*/

t.msisdn,

MAX (t.day1) day1,

......

MAX (t.day31) day31

FROM interim_cp_emu_02 t

GROUP BY t.msisdn

) t2

WHERE t.msisdn = t2.msisdn;

这个开发人员知道使用row_number()分析函数做记录去重，但是却不知道还有其他的分析函数可以取MAX，根据原SQL的业务逻辑，改写如下：

INSERT /*+ append*/

INTO MD_KPI_ACT_EMU_PRODUCT_MON_01 nologging

(month_number,

day1,

......

day31)

SELECT /*+ parallel(8) */

t.month_number,

TO_NUMBER (t2.day1) day1,

......

TO_NUMBER (t2.day31) day31

FROM (SELECT /*+parallel(t, 8) full(t)*/

t.month_number,

......

t.town_id,

ROW_NUMBER () OVER (PARTITION BY t.msisdn

ORDER BY t.stat_date DESC) row_id,

MAX (t.day1) over (partition by t.msisdn) as day1,

......

MAX (t.day31)over (partition by t.msisdn) as day31

FROM bsdata.interim_cp_emu_02 t

) t2

WHERE t2.row_id = 1

);

改写后的的代码更简洁了，更重要的是，大表扫描只需要一次，而且不需要做Join操作，当然效率也提高了很多倍（select部分做测试，原SQL主要消耗时间就是在select部分），只需要3.9分钟：

总结：

分析函数，最最常用的当数row_number()，但是，不要忽略了其他函数的存在，这个案例说明了多了解一些SQL知识还是很重要的！