遇到Sql一个问题求助
本帖最后由 艾雨 于 2022-5-26 22:39 编辑需求是循环每个月的每一天求出金额的和, 循环和别的代码就没放进来了, 下面的代码是循环内部的, 用到了公共表达式(with as)和临时表(#), 才工作没多久, 大佬勿喷, 能给小弟些指导最好, 目前遇到的问题是同一个sql每次算出来的金额和有时一样, 有时不一样, 同样的代码, 为什么会出现不同的结果, 排查了一天, 感觉是with as问题, 现在统统替换成临时表就没有问题了, 不知道是什么原因.
备注: 有些日期的金额和算出来正确的就不会变动, 发现只有错误的和一直在变动
with je as (
select .(Remark) as deal_no,TransactionDate, CurrencyCode, Amount_BaseCCY, RIGHT(Remark, 8) as acctg_id, DrCrFlag,
row_number() over (partition by .(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. IS NOT NULL
and a. <> '' -- 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. IS NOT NULL
and a. <> ''
), 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. IS NOT NULL
and a. <> ''
), 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. IS NOT NULL
and a. <> ''
), 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
每个月数据量有多少，这么多地方用like，你这条语句执行时间需要多长？
能在后台处理的数据，不要增加数据库的压力；
简单处理就是每天晚上12点后处理昨天的汇总数据，写入汇总表 看不懂 ，哈哈哈哈哈哈 lix909 发表于 2022-5-27 11:44
每个月数据量有多少，这么多地方用like，你这条语句执行时间需要多长？
能在后台处理的数据，不要增加数据 ...
主要给银行做报送, 我只负责驻场数据处理这一块, 后台都是公司的人写好了, 直接调用我写的存储执行, 这个报表每日体量是万条, 一个月大概7分钟:'(weeqw, 属于月报, 没办法只让我写存储过程 艾雨 发表于 2022-5-27 12:54
主要给银行做报送, 我只负责驻场数据处理这一块, 后台都是公司的人写好了, 直接调用我写的存储执行, 这个 ...
=，=新建一张汇总表，每天汇总前日的明细数据，这样减小数据库压力；
这是银行报送监管接口，那也得经过java后台啊，后台人怎么这么懒，一切应该以需求跟服务性能业务体验为主 一个月W条数据不多，但是需要处理7分钟就是很慢了，这个得看业务数据逻辑该怎么优化了
有like的地方需要做索引 日期关键字等
页:
[1]