讲师中心 微信公众号

工具推荐

视频效率加速
AniShort
AniShort

AI短剧协同制作平台

立刻MV
立刻MV

低成本歌曲 MV、视频生成

剧云
剧云

中文短剧剧本制作平台

updream

一键生成高质量推广成片

集算器如何优化SQL计算(2)分组_MySQL

老伟姑娘_7274

老伟姑娘_7274

发布时间:2016-05-27 19:11:39

|

1583人浏览过

|

来源于php中文网

原创

非等值分组

 

按段分组是常见的需求,如成绩段(优秀,良好,…)、年龄段(青年、中年、…)等。

 

SQL实现分段一直很繁琐,段数不多的静态分段,可以用case when条件比较实现;而段数很多或规则变化的动态分段,一般则要建立临时表用非等值JOIN实现了。无论哪种情况的处理方法都很繁琐。

 

集算器中用penum函数即可返回枚举条件的序号:

 

[”?=60&&?=75&&?=90”].penum(成绩)

 

如果分段是连续的,还可以用pseg函数更简单地获得分段序号:

 

[60,75,90].pseg(成绩)

 

这里的条件和分段都是普通数组,可作为参数传递进来,长度也不限制。基于分段号即可将枚举分组和按段分组转变成普通的等值分组:

      

A

B 

1

[”?=60&&?=75&&?=90”]

条件段,可以是参数

2

[60,75,90]

区间段,可以是参数

3

=db.query("select * from 成绩表")

 

4

=A3.groups(A1.penum(成绩);count(1):人数)

按条件段分组

5

=A3.groups(A2.pseg(成绩);count(1):人数)

按区间段分组

与非等值分组相关的问题还有固定排序:统计结果呈现时经常要求按指定的次序而不是数据本身排序,比如中国省份排列时一般要将北京放在第一个。SQL处理方法和分段类似,对于条目较少且静态的规则可以用decode转码成序号,而条目较多或规则动态时也需要建立临时表用JOIN生成序号。

 

集算器提供了align@s函数专门用于对齐排序:

 

         T.align@s([“北京”,”河北”,”山东”,…],地区)

 

即可将表T以字段地区按指定的次序排序,同样的,排序依据是个普通数据,可以作为参数传递:

        

A

 B

1

[“北京”,”河北”,”山东”,…]

排序依据,可以是参数

2

=db.query("select * from T")

 

3

=A2.align@s(A1,地区)

按指定次序排列

与不产生空子集的等值分组不同,有时我们要求分组的结果是连续区间,要补齐中间缺省的空子集。SQL实现这个过程非常麻烦,要手工先造出连续不断的分组区间再left join要统计的数据表,复杂的子查询将不可避免。而集算器有专门的对齐函数,基准区间准备也很方便,实现该运算要简单得多。

 

简化的交易记录表结构为:序号、日期、金额。现需要按周统计累计的交易金额,没有交易记录的周也要列出。

      

A

B 

1

=db.query("select * from 交易记录表 order by 日期")

 

2

>start=A1(1).日期

 

3

=interval(start,A1.m(-1).日期)\7+1

计算总周数

4

=A1.align@a(A2,interval(start,日期)\7)

按周分组,可能有空集

5

=A4.new(#:周,累计金额[-1]+~.sum(金额):累计金额)

汇总并计算累计

分组子集

 

由于没有显式的集合数据类型,SQL在分组时会强迫计算出聚合值。但有时我们感兴趣的不只是聚合值,还有分组子集,这时用SQL就很难处理,要用子查询反复计算。

 

集算器有集合数据,也提供了返回子集的分组函数。这样就能方便地处理分组后运算。

 

比如找出总分500分以上的学生的各科成绩记录。SQL需要先分组计算出各学生总分,从中过滤出500分以上的,再用这个名单与原成绩记录JOIN或用IN判断,较麻烦且要重复取数。而集算器则可以按自然思路写出来:

        

A

1

=db.query("select * from 成绩表")

2

=A1.group(学生).select(~.sum(成绩)>=500).conj()

这种分组后却要返回子集明细记录的情况很多,分组聚合是用来实现某种过滤的中间步骤而不是结果。事实上,后面要讲到的报表按分组汇总值排序的例子也是类似的运算。

 

有时即使是只要返回聚合值,但聚合计算较为特别,难以用简单聚合函数表示时,也需要保留分组子集用于再计算。

 

这类计算在现实中并不少见,但因为计算复杂,常常涉及较多的业务背景,不适合举例说明,这里改造了一个简化后的例子:

 

设有用户登录表结构为:user(帐号),login(登录时刻);现要计算出每个帐号最后登录时刻以及该时刻前三天内的登录次数。

 

找出最后登录时刻很容易,但如果不保留分组子集时则很难计算出那个时间段登录次数。用SQL需要先分组计算出最后登录时间,与原表JOIN后过滤相应时间段的记录再次分组汇总,不仅麻烦而且记录效率很低。而使用集算器保留了分组子集则容易实现分步式计算:

       

A

1

=db.query("select * from 登录表")

2

=A1. .group(user;~.max(login):last,~.count(interval(login,last)

其中~就是按user分组后的子集。

 

如果数据有序还可以用高效的方法计算:

       

A

1

=db.query("select * from 登录表 order by login desc")

2

=A1. .group(user;~(1).login:last,~.pselect@n(interval(login,last)>3)-1:num)

有序聚合

 

取出每组的前N条、最大值对应记录等也是较常见的运算。显然,这些都可以用保留分组子集的方法实现,但由于这类运算较常见,集算器将其理解成某种聚合而提供了专门的函数,这样就可以采用和普通的分组汇总基本一致的处理方式。

 

先看最简单的情况,用户登录表结构为:用户、登录时刻、IP地址、…;列出每个用户首次登录的记录。

 

SQL可以用窗口函数生成组内排序序号,并取出所有序号为1的记录,但窗口函数是在结果集上再计算的,因而必须用子查询再过滤的形式,写法有些复杂。而不支持窗口函数的数据库写起来就会更困难了。

 

集算器提供了group@1方法可直接取出每个分组的第一个成员。

    

A

1

=db.query("select * from 登录表 order by 登录时刻")

2

=A1.group@1(用户)

这类日志数据经常存在文件中,且已经对时刻有序,用集算器就可以直接取出第一条而不必再排序。数据量大到内存放不下时也可以基于游标实现类似的运算。

 

股价表的结构为:股票、交易日、收盘价;计算每支股票最近的涨幅。

 

计算涨幅涉及到最后两个交易日的记录,需要用两重窗口函数分别实施组内跨行计算再取出结果的第一行,写法很繁琐。集算器提供了topN聚合函数,利用集合数据直接返回多条记录作为汇总值参与进一步计算。

      

A

 B

1

=db.query("select * from 股价表")

 

2

=A1.groups(股票;top(2,-交易日))

最后2个交易日的数据

3

=A2.new(股票,#2(1).收盘价-#2(2).收盘价:涨幅)

计算涨幅

聚合函数并不会先计算出分组子集,而是直接在已有结果上累积,这样可获得更高的性能,而且在数据量大到内存放不下时还可以基于游标工作。

 

如果数据已有序,则可以更高效地用位置取出相应记录:

       

A

B 

1

=db.query("select * from 股价表 order by 交易日 desc")

 

2

=A1.groups(股票;top(2,0))

直接取前2条

3

=A2.new(股票,#2(1).收盘价-#2(2).收盘价:涨幅)

 

取出最大值对应记录、第1条最后1条等类似计算都是topN聚合的特例了。 

 

逆分组

 

与分组汇总相反,逆分组指将汇总数据拆分成多条明细数据。这种情况不多见,但碰到了用SQL很难处理,这里仅举一例。

 

分期付款表结构为:编号、总金额、起始日、总期数;要将每笔贷款拆分成多期记录,结构为:编号、期数、还款日、金额。总金额将简单地平均分配到每一期,一期为一个月。

 

从明细到汇总很容易,反过来就困难很多,用SQL将记录数变多一般是和一个序号表JOIN或用递归查询,思路都不直接。而用集算器则按常规思路写出来即可:

  

A

1

=db.query("select * from 分期付款表")

2

=A1.news(总期数;编号,~:期数,after@m(起始日,~-1):还款日,总金额/总期数:金额)


本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热门AI工具

更多
豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

蛙蛙写作

一款AI论文写作工具,主要用于超级AI智能写作助手,适合需要提升相关任务效率的用户。

切问学术

切问学术是一款AI论文写作工具,复旦大学NLP团队推出的AI学术智能体。

Laper
Laper Hot

Laper是专为编剧、导演和制片人推出的 AI 原生剧本创作工具。

笔灵降AI

一款AI论文写作工具,主要用于论文降AI神器,适配知网及维普!一键降至安全线,100%保留原文格式;无口语化问题,文风更学术,降后字数控制最佳!,适合需要提升相关任务效率的用户。

Lovart
Lovart Hot

一款面向视觉设计创作的AI设计平台,可通过智能体和画布工作流辅助制作海报、Logo、网页、PPT及其他视觉内容。

音述AI
音述AI Hot

一款AI音频处理工具,主要用于音述AI是一个以“用声音述说故事”为核心的 AI 音乐创作与声音分享社区,适合需要提升相关任务效率的用户。

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

0

2026.11.17

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

0

2026.11.17

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

0

2026.11.17

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

0

2026.11.17

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

0

2026.11.17

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

0

2026.11.17

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

180

2026.11.12

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

120

2026.11.12

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

100

2026.11.12

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn