毋庸置疑,SQL
绝对是人类计算机发展史上最伟大的发明之一。这门存在了近半个世纪的技术,即使在大数据时代的当下依然扮演着重要的角色。像什么Flink
、Pulsar
、Cassandra
这样炙手可热的技术,又有哪个不支持SQL
呢。可是即使SQL
重要如此,还有好多小伙伴觉得SQL
是一门过时的技术,因而不愿意花精力去学。更有一众ORM
框架为了追求面向对象的极致,掩盖了太多SQL
之美,这真是我不乐见的。
首先,我必须强调,SQL
虽是一门古老的技术,但却并非停滞不前。看下历代SQL
标准年表:
SQL-86
SQL-89
SQL-92
SQL:1999
SQL:2003
SQL:2006
SQL:2008
SQL:2011
SQL:2016
标准这种东西迭代太快也不行,不然就算程序员们的头发能受了,数据库厂家也受不了。所以目前SQL
标准这种稳健的迭代是比较正确的做法。但是,即使是这样,数据库厂家们也没能跟上节奏,至今没有任何一家数据库产品声称完全符合SQL:2016
的核心特性。
那么数据库里的优等生,我们的主角PostgreSQL
表现得到底怎么样呢?
As of the version 13 release in September 2020, PostgreSQL conforms to at least 170 O of the 179 mandatory features for SQL:2016 Core conformance. As of this writing, no relational database meets full conformance with this standard.
也就是说目前SQL:2016
标准中的核心标准中的179项特性,PostgreSQL
至少实现了170项,虽说离“完全体”还有一点差距,但是考虑到至今也没有任何一个关系型数据能够完全吻合标准,PostgreSQL
的表现还是相当不错的。如果你感兴趣的话,可以看看其他数据库的标准完成度,基本都还差得远呢,甚至有些数据库因为实在是跟不上标准,竟然对此有点讳莫如深了。
关于SQL
的技艺展示,没有几本大部头的书是讲不完的。本文将从PostgreSQL
入手,展示几个在实际项目中会用到的SQL
特性。这些特性可能并非PostgreSQL
独有,也并不是只有最新版本的PostgreSQL
才支持。关于PostgreSQL
独家特性展示,我将放在下一期的文章中。
想象这样一个表(表名:score),罗列了不同同学、不同课程的成绩单。现实场景中,人员信息、课程信息可能都需要单独的表进行存放,成绩单应该通过人员ID、课程ID字段对前两者进行关联。但是本文着重介绍PostgreSQL
的特性,为了大家阅读方便,采用了这个非标准范式的设计方案。
id | i_score | v_name | v_course | v_type |
---|---|---|---|---|
981222c9aa8e | 78 | 张无忌 | 语文 | 文 |
b7e321377c21 | 73 | 张无忌 | 数学 | 理 |
311365068bdc | 82 | 张无忌 | 英语 | 文 |
293721bcb0f0 | 61 | 张无忌 | 物理 | 理 |
751567a4724e | 81 | 令狐冲 | 语文 | 文 |
0f639b6c2cf6 | 68 | 令狐冲 | 数学 | 理 |
88edf8e530ad | 77 | 令狐冲 | 英语 | 文 |
a841d6175683 | 83 | 令狐冲 | 物理 | 理 |
8e0490684601 | 66 | 乔峰 | 语文 | 文 |
50167805bbfc | 94 | 乔峰 | 数学 | 理 |
261f0f9de93b | 76 | 乔峰 | 英语 | 文 |
18bb6bfafdf6 | 95 | 乔峰 | 物理 | 理 |
需求1. 找出每门课程的第一名
目标结果:
id | i_score | v_name | v_course | v_type |
---|---|---|---|---|
311365068bdc | 82 | 张无忌 | 英语 | 文 |
751567a4724e | 81 | 令狐冲 | 语文 | 文 |
50167805bbfc | 94 | 乔峰 | 数学 | 理 |
18bb6bfafdf6 | 95 | 乔峰 | 物理 | 理 |
传统做法:
select a.*
from score as a
join (
select v_course, max(i_score) as i_score
from score
group by v_course
) b on a.v_course = b.v_course and a.i_score = b.i_score;
PostgreSQL
做法:
select distinct on (v_course) * from score order by v_course, i_score desc;
怎么样?本来需要子查询join
一下才能解决的问题,PostgreSQL
轻松一行搞定。
需求2. 查询每名同学的总平均分、文科平局分、理科平均分
目标结果:
v_name | 总平均分 | 文科平均 | 理科平均 |
---|---|---|---|
乔峰 | 82.75 | 71 | 94.5 |
令狐冲 | 77.25 | 79 | 75.5 |
张无忌 | 73.5 | 80 | 67 |
传统做法:
select v_name,
avg(i_score) as 总平均分,
sum(case when v_type ='文' then i_score else 0 end)/sum(case when v_type ='文' then 1.0 else 0 end) 文科平均,
sum(case when v_type ='理' then i_score else 0 end)/sum(case when v_type ='理' then 1.0 else 0 end) 理科平均
from score
group by v_name
说实话,这样写看着也还行,但是case when
这种东西对索引实在是不够友好,在应对大数据量的时候会力不从心。
PostgreSQL
做法:
select v_name,
avg(i_score) as 总平均分,
avg(i_score) filter ( where v_type = '文' ) as 文科平均,
avg(i_score) filter ( where v_type = '理' ) as 理科平均
from score
group by v_name
可读性立马高很多,隐隐有种SQL
艺术的感觉,执行效率也要比前者高。
需求3. 查询每名同学的成绩与该课程平均分的差异
目标结果:
id | i_score | v_name | v_course | v_type | 该课平均 | 成绩差 |
---|---|---|---|---|---|---|
0f639b6c2cf6 | 68 | 令狐冲 | 数学 | 理 | 78.33 | -10.33 |
18bb6bfafdf6 | 95 | 乔峰 | 物理 | 理 | 79.67 | 15.33 |
261f0f9de93b | 76 | 乔峰 | 英语 | 文 | 78.33 | -2.33 |
293721bcb0f0 | 61 | 张无忌 | 物理 | 理 | 79.67 | -18.67 |
311365068bdc | 82 | 张无忌 | 英语 | 文 | 78.33 | 3.67 |
50167805bbfc | 94 | 乔峰 | 数学 | 理 | 78.33 | 15.67 |
751567a4724e | 81 | 令狐冲 | 语文 | 文 | 75 | 6 |
88edf8e530ad | 77 | 令狐冲 | 英语 | 文 | 78.33 | -1.33 |
8e0490684601 | 66 | 乔峰 | 语文 | 文 | 75 | -9 |
981222c9aa8e | 78 | 张无忌 | 语文 | 文 | 75 | 3 |
a841d6175683 | 83 | 令狐冲 | 物理 | 理 | 79.67 | 3.33 |
b7e321377c21 | 73 | 张无忌 | 数学 | 理 | 78.33 | -5.33 |
传统做法:
select score.*, avg as 该课平均, round(i_score - avg, 2) 成绩差
from score
left join (
select round(avg(i_score), 2) as avg, v_course
from score
group by v_course
) as a on a.v_course = score.v_course
order by score.id;
进阶做法:
with cte as (
select round(avg(i_score), 2) as avg, v_course
from score
group by v_course)
select score.*,avg as 该课平均, round(i_score - avg, 2) 成绩差
from score
left join cte on cte.v_course = score.v_course
order by score.id;
PostgreSQL
中究极做法:
select *,
round(avg(i_score) over (PARTITION BY v_course), 2) as 该课平均,
round(i_score - avg(i_score) over (PARTITION BY v_course), 2) as 成绩差
from score
order by score.id;
怎么样,如果你惊叹于SQL
的强大,但是对此还不太了解的话,可以搜一下窗口函数
。主流的商用数据库都是支持的,PostgreSQL
作为一款开源免费的数据库,对于窗口函数的支持也是第一梯队的。
需求4. 算出成绩单的合计值以及每个人的总成绩
目标结果:
v_name | v_course | sum |
---|---|---|
合计 | 934 | |
张无忌 | 总分 | 294 |
张无忌 | 语文 | 78 |
张无忌 | 英语 | 82 |
张无忌 | 物理 | 61 |
张无忌 | 数学 | 73 |
令狐冲 | 总分 | 309 |
令狐冲 | 语文 | 81 |
令狐冲 | 英语 | 77 |
令狐冲 | 物理 | 83 |
令狐冲 | 数学 | 68 |
乔峰 | 总分 | 331 |
乔峰 | 语文 | 66 |
乔峰 | 英语 | 76 |
乔峰 | 物理 | 95 |
乔峰 | 数学 | 94 |
说实话,对于这种需求,不给我一个高级语言的话,真的是有点为难我了。好在我们有PostgreSQL
,做起来并没有你想象中的难。
SELECT
CASE WHEN GROUPING(v_name) = 1
THEN '合计'
ELSE v_name END,
CASE WHEN GROUPING(v_name) <> 1 and GROUPING(v_course) = 1
THEN '总分'
ELSE v_course END,
sum(i_score)
FROM score
GROUP BY ROLLUP (v_name, v_course)
ORDER BY GROUPING(v_name) DESC ,v_name DESC, GROUPING(v_course) DESC ,v_course DESC;
很神奇是不是?寥寥数语就解决了需要高级语言编写逻辑代码才能搞定的问题。
总结
SQL
是一门古老而强大的技艺,我一直觉得SQL
的优雅是一种介于技术与艺术之间的美。在本文中,我以PostgreSQL
为例展示了一些实用的SQL
用法,本质上说,这些用法都是存在于SQL标准
中的,而并非PostgreSQL
独有,但不可否认,PostgreSQL
作为一款开源免费的产品,能提供如此优秀的标准支持,是社区为全世界开发者提供的宝贵财富。
很多开发人员,他们的日常工作常常陷在无边的业务堆叠之中,每日都是在做增删改查。我听到过不止一次,大家对增删改查的厌倦、厌恶,甚至是彻头彻尾的鄙视。但是请不要忘记两点:
SQL
是绝大多数软件系统运转的基石,在这个世界上任何被称作基石
的东西,都不可以被轻视,谁轻视它,谁就要栽跟头。- “世事洞明皆学问”,增删改查一样可以彰显技术实力。同样是搬砖,优秀的人,总有优秀的解决方案。
相信今天你已经初步体会到了PostgreSQL
与SQL
之美,在下一期中,我会展示真正属于PostgreSQL
的独门绝技,带你领略更绚丽的风景,我们下期再见。
推荐阅读本系列的其他文章: