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

最新推荐文章于 2023-04-28 11:03:46 发布
最新推荐文章于 2023-04-28 11:03:46 发布
阅读量291 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/yangw/p/4423018.html
版权

SQL实现的目标：

基本情况现金交易情况转账情况转账交易情况(明细）其它业务情况交易量汇总日均交易量交易金额绩效情况(万元）
支行名支行号所属网点网点号管理员帐户管理员终端编号取款笔数取款金额存款笔数存款金额转账笔数转账金额卡卡笔数卡卡金额卡折笔数卡折金额折卡笔数折卡金额折折笔数折折金额代缴费笔数代缴费金额查询笔数存款余额（月日均）存款余额（月日均比上月）
巴南支行                10101302东泉分理处    402230080416813378骆涌2300332612715195051433007135620000330804104820025343816 208812 21.020.19
巴南支行                10103201跳石分理处    402230080344302189万敏2300187435729406280327320141005100000427000107351000754120543 721892 295.229.01
巴南支行                10100401木洞分理处    402230080350688018唐自利23001620674512739.4295534862252060800031850043018018157400001541253591 1253681 228.960.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函数处理）

转载于:https://www.cnblogs.com/yangw/p/4423018.html

weixin_30394333
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
交易统计.sql
05-07
交易统计.sql
oracle查询统计语句,orale查询每年、每月、每日统计量的sql语句-Oracle
weixin_36021461的博客
04-12 1125
每年 select to_char(createtime, ‘YYYY’) 年, count(*) from table group by to_char(createtime, ‘YYYY’); 每季度 select to_char(createtime, ‘q’) 年, count(*) from table group by to_char(createtime, ‘q’); 每月 sele...
oracle的按月统计sql（一个数据库面试题）
01-10
oracle的按月统计sql..............................
oracle取平均每日交易量,收集每日对象数据量情况
weixin_30428981的博客
04-14 591
文章版权所有Jusin Hao(luckyfriends)，支持原创，转载请注明。--创建记录数据量的表create table cux_datasize(tb_lb_size number,idx_size number,lbidx_size number,allseg_size number,query_date DATE)tablespace PSDEFAULT;---创建收集的存储过程CR...
sql 统计类语句
stevenbill的博客
03-17 494
1、统计每月的成交量，按照逆序排序(2013年以来的每月销量，oracle实现) select to_char(t.payment_time,'yyyy-MM'),count(t.id) as 笔数,sum(t.payment_amount) as 月总金额 from business_journal t where t.payment_type='11' and t.err_code='00...
flink-sql-connector-oracle-cdc-2.5-SNAPSHOT.jar
08-15
flink-sql-connector-oracle-cdc 2.5-SNAPSHOT
flink-sql-connector-oracle-cdc-2.3.0.jar
08-23
flinkcdc oracle 2.3.0
Oracle-SQL语句大全.pdf
最新发布
06-14
sql语句 Oracle_SQL语句大全.pdf
oracle-sql.rar_oracle
09-14
本压缩包“oracle-sql.rar_oracle”显然针对的是Oracle SQL的学习资源，特别是对于初学者而言，它提供了丰富的实用技巧和经典查询示例。 在“oracle-sql.rar”中，我们有两个文件：“oracle常用经典sql查询.doc”和...
oracle PL-SQL 基础
03-06
根据提供的文件信息，我们可以深入探讨Oracle PL/SQL的基础知识点，包括其历史背景、优势、基本结构、以及变量的定义和使用等内容。 ### PL/SQL的历史 PL/SQL（Procedural Language for SQL）是一种专门为Oracle...
oracle统计日均,oracle效率优化实战——计算3个月的平均值
weixin_39692557的博客
04-03 1273
项目需求：根据会员在三个月里指标的平均值(有20个指标，在此以一个指标为例)，按照会员所在的县级市(city_id)和行业(category_id)分别进行排名，并依据排名等级和各个指标的权重分值计算分数。分数计算规则：过去3个月日平均值数据量最高的或者数值最高的排名为10≤该客户在该县级市该行业的排名/该县级市该行业总客户数＜20％：满分20％≤该客户在该县级市该行业的排名/该县级市该行业总客户...
orale 查询每年、每月、每日统计量的sql语句
qq345425170的专栏
05-12 798
每年 select to_char(createtime, 'YYYY') 年, count(*) from table  group by to_char(createtime, 'YYYY'); 每月 select to_char(createtime, 'YYYY') 年, to_char(createtime, 'mm') 月, count(*) from table  gr
Oracle 统计每10分钟的数据量
SuperWhitePoplar的博客
06-27 7834
select to_char(begin_time, 'yyyy-mm-dd hh24'), trunc(to_char(begin_time, 'mi')/10), count(1), sum(log_num)from tb_e2e_trace_0616group by to_char(begin_time, 'yyyy-mm-dd hh24'), trunc(to_char(begin_tim...
oracle查询5分钟K线数据
gary16的专栏
08-16 1732
最近在将200W左右的期货实时数据转换成5分钟数据
oracle 按每天,每周,每月,每季度,每年查询统计数据
chengwa9834的博客
11-19 752
//按天统计 select count(dataid) as 每天操作数量, sum() from tablename group by trunc(createtime, 'DD')) //按自然周统计 select to_char(date,'iw'),sum(...
Oracle 统计当月每天/每小时/每分钟数据处理量
dragon4917的博客
02-18 5114
有一批数据需要完成数据匹配，匹配完成后会在结果表的 MatchDate 字段存储匹配时间。匹配的数据无法在1天内完成，现在需要统计每小时匹配数据量。 SELECT TO_CHAR(TR.MatchDate, 'mm-dd hh24') AS MD, COUNT(*) AS HOUR_TOTAL FROM result_table_name TR -- result_table_name 所需查询数据的表名 WHERE TO_CHAR(TRUNC(TR.MatchDate), 'yyyy-mm') --
sql按月统计数据和按月统计累计数量
热门推荐
qq_41250747的博客
07-23 2万+
sql按月统计数据和按月统计累计数量 1.简单的，按月统计数量 SELECT CREATE_DATE, DATE_FORMAT(CREATE_DATE, '%Y-%m') AS month, COUNT(*) AS sum FROM pt_user GROUP BY month; 2.按月累加统计数据 SELECT a.month, SUM(b.total) AS total FROM ( SELECT DATE_FORMAT(CREATE_DATE, '%Y-%m') AS month, SUM
Oracle中按天、周、月、季、年统计数据
.
04-28 8191
Oracle中按天、自然周、月、季、年统计数据、包含日期函数IW和WW的区别
Mysql统计平台每月的交易金额和交易笔数
hit、run
04-26 3654
SELECT DATE_FORMAT(t.`payDateTime`, "%Y-%m") month, COUNT(1) num, IFNULL(SUM(payMoney), 0) sumAmount FROM trade t <where> <if test=...
ORACLE-SQL性能优化: 选用合适的优化器，提高查询效率
ORACLE-SQL性能优化是指针对Oracle数据库中的SQL语句进行优化，以提高数据库性能和执行效率的一系列措施和方法。本文将详细介绍ORACLE-SQL性能优化的内容，包括选用适合的ORACLE优化器、优化SQL查询语句、优化索引和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值