SQL：数据与运算的融合体

本公众号主要关注大数据中的非结构处理，但在实际应用中，通常也离不开结构化数据处理，只不过相对而言其工具和开发环境比较成熟了。

SQL语言是结构化数据处理最常用的语言了，主要的规范从最早的SQL/86（SQL1）、SQL/92（SQL2）、SQL/99（SQL3）到SQL/2008、SQL/2016，其功能不断增强，从最初的简单规范到现在对文本、图像、数据挖掘、空间数据等提供统一的访问方式，但一直忠诚地支持数据库系统，为数据与运算的融合体提供了优秀的编程方法。

本文就介绍一个利用SQL语言来实现数据库记录前后记录运算的方法。

假设有一张表 abc，其结构和数据如图所示，像每个城市每天的空气质量数值、每个学校每天的上课学生数等，都与这个表结构类似。在数据分析中，我们要计算前后两天n1,n2,n3的数值增量或增比，显然用一个简单的SQL语句是无法实现的。

这个需求实际上是一种时间序列的运算，通常我们会想到用SQL中的CURSOR来循环运算，但是多次反复的数据表查询其计算效率很低，特别是在表比较大的情况下。为此，我们可以利用虚拟表的特性。

首先构建用下面的语句构建两个表a,b，注意这里增加了一个新的列表示数据的行号id，是按时间排序的。

select a, hdate, n1, n2,n3,

row_number() over (order by hdate desc) as id

from abc where a='x0001'

那么接下来就可以利用这两张表的行号进行连接运算，并求得相关数值的增量。如下：

select a.a, a.hdate,

a.n1-b.n1, --计算增量

a.n2-b.n2,--计算增量

a.n3-b.n3--计算增量

from

(select a, hdate, n1, n2,n3,

row_number() over (order by hdate desc) as id

from abc where a='x0001') as a

inner join

(select a, hdate, n1, n2,n3,

row_number() over (order by hdate desc) as id

from abc where a='x0001' ) as b

on a.id = b.id-1

order by a.a

以下是增量计算结果：

作者编著的《互联网大数据处理技术与应用》专著（清华大学出版社，2017）、同名公众号，专注于大数据技术的相关科学和工程知识传播，同时也为读者提供一些拓展阅读材料。关注后可阅读以前推送的原创文章。部分如下，关注后可阅读更多原创文章。欢迎选用本书做大数据相关的教材，有相关教学资源共享。