2021年了，你真的应该考虑PostgreSQL了（2）

毋庸置疑，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的特性，为了大家阅读方便，采用了这个非标准范式的设计方案。

需求1. 找出每门课程的第一名

目标结果：

传统做法：

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. 查询每名同学的总平均分、文科平局分、理科平均分

目标结果：

传统做法：

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. 查询每名同学的成绩与该课程平均分的差异

目标结果：

传统做法：

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. 算出成绩单的合计值以及每个人的总成绩

目标结果：

说实话，对于这种需求，不给我一个高级语言的话，真的是有点为难我了。好在我们有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作为一款开源免费的产品，能提供如此优秀的标准支持，是社区为全世界开发者提供的宝贵财富。

很多开发人员，他们的日常工作常常陷在无边的业务堆叠之中，每日都是在做增删改查。我听到过不止一次，大家对增删改查的厌倦、厌恶，甚至是彻头彻尾的鄙视。但是请不要忘记两点：

1. SQL是绝大多数软件系统运转的基石，在这个世界上任何被称作基石的东西，都不可以被轻视，谁轻视它，谁就要栽跟头。
2. “世事洞明皆学问”，增删改查一样可以彰显技术实力。同样是搬砖，优秀的人，总有优秀的解决方案。

相信今天你已经初步体会到了PostgreSQL与SQL之美，在下一期中，我会展示真正属于PostgreSQL的独门绝技，带你领略更绚丽的风景，我们下期再见。

推荐阅读本系列的其他文章：

• 2021年了，你真的应该考虑PostgreSQL了（1）
• 2021年了，你真的应该考虑PostgreSQL了（3）
• 2021年了，你真的应该考虑PostgreSQL了（4·终章）