行列均按段分组汇总

行列均按段分组汇总

【问题】

Please help. I have a report to do and Im hopless.

I have this table setup.

I need a grid or table withempirica_scoreas the rows   (DISTINCT) andmfin_scoreas the columns.   (DISTINCT)    e.g.

Thats the grid done.

But then I need to  divide the totalpaysoft_resultrecords per customer with the totalNAEDOrecords per customer wherestart_dateis DISTINCT

times * 100 to get percentage

This calculated value needs to be placed on the grid in the correctemprica_scoremfin_scorelocation on the grid.

Man I dont even know how or where to start

【回答】

这张交叉表的行组和列组都是分段区间,测度来自其他表。这种情况可以用集算器准备数据。思路是先分好分组区间,从两个外键表中聚合出计数值除用户数量,并填入对应的单元格。SPL 代码如下:

A
1 =[560,575,585,595,605,615,625,635,645,655,665]
2 =[39,66,91,116,137,155]
3 =connect(“test”)
4 >account_detail=A3.query(“select * from account_detail where empirica_score >= ? and mfin_score >= ?”,“560”,“39”)
5 >paysoft_result=A3.query(“select * from paysoft_result”)
6 >NAEDO=A3.query("select start_date,cust_code from naedo")
7 =account_detail.new(account_no,A1.pseg(empirica_score):row,A2.pseg(mfin_score):col,paysoft_result.count(custno==account_no):pcount,NAEDO.count(cust_code==account_no):ncount)
8 =A7.group(row,col;~.sum(pcount)/~.sum(ncount):rate)

A1,A2: 规定分组区间。

A3:链接 test 库。

A4:将 account_detail 表数据查出序表,然后选出满足分组区间最小值的记录,存为对象 account_detail。

A5:类似 A4 查 paysoft_result 表。

A6:类似 A4 查 NAEDO 表。

A7:计算出每个账户存在于的结果表的行列位置。按照账户号分别计算 paysoft_result 和 NAEDO 中出现次数。

A8:根据结果表行列位置分组,计算结果比率。

account_detail 对象:

imagepng

paysoft_result 对象:

imagepng

NAEDO 对象(distinct start_date):

imagepng

A7

imagepng

A8

imagepng