遇到Sql一个问题求助

艾雨

需求是循环每个月的每一天求出金额的和, 循环和别的代码就没放进来了, 下面的代码是循环内部的, 用到了公共表达式(with as)和临时表(#), 才工作没多久, 大佬勿喷, 能给小弟些指导最好, 目前遇到的问题是同一个sql每次算出来的金额和有时一样, 有时不一样, 同样的代码, 为什么会出现不同的结果, 排查了一天, 感觉是with as问题, 现在统统替换成临时表就没有问题了, 不知道是什么原因.
备注: 有些日期的金额和算出来正确的就不会变动, 发现只有错误的和一直在变动

[SQL] 纯文本查看 复制代码

        with je as (
                select [dbo].[GET_NBFHZMX_TBO_DEAL_NO](Remark) as deal_no,TransactionDate, CurrencyCode, Amount_BaseCCY, RIGHT(Remark, 8) as acctg_id, DrCrFlag,
                        row_number() over (partition by [dbo].[GET_NBFHZMX_TBO_DEAL_NO](Remark) order by RIGHT(Remark, 8) asc) as rn
                from ODS_DT_JournalEntry
                where AccountCode = '0065920' and Remark like 'DEALIDFO%' 
                        and TransactionDate = '20220208'
        ), je_num as (
                select je.*, a.num from je
                        left join (
                                select deal_no, count(deal_no) as num from je
                                group by deal_no
                        )a on a.deal_no = je.deal_no
        )

        select * into #je_num from je_num; 
        select * into #je_2_temp from #je_num where num = 2; -- 2条 
        select * into #je_4_temp from #je_num where num = 4; -- 4条 

        -- 2条cd组 start
        with je_2_group_1 as (
                select *,
                        row_number() over (partition by deal_no, DrCrFlag order by deal_no asc) as flag
                from #je_2_temp -- 68 68 68
        ), swap_group as (
                select a.* from je_2_group_1 a 
                        left join je_2_group_1 b on b.deal_no = a.deal_no and b.DrCrFlag <> a.DrCrFlag and b.flag = a.flag 
                where b.deal_no is not null
        )

        select * into #swap_group_2 from swap_group;
        select deal_no, TransactionDate, CurrencyCode, Amount_BaseCCY, acctg_id, DrCrFlag into #je_2_c from #swap_group_2 where DrCrFlag = 'C';
        select deal_no, TransactionDate, CurrencyCode, Amount_BaseCCY, acctg_id, DrCrFlag into #je_2_d from #swap_group_2 where DrCrFlag = 'D';


        with swap_red as (
                select * from (select * from ODS_DT_TBO_Deals_outstanding union select * from ODS_DT_TBO_Deals_volume) a where a.Instrument = 'FXSWAP' 
                        and a.Folders NOT LIKE 'BJ%' AND a.Folders NOT LIKE 'KM%'
                        and a.Folders NOT LIKE 'SZ%'AND a.[Cpty Class] IS NOT NULL
                        and a.[Cpty Class] <> '' -- 6728 6728 6728
        ), swap_blue as (
                select c.deal_no, c.TransactionDate, c.CurrencyCode, (c.Amount_BaseCCY - d.Amount_BaseCCY) as amount from #je_2_c c
                        left join #je_2_d d on d.deal_no = c.deal_no
        ), swap_2 as (
                select * from swap_blue a
                        left join swap_red b on b.DealNumber = a.deal_no and a.TransactionDate <> b.MaturityDate
                where b.DealNumber is not null
        )

        select * into #swap_2 from swap_2;
        -- 2条cd组 end

        -- 4条cd组 start
        select deal_no, TransactionDate, CurrencyCode, Amount_BaseCCY, acctg_id, DrCrFlag into #je_4_4 from #je_4_temp where rn = '4';
        select deal_no, TransactionDate, CurrencyCode, Amount_BaseCCY, acctg_id, DrCrFlag into #je_4_3 from #je_4_temp where rn = '3';

        with je_4_group_1 as (
                select *,
                        row_number() over (partition by deal_no, DrCrFlag order by deal_no asc) as flag
                from (select * from #je_4_4 union select * from #je_4_3)a
        ), swap_group as (
                select a.* from je_4_group_1 a 
                        left join je_4_group_1 b on b.deal_no = a.deal_no and b.DrCrFlag <> a.DrCrFlag and b.flag = a.flag 
                where b.deal_no is not null
        )
        select * into #swap_group_4 from swap_group; 
        select * into #je_4_c from #swap_group_4 where DrCrFlag = 'C'; 
        select * into #je_4_d from #swap_group_4 where DrCrFlag = 'D';

        with swap_red as (
                select * from (select * from ODS_DT_TBO_Deals_outstanding union select * from ODS_DT_TBO_Deals_volume) a where a.Instrument = 'FXSWAP' 
                        and a.Folders NOT LIKE 'BJ%' AND a.Folders NOT LIKE 'KM%'
                        and a.Folders NOT LIKE 'SZ%'AND a.[Cpty Class] IS NOT NULL
                        and a.[Cpty Class] <> ''
        ), swap_blue as (
                select c.deal_no, c.TransactionDate, c.CurrencyCode, (c.Amount_BaseCCY - d.Amount_BaseCCY) as amount from #je_4_c c
                        left join #je_4_d d on d.deal_no = c.deal_no
        ), swap_4 as (
                select * from swap_blue a
                        left join swap_red b on b.DealNumber = a.deal_no
        )

        select * into #swap_4 from swap_4;
        -- 4条cd组 end

        -- 2条 同cd组 start
        with je_2_group as (
                select *,
                        row_number() over (partition by deal_no, DrCrFlag order by deal_no asc) as flag
                from #je_2_temp 
        ), swap_red as (
                select * from (select * from ODS_DT_TBO_Deals_outstanding union select * from ODS_DT_TBO_Deals_volume) a where a.Instrument = 'FXSWAP' 
                        and a.Folders NOT LIKE 'BJ%' AND a.Folders NOT LIKE 'KM%'
                        and a.Folders NOT LIKE 'SZ%'AND a.[Cpty Class] IS NOT NULL
                        and a.[Cpty Class] <> ''
        ), swap_blue as (
                select a.deal_no, a.TransactionDate, a.CurrencyCode, 
                        case 
                                when a.DrCrFlag = 'D' and b.DrCrFlag = 'D' then -abs(a.Amount_BaseCCY + b.Amount_BaseCCY) 
                                when a.DrCrFlag = 'C' and b.DrCrFlag = 'C' then abs(a.Amount_BaseCCY + b.Amount_BaseCCY) 
                        end as amount
                from je_2_group a
                        left join je_2_group b on b.deal_no = a.deal_no and b.DrCrFlag = a.DrCrFlag and b.flag = 1
                where a.flag = 2
        ), swap_2_cd as (
                select * from swap_blue a
                        left join swap_red b on b.DealNumber = a.deal_no and a.TransactionDate <> b.MaturityDate
                where b.DealNumber is not null
        )

        select * into #swap_2_cd from swap_2_cd;
        -- 2条 同cd组 end

        -- 4条 同cd组 start
        with je_4_group as (
                select *,
                        row_number() over (partition by deal_no, DrCrFlag order by deal_no asc) as flag
                from (select * from #je_4_4 union select * from #je_4_3)a 
        ), swap_red as (
                select * from (select * from ODS_DT_TBO_Deals_outstanding union select * from ODS_DT_TBO_Deals_volume) a where a.Instrument = 'FXSWAP' 
                        and a.Folders NOT LIKE 'BJ%' AND a.Folders NOT LIKE 'KM%'
                        and a.Folders NOT LIKE 'SZ%'AND a.[Cpty Class] IS NOT NULL
                        and a.[Cpty Class] <> ''
        ), swap_blue as (
                select a.deal_no, a.TransactionDate, a.CurrencyCode, 
                        case 
                                when a.DrCrFlag = 'D' and b.DrCrFlag = 'D' then -abs(a.Amount_BaseCCY + b.Amount_BaseCCY) 
                                when a.DrCrFlag = 'C' and b.DrCrFlag = 'C' then abs(a.Amount_BaseCCY + b.Amount_BaseCCY) 
                        end as amount
                from je_4_group a
                        left join je_4_group b on b.deal_no = a.deal_no and b.DrCrFlag = a.DrCrFlag and b.flag = 1
                where a.flag = 2
        ), swap_4_cd as (
                select * from swap_blue a
                        left join swap_red b on b.DealNumber = a.deal_no and a.TransactionDate <> b.MaturityDate
                where b.DealNumber is not null
        )

        select * into #swap_4_cd from swap_4_cd;
        -- 4条 同cd组 end

        -- 汇总
        if OBJECT_ID('Tempdb..#swap') is not null
                insert into #swap select * from(select * from #swap_2 union select * from #swap_4 union select * from #swap_2_cd union select * from #swap_4_cd)a 
        else
                select * into #swap from (select * from #swap_2 union select * from #swap_4 union select * from #swap_2_cd union select * from #swap_4_cd) a 

        -- close 临时表
        drop table #je_num;
        drop table #je_2_temp;drop table #je_4_temp;
        drop table #je_2_c;drop table #je_2_d;drop table #swap_2;
        drop table #je_4_4;drop table #je_4_3;drop table #je_4_c;drop table #je_4_d;drop table #swap_4;
        drop table #swap_group_2;drop table #swap_group_4;
        drop table #swap_2_cd;drop table #swap_4_cd;

-- 清洗数据 end

-- 校验
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220207' group by TransactionDate union -- y
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220208' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220209' group by TransactionDate union -- y
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220210' group by TransactionDate union -- y
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220211' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220214' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220215' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220216' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220217' group by TransactionDate union -- y
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220218' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220221' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220222' group by TransactionDate union -- y
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220223' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220224' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220225' group by TransactionDate union -- n
select TransactionDate, sum(amount) from #swap where TransactionDate = '20220228' group by TransactionDate                 -- y

drop table #swap; --96585889.30 --96585889.30 --96604629.06

lix909

每个月数据量有多少，这么多地方用like，你这条语句执行时间需要多长？
能在后台处理的数据，不要增加数据库的压力；
简单处理就是每天晚上12点后处理昨天的汇总数据，写入汇总表

renxiaofeixia

看不懂 ，哈哈哈哈哈哈

艾雨

lix909 发表于 2022-5-27 11:44
每个月数据量有多少，这么多地方用like，你这条语句执行时间需要多长？
能在后台处理的数据，不要增加数据 ...

主要给银行做报送, 我只负责驻场数据处理这一块, 后台都是公司的人写好了, 直接调用我写的存储执行, 这个报表每日体量是万条, 一个月大概7分钟, 属于月报, 没办法只让我写存储过程

lix909

艾雨 发表于 2022-5-27 12:54
主要给银行做报送, 我只负责驻场数据处理这一块, 后台都是公司的人写好了, 直接调用我写的存储执行, 这个 ...

=，=新建一张汇总表，每天汇总前日的明细数据，这样减小数据库压力；
这是银行报送监管接口，那也得经过java后台啊，后台人怎么这么懒，一切应该以需求跟服务性能业务体验为主

lix909

一个月W条数据不多，但是需要处理7分钟就是很慢了，这个得看业务数据逻辑该怎么优化了
有like的地方需要做索引 日期关键字等