Oracle-SQL-按月统计自助终端交易量

SQL实现的目标：

基本情况							现金交易情况				转账情况		转账交易情况(明细）								其它业务情况			交易量汇总	日均交易量	交易金额	绩效情况(万元）
支行名	支行号	所属网点	网点号	管理员帐户	管理员	终端编号	取款笔数	取款金额	存款笔数	存款金额	转账笔数	转账金额	卡卡笔数	卡卡金额	卡折笔数	卡折金额	折卡笔数	折卡金额	折折笔数	折折金额	代缴费笔数	代缴费金额	查询笔数				存款余额（月日均）	存款余额（月日均比上月）
巴南支行	10	101302	东泉分理处	402230080416813378	骆涌	23003326	127	151950	51	43300	7	13562	0	0	0	0	3	3080	4	10482	0	0	253	438	16	208812	21.02	0.19
巴南支行	10	103201	跳石分理处	402230080344302189	万敏	23001874	357	294062	80	327320	14	100510	0	0	0	0	4	27000	10	73510	0	0	754	1205	43	721892	295.22	9.01
巴南支行	10	100401	木洞分理处	402230080350688018	唐自利	23001620	674	512739.4	295	534862	25	206080	0	0	3	18500	4	30180	18	157400	0	0	1541	2535	91	1253681	228.96	0.48

 

规则，  每天每种交易最多统计3笔

完成按月统计任务

 

用到的知识点总结：

1） case when的使用；

case  when count(transQK.tran_amt) > 3 then 3  else count(transQK.tran_amt) end  count
case  when dayQK.count is null then 0 else dayQK.count end

2）left join的使用；   left join 中可以写where 的条件

--外连接取款
left join 
    ( 
    select
        case  when sum(dayQKIn.count) is null then 0 else sum(dayQKIn.count)  end count,
        case  when sum(dayQKIn.money) is null then 0 else sum(dayQKIn.money)  end money,
        term_id_in
    from
    (
        select 
            case  when count(transQK.tran_amt) > 3 then 3  else count(transQK.tran_amt) end  count,
            case  when sum(transQK.tran_amt) is null then 0 else sum(transQK.tran_amt)  end  money,
            to_char(to_date(transQK.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
            term_id_in
        from 
            BIZ_OPER_TRANS transQK
        where  
        -- 取款 便民取款
            (transQK.P_TRANS_CODE='1011101' or transQK.P_TRANS_CODE='1011231')
            and     transQK.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')
        group by 
            term_id_in,P_TRANS_CODE,to_char(to_date(transQK.p_req_date, 'yyyyMMdd'), 'dd')
        ) dayQKIn
         group by dayQKIn.term_id_in
    ) dayQK
    on dayQK.term_id_in=term.TERMINAL_ID 

 

 

3） 时间函数 表示  上个月第一天  、 上个月最后一天   

transCX.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')

 

4）四舍五入   月笔数/天数

round(( case  when dayQK.count is null then 0 else dayQK.count end + 
    case  when dayCK.count is null then 0 else dayCK.count end +
    case  when dayZZ.count is null then 0 else dayZZ.count end + 
    case  when dayDJ.count is null then 0 else dayDJ.count end + 
    case  when dayCX.count is null then 0 else dayCX.count end )
    /(1+to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')-to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd')),2)

 

 

5)  最终的SQL

 

-----------------------------------终极版终结版-------------------------------------------------------
-- 每月每种交易最多统计3笔
-- T05 便民终端
select 
    parent.inst_code 支行号,
    parent.inst_name 支行名,
    inst.inst_code 网点号,
    inst.inst_name 网点名,
    term.admin_Acct 管理员账户 ,
    term.admin_Name 管理员, 
    term.terminal_id 终端号,
    case  when dayQK.count is null then 0 else dayQK.count end   取款笔数,
    case  when dayQK.money is null then 0 else dayQK.money end   取款金额,
    case  when dayCK.count is null then 0 else dayCK.count end   存款笔数,
    case  when dayCK.money is null then 0 else dayCK.money end   存款金额,
    case  when dayZZ.count is null then 0 else dayZZ.count end   转账笔数,
    case  when dayZZ.money is null then 0 else dayZZ.money end   转账金额,
    case  when dayDJ.count is null then 0 else dayDJ.count end   代缴笔数,
    case  when dayDJ.money is null then 0 else dayDJ.money end   代缴金额,
    case  when dayCX.count is null then 0 else dayCX.count end   查询笔数,
    -- 汇总
    case  when dayQK.count is null then 0 else dayQK.count end + 
    case  when dayCK.count is null then 0 else dayCK.count end +
    case  when dayZZ.count is null then 0 else dayZZ.count end + 
    case  when dayDJ.count is null then 0 else dayDJ.count end + 
    case  when dayCX.count is null then 0 else dayCX.count end 总笔数,
    -- 这里用了四舍五入
    round(( case  when dayQK.count is null then 0 else dayQK.count end + 
    case  when dayCK.count is null then 0 else dayCK.count end +
    case  when dayZZ.count is null then 0 else dayZZ.count end + 
    case  when dayDJ.count is null then 0 else dayDJ.count end + 
    case  when dayCX.count is null then 0 else dayCX.count end )
    /(1+to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')-to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd')),2) 日均笔数,
    --
    case  when dayQK.money is null then 0 else dayQK.money end + 
    case  when dayCK.money is null then 0 else dayCK.money end +
    case  when dayZZ.money is null then 0 else dayZZ.money end + 
    case  when dayDJ.money is null then 0 else dayDJ.money end  总金额
--
from 
    ops_device_info device ,ops_institution inst,ops_institution parent ,OPS_TERMINAL_INFO term 
--外连接取款
left join 
    ( 
    select
        case  when sum(dayQKIn.count) is null then 0 else sum(dayQKIn.count)  end count,
        case  when sum(dayQKIn.money) is null then 0 else sum(dayQKIn.money)  end money,
        term_id_in
    from
    (
        select 
            case  when count(transQK.tran_amt) > 3 then 3  else count(transQK.tran_amt) end  count,
            case  when sum(transQK.tran_amt) is null then 0 else sum(transQK.tran_amt)  end  money,
            to_char(to_date(transQK.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
            term_id_in
        from 
            BIZ_OPER_TRANS transQK
        where  
        -- 取款 便民取款
            (transQK.P_TRANS_CODE='1011101' or transQK.P_TRANS_CODE='1011231')
            and     transQK.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')
        group by 
            term_id_in,P_TRANS_CODE,to_char(to_date(transQK.p_req_date, 'yyyyMMdd'), 'dd')
        ) dayQKIn
         group by dayQKIn.term_id_in
    ) dayQK
    on dayQK.term_id_in=term.TERMINAL_ID 
--外连接存款
left join 
(
    select
        case  when sum(dayCKIn.count) is null then 0 else sum(dayCKIn.count)  end count,
        case  when sum(dayCKIn.money) is null then 0 else sum(dayCKIn.money)  end money,
        term_id_in
    from
    (
        select 
            case  when count(transCK.tran_amt) > 3 then 3  else count(transCK.tran_amt) end  count,
            case  when sum(transCK.tran_amt) is null then 0 else sum(transCK.tran_amt)  end  money,
            to_char(to_date(transCK.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
            term_id_in
        from 
            BIZ_OPER_TRANS transCK
        where  
            (transCK.P_TRANS_CODE='1011103' or transCK.P_TRANS_CODE='1011232')
        and    transCK.p_req_date between
            to_char(add_months(last_day(sysdate) + 1, -2), 'yyyyMMdd') and
            to_char(add_months(last_day(sysdate), -1), 'yyyyMMdd')
        group by 
            term_id_in,P_TRANS_CODE,to_char(to_date(transCK.p_req_date, 'yyyyMMdd'), 'dd')
        ) dayCKIn
        group by dayCKIn.term_id_in
    ) dayCK
    on dayCK.term_id_in=term.TERMINAL_ID 
--外连接转账
left join 
(
    select
        case  when sum(dayZZIn.count) is null then 0 else sum(dayZZIn.count)  end count,
        case  when sum(dayZZIn.money) is null then 0 else sum(dayZZIn.money)  end money,
        term_id_in
    from
    (
        select 
            case  when count(transZZ.tran_amt) > 3 then 3  else count(transZZ.tran_amt) end  count,
            case  when sum(transZZ.tran_amt) is null then 0 else sum(transZZ.tran_amt)  end  money,
            to_char(to_date(transZZ.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
            term_id_in
        from 
            BIZ_OPER_TRANS transZZ
        where  
            ( transZZ.P_TRANS_CODE='1011105' or transZZ.P_TRANS_CODE='1011203' or transZZ.P_TRANS_CODE='1011206' or
            transZZ.P_TRANS_CODE='1011233' or transZZ.P_TRANS_CODE='1011107' )
        and    transZZ.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and         to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')
        group by 
            term_id_in,P_TRANS_CODE,to_char(to_date(transZZ.p_req_date, 'yyyyMMdd'), 'dd')
        ) dayZZIn
    group by dayZZIn.term_id_in
    ) dayZZ
    on dayZZ.term_id_in=term.TERMINAL_ID 
--代缴费
left join 
(
    select
        case  when sum(dayDJIn.count) is null then 0 else sum(dayDJIn.count)  end count,
        case  when sum(dayDJIn.money) is null then 0 else sum(dayDJIn.money)  end money,
        term_id_in
    from
    (
        select 
        case  when count(transDJ.tran_amt) > 3 then 3  else count(transDJ.tran_amt) end  count,
        case  when sum(transDJ.tran_amt) is null then 0 else sum(transDJ.tran_amt)  end  money,
        to_char(to_date(transDJ.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
        term_id_in
        from 
        BIZ_OPER_TRANS transDJ
    where  
        ( transDJ.P_TRANS_CODE='1011105' or transDJ.P_TRANS_CODE='1011203' or transDJ.P_TRANS_CODE='1011206' or
        transDJ.P_TRANS_CODE='1011233' or transDJ.P_TRANS_CODE='1011107' )
    and    transDJ.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')
         group by 
        term_id_in,P_TRANS_CODE,to_char(to_date(transDJ.p_req_date, 'yyyyMMdd'), 'dd')
            ) dayDJIn
    group by dayDJIn.term_id_in
    ) dayDJ
    on dayDJ.term_id_in=term.TERMINAL_ID 
--查询
left join 
(
    select
        case  when sum(dayCXIn.count) is null then 0 else sum(dayCXIn.count)  end count,
        term_id_in
    from
    (
        select 
        case  when count(transCX.tran_amt) > 3 then 3  else count(transCX.tran_amt) end  count,
        to_char(to_date(transCX.p_req_date, 'yyyyMMdd'), 'dd') p_req_date,
        term_id_in
        from 
        BIZ_OPER_TRANS transCX
    where  
        ( transCX.P_TRANS_CODE='1011105' or transCX.P_TRANS_CODE='1011203' or transCX.P_TRANS_CODE='1011206' or
        transCX.P_TRANS_CODE='1011233' or transCX.P_TRANS_CODE='1011107' ) 
    and transCX.p_req_date between to_char(add_months(last_day(sysdate)+1,-2),'yyyyMMdd') and to_char(add_months(last_day(sysdate),-1),'yyyyMMdd')
         group by 
        term_id_in,P_TRANS_CODE,to_char(to_date(transCX.p_req_date, 'yyyyMMdd'), 'dd')
            ) dayCXIn
    group by dayCXIn.term_id_in
    ) dayCX
    on dayCX.term_id_in=term.TERMINAL_ID 
--
where 1=1 
    and device.DEVICE_TYPE='T05' 
    and term.TERMINAL_ID=device.TERMINAL_ID 
    and term.inst_id=inst.inst_id
    and inst.PARENT_INST_ID=parent.inst_id 
    --
    -- and term.admin_name is not null  
     and inst.inst_level=3 
;

 

这个sql写的不好，太臃肿，  可以用交易码分组查询（然后用decode函数处理）