行列均按段分组汇总
行列均按段分组汇总
【问题】
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 对象：
paysoft_result 对象：
NAEDO 对象（distinct start_date）：
A7
A8
