数据查询语言优化实例-20210104

最新推荐文章于 2022-11-16 17:00:44 发布
最新推荐文章于 2022-11-16 17:00:44 发布
阅读量283 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库 数据分析 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39496190/article/details/112523055
版权

数据查询语言优化实例-20210104

最近的实际工作中要参与一些数据汇总工作，因为工作内容不是特别的系统，写的代码也不准备留给谁，所以一开始并没有特别注重执行效率，只要执行不超过5分钟的并没有特别在意。

但工作能让你这么舒服吗，显然不可能。还是遇到了下面必须要盘（优化）的代码。

一 需要优化的语句

-- 执行效率：40分钟以上没有执行完
create table test_table as 
select
    '20210101' as STAT_DT 
    ,a1.CUST_ID as CUST_ID 
    ,a1.USER_LOAN_FLG as USER_LOAN_FLG 
    ,a1.SQ_LOAN_FLG as SQ_LOAN_FLG 
    ,a1.CURR_SQ_LOAN_FLG as CURR_SQ_LOAN_FLG 
    ,(case when a7.CIF_NO is not null then 1 else 0 end) as BLK_FLG 
    ,'0' as EMP_FLG 
    ,a1.EC_LOAN_FLG as EC_LOAN_FLG 
    ,a1.BAD_LOAN_FLG as BAD_LOAN_FLG  
    ,'0' as OD_LOAN_FLG 
    ,a2.AGE_CD as AGE_CD 
    ,a2.SEX_CD as SEX_CD 
    ,a2.DEPO_OPEN_DUR as DEPO_OPEN_DUR 
    ,a2.CROSS_ORG_CNT as CROSS_ORG_CNT 
    ,a2.DEPO_ACCT_CNT as DEPO_ACCT_CNT 
    ,a2.CURR_BAL as DEPO_BAL 
    ,a2.LOAN_BAL as LOAN_BAL 
    ,a2.CURR_BAL/a2.LOAN_BAL as DEPO_LOAN_RT 
    ,a4.DEPO_BAL_20191130 as DEPO_BAL_20191130 
    ,a5.M_AVG_DEPO_BAL as M_AVG_DEPO_BAL 
    ,a6.MAX_M_AVG_DEPO_BAL as MAX_M_AVG_DEPO_BAL 
    ,a6.MAX_M_AVG_DEPO_BAL_MCNT as MAX_M_AVG_DEPO_BAL_MCNT 
    ,(case when a10.CUST_ID is null then 1 else 0 end) as CARD_FLG 
    ,a1.LOAN_H_CNT as LOAN_H_CNT 
    ,(case when a8.master_cust_no is null then 0 else 1 end) as TIME_FLG 
    ,a9.M12_TRAN_AMT as M12_TRAN_AMT 
    ,a9.M12_TRAN_CNT as M12_TRAN_CNT 
    ,a9.M12_D_TRAN_AMT as M12_D_TRAN_AMT 
    ,a9.M12_C_TRAN_AMT as M12_C_TRAN_AMT 
    ,a9.M12_MAX_SIGLE_TRAN_AMT as M12_MAX_SIGLE_TRAN_AMT 
    ,a9.M12_D_MAX_SIGLE_TRAN_AMT as M12_D_MAX_SIGLE_TRAN_AMT 
    ,a9.M12_C_MAX_SIGLE_TRAN_AMT as M12_C_MAX_SIGLE_TRAN_AMT 
    ,(a2.CURR_BAL-a4.DEPO_BAL_20191130)/M12_C_TRAN_AMT as RETEN_RT 
    ,'0' as Y1 
    ,'0' as Y2 
    ,(case when a1.LOAN_H_CNT>1 and a2.LOAN_BAL>0 then 1 else 0 end) as Y3 
from (
    select 
        b1.CIF_ID as CUST_ID
        ,(CASE WHEN SUM(CASE WHEN b1.BEG_DATE BETWEEN 20171201 AND 20201130 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END) as USER_LOAN_FLG
        ,(CASE WHEN SUM(CASE WHEN b1.FIN_DATE BETWEEN 20191201 AND 20201130 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END) as SQ_LOAN_FLG
        ,(CASE WHEN SUM(CASE WHEN b1.DUE_STS='9' THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END) as CURR_SQ_LOAN_FLG
        ,(CASE WHEN SUM(CASE WHEN b1.PRDT_NO IN ('C107','C118','C119','C120','C127','129','C130','C108','C112') THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END) AS EC_LOAN_FLG
        ,(CASE WHEN SUM(CASE WHEN b1.FIVE_STS IN ('3','4','5') THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END) AS BAD_LOAN_FLG
        ,SUM(CASE WHEN b1.BEG_DATE BETWEEN 20171201 AND 20201130 THEN 1 ELSE 0 END) AS LOAN_H_CNT
    from F_CMS_RPT_XD b1
    where CIF_ID like '010%'--个人客户
    group by b1.CIF_ID
) a1
LEFT JOIN ANA_STRUCTURAL a2
ON a1.CUST_ID = a2.CUST_ID
-- LEFT JOIN
-- (
--     select CUST_NO as CUST_NO,sum(DUEBILL_BAL) as LOAN_BAL
--     from F_LN_LN_DUE_RECORD
--     where CUST_NO like '01%' and record_dt = '20201130'-- 取对私客户
--     group by CUST_NO
-- ) a3 --借据月表
-- ON a1.CUST_ID = a3.CUST_NO
LEFT JOIN 
(
    select master_cust_no,sum(PRT_LED_BAL) as DEPO_BAL_20191130
    from ADPXX_CQ_RECORD
    where record_dt = '20191130' 
    group by master_cust_no
) a4 --存款账户月表
ON a1.CUST_ID = a4.master_cust_no
LEFT JOIN 
(
    select master_cust_no,sum(AVGBAL) as M_AVG_DEPO_BAL
    from ADPXX_AVG_ACCTNO
    where ym = '202011'
    group by master_cust_no
) a5 --历史月日均记录
ON a1.CUST_ID = a5.master_cust_no
LEFT JOIN 
(
    select 
        master_cust_no,AVG_MONTH as MAX_M_AVG_DEPO_BAL,
        months_between(to_date('202011','yyyymm'),to_date(ym,'yyyymm')) as MAX_M_AVG_DEPO_BAL_MCNT
    from ADPXX_MAXAVG_MASTER_CUST_NO 
) a6 --最高月日均记录
ON a1.CUST_ID = a6.master_cust_no
LEFT JOIN 
(
    select distinct CIF_NO
    from F_CMS_CIF_BLACK_CAUSE 
) a7 --黑名单
ON a1.CUST_ID = a7.CIF_NO
LEFT JOIN 
(
    select distinct master_cust_no
    from ADPXX_CQ_RECORD
    where 
        (record_dt between 20171201 and 20201130) --近三年
        and LIAB_PRDT_TYPE = '1'--取定期
    group by master_cust_no
) a8 --取定期历史记录
ON a1.CUST_ID = a8.master_cust_no
LEFT JOIN 
(
    select 
        b2.MASTER_CUST_NO as CUST_ID
        ,SUM(b1.TX_AMT) as M12_TRAN_AMT 
        ,count(1) as M12_TRAN_CNT
        ,SUM(CASE WHEN b1.CR_DR_MAINT_IND = 0 THEN b1.TX_AMT ELSE 0 END) as M12_D_TRAN_AMT
        ,SUM(CASE WHEN b1.CR_DR_MAINT_IND = 1 THEN b1.TX_AMT ELSE 0 END) as M12_C_TRAN_AMT
        ,MAX(b1.TX_AMT) as M12_MAX_SIGLE_TRAN_AMT
        ,MAX(CASE WHEN b1.CR_DR_MAINT_IND = 0 THEN b1.TX_AMT ELSE 0 END) as M12_D_MAX_SIGLE_TRAN_AMT
        ,MAX(CASE WHEN b1.CR_DR_MAINT_IND = 1 THEN b1.TX_AMT ELSE 0 END) as M12_C_MAX_SIGLE_TRAN_AMT
    from F_CI_BDPAL b1
    left join ADPXX_CQ b2 
    on b1.acct_no = b2.acct_no
    where 
        b1.TX_DT BETWEEN 20191201 AND 20201130
        and b1.MEMO_CODE NOT IN ('000212','B00102','B00104','B10003') --入息、放贷、还息、还贷
        and (b1.RVS_FLG NOT IN ('1','2') OR b1.BRVS_FLG<>'1') --冲正和被冲正
    group by b2.MASTER_CUST_NO
) a9 --账户余额发生明细表
ON  a1.CUST_ID = a9.CUST_ID
LEFT JOIN 
(
    select 
        distinct a2.kehhao as CUST_ID
    from F_CCS_CARD a1
    left join F_CI_BDSKH a2
    on trim(a1.CUSTR_NBR) = trim(a2.ZHJHAO)
    where a2.ZHJNZL = '10' and a1.CANCL_DAY <= 20201130 and a1.ISSUE_DAY <= 20201130
) a10
ON a1.CUST_ID = a10.CUST_ID
;

二 语句效率执行慢原因解析 以及 解决方式

  1. left join 的表太多

说一下left join 的算法

解决方式：
根据业务类别和left join 表数据量的的不同，将代码拆分成几部分。具体的实现方式就是建立中间表（临时表），上述代码我拆分成了两部分，第一部分为客户的基本信息以及一些容易算出的字段指标，第二部分则为数据量庞大的交易明细汇总部分。

  1. left join 的一些表数据太大

解决方式：
我们这些数据量很大的表，我们可以通过加强where条件筛选出我们需要的数据，也可以直接将数据一次性汇总成我们需要的表。如上述代码中的 a9 表，表数据总数约2亿，我们只提取固定1年时间仍然是很大的数据量，因此可以在此的基础上在增加客户号的筛选条件，以及增加索引。

  1. 语句中牵扯了大量的聚合函数

解决方式：
在创建中间表的过程中，已经将大部分的聚合函数执行完毕，因此在汇总最后结果时，两张表只需要简单的left join 即可。

  1. 其它

上述的解决方式都是大方向的优化，在具体的优化还可以补充以下方式：

  • 为中间表及需要的表创建索引，创建索引可以解决大部分的查询效率问题;
  • 增加临时表空间、分库分表等操作也会增加数据的查询效率，但在本次优化过程中并没有使用，不做讨论。

三 优化后代码

------------------------------------------------------------------------------
-- 1. 客户群主表
------------------------------------------------------------------------------
drop table ana_tmp_t1 ;
create table ana_tmp_t1 as
select 
    a1.CUST_ID as CUST_ID -- 客户号
    ,a1.USER_LOAN_FLG as USER_LOAN_FLG -- 是否在20171201-20201130有过用信
    ,a1.SQ_LOAN_FLG as SQ_LOAN_FLG -- 是否在20191201-20201130结清贷款
    ,a1.CURR_SQ_LOAN_FLG as CURR_SQ_LOAN_FLG -- 当前是否结清客户
    ,(case when a7.CIF_NO is not null then 1 else 0 end) as BLK_FLG -- 是否黑名单客户
    ,a1.EC_LOAN_FLG as EC_LOAN_FLG -- 是否有过按揭贷款、贫困贷、助学贷
    ,a1.BAD_LOAN_FLG as BAD_LOAN_FLG -- 是否有过五级不良贷款
    ,a1.LOAN_H_YQDAY_MCNT as LOAN_H_YQDAY_MCNT -- 历史最大逾期天数 -- 76 结清日期都为null
    ,a11.AGE_CD as AGE_CD -- 年龄 
    ,a11.SEX_CD as SEX_CD -- 性别 
    ,nvl2(a2.DEPO_OPEN_DUR,a2.DEPO_OPEN_DUR,0) as DEPO_OPEN_DUR -- 存款开户时长 
    ,nvl2(a2.CROSS_ORG_CNT,a2.CROSS_ORG_CNT,0) as CROSS_ORG_CNT -- 跨网点数 
    ,nvl2(a2.DEPO_ACCT_CNT,a2.DEPO_ACCT_CNT,0) as DEPO_ACCT_CNT -- 存款账户数 
    ,nvl2(a2.CURR_BAL,a2.CURR_BAL,0) as DEPO_BAL -- 当前时点存款余额
    ,a1.LOAN_BAL as LOAN_BAL -- 当前贷款余额
    ,(case when a1.LOAN_BAL = 0 then -1 else nvl2(a2.CURR_BAL,a2.CURR_BAL,0)/a1.LOAN_BAL end ) as DEPO_LOAN_RT -- 当前存贷比 
    ,nvl2(a4.DEPO_BAL_20191130,a4.DEPO_BAL_20191130,0) as DEPO_BAL_20191130 -- 20191130时点余额 
    ,nvl2(a5.M_AVG_DEPO_BAL,a5.M_AVG_DEPO_BAL,0) as M_AVG_DEPO_BAL -- 当前月日均存款
    ,nvl2(a6.MAX_M_AVG_DEPO_BAL,a6.MAX_M_AVG_DEPO_BAL,0) as MAX_M_AVG_DEPO_BAL -- 历史最高月日均存款 
    ,nvl2(a6.MAX_M_AVG_DEPO_BAL_MCNT,a6.MAX_M_AVG_DEPO_BAL_MCNT,0) as MAX_M_AVG_DEPO_BAL_MCNT -- 历史最高月日均存款距当前月份 
    ,(case when a10.CUST_ID is null then 1 else 0 end) as CARD_FLG -- 是否有信用卡产品
    ,a1.LOAN_H_CNT as LOAN_H_CNT -- 近3年贷款次数
    ,a1.BAD_DUE_FLG as BAD_DUE_FLG -- 是否有过借据不正常的借据
from
(
    select 
        b1.cif_no as CUST_ID
        ,(CASE WHEN SUM(CASE WHEN b1.BEG_DATE BETWEEN 20171201 AND 20201130 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END) as USER_LOAN_FLG
        ,(CASE WHEN SUM(CASE WHEN b1.due_sts='9' THEN 0 ELSE 1 END)=0 AND MAX(b1.FIN_DATE) >= 20191201 THEN 1 ELSE 0 END) as SQ_LOAN_FLG
        ,(CASE WHEN SUM(CASE WHEN b1.due_sts='9' THEN 0 ELSE 1 END)=0 THEN 1 ELSE 0 END) as CURR_SQ_LOAN_FLG
        ,(CASE WHEN SUM(CASE WHEN b1.PRDT_NO IN ('C107','C118','C119','C120','C127','129','C130','C108','C112','G101','G104','I103') THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END) AS EC_LOAN_FLG
        ,(CASE WHEN SUM(CASE WHEN b1.FIVE_STS IN ('3','4','5') THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END) AS BAD_LOAN_FLG
        ,SUM(CASE WHEN b1.BEG_DATE BETWEEN 20171201 AND 20201130 THEN 1 ELSE 0 END) AS LOAN_H_CNT
        ,(CASE WHEN SUM(CASE WHEN b1.due_sts not in ('1','9') THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 END) AS BAD_DUE_FLG -- 是否有过借据不正常的借据
        ,MAX(b1.LOAN_H_YQDAY_CNT) AS LOAN_H_YQDAY_MCNT -- 最大逾期天数
        ,SUM(BAL) as LOAN_BAL -- 当前贷款余额
    from 
    (
        SELECT 
            c1.*
            ,(
                CASE 
                    WHEN c1.due_sts = '1' THEN (to_date('20201130','yyyymmdd') -to_date(c1.EXP_ENDDATE,'yyyymmdd')-1) 
                    WHEN c1.due_sts = '9' THEN (to_date(c1.FIN_DATE,'yyyymmdd')-to_date(c1.EXP_ENDDATE,'yyyymmdd')-1)
                    ELSE 99999
                END
            ) as LOAN_H_YQDAY_CNT
        from F_CMS_RPT_XD c1
        where c1.cif_no like '010%'--个人客户
            -- and c1.FIN_DATE BETWEEN 20191201 AND 20201130
            -- and c1.PRDT_NO not in ('G101','G104','I103') -- 'I103':'公积金'
    ) b1
    group by b1.cif_no
) a1
LEFT JOIN ANA_STRUCTURAL a2
ON a1.CUST_ID = a2.CUST_ID
LEFT JOIN 
(
    select master_cust_no,sum(PRT_LED_BAL) as DEPO_BAL_20191130
    from ADPXX_CQ_RECORD
    where record_dt = '20191130' 
    group by master_cust_no
) a4 --存款账户月表
ON a1.CUST_ID = a4.master_cust_no
LEFT JOIN 
(
    select master_cust_no,sum(AVGBAL) as M_AVG_DEPO_BAL
    from ADPXX_AVG_ACCTNO
    where ym = '202011'
    group by master_cust_no
) a5 --历史月日均记录
ON a1.CUST_ID = a5.master_cust_no
LEFT JOIN 
(
    select 
        master_cust_no,AVG_MONTH as MAX_M_AVG_DEPO_BAL,
        months_between(to_date('202011','yyyymm'),to_date(ym,'yyyymm')) as MAX_M_AVG_DEPO_BAL_MCNT
    from ADPXX_MAXAVG_MASTER_CUST_NO 
) a6 --最高月日均记录
ON a1.CUST_ID = a6.master_cust_no
LEFT JOIN 
(
    select distinct CIF_NO
    from F_CMS_CIF_BLACK_CAUSE 
) a7 --黑名单
ON a1.CUST_ID = a7.CIF_NO
LEFT JOIN 
(
    select 
        distinct a2.kehhao as CUST_ID
    from F_CCS_CARD a1
    left join F_CI_BDSKH a2
    on trim(a1.CUSTR_NBR) = trim(a2.ZHJHAO)
    where a2.ZHJNZL = '10' and a2.kehhao like '010%' and a1.CANCL_DAY <= 20201130 and a1.ISSUE_DAY <= 20201130
) a10
ON a1.CUST_ID = a10.CUST_ID
left join 
(
    select 
        c1.cif_no as CUST_ID,c2.id_no
        ,case 
            when isidcard_new(c2.id_no) = '1' then
                trunc(
                    months_between(
                        to_date('20201130','yyyymmdd'),
                        to_date(
                            case when length(trim(c2.id_no)) = 18 
                            then SubStr(c2.id_no, 7, 8) 
                            else '19' || SubStr(c2.id_no, 7, 6)
                            end , 'yyyymmdd'
                        )
                    )/12
                ) 
            else 999 end
        as AGE_CD
        ,case 
            when isidcard_new(c2.id_no) = '1' 
            then (
                case 
                    when length(trim(c2.id_no)) = 18 and mod(SubStr(c2.id_no, 17, 1),2) = 1 then '1' --男
                    when length(trim(c2.id_no)) = 18 and mod(SubStr(c2.id_no, 17, 1),2) = 0 then '2' --女
                    when length(trim(c2.id_no)) = 15 and mod(SubStr(c2.id_no, 15, 1),2) = 1 then '1' --男
                    when length(trim(c2.id_no)) = 15 and mod(SubStr(c2.id_no, 15, 1),2) = 0 then '2' --女
                else '999'
                end 
            ) else '999' end 
        as SEX_CD
    from
    (
        SELECT
            b1.cif_no,max(b1.beg_date) as x
        FROM F_CMS_RPT_XD b1
        where 
            CIF_NO like '010%'
        group by b1.cif_no
    ) c1
    left join
    (
        SELECT
            b1.cif_no,b1.id_no,max(b1.beg_date) as x
        FROM F_CMS_RPT_XD b1
        where 
            CIF_NO like '010%'
        group by b1.cif_no,b1.id_no
    ) c2
    on c1.cif_no = c2.cif_no and c1.x = c2.x
) a11 -- 对私客户信息表
on a1.CUST_ID = a11.CUST_ID
;
alter table ana_tmp_t1 add primary key (CUST_ID);

------------------------------------------------------------------------------
-- 2. 账户余额发生明细表-数据汇总
------------------------------------------------------------------------------
drop table ana_tmp_t2;
create table ana_tmp_t2 as
select 
    b2.MASTER_CUST_NO as CUST_ID
    ,SUM(b1.TX_AMT) as M12_TRAN_AMT 
    ,count(1) as M12_TRAN_CNT
    ,SUM(CASE WHEN b1.CR_DR_MAINT_IND = 0 THEN b1.TX_AMT ELSE 0 END) as M12_D_TRAN_AMT
    ,SUM(CASE WHEN b1.CR_DR_MAINT_IND = 1 THEN b1.TX_AMT ELSE 0 END) as M12_C_TRAN_AMT
    ,MAX(b1.TX_AMT) as M12_MAX_SIGLE_TRAN_AMT
    ,MAX(CASE WHEN b1.CR_DR_MAINT_IND = 0 THEN b1.TX_AMT ELSE 0 END) as M12_D_MAX_SIGLE_TRAN_AMT
    ,MAX(CASE WHEN b1.CR_DR_MAINT_IND = 1 THEN b1.TX_AMT ELSE 0 END) as M12_C_MAX_SIGLE_TRAN_AMT
from F_CI_BDPAL b1
left join ADPXX_CQ b2 
on b1.acct_no = b2.acct_no
where 
    b1.TX_DT BETWEEN 20191201 AND 20201130
    AND( MEMO_CODE NOT IN ('000212','B00102','B00104','B10003') OR MEMO_CODE IS NULL) --入息、放贷、还息、还贷
    and (b1.RVS_FLG NOT IN ('1','2') OR b1.BRVS_FLG<>'1') --冲正和被冲正
group by b2.MASTER_CUST_NO
;
--alter table ana_tmp_t2 add primary key (CUST_ID);

------------------------------------------------------------------------------
-- 2.1. 账户余额发生明细表-数据汇总
------------------------------------------------------------------------------
drop table ana_tmp_t3;
create table ana_tmp_t3 as
select 
    b2.MASTER_CUST_NO as CUST_ID
    ,sum(b1.TX_AMT) as M12_C_ALL_TRAN_AMT
from F_CI_BDPAL b1
left join ADPXX_CQ b2 
on b1.acct_no = b2.acct_no
where 
    b1.TX_DT BETWEEN 20191201 AND 20201130
    and (b1.RVS_FLG NOT IN ('1','2') OR b1.BRVS_FLG<>'1') --冲正和被冲正
    and b1.CR_DR_MAINT_IND = 1
group by b2.MASTER_CUST_NO
;

------------------------------------------------------------------------------
-- 3. 个人贷款客户汇总表-数据汇总
------------------------------------------------------------------------------
drop table ANA_MARKETING_TMP ;
create table ANA_MARKETING_TMP as
select
    '20201130' as STAT_DT -- 统计日期
    ,a1.CUST_ID as CUST_ID -- 客户号
    ,a1.USER_LOAN_FLG as USER_LOAN_FLG -- 是否在20171201-20201130有过用信
    ,a1.SQ_LOAN_FLG as SQ_LOAN_FLG -- 是否在20191201-20201130结清贷款
    ,a1.CURR_SQ_LOAN_FLG as CURR_SQ_LOAN_FLG -- 当前是否结清客户
    ,a1.BLK_FLG as BLK_FLG -- 是否黑名单客户
    ,'0' as EMP_FLG -- 是否本行员工
    ,a1.EC_LOAN_FLG as EC_LOAN_FLG -- 是否有过按揭贷款、贫困贷、助学贷
    ,a1.BAD_LOAN_FLG as BAD_LOAN_FLG -- 是否有过五级不良贷款
    ,a1.LOAN_H_YQDAY_MCNT as LOAN_H_YQDAY_MCNT -- 历史最大逾期天数
    ,a1.AGE_CD as AGE_CD -- 年龄
    ,a1.SEX_CD as SEX_CD -- 性别
    ,a1.DEPO_OPEN_DUR as DEPO_OPEN_DUR -- 存款开户时长
    ,a1.CROSS_ORG_CNT as CROSS_ORG_CNT -- 跨网点数
    ,a1.DEPO_ACCT_CNT as DEPO_ACCT_CNT -- 存款账户数
    ,a1.DEPO_BAL as DEPO_BAL -- 当前时点存款余额
    ,a1.LOAN_BAL as LOAN_BAL -- 当前贷款余额
    ,a1.DEPO_LOAN_RT as DEPO_LOAN_RT -- 当前存贷比
    ,a1.DEPO_BAL_20191130 as DEPO_BAL_20191130 -- 20191130时点余额
    ,a1.M_AVG_DEPO_BAL as M_AVG_DEPO_BAL -- 当前月日均存款
    ,a1.MAX_M_AVG_DEPO_BAL as MAX_M_AVG_DEPO_BAL -- 历史最高月日均存款
    ,a1.MAX_M_AVG_DEPO_BAL_MCNT as MAX_M_AVG_DEPO_BAL_MCNT -- 历史最高月日均存款距当前月份
    ,a1.CARD_FLG as CARD_FLG -- 是否有信用卡产品
    ,a1.LOAN_H_CNT as LOAN_H_CNT -- 近3年贷款次数
    ,(case when a8.master_cust_no is null then 0 else 1 end) as TIME_FLG -- 历史是否有定期产品
    ,nvl2(a9.M12_TRAN_AMT,a9.M12_TRAN_AMT,0) as M12_TRAN_AMT -- 近一年总交易金额
    ,nvl2(a9.M12_TRAN_CNT,a9.M12_TRAN_CNT,0) as M12_TRAN_CNT -- 近一年总交易笔数
    ,nvl2(a9.M12_D_TRAN_AMT,a9.M12_D_TRAN_AMT,0) as M12_D_TRAN_AMT -- 近一年借方总交易金额
    ,nvl2(a9.M12_C_TRAN_AMT,a9.M12_C_TRAN_AMT,0) as M12_C_TRAN_AMT -- 近一年贷方总交易金额
    ,nvl2(a9.M12_MAX_SIGLE_TRAN_AMT,a9.M12_MAX_SIGLE_TRAN_AMT,0) as M12_MAX_SIGLE_TRAN_AMT -- 近一年单笔最大交易金额
    ,nvl2(a9.M12_D_MAX_SIGLE_TRAN_AMT,a9.M12_D_MAX_SIGLE_TRAN_AMT,0) as M12_D_MAX_SIGLE_TRAN_AMT -- 近一年借方单笔最大交易金额
    ,nvl2(a9.M12_C_MAX_SIGLE_TRAN_AMT,a9.M12_C_MAX_SIGLE_TRAN_AMT,0) as M12_C_MAX_SIGLE_TRAN_AMT -- 近一年贷方单笔最大交易金额
    ,(case when nvl2(a10.M12_C_ALL_TRAN_AMT,a10.M12_C_ALL_TRAN_AMT,0) = 0 then -999999 else (a1.DEPO_BAL-a1.DEPO_BAL_20191130)/nvl2(a10.M12_C_ALL_TRAN_AMT,a10.M12_C_ALL_TRAN_AMT,0) end) as RETEN_RT -- 留存率
    ,'0' as Y1 -- 
    ,'0' as Y2 -- 
    ,(case when a1.LOAN_H_CNT>1 and a1.LOAN_BAL>0 then 1 else 0 end) as Y3 -- 是否近3年有过多次贷款且当前正在贷款的客户
from 
(
    select * from ana_tmp_t1 where BAD_DUE_FLG = '0'
) a1
LEFT JOIN 
(
    select distinct master_cust_no
    from ADPXX_CQ_RECORD
    where 
        (record_dt between 20171201 and 20201130) --近三年
        and LIAB_PRDT_TYPE = '1'--取定期
    group by master_cust_no
) a8 --取定期历史记录
ON a1.CUST_ID = a8.master_cust_no
LEFT JOIN ana_tmp_t2 a9 
ON a1.CUST_ID = a9.CUST_ID
LEFT JOIN ana_tmp_t3 a10 
ON a1.CUST_ID = a10.CUST_ID
;
alter table ANA_MARKETING_TMP add primary key (CUST_ID);

四 结论

在实际工作中，优化查询有时并不需要多么先进的理论和技术，而是根据工作需求找准清晰的目标和问题所在(业务拆分、关键信息提炼、数据整合)，灵活使用基础技术即可达到目的。

呼呼哈嗨
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
数据库作业：第九章： 关系查询处理和查询优化习题
12-14
（1）首先读取表R，一块能装40块R元组读取的总块数是：20000/40=500块，即可得到结果； （2）有三层B+树索引，数据块是3+1=4块； （3）嵌套循环连接是指对外层循环的每一个元组，检索内层循环中的每一个元组。 首先读取表R和S，一般做法：在内存中尽可能多的装入某个表的若干块，留出一块存放另一个表的元组。设内存中可以存放10块R元组和1块S元组； 则读取总块数为：20000/40+（20000/（40*10）*1200/30=500+2000=2500块； 读R表500块，S表40块，50遍。连接后的元组数为20000个，设每块装40个元组，写成数据块是20000/40=500块；
数据库作业19：第九章： 关系查询处理和查询优化 课后习题
weixin_45871977的博客
05-03 8205
2、假设关系R(A,Bnz71)https://img-blog.csdnimg.cn2、假设关系R(A,B)]和S(B,C,D)情况如下：R有20000个元组，S有1200个元组，一个块能装40个R的元组，能装30个S的元组，估算下列操作需要多少次磁盘块读写。 （1）R上没有索引，select * from R; （2）R中A为主码，A有三层B+树索引，select * from R where...
关系查询处理和查询优化习题
qq_44606649的博客
12-19 2377
1.假设关系R(A, B)和S(B, C, D)情况如下: R有20000个元组，S有1 200个元组，一个块能装40 个R的元组，能装30个S的元组，估算下列操作需要多少次磁盘块读写。 1> R上没有索引，sclect* from R; 2> R中A为主码，A有3层B+数索引，`select * from R where A = 10; 3> 嵌套循环连接RDAS; 4> 排序合并连接RDAS,区分R与S在B属性上已经有序和无序两种情况。 3.对学生课程数据库，查询信息系学生选修了
关系查询处理和查询优化典型例题分析
gffytft的博客
04-16 3007
关系查询处理和查询优化典型例题分析 一 单选题 1.关于查询过程中的表与表之间的连接方法，效率比较低的是： A、 嵌套循环 B、 排序合并 C、 索引连接 D、 Hash Join 2.1000个学生，10000个选课记录。2号课程选课记录50个。一块内存可以放10个学生或者100个选课记录。计算机的内存有限，只能同时放下5块学生、1块选课记录和若干连接结果。计算机每秒...
mysql高级
weixin_42248522的博客
10-19 714
github笔记 csdn笔记 一、 linux var 目录，变量配置文件 var/lib/mysql/ mysql数据库文件的存放路径 没有提示即可 rpm -qa|grep -i mysql 查看是否按照mysql 。 rpm -ivh mysql.rpm 查看安装进度 chkconfig mysql on 设置开机自启动 ntsysv 查看开机启动项（*为开机启动项） top 查看开机时间 二、B+树 叶子节点有指针（顺序），叶子节点包含所有的元素，一个节点里面可以有多个元素 （B+树只有叶子节点才
java源码包---java 源码 大量 实例
04-18
　Java数据压缩与传输实例，可以学习一下实例化套按字、得到文件输入流、压缩输入流、文件输出流、实例化缓冲区、写入数据到文件、关闭输入流、关闭套接字关闭输出流、输出错误信息等Java编程小技巧。 Java数组倒置...
SQLServer2008查询性能优化 2/2
03-02
《SQL Server 2008查询性能优化》通过大量实例，详细介绍了SQL Server数据库系统优化的各种方法和技巧。内容涵盖了数据库应用系统中各种性能瓶颈的表现形式及其发生的根源和解决方法，从硬件瓶颈到查询、索引设计...
SQLServer2008查询性能优化 1/2
03-02
《SQL Server 2008查询性能优化》通过大量实例，详细介绍了SQL Server数据库系统优化的各种方法和技巧。内容涵盖了数据库应用系统中各种性能瓶颈的表现形式及其发生的根源和解决方法，从硬件瓶颈到查询、索引设计...
Microsoft SQL SERVER 2008技术内幕 T-SQL查询
09-04
主要内容包括SQL 的基础理论、查询优化、查询算法及复杂度，以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP 和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...
Microsoft SQL Server 2008技术内幕：T-SQL查询(第二卷)
10-22
主要内容包括SQL的基础理论、查询优化、查询算法及复杂度，以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...
MySQL查询优化实例
weixin_44139651的博客
11-16 634
导语 通过几个小实例，对实际会经常用到的查询进行对比，通过MySQL的执行计划分析语句的执行性能，最后分析几个在实际中会遇到的小问题。我们知道一般应用系统的读写比列在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化是非常重要的。
【更新】SQL查询效率优化思路，附经典50道题及答案
weixin_44778063的博客
03-22 1289
先了解SQL Select语句完整的执行顺序： from子句组装来自不同数据源的数据； where子句基于指定的条件对记录行进行筛选； 1）where条件的执行顺序是从右到左的； 2）where条件优先执行索引类条件； group by子句将数据划分为多个分组； 使用聚集函数进行计算； 使用having子句筛选分组； 计算所有的表达式； 使用order by对结果集进行排序； 因此优化思路也显...
【4/27 -19】第九章课后习题关系查询处理和查询优化
summer__kkkk的博客
04-28 4996
第九章的知识点已在：第九章：关系查询处理和查询优化写出 1.假设关系R(A,B)和S(B、C、D)情况如下：R有20000个元组，S有1200个元组，一个块能装40个R的元组，能装30个S的元组，估算下列操作需要多少次磁盘块读写。 （1）R上没有索引 ，select * from R; R中没有索引，由题意需要进行20000/40=500次磁盘块读写。 （2）R中A为主码，A有3层B+树索引 ，...
启发式查询树优化实例
欢迎访问,Viola的博客!
04-24 5066
树形查询的优化案例
24号信仰
09-26 1262
某数仓系统一条SQL执行了很长时间，要求分析原因 使用如下查询获取时间段内SQL语句的执行情况 select count(1),sql_id from dba_hist_active_sess_history where to_char(sample_time, 'yyyy-mm-dd hh24:mi:ss') &gt; '2018-07-13 12:03:57' ...
一次非常有意思的SQL优化经历:从30248.271s到0.001s
iteye_6274的博客
05-19 1246
场景 我用的数据库是mysql5.6，下面简单的介绍下场景 课程表 create table Course( c_id int PRIMARY KEY, name varchar(10) ) 数据100条 学生表: create table Student( id int PRIMARY KEY, name varchar(10) ) 数据70000条 ...
SQL优化经典案例----树形查询优化
mengxiangfeiyang的专栏
05-20 6227
SQL优化经典案例----树形查询优化    ---转载http://blog.sina.com.cn/s/blog_61cd89f60102efcb.html     对于树形查询的案例在我接触的系统中并不多见，只是在学习理论知识的时候知道有这么个东东查询，就好比一个公司的职员表，总经理下面有5位副总，副总下面有各有20个小弟，现在让查询总经理下面有多少小弟，这个时候就用到了树
一个完整的hive项目的实例包括数据
最新发布
06-02
一个完整的Hive项目实例包括数据如下： 数据准备： 假设我们需要对一个电商网站的用户行为数据进行分析，我们从该网站的数据库中导出用户行为数据，包括用户ID、商品ID、行为类型（浏览、收藏、购买、评价等）、...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值