按指定基准对齐的分组运算

于 2020-11-04 16:53:30 发布
阅读量189 收藏
点赞数
分类专栏： 集算器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/raqsoft/article/details/109493943
版权

【摘要】
    在分组时经常会要求结果集必须按基准集合的次序出现，这种对齐分组在日常统计中是很常见的。我们还能把对齐分组推广成更一般的枚举分组。如何简便快捷的处理对齐分组，这里为你全程解析，并提供 esProc 示例代码。按指定基准对齐的分组运算

 

对照一个基准集合，将待分组集合成员的某个字段或表达式与基准集合成员比较，相同者则分到同一个组中，最后拆分出来的组数和基准集合成员数是相同的。这种分组我们称为对齐分组。对齐分组可能会有空组，也可能有成员未分配到任何一个组中。

1.     普通对齐分组

1.1    每组保留最多一个匹配成员

按某字段的指定顺序，将表中所有记录分组并汇总求和。

【例 1】 根据相互关联的课程表和选课表，按课程表顺序查询有哪些课程无人选择：

..

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from   SelectCouse")/查询选课表
3=A1.query("select * from Course")/查询课程表
4=A2.align(A3:ID,CourseID)/使用函数 A.align()，将选课表按照课程表的 ID 对齐，每组选择一个匹配成员
5=A3(A4.pos@a(null))/在课程表中选出没有选择（值为 null）的课程信息

    A5的执行结果如下：

IDNAMETeacherID
1Environmental   protection and sustainable development5
10Music   appreciation18

 

1.2    每组保留所有匹配成员

按某字段的指定顺序，将表中所有记录分组并汇总求和。

【例 2】 根据相互关联的员工表和部门表，按部门表中的部门顺序统计各部门人数：

..

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from   EMPLOYEE")/查询员工表
3=A1.query("select * from   DEPARTMENT")/查询部门表
4=A2.align@a(A3:ID, DEPARTMENT)/使用函数 A.align@a()，将员工表按部门对齐分组，@a 选项每组返回所有匹配成员
5=A4.new(DEPT, ~.count():COUNT)/统计各部门的人数

    A5的执行结果如下：

DEPTCOUNT
Admin4
R&D29
Sales187
……

 

1.3    不匹配记录放到新组

按某字段的指定顺序，将表中所有记录分组，不匹配记录放到新组。

【例 3】 根据员工薪资表，统计 [California, Texas, New York, Florida] 的平均工资，未指定的州作为“Other”统计。员工薪资表部分数据如下：

IDNAMESTATESALARY
1RebeccaCalifornia7000
2AshleyNew   York11000
3RachelNew   Mexico9000
4EmilyTexas7000
5AshleyTexas16000
…………

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from   EMPLOYEE")/查询雇员表
3[California,Texas,New York,Florida]/创建地区序列
4=A2.align@an(A3,STATE)/使用函数 A.align@an()，将雇员表按地区对位分组，@a 选项每组返回所有匹配成员，@n 选项不匹配成员存放到新组。
5=A4.new(if (#>A3.len(),"Other",STATE):STATE,~.avg(SALARY):AvgSalary)/统计每组的平均工资，产生新序表。最后一组的地区更名为 Other，否则为当前组第一条记录的地区。

    A5的执行结果如下：

STATESALARY
California7700.0
Texas7592.59
New   York7677.77
Florida7145.16
Other7308.1

 

2.     序号对齐分组

序号对齐分组，是指按照指定的序号进行分组，序号相同的成员分到同一组。

2.1    每组保留最多一个匹配成员

在相互关联的两个表中，查找未被引用的记录。

【例 4】 根据相互关联的销售表和客户表，顺序列出 2014 年没有销售记录的客户：

..

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from Sales")/查询销售表
3=A1.query("select * from   Customer")/查询客户表
4=A3.(ID)/从客户表中选出客户序号
5=A2.align(A4.len(), A4.pos(CustomerID))/使用函数 A.align(n,y)，将销售表按照客户序号对位分组
6=A3(A5.pos@a(null))/在客户表中选出没有销售记录（值为 null）的客户信息

A6的执行结果如下：

IDNameState…
ALFKICMA-CGMTexas…
CENTCNedlloydFlorida…

 

2.2    每组保留所有匹配成员

按序号将表中所有记录分组并汇总求和。

【例 5】 根据订单表，顺序列出 2013 年每月的订单总数。订单表部分数据如下：

 

IDCustomerIDOrderDateAmount
10248VINET2012/07/04428.0
10249TOMSP2012/07/051842.0
10250HANAR2012/07/081523.5
10251VICTE2012/07/08624.95
10252SUPRD2012/07/093559.5
…………

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from Orders where   year(OrderDate)=2013")/查询 2013 年的订单
3=A2.align@a(12,month(OrderDate))/使用函数 A.align@a()，将订单表按月份对位分为 12 组，@a 选项每组选择所有匹配成员
4=A3.new(#:Month,~.count():OrderCount)/列出每月的订单总数

A4的执行结果如下：

MonthOrderCount
133
229
330
431
532
630
733
833
937
1038
1134
1248

 

2.3    按序号重复性分组

按计算出的序号数列重复性分组并计算。

【例 6】 根据发帖记录表，按标签将帖子分组，并统计各个标签出现频数。发帖记录表部分数据如下：

 

IDTitleAuthorLabel
1Easy   analysis of Excel2Excel,ETL,Import,Export
2Early   commute: Easy to pivot excel3Excel,Pivot,Python
3Initial   experience of SPL1Basics,Introduction
4Talking   about set and reference4Set,Reference,Dispersed,SQL
5Early   commute: Better weapon than Python4Python,Contrast,Install
…………

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from   PostRecord")/查询发帖记录表
3=A2.conj(Label.split(",")).id()/将标签按逗号分隔后合并到一个序列，获得没有重复值的全部标签。
4=A2.align@ar(A3.len(),A3.pos(Label.split(",")))/使用 align 函数的 @r 选项，按照每个帖子的标签在全部标签中的定位分组。
5=A4.new(A3(#):Label,~.count():Count).sort@z(Count)/统计每个标签的帖子数量，按降序排列

A5的执行结果如下：

LabelCount
SPL7
SQL6
Basics5
……

 

2.4    分段分组

根据指定字段的值，分段分组并汇总计数。

【例 7】 根据员工薪资表，按工资 8000 以下、8000~12000 和 12000 以上分组，并统计各组的人数。员工薪资表部分数据如下：

 

IDNAMEBIRTHDAYSALARY
1Rebecca1974-11-207000
2Ashley1980-07-1911000
3Rachel1970-12-179000
4Emily1985-03-077000
5Ashley1975-05-1316000
…………

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from   EMPLOYEE")/查询员工表
3[0,8000,12000]/定义工资区间
4=A2.align@a(A3.len(),A3.pseg(SALARY))/使用函数 A.pseg(x) 获取工资所在区间
5=A4.new(A3 (#):SALARY,~.count():COUNT)/统计每组的人数

A5的执行结果如下：

SALARYCOUNT
0308
8000153
1200039

 

根据表达式的计算结果，将记录分段分组并汇总计算平均值。

【例 8】根据员工表，按入职时间 10 年以下，10~20 年和 20 年以上分组，并统计每组的平均工资。员工表部分数据如下：

IDNAMEBIRTHDAYSALARY
1Rebecca1974-11-207000
2Ashley1980-07-1911000
3Rachel1970-12-179000
4Emily1985-03-077000
5Ashley1975-05-1316000
…………

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from   EMPLOYEE")/查询员工表
3[0,10,20]/定义入职年限区间
4=now()/获取当前日期时间
5=A2.align@a(A3.len(),A3.pseg(elapse@y(A4,-~),   HIREDATE))/使用函数 A.pseg(x,y) 获取入职时间所在区间
6=A5.new(A3(#):EntryYears,~.avg(SALARY):AvgSalary)/统计每组的平均工资

A6的执行结果如下：

EntryYearsAvgSalary
06777.78
107445.53
206928.57

 

3.     枚举分组

枚举分组是指，事先指定一组枚举条件，将待分组集合的成员作为参数计算这批条件，条件成立者都被划分到与该条件对应的一个子集中，结果集的子集和事先指定的条件一一对应。

3.1    每个成员只存放到第一个匹配组

根据枚举条件表达式，将记录分组，分组时记录只置于第一个匹配组。

【例 9】 根据中国主要城市人口表，按人口将城市分类。中国主要城市人口表部分数据如下：

 

IDCityPopulationProvince
1Shanghai12286274Shanghai
2Beijing9931140Beijing
3Chongqing7421420Chongqing
4Guangzhou7240465Guangdong
5Hong   Kong7010000Hong   Kong Special Administrative Region
…………

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from   UrbanPopulation")/查询城市人口表
3[?>2000000,?>1000000,?>500000,?<=500000]/超大城市 200 万人口以上，特大城市 100-200 万人口，大城市 50-100 万，其他中小城市。
4=A2.enum(A3,Population)/使用函数 A.enum()，将人口按 A3 定义的条件进行枚举分组

A4的执行结果如下：

..

3.2    不匹配成员存放到新组

根据枚举条件表达式，将记录分组，不匹配记录放到新组。

【例 10】 根据员工薪资表，按年龄条件 [小于 35 岁, 小于 45 岁] 将员工分组，统计平均工资，不满足条件的分到新组。员工薪资表部分数据如下：

IDNAMEBIRTHDAYSALARY
1Rebecca1974-11-207000
2Ashley1980-07-1911000
3Rachel1970-12-179000
4Emily1985-03-077000
5Ashley1975-05-1316000
…………

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from   EMPLOYEE")/查询员工表
3[?<35,?<45]/将年龄段划分为 35 岁以下，45 岁以下
4=A2.enum@n(A3, age(BIRTHDAY))/使用函数 A.enum@n()，按年龄枚举分组，选项 @n 不匹配成员存放到新组。
5=A4.new(if (#>A3.len(),   "Other",A3(#)):AGE,~.avg(SALARY):AvgSalary)/统计每组的平均工资，最后一组名称设置为 Other

A5的执行结果如下：

AGEAvgSalary
?<357118.18
?<457448.16
Other7395.06

 

3.3    按照枚举条件可重复分组

根据不同的指定序列，将记录分组并计算，分组时记录可重复。

【例 11】 根据城市 GDP 表，分别统计直辖市、一线城市、二线城市的人均 GDP，分组时可能重复。城市 GDP 表部分数据如下：

 

IDCityGDPPopulation
1Shanghai326792418
2Beijing303202171
3Shenzhen246911253
4Guangzhou230001450
5Chongqing203633372
…………

【SPL 脚本】

 AB
1=connect("db")/连接数据库
2=A1.query("select * from GDP")/查询城市 GDP 表
3[["Beijing","Shanghai","Tianjing","Chongqing"].pos(?)>0,["Beijing","Shanghai","Guangzhou","Shenzhen"].pos(?)>0,["Chengdu","Hangzhou","Chongqing","Wuhan","Xian","Suzhou","Tianjing","Nanjing","Changsha","Zhengzhou","Dongguan","Qingdao","Shenyang","Ningbo","Kunming"].pos(?)>0]/枚举直辖市、一线城市和二线城市
4=A2.enum@r(A3,City)/使用函数 A.enum@r()，按城市枚举分组。选项 @r 表示可重复分组。
5=A4.new(A3(#):Area,~.sum(GDP)/~.sum(Population)*10000:CapitaGDP)/统计每组的人均 GDP

A5的执行结果如下：

AreaCapitaGDP
["Beijing","Shanghai","Tianjing","Chongqing"].pos(?)>0107345.03
["Beijing","Shanghai","Guangzhou","Shenzhen"].pos(?)>0151796.49
["Chengdu","Hangzhou","Chongqing","Wuhan","Xian","Suzhou","Tianjing","Nanjing","Changsha","Zhengzhou","Dongguan","Qingdao","Shenyang","Ningbo","Kunming"].pos(?)>0106040.57

 

《SPL CookBook》中还有更多相关计算示例。

润乾软件
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
博客
给 BIRT 的 SQL 数据集传多值参数
02-03 573
但预览结果看不到数据，原因在于JDBC因为“预防SQL注入”的安全问题，禁止了这种多值参数的传值方式。arg1相当于第一个问号，然后看结果，能查出数据来了。
博客
SQL 多字段条件查询
02-02 999
SPL语言从理论模型上做了大量的这类创新，能极大的提高描述计算的能力，可以想象掌握了 SPL 语言的你，一个人维护的。=A2.select(pos(~.array().concat(","),"北京 ")!（可以理解为 JAVA 里的数组，只是这个数组比较广义，元素可以是任意数据类型）；还可以依次类推，~[-1] 是前一条记录,~[3] 是当前记录后面的第三条记录;=A2.select((~.销售额 -~[1]. 销售额 )>100000)pos(~.array().concat(","),"北京 ")
博客
SQL 分组条件深入剖析
01-19 2330
才导致自然而然的解题思路，能说的出，想得明白，但就是写不出相应的计算机语言。这一系列设计上的缺陷，造成了 SQL 的编写、维护、性能调优都成倍的增加工作量。集算器的 SPL 语言从根源上弃用了 SQL 背后的关系代数理论，发明新的离散数据集理论解决程序员描述计算的困难。追根溯源，上面 SQL 反映出来的问题，它缺少单纯的分组动作，不能显式的表达每个小分组，进而对每个小分组的更细致操作就无法直观表达。1、 按设备分组，找到每个设备下Ready的个数c1；2、 按设备分组，找到每个设备下所有状态的个数c2；
博客
多步骤复杂 SQL 优化实例
01-18 802
稍微复杂点的查询需求，写SQL就会是个烧脑的过程，除了证明我们人脑很聪明，逻辑思维能力强之外，剩余的就全是缺点，每个人经常用不同于其他人思路的方式绕行到同一个结果上，个性化这么强的编程方式，导致编写SQL、阅读SQL、调试SQL都很困难，维护成本也大大增高。在程序员编程描述计算这件事上，集算器 SPL 语言通过创新的数学理论模型《离散数据集》，大大改善《关系代数》（SQL背后的数学模型）在描述计算时的困难。简单的说是对有序计算更彻底的集合运算提倡分步等多方面创新，达到。
博客
集算器读写 json
01-17 209
在 json 文件中有上述内容，欲将其中的 results 取出并输出成新的 json 文件。A2：将 A1 的 results 字段导出成 json 字符串。A1: 读取 json 文件成字符串，导入为序表。A3：输出 json 串到文件中。
博客
解析 json，整理分散数据入库
01-16 760
根据 B5.SERVICES 记录里的字段数量循环，每个字段的名字可以在 C7 获得，每个字段的值可在 C8 取到。到此我们可以获得开篇所述“GROUPNAME”值、”SERVICES”中的键编号和“SERVICES”的值。“GROUPNAME”值、”SERVICES”中的键编号对应了数据库表 _groups 的 Groupname 和 ID。“LIST”中包含多个子孙记录，主要目标是”GROUPNAME”、”SERVICES”。“SERVICES”每个值对应数据库表 Services 中的一条记录。
博客
用集算器比较字符串相似性
01-13 474
1、有 1001 个数字串（每个数字串长度不等，都是 0~9 之内），每一个数字串都和另外 1000 个中找出相同数最多和不同数最多的数字串（等于 1001 个数字数字串都要找一次）如果只有第 1 行数字串含有 1，而第 1001 行数字串没有，算是不同数有 1 个；D7：计算 A4 与 diff 的差为 same 的值，循环并总是存下最大值，循环 2 结束。B5：循环 2，遇到成员 1 时候跳过，取成员 2 与成员 1 比较数字字符。B4：循环 1 的变量，初始为 0，记录比较的结果不同数与相同数。
博客
行转列，动态列枚举分组
01-12 399
Mongodb 不直接支持行转列，枚举分组做起来也很麻烦，要把数据读出来再用 Java、Php 等编程语言来实现；以下例子不使用 MongoDB 展现，如果使用 MongoDB 需要用 SPL 的 mongo_open()函数连接数据库，用 mongo_shell() 函数查询原始数据结构。B8、C8：根据 subs 循环，将 A4 中单个学校各科的学生成绩统计出来并按顺序拼接成序列。A5、B5、A6：生成结果表，结构是 school、sub1_5、sub1_4…sub1_1、sub2_5、sub2_4…
博客
MongoDB 导出数据成 csv
01-11 404
将 MongoDB 中的多层数据导出为结构化二维数据，实现这种算法要考虑大数据的处理，结构化数据计算，用 python 实现存在一定难度。
博客
动态 json 转化序表
01-10 770
A1：将 json 文件读成文本，导入成序表。可以看出 A1 此时相当于按照 custName 分组的目标结果表。A2：用对 A1 用 news 函数解析出 custCars 序表，合并到 A1。
博客
mongodb 中做 join 的方法
01-09 910
用 Mongodb 是不支持 join 查询的, 要多次查询很不方便。可以考虑配合 mongodb 使用的本身有计算能力的工具，比如使用 SPL。通过 employee 文档和 seller 文档查询 employee.state 为 California 的 sales orders。A4：选出满足条件的结果，订单 1、3 都属于来自 California 的雇员 1。
博客
查找满足条件的子记录并合并
01-06 218
问题是，要将 mongodb 中的一系列字符串记录按照逗号分割的首个数字值筛选。A4：将每个字符串成员转成序列，取第一个子成员，根据其选择数据。A3：找到需要计算的数据序列。
博客
筛选用户权限子集记录
01-05 401
A4：对照 A2 每条记录的 au 是不是 A3 的子集，如果是就查出来。
博客
mongodb 分组子文档合并
01-04 587
A4：每组数据的 acls 列内数据合并成一个序列，作为 readUser 保存。A3：数据根据 name 分组。
博客
对序表记录固定排序
01-03 497
A2：使用 align 函数把 A1 按照排序依据进行排序 / 对齐。[300,200,100,400] 可以是外部参数（假设命名为 list），A2 的代码则可以改为 A1.align@s(list,PRODUCT_ID)集算器提供 JDBC 接口，可以像数据库一样使用，
博客
组内每隔 5 行加一个分隔线
12-30 858
BIRT 可通过 JDBC 访问集算器，可以像数据库一样使用，参考。：在组内每隔 5 行设一个标签。
博客
文本中按规则分组区段随机抽样
12-29 356
A1:读入文件，第一行作为列名，并设置SessionDate、Start和End的数据类型，最后将SessionDate和Start拼成完整日期DateStart。A2:将A1按Easting,Northing分组，循环每一组。循环体即缩进的B2-D4，可用A2来引用循环变量。类似地，B3的循环体是C3-D4。C4-D4:如果C3过滤的结果不空，则随机取记录存入B2，并将该记录追加到D4。C3:第1组(B2==null)数据直接返回，否则过滤出与B2间隔大于6小时的记录。B3:循环C3每组数据。
博客
拆串后结构化，其中按行对齐
12-28 385
直观的办法是两个文件按行JOIN起来，用计算列就能获得sku。A1：读取文本文件的内容，将每一行作为一个成员返回成序列。A2：读取文本文件的内容，将每一行作为一个成员返回成序列。A3：拆出A2中的sku，并和A1进行连接。这段代码可以方便地集成进Java（参考。
博客
动态列合并更新
12-27 850
提问者需要将两表的一些列进行合并，将 B 表的一些列更新到 A 表。题目中已经给出了 MERGE 语句的写法，难点在于他需要合并的列数是动态的。A2、A3：查询 A 表、B 表，设置 id 为序表主键。A4：按 id 合并两表去重，保留 B 中的记录。A2：查询 B 表中要更新到 A 表的字段。
博客
记录内值排序
12-26 739
需求是把源表中每行数据中的数据排序，重新填到改行中。A3：根据 A2 每条记录转换成数组重排，再填回去。A1: 连接数据库。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值