SQL 如何计算每个分组的中位数

Wesley13
• 阅读 141

中位数是指一组数据排序以后,位于中间位置的数据值。如果数据个数是奇数,中位数就是最中间位置那个值;如果是偶数,则是中间位置那两个数的平均值。

怎么查询出数据分组以后每个组的中位数呢?

用SQL来解决这个问题是很有难度的!

SQL的集合是无序的,没有数据位置的概念,需要人为地造出行号,但是要对各分组独立编行号也困难。后来在SQL2003标准中加入了窗口函数,可以对分组编行号了,但是求各组中位数依然繁琐。

举个例子:现有成绩表SCORES数据如下,要求查出每科成绩的中位数。

COURSE

SCORE

History

68.5

History

79.0

History

82.5

History

88.0

History

93.5

Maths

75.5

Maths

83.0

Maths

85.0

Maths

95.5

查询出来的各科成绩中位数应该是:

COURSE

SCORE

History

82.5

Maths

84.0

以Oracle为例,用SQL写出来是这样:

WITH  A  AS 

       ( SELECT COURSE, SCORE,

              ROW_NUMBER()OVER ( PARTITION BY COURSE ORDER BY SCORE) AS RN,

              COUNT(*) OVER (PARTITION BY COURSE) AS CNT

       FROM SCORES ),

B  AS

       (SELECT * FROM A WHERE RN>(CNT-0.5)/2 AND RN<(CNT+2.5)/2 )

SELECT COURSE, AVG(SCORE) AS SCORE FROM B

GROUP BY COURSE

ORDER BY COURSE;

这里的A为每组数据加上组内行号并统计每组记录数,B查出位于每组中间位置的记录,最后从B里算出每组平均值,即为中位数。解题步骤比较多,这种SQL不好写。另外还有不用窗口函数的办法,语句就更加复杂了,这里不再列出。

集算器的SPL语言支持组内运算,也提供了中位数函数,解决这个问题就会简单很多,只需1行代码:

connect("mydb").query("select * from scores order by course, score").group(COURSE).new(.COURSE,.(SCORE).median():SCORE)

SPL 擅长解决这类分组子集和组内有序计算,请阅计算各组前 N 行组内运算行间计算

集算器 SPL 是解决 SQL 难题的专业脚本语言,它语法简单,符合自然思维,是天然分步、层次清晰的面向过程计算语言。它采用与数据库无关的统一语法,编写的算法可在数据库间无缝迁移。它是桌面级计算工具,即装即用,配置简单,调试功能完善,可设置断点、单步执行,每步执行结果都可查看。请参阅SQL 解题手

SPL也能很方便地嵌入到JAVA应用,可参考Java 如何调用 SPL 脚本

具体使用方法可参考 如何使用集算器

点赞
收藏
评论区
推荐文章
技术小男生 技术小男生
2个月前
linux环境jdk环境变量配置
1:编辑系统配置文件vi /etc/profile2:按字母键i进入编辑模式,在最底部添加内容: JAVAHOME/opt/jdk1.8.0152 CLASSPATH.:$JAVAHOME/lib/dt.jar:$JAVAHOME/lib/tools.jar PATH$JAVAHOME/bin:$PATH3:生效配置
光头强的博客 光头强的博客
2个月前
Java面向对象试题
1、 请创建一个Animal动物类,要求有方法eat()方法,方法输出一条语句“吃东西”。 创建一个接口A,接口里有一个抽象方法fly()。创建一个Bird类继承Animal类并实现 接口A里的方法输出一条有语句“鸟儿飞翔”,重写eat()方法输出一条语句“鸟儿 吃虫”。在Test类中向上转型创建b对象,调用eat方法。然后向下转型调用eat()方
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录 问题 用navicat导入数据时,报错: 原因这是因为当前的MySQL不支持datetime为0的情况。 解决修改sql\mode: sql\mode:SQL Mode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。 全局s
Peter20 Peter20
1年前
mysql中like用法
like 的通配符有两种%(百分号):代表零个、一个或者多个字符。\(下划线):代表一个数字或者字符。1\. name以"李"开头where name like '李%' 2\. name中包含"云",“云”可以在任何位置where name like '%云%' 3\. 第二个和第三个字符是0的值where salary like '\00%' 4\
干货满满张哈希 干货满满张哈希
11个月前
由一次 UPDATE 过慢 SQL 优化而总结出的经验
最近,线上的 ETL 数据归档 SQL 发生了点问题,有一个 UPDATE SQL 跑了两天还没跑出来: update torderrecord set archiveid '420a7fe7476745e8a5f572280c192faa', updatetime updatetime where orderid in (select orderid
Wesley13 Wesley13
1年前
cobol学习之十数据库的增删改查模板
这次连接数据库使用的是ODBC连接access数据库,里面主要是一个增删改查的模板备份,方便以后查询。 000001 IDENTIFICATION DIVISION. 000002 PROGRAM-ID. SAMPLEDB2. 00
Stella981 Stella981
1年前
Hive 和普通关系数据库的异同
1. 查询语言。由于 SQL 被广泛的应用在数据仓库中,因此,专门针对 Hive 的特性设计了类 SQL 的查询语言 HQL。熟悉 SQL 开发的开发者可以很方便的使用 Hive 进行开发。 2. 数据存储位置。Hive 是建立在 Hadoop 之上的,所有 Hive 的数据都是存储在 [HDFS](https://www.oschina.net/act
Wesley13 Wesley13
1年前
Oracle 的开窗函数 rank,dense_rank,row_number
1、开窗函数和分组函数的区别 分组函数是指按照某列或者某些列分组后进行某种计算,比如计数,求和等聚合函数进行计算。 开窗函数是指基于某列或某些列让数据有序,数据行数和原始数据数相同,依然能曾现个体数据的原貌。 事例数据 create table student\_scores( stu\_id varchar2(10),--学号 stu\_n
Stella981 Stella981
1年前
Shell(希尔)排序
### **Shell(希尔)排序**     对于直接插入排序而言,如果一个很小的数据单元位于很靠近右端的位置上,为了把这个数据单元移动到左边正确的位置上,中间所有的数据单元都需要向右移动一格。这个步骤对每一个数据项都执行了近n次复制。虽然不是所有的数据项都必须移动n个位置,但平均下来,每个数据项都会移动n/2格,总共是n\*n/2次复制。因此,直接插入
Easter79 Easter79
1年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用 0x00 SQL注入 ---------- 反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下 1)表名 payload:select \* from \`users\` where user\_id=1 limit 0,1; ![](https://o
helloworld_34035044 helloworld_34035044
4个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。 uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid() 或 uuid(sep)参数说明:sep 布尔值,生成的uuid中是否包含分隔符'',缺省为