1.1 GROUP by用函数的问题

  • 方法一：截止mid中间表，先把结果加工出来，再做处理
  • 方法二：group by 后面第一个字段别用函数
    1. select aes_decrypt(MSISDN,'asd') msisdn,
    2. substr(aes_decrypt(IMEI,'asd'),1,14) IMEI_NBR,
    3. GPRS_NET_TYPE,
    4. SUM(FLUX_CNT) FLUX_CNT
    5. from   test.ms
    6. GROUP BY msisdn,substr(aes_decrypt(IMEI,'asd'),1,14),GPRS_NET_TYPE;

    group by 1,2,3 改成 GROUP BY msisdn,substr(aes_decrypt(IMEI,’asd’),1,14),GPRS_NET_TYPE

INSERT INTO PROVAG.DWI_D_FLUX_G_BASE_MID2_12300_20191130 SELECT 这个SQL， 可以适当优化下。
样例：
（1）(cast(substr(b_b.call_start_time, 9, 2) AS signed) = 2)，没必要转换为数值，直接(substr(b_b.call_start_time, 9, 2) = ‘02’)；（2）建一以GROUP BY b_b.msisdn, 这个字段为分布键的中间表。选取要的字段，同时，加一个字段gprs_moment 记 substr(b_b.call_start_time, 9, 2) ，做好数据后，再这表做group by；（3）group 那直接 用b_b.imei，不要用substr(SM4_DECRYPT(b_b.imei, ‘684e4c4a6a2340466750515979675141’), 1, 14)，密文原文是一一对应的，没必要在group by 里用解密函数（若IMEI不是14位，则和上面类似，先在中间表里处理好，否则直接放到group by 里去做会慢很多）
1.2判读字符非空的写法
一 、如果 LAC_ID，CELL_ID 是字符类型
WHERE STATIS_DATE = ‘20191130’ AND (LENGTH(LAC_ID)>0 AND LENGTH(CELL_ID)>0) AND GPRS_FLUX > 0
改成
WHERE STATIS_DATE = ‘20191130’ AND LAC_ID>’’ AND CELL_ID>’’ AND GPRS_FLUX > 0

1.3Where 语句等号左边避免用函数
如果 EXP_DATE 格式是 ‘2019/11/30’ 这样子。
WHERE REPLACE(EXP_DATE,’/‘,’’)>=’20191130’
改成
WHERE EXP_DATE>=’2019/11/30’

1.4GROUP BY里避免写常量
样例1
‘12300’ PROV_ID, 这个地方已写死了值 。
最后 GROUP BY STATIS_DATE ,PROV_ID,MSISDN ，就不用加PROV_ID了。最外层select那改成’12300’ MSISDN_PROV_ID

‘20191130’ STATIS_DATE, 这个也是固定值。
最后的 GROUP BY STATIS_DATE ,PROV_ID,MSISDN ，直接 group by msidn
相应最外层select 那相应改固定。 ‘20191130’ STATIS_DATE,’12300’ MSISDN_PROV_ID,

样例2
GROUP BY MSISDN,PROV_ID,OPPOSE_PHONE,OPPO_BELONG_ADD
在下面的语句中已经指定了PROV_ID
SELECT STATIS_DATE,MSISDN,PROV_ID,CALL_TYPE,OPPOSE_PHONE,OPPO_BELONG_ADD,OPPOSE_BRAND,CALL_DURA,CALL_DURA_6,CALL_DURA_7,CALL_DURA_8,CALL_DURA_9,CALL_DURA_10,CALL_DURA_11,CALL_DURA_12,CALL_DURA_13,CALL_DURA_14,CALL_DURA_15,CALL_DURA_16,CALL_DURA_17,CALL_DURA_18,CALL_DURA_19,CALL_DURA_20,CALL_DURA_21,CALL_DURA_22,CALL_DURA_23_6,JF_TIMES,LONG_TIMES,FAV_CALL_FEE,FAV_AFTER_BASIC_FEE,FAV_AFTER_TOLL_FEE,FAV_AFTER_OTHER_FEE,VOICE_CNT,VOICE_CNT_6,VOICE_CNT_7,VOICE_CNT_8,VOICE_CNT_9,VOICE_CNT_10,VOICE_CNT_11,VOICE_CNT_12,VOICE_CNT_13,VOICE_CNT_14,VOICE_CNT_15,VOICE_CNT_16,VOICE_CNT_17,VOICE_CNT_18,VOICE_CNT_19,VOICE_CNT_20,VOICE_CNT_21,VOICE_CNT_22,VOICE_CNT_23_6
FROM DWI_D_EVNT_MB_VOICE_CIRCLE_11100@SH74_PROVAG_MN128
WHERE STATIS_DATE LIKE ‘201912%’ AND PROV_ID = ‘11100’

样例3
Where 子句中，等号左边不要用截取函数，改写成like
如：where statis_date like ‘201809%’

（1）不要从这里面查，直接手动将每月的周末定值写入查询
SELECT STATIS_DATE,IS_WEEKEND
FROM DIM.DIM_HOLIDAY
WHERE STATIS_DATE LIKE ‘201912%’

1.5借助中间表，将数据量大的结果放到中间表
（3）相同的子查询，且数据量大的建立先将结果放到中间表。而且有条件的子查询有时可能走不了hash join，会变成嵌套循环join
将SELECT USER_ID,MSISDN FROM PROVAG.DWI_D_SVC_USER_MSISDN_RELATION_11100 WHERE STATIS_DATE = 20191231生成到中间表
（4）为了数据库运行效率，可以考虑数据冗余
如果经常有以下需求，考虑将他固定为表的字段
SUM(CASE WHEN IS_WEEKEND = 0 THEN
NVL(VOICE_CNT_6,0)+NVL(VOICE_CNT_7,0)+NVL(VOICE_CNT_8,0)+
NVL(VOICE_CNT_9,0)+NVL(VOICE_CNT_10,0)+NVL(VOICE_CNT_11,0)+
NVL(VOICE_CNT_12,0)+NVL(VOICE_CNT_13,0)+NVL(VOICE_CNT_14,0)+
NVL(VOICE_CNT_15,0)+NVL(VOICE_CNT_16,0)+NVL(VOICE_CNT_17,0)+
NVL(VOICE_CNT_18,0)
ELSE 0 END) WKG_VOICE_CNT
1.6减少group by层级
好多SQL种group by嵌套好多层，严重影响效率，建议使用中间表或修改逻辑
样例1
上成形如select a from (select a,sum(b) from idid where a>0 group by a having sum(b)>2)t2; 这样的查询。可以去掉一层
直接select a from idid where a>0 group by a having sum(b)>2; 这样就可以等号左边不要用截取函数

还有就是group by 层数多。有九层。这很耗时
可以把结果先写入一以msisdn为分布键的中间表中。再对中间表做group，因为按msisdn重分布后，分散到很多节点去做；原来那来，是集中数据到一节点做group，就慢了
1.7分布键使用
排序 关联 能用分布键的都把分布键写上

1.8少用子查询
尽量用表关联替代子查询
On a.msisdn=b.msisdn and a.statis_date=’20191101’
这里用and 替换 where 效率更高一些
样例1
可以参考这个 。A表很小。没必要用子查询。这样反而会破坏分布。不能在节点hash join

SELECT
A.RN,
B.PROVINCE as PROV_ID,
B.MSISDN,
B.CALL_START_TIME,
TO_CHAR(date_add(to_date(B.CALL_START_TIME,’YYYYMMDDHH24MISS’), interval round(B.CALL_DUR) second ),’YYYYMMDDHH24MISS’) AS CALL_END_TIME,
B.TAC_ID as LAC_ID,
B.CELL_ID,
A.STATIS_DATE
FROM
boncres.anti_jm_10100 A
JOIN
PROVNZ.ods_d_evnt_4g_gprs_voice_12300_201911 B
ON A.MSISDN=B.MSISDN
and B.CALL_START_TIME between to_char(date_add(to_date(A.CASE_TIME,’YYYYMMDDHH24MISS’), interval -3 hour),’YYYYMMDDHH24MISS’) and to_char(date_add(to_date(A.CASE_TIME,’YYYYMMDDHH24MISS’), interval 3 hour),’YYYYMMDDHH24MISS’)
and A.CASE_TIME like ‘201911%’;
1.9用between and代替>= <=
WHERE (provag.dwi_d_evnt_mb_gprs_12300_202001.statis_date >= ‘20200101’)
AND (provag.dwi_d_evnt_mb_gprs_12300_202001.statis_date <= ‘20200110’) — 这个用between

1.10datetime 类型，找前一天的写法
TO_CHAR(ENDDATE,’YYYYMMDD’)=TO_CHAR(now(),’YYYYMMDD’)-1改成 enddate between DATE_SUB( CURDATE(),INTERVAL 1 DAY)||’ 00:00:00’ and DATE_SUB( CURDATE(),INTERVAL 1 DAY)||’ 23:59:59’
1.11视图中涉及的32张表，分布键尽量不要用记录日期的字段
会造成同一天数据集中一节点。如果provag.ods_execute_log_10100 之类表的数据都是load进去的话，没合适的，就不设分布键了
1.12Group by字段避免用常量
sql优化建议：1）prov_id = ‘12200’ 为定值，group by 中，可以去掉这个字段，减少不必要的运算。select 部分有需要，可以直接写入定值在select 中。
1.13灵活使用中间表
b1至b4的表都是对boncres.dwi_d_res_cell_sum_qsn_10000巨表的查询。可根据选择字段，创建以 user_id为分布字段的四个中间表。将需要的数据分别插入相应的中间表，然后与其它表做关联