分组

作者：刘晨 网名 bisal ，具备十年以上的利用运维工作教训，目前次要从事数据库利用研发能力晋升方面的工作，Oracle ACE ，领有 Oracle OCM & OCP 、EXIN DevOps Master 、SCJP 等国内认证，国内首批 Oracle YEP 成员，OCMU 成员，《DevOps 最佳实际》中文译者之一，CSDN & ITPub 专家博主，公众号"bisal的集体杂货铺"，长期保持分享技术文章，屡次在线上和线下分享技术主题。 本文起源：原创投稿 *爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。 前两天共事有个 MySQL 数据分组的需要，如下测试数据，须要找出每个 name 分组中 create_date 最近的记录： 须要留神的是，此处用的 MySQL 是5.6，最后是应用这条语句： select name, value, create_date, update_date from t1 group by name order by create_date desc;查问后果如下，看着如同是对的，然而认真看下，就会发现其中的问题，例如 name=a 最近的 create_date 应该是 value=3 的记录，name=d 最近的create_date应该是 value=10 的记录： 用这条 SQL 失去的其实只是每个 name 分组中最先插入的记录，而后依照 create_date 进行了降序排列，和原始需要，齐全不同。 此时可采纳分而治之的策略，先做排序，再做分组： select * from (select name, value, create_date, update_date from t1 order by create_date desc) t group by t.name;即可失去原始需要的数据： 当然，针对此需要，可能有其余办法，有趣味的敌人，能够尝试写写，共享一下。 可能有仔细的敌人会发现个问题，就是上述 SQL 中的 group by ，如同有些奇怪，如果依照惯例，select 中的字段须要呈现在 group by 中，上述语句居然没报错？ 如果咱们在 MySQL 5.7 执行雷同的语句： ...

问题介绍要在 Java 代码中实现类似 SQL 中的 GroupBy 分组聚合运算，是比较繁琐的，通常先要声明数据结构（Java 实体类），然后用 Java 集合进行循环遍历，最后根据分组条件添加到某个子集合中。Java 8 有了 Lambda（stream）代码简洁了许多，分组后往往还要跟着聚合操作，仍然需要单写聚合函数 sum(),count(),topN()等。这些还都是最常规的分组和聚合运算，遇到对位分组、枚举分组、多重分组等非常规分组加上其他聚集函数 (FIRST，LAST…)，代码就变得非常冗长且不通用。如果能有一个中间件专门负责这类计算，采用类似 SQL 脚本做算法描述，在 Java 中直接调用脚本并返回结果集就好了。Java 版集算器和 SPL 脚本，就是这样的机制，下面举例说明如何使用。SPL 实现常规分组duty.xlsx 文件中保存着每个人的加班记录:汇总每个人的值班天数：保存脚本文件CountName.dfx(嵌入 Java 会用到)每组 TopN取每个月、每个人、头三天的加班记录保存脚本文件RecMonTop3.dfx(嵌入 Java 会用到)Java 调用SPL 嵌入到 Java 应用程序十分方便，通过 JDBC 调用存储过程方法加载，用常规分组保存的文件CountName.dfx，示例调用如下：… Connection con = null; Class.forName(“com.esproc.jdbc.InternalDriver”); con= DriverManager.getConnection(“jdbc:esproc:local://”); //调用存储过程，其中CountName是dfx的文件名 st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call CountName()”); //执行存储过程 st.execute(); //获取结果集 ResultSet rs = st.getResultSet(); … 替换成 RecMonTop3.dfx 是同样的道理，只需 call RecMonTop3() 即可，也可同时返回两个结果集。这里只用 Java 片段粗略解释了如何嵌入 SPL，详细步骤请参阅Java 如何调用 SPL 脚本，也非常简单，不再赘述。同时，SPL 也支持 ODBC 驱动，集成到支持 ODBC 的语言，嵌入过程类似。拓展节选之前没有相关的总结，其实关于数据分组，细分起来其实还有很多种，对位分组、枚举分组、多重分组…，在乾学院 SPL 官方论坛都有总结和示例，这里节选其中两种。SPL 对位分组示例 1：按顺序分别列出使用 Chinese、English、French 作为官方语言的国家数量MySQL8: with t(name,ord) as (select ‘Chinese’,1 union all select ‘English’,2 union all select ‘French’,3) select t.name, count(countrycode) cnt from t left join world.countrylanguage s on t.name=s.languagewhere s.isofficial=‘T’ group by name,ord order by ord; 注意：表的字符集和数据库会话的字符集要保持一致。(1) show variables like ’character_set_connection’查看当前会话字符集(2) show create table world.countrylanguage 查看表的字符集(3) set character_set_connection=[字符集] 更新当前会话字符集集算器 SPL:A1: 连接数据库A2: 查询出所有官方语言的记录A3: 需要列出的语言A4: 将所有记录按 Language 对位到 A3 相应位置A5: 构造以语言和使用此语言为官方语言的国家数量的序表示例 2：按顺序分别列出使用 Chinese、English、French 及其它语言作为官方语言的国家数量MySQL8: with t(name,ord) as (select ‘Chinese’,1 union all select ‘English’,2 union all select ‘French’,3 union all select ‘Other’, 4), s(name, cnt) as (select language, count(countrycode) cnt from world.countrylanguage swhere s.isofficial=‘T’ and language in (‘Chinese’,‘English’,‘French’) group by language union allselect ‘Other’, count(distinct countrycode) cntfrom world.countrylanguage swhere isofficial=‘T’ and language not in (‘Chinese’,‘English’,‘French’) ) select t.name, s.cntfrom t left join s using (name) order by t.ord; 集算器 SPL:A4: 将所有记录按 Language 对位到 A3.to(3) 相应位置，并追加一组用于存放不能对位的记录A5: 第 4 组计算不同 CountryCode 的数量SPL 枚举分组示例 1：按顺序列出各类型城市的数量MySQL8: with t as (select * from world.city where CountryCode=‘CHN’), segment(class,start,end) as (select ’tiny’, 0, 200000 union all select ‘small’, 200000, 1000000 union all select ‘medium’, 1000000, 2000000 union all select ‘big’, 2000000, 100000000 ) select class, count(1) cnt from segment s join t on t.population>=s.start and t.population<s.endgroup by class, start order by start; 集算器 SPL:A3: ${…} 宏替换，以大括号内表达式的结果作为新表达式进行计算，结果为序列 [“?<200000”,“?<1000000”,“?<2000000”,“?<100000000”]A5: 针对 A2 中每条记录，寻找 A3 中第 1 个成立的条件，并追加到对应的组中示例 2：列出华东地区大型城市数量、其它地区大型城市数量、非大型城市数量MySQL8: with t as (select * from world.city where CountryCode=‘CHN’) select ‘East&Big’ class, count() cnt from twhere population>=2000000 and district in (‘Shanghai’,‘Jiangshu’, ‘Shandong’,‘Zhejiang’,‘Anhui’,‘Jiangxi’) union allselect ‘Other&Big’, count() from twhere population>=2000000 and district not in (‘Shanghai’,‘Jiangshu’,‘Shandong’,‘Zhejiang’,‘Anhui’,‘Jiangxi’) union allselect ‘Not Big’, count() from twhere population<2000000; 集算器 SPL:A5: enum@n 将不满足 A4 中所有条件的记录存放到追加的最后一组中示例 3：列出所有地区大型城市数量、华东地区大型城市数量、非大型城市数量MySQL8: with t as (select * from world.city where CountryCode=‘CHN’) select ‘Big’ class, count() cnt from twhere population>=2000000 union allselect ‘East&Big’ class, count() cnt from twhere population>=2000000 and district in (‘Shanghai’,‘Jiangshu’,‘Shandong’,‘Zhejiang’,‘Anhui’,‘Jiangxi’) union allselect ‘Not Big’ class, count(*) cnt from twhere population<2000000; 集算器 SPL:A6: 若 A2 中记录满足 A4 中多个条件时，enum@r 会将其追加到对应的每个组中优势总结有库写 SQL，没库写 SPL 用 Java 程序直接汇总计算数据，还是比较累的，代码很长，并且不可复用，很多情况数据也不在数据库里，有了 SPL，就能像在 Java 中用 SQL 一样了，十分方便。常用无忧，不花钱就能取得终身使用权的入门版 如果要分析的数据是一次性或临时性的，润乾集算器每个月都提供免费试用授权，可以循环免费使用。但要和 Java 应用程序集成起来部署到服务器上长期使用，定期更换试用授权还是比较麻烦，润乾提供了有终身使用权的入门版，解决了这个后顾之忧，获得方式参考如何免费使用润乾集算器？技术文档和社区支持 官方提供的集算器技术文档本身就有很多现成的例子，常规问题从文档里都能找到解决方法。如果获得了入门版，不仅能够使用 SPL 的常规功能，碰到任何问题都可以去乾学院上去咨询，官方通过该社区对入门版用户提供免费的技术支持。 ...

1、 对位分组示例 1：按顺序分别列出使用 Chinese、English、French 作为官方语言的国家数量MySQL8:with t(name,ord) as (select ‘Chinese’,1union all select ‘English’,2union all select ‘French’,3)select t.name, count(countrycode) cntfrom t left join world.countrylanguage s on t.name=s.languagewhere s.isofficial=‘T’group by name,ordorder by ord;注意：表的字符集和数据库会话的字符集要保持一致。(1) show variables like ‘character_set_connection’查看当前会话字符集(2) show create table world.countrylanguage查看表的字符集(3) set character_set_connection=[字符集]更新当前会话字符集集算器SPL: A1: 连接数据库A2: 查询出所有官方语言的记录A3: 需要列出的语言A4: 将所有记录按Language对位到A3相应位置A5: 构造以语言和使用此语言为官方语言的国家数量的序表 示例 2：按顺序分别列出使用 Chinese、English、French 及其它语言作为官方语言的国家数量MySQL8:with t(name,ord) as (select ‘Chinese’,1 union all select ‘English’,2union all select ‘French’,3 union all select ‘Other’, 4),s(name, cnt) as (select language, count(countrycode) cntfrom world.countrylanguage swhere s.isofficial=‘T’ and language in (‘Chinese’,‘English’,‘French’)group by languageunion allselect ‘Other’, count(distinct countrycode) cntfrom world.countrylanguage swhere isofficial=‘T’ and language not in (‘Chinese’,‘English’,‘French’))select t.name, s.cntfrom t left join s using (name)order by t.ord;集算器SPL: A4: 将所有记录按Language对位到A3.to(3)相应位置，并追加一组用于存放不能对位的记录A5: 第4组计算不同CountryCode的数量 2、 枚举分组示例 1：按顺序列出各类型城市的数量MySQL8:with t as (select * from world.city where CountryCode=‘CHN’),segment(class,start,end) as (select ’tiny’, 0, 200000union all select ‘small’, 200000, 1000000union all select ‘medium’, 1000000, 2000000union all select ‘big’, 2000000, 100000000)select class, count(1) cntfrom segment s join t on t.population>=s.start and t.populationgroup by class, startorder by start;集算器SPL: A3: ${…}宏替换，以大括号内表达式的结果作为新表达式进行计算，结果为序列["?<200000","?<1000000","?<2000000","?<100000000"]A5: 针对 A2 中每条记录，寻找 A3 中第 1 个成立的条件，并追加到对应的组中 示例 2：列出华东地区大型城市数量、其它地区大型城市数量、非大型城市数量MySQL8:with t as (select * from world.city where CountryCode=‘CHN’)select ‘East&Big’ class, count() cntfrom twhere population>=2000000and district in (‘Shanghai’,‘Jiangshu’, ‘Shandong’,‘Zhejiang’,‘Anhui’,‘Jiangxi’)union allselect ‘Other&Big’, count()from twhere population>=2000000and district not in (‘Shanghai’,‘Jiangshu’,‘Shandong’,‘Zhejiang’,‘Anhui’,‘Jiangxi’)union allselect ‘Not Big’, count()from twhere population<2000000;集算器SPL: A5: enum@n将不满足 A4 中所有条件的记录存放到追加的最后一组中 示例 3：列出所有地区大型城市数量、华东地区大型城市数量、非大型城市数量MySQL8:with t as (select * from world.city where CountryCode=‘CHN’)select ‘Big’ class, count() cntfrom twhere population>=2000000union allselect ‘East&Big’ class, count() cntfrom twhere population>=2000000and district in (‘Shanghai’,‘Jiangshu’,‘Shandong’,‘Zhejiang’,‘Anhui’,‘Jiangxi’)union allselect ‘Not Big’ class, count() cntfrom twhere population<2000000;集算器SPL: A6: 若A2中记录满足A4中多个条件时，enum@r会将其追加到对应的每个组中 3、 返回值直接作为序号进行定位分组示例 1: 按顺序列出各类型城市的数量MySQL8: 参见“枚举分组”中 SQL集算器SPL: A5: 先计算 A2.Population 在 A3 中段号，然后根据段号进行定位分组4、 原序保持下的相邻记录分组示例 1: 列出前 10 届奥运金牌榜 (olympic 表中只有历届成绩前 3 名的信息，且没有奖牌完全相同的情况)MySQL8:with t1 as (select_,rank() over(partition by game order by gold_1000000+silver1000+copper desc) rn from olympic where game<=10)select game,nation,gold,silver,copper from t1 where rn=1;集算器SPL: A3: 按原序分到各组，每组取第 1 条记录组成新序表 示例 2: 求奥运会国家总成绩蝉联第 1 的最长届数MySQL8:with t1 as (select_,rank() over(partition by game order by gold_1000000+silver1000+copper desc) rn from olympic),t2 as (select game,ifnull(nation<>lag(nation) over(order by game),0)neq from t1 where rn=1),t3 as (select sum(neq) over(order by game) acc from t2),t4 as (select count(acc) cnt from t3 group by acc)select max(cnt) cnt from t4;t1: 求出成绩排名t2: 列出历届第1名，并根据nation是否与上届不同置标志neq(不同置1，相同置0)t3: 累积标志neq到acc，可以保证相邻nation相同的acc相同，不相邻nation的acc不相同集算器SPL: A4: 将相邻nation相同的记录按原序分到同组A5: 求各组长度的最大值即最大届数 示例3：列出奥运会总成绩排名第一最长蝉联时的各届信息MySQL:with t1 as (select_,rank() over(partition by game order by gold_1000000+silver*1000+copper desc) rn from olympic),t2 as (select *,ifnull(nation<>lag(nation) over(order by game),0)neq from t1 where rn=1),t3 as (select , sum(neq) over(order by game) acc from t2),t4 as (select acc,count(acc) cnt from t3 group by acc),t5 as (select * from t4 where cnt=(select max(cnt) cnt from t4))select game,nation,gold,silver,copper from t3 join t5 using (acc);集算器SPL: A5: 求出长度最大组 示例 4：求奥运会前3名金牌总数连续增长的最大届数MySQL8:with t1 as (select game,sum(gold) gold from olympic group by game),t2 as (select game,gold, gold<=lag(gold,1,-1) over(order by game) lt from t1),t3 as (select game, sum(lt) over(order by game) acc from t2),t4 as (select count() cnt from t3 group by acc)select max(cnt)-1 cnt from t4;集算器SPL: A3: 根据条件值按原序分组，若gold小于等于上一个gold则产生新分组 ...