背景介绍
题外话:疫情过了以后,我们是最早上班的一批人,当时的街上还没有人,其实反而是安全的,现在街上的人多起来了,更加要注意安全了。作为IT从业人员,不能长期居家办公,有点儿遗憾,不过从另外一个角度来说,一个人独处,总是不如在单位里有人气。
本项目主要是为了应付监管机构对于数据统计的要求而实现的,由于监管对于数据的细节关心不多,主要是将数据用于统计分析。所以日常改动并不多,如果不是这次疫情,估计这个需求也不会提出来,本次是为了统计贷款用途,估计是希望对于贷款的投放更加精准。
整体思路
好在贷款用途我们一直都在做统计,这次的修改主要是针对数据输出的改动。
系统也做了前后端的分离,由于并不是对列的新增,所以在前端并不需要修改。
后端比较特殊,这张报表当时过于复杂,结果是用存储过程实现的,本文的重点就是介绍这个存储过程的实现。
框架分析
这个代码主要是每个月执行一次,统计各种分类标准,并且将数据固化下来。举例来说:
金额分类(大于30万小于50万)
期限分类(大于3个月小于6个月)
行业分类(各种行业,你懂得,监管也不一定懂)
每个分类后面就是一些统计值,主要是统计类金额,比如平均数,累计数,日均、月均、年均等等。
所以代码的大概框架是这样的:
声明各种变量
如刚才的各种统计类金额
创建一个临时表
用Declare Tabel命令,稍后存放中间数据,这个表的字段与数据库中最终存储数据的物理表是一致的。
创建分类临时表
存放各种分类标准,以便稍后与明细数据表进行关联,并向其中添加数据,例如本次新增的贷款用途,代码如下:
IF(@StatClass='主体分类')
BEGIN
INSERT INTO @ParentTypeTable(ParentType) SELECT '个人类'
INSERT INTO @ParentTypeTable(ParentType) SELECT '个人涉农类'
INSERT INTO @ParentTypeTable(ParentType) SELECT '企业类'
INSERT INTO @ParentTypeTable(ParentType) SELECT '企业涉农类'
END
IF(@StatClass='贷款用途')
BEGIN
INSERT INTO @ParentTypeTable(ParentType,DictionaryCode)
SELECT DictionaryName,DictionaryCode FROM dbo.SYSDictionary WHERE ParentId=68
END
代码可以看出来,一共有两种方式插入数据,第一种是监管临时要求的,只能直接出入中文,另一种是系统本身就存在的,可以直接从样本表里读取。
数据特殊处理
对于一些特殊的统计,都放在这里,以便在最终生成数据之前,全部处理完毕。
循环插入Key数据
根据上面的步骤准备,将明细数据表里的数据进行分类关联并插入临时表,比如类似下面的代码:
ELSE IF @StatIndex='企业类'
INSERT INTO @RepaymentAutoTable( RepaymentAutoId,ContractId )
SELECT MFRepaymentAuto.Id,MFRepaymentAuto.ContractId FROM dbo.MFRepaymentAuto
LEFT JOIN dbo.CRMCustomer ON CRMCustomer.Id=MFRepaymentAuto.CustomerId
WHERE AmountType=5 AND CRMCustomer.CustomerType=0
ELSE IF (@StatClass='贷款用途')
INSERT INTO @RepaymentAutoTable( RepaymentAutoId,ContractId )
SELECT MFRepaymentAuto.Id,MFRepaymentAuto.ContractId FROM dbo.MFRepaymentAuto
LEFT JOIN dbo.MFContract ON MFRepaymentAuto.ContractId=MFContract.Id
WHERE AmountType=5 AND MFContract.LoanReason=@DictionaryCode
由上述代码可以看出,在插入明细数据的时候,也分为了两种情况,一种是监管与系统不一致分类的情况,只能靠代码硬处理实现;另外一种,就是可以直接从系统中获取,从而自动填充数据。
循环插入明细数据
为了加快效率,此处将最终数据的获取分为两步,刚才是指插入key,现在就可以把key对应的实际数据都取出来了,这样做,有两个好处:
加快表关联的效率
将统计数据的逻辑与key关联逻辑分开,便于今后维护
不过此处有个问题,用in来进行了数据匹配,降低了数据的select效率,是将来仍然可以改进的地方,好在每次匹配的分类数据在临时表里,一般不会超过10条数据,性能还可以接受。代码如下:
SELECT @AveMonth=ISNULL(SUM(DATEDIFF(DAY,StartDate,EndDate)*SurplusAmount)/ISNULL(SUM(SurplusAmount),1),0)
FROM dbo.MFRepaymentAuto
WHERE CompanyId =@CompanyId AND AmountType=5 AND SurplusAmount>0 AND StatusCode='0'
AND Id IN (SELECT RepaymentAutoId FROM @RepaymentAutoTable)
无法循环的数据处理
最后一步,就是对于刚才统计分类里,不能直接写入循环的数据处理,例如客户经理分类的统计,监管机构肯定是不需要的,但是作为公司内部管理的需求是存在的,为此,还特意创建了一个客户经理的临时表,代码如下:
DECLARE @strCompany table(CompanyId INT)
小结
随着年龄增长,之前的很多代码已经不再存在于记忆中,好在有CSDN可以记忆和分享,希望能给自己有所促进。
后记
今天发现这段代码的修改还有问题,原来由于分公司的缘故,除了存储过程,在service端还有一段代码调用此存储过程。由于没有修改这段代码,结果造成在月底执行汇总的时候,由于分公司没有数据,结果汇总的虚拟总公司的数据出现了null值,无法插入表,系统报错。
修改了这段代码以后,问题解决,为了将来能够回溯,将代码罗列如下:
List<string> strList = new List<string>() { "金额分类", .......,"贷款用途"};
foreach (string str in strList)
{
base.RunProcedure(//后面的代码省略
究其原因,是因为当时偷懒,没有按照实际的场景进行模拟测试造成的,记录一下,时刻提醒自己做事不能偷懒。