SQL 数据分析应用示例

前言

本文是基于我 2012 年在新浪博客上发表的一篇文章，现对其内容做了一些修改，重新排版后，发表在微信公众平台。

正文

SQL 全名是结构化查询语言（Structured Query Language），主要用于查询、更新和管理关系数据库系统。作为数据分析师，SQL 应用最多的是其查询功能，即用 SELECT 语句来检索数据表中所需的数据。

在不同的数据库系统中，SQL 语法有些差异，但基本思路应该一致。本文将基于 Oracle 数据库，介绍一些 SQL 数据分析应用示例。

首先，我们看一看常用的聚集函数，即将一列中所有的值聚集为单个值的函数。

1、记数：count

2、总和：sum

3、平均值：avg

4、最大值：max

5、最小值：min

6、方差：variance

7、标准差：stddev

8、排名：rank

下面举一个综合应用聚集函数的例子。

假如在订单表（orders）中，订单号字段为 orderid，订单总金额字段为 totalprice，金额为空值代表为 0，业务需求是：查询订购时间（crdt）在过去 10 个月中每个月每种订单类型的订单数、总金额、平均每单金额(最多保留两位小数)、最大订单金额、最小订单金额、方差、标准差、总金额月度排名，输出订单数大于 1000 个的订单类型，并按月份升序、总金额降序排列。

参考 SQL 语句如下：

select to_char(a.crdt,'yyyy-mm') as 月份, 
    a.ordertype as 订单类型, 
    count(distinct a.orderid) as 订单数,
    sum(nvl(a.totalprice, 0)) as 总金额, 
    round(avg(nvl(a.totalprice, 0)), 2) as 平均每单金额, 
    max(nvl(a.totalprice, 0)) as 最大订单金额, 
    min(nvl(a.totalprice, 0)) as 最小订单金额, 
    variance(nvl(a.totalprice, 0)) as 方差, 
    stddev(nvl(a.totalprice, 0)) as 标准差, 
    rank() over (partition by to_char(a.crdt,'yyyy-mm') 
    order by sum(nvl(a.totalprice, 0)) desc) as 排名
  from orders a
 where a.crdt >= add_months(trunc(sysdate,'mm'), -10)
   and a.crdt < trunc(sysdate,'mm')
 group by to_char(a.crdt,'yyyy-mm'), a.ordertype
having count(distinct a.orderid) > 1000
 order by to_char(a.crdt,'yyyy-mm'), 
       sum(nvl(a.totalprice, 0)) desc

根据实际的业务需求变化情况，上面的 rank 函数可以换成 dense_rank 或 row_number 。

其次，我们再看一些常用的字符串函数。

1、字符串截取：substr 

示例：

select substr('abcdef',1,3) 
  from dual; 

结果：abc

2、查找子串位置：instr 

示例：

select instr('abcfdgfdhd','fd') 
  from dual; 

结果：4

3、字符串连接：concat 

示例：

select concat('Hello',' world') 
  from dual; 

结果：Hello world

4、去掉空格：ltrim、rtrim、trim 

示例：

select ltrim(' abc') s1, 
       rtrim('def ') s2, 
       trim(' ghi ') s3 
  from dual; 

结果：abc, def, ghi

5、去掉前导和后缀：trim 

示例：

select trim(leading 9 from 998799) s1,
       trim(trailing 9 from 998799) s2,
       trim(9 from 998799) s3 
  from dual; 

结果：8799, 9987, 87

6、返回字符串首字母的ascii值：ascii 

示例：

select ascii('A') 
  from dual; 

结果：65

7、返回ascii值对应的字母：chr 

示例：

select chr('65') 
  from dual; 

结果：A

8、计算字符串长度：length 

示例：

select length('abcdef') 
  from dual; 

结果：6

9、大小写转换：lower, upper, initcap 

示例：

select lower('AbC') s1, 
       upper('dEf') s2, 
       initcap('gHi') s3 
  from dual; 

结果：abc, DEF, Ghi

10、匹配替换：replace 

示例：

select replace('abcd','bc','xyz') 
  from dual; 

结果：axyzd

11、绝对匹配替换：translate 

示例：

select translate('What','th','T-') 
  from dual; 

结果：W-aT

说明：replace 是将字符串中指定的连续字符替换成其它字符，translate 则是将各个字符替换成顺序与其相同的字符。

12、左右填充：lpad, rpad 

示例：

select lpad('ab',5,'=') s1, 
       rpad('ab',6,'*') s2 
  from dual; 

结果：===ab, ab****

13、指令解码：decode 

示例：

select decode('a','b','1','c','2','3') 
  from dual; 

结果：3

最后，我们了解一些常用的运算符，主要用于处理数据之间的运算、比较、筛选等。

1、算术运算符：

+, -, *, / 

2、比较运算符：

>, >=, =, != 或 <>, <, <=, like, between, in, is null

3、逻辑运算符：

not, and, or

4、集合运算符： 

intersect, union, union all, minus

5、连接运算符：

||

SQL 还有很多比较高级的应用知识，例如：创建表、插入表、删除表、创建函数、存储过程、包、类型、定时任务、游标、触发、索引、约束、物化视图、DBLink 等等，这些往往是 DBA 或软件开发人员需要掌握的知识，但做数据分析的时候，如果能适当地掌握一些 SQL 高级应用知识，有时能非常明显地提升工作效率。本文限于篇幅和时间，就不展开讨论了。

---

近期文章

封面图片来源：www.unsplash.com