选读SQL经典实例笔记22_2版增补.png

1. 2版DB版本

1.1. DB2 11.5

1.2. Oracle 19c

1.3. PostgreSQL 12

1.4. MySQL 8.0

1.5. SQL Server 2017

2. 子查询

2.1. 如果想创建虚拟表，以便对其执行包含窗口函数或聚合函数的查询，那么最简单的做法无疑是使用子查询

2.2. 只需编写一个查询并将其放在括号内，然后再编写另一个使用它的查询

2.3. 有些 RDBMS 要求给子查询表指定别名，有些则不要求这样做

2.3.1. Oracle 不要求

2.4. 示例

2.4.1. sql

select max(HeadCount) as HighestJobHeadCount from
(select job,count(empno) as HeadCount
from emp
group by job) head_count_tab

3. 通用表表达式

3.1. 为了克服子查询的一些局限性，引入了 CTE

3.1.1. 让 SQL 支持递归

3.2. CTE 的可读性更高

3.2.1. sql

with head_count_tab (job,HeadCount) as
(select job,count(empno)
from emp
group by job)
select max(HeadCount) as HighestJobHeadCount
from head_count_tab

3.3. 递归 CTE 来计算前 20 个斐波那契数

3.3.1. sql

with recursive workingTable (fibNum, NextNumber, index1)
as
(select 0,1,1
union all
select fibNum+nextNumber,fibNUm,index1+1
from anchor
where index1＜20)
select fibNum from workingTable as fib

3.3.2. 关键字 RECURSIVE，它在 MySQL、Oracle 和 PostgreSQL 中必不可少，但在 SQL Server 和 DB2 中是可选的

3.3.3. index1 列旨在简化在 WHERE 子句中指定返回行数的工作

3.3.4. WHERE 子句至关重要，如果没有它，查询将不会终止

4. 根据发音比较字符串

4.1. 匹配单词

4.1.1. 匹配拼写正确和拼写错误的单词

4.1.2. 匹配拼写方式不同（比如英式拼写和美式拼写）的单词

4.1.3. 匹配由不同字符串表示的单词

4.1.3.1. 查找拼写不同但发音相同的字符串

4.2. 函数 SOUNDEX 将字符串转换为英语发音

4.2.1. SOUNDEX 会保留第一个字母，并将其他字母替换为数字

4.2.2. 发音相似的字母将被替换为相同的数字

4.3. SQL Server 函数 DIFFERENCE 会使用 SOUNDEX 对两个字符串进行比较，并返回表示相似程度的数字 0~4

4.4. sql

a_name
----
1 Johnson
2 Jonson
3 Jonsen
4 Jensen
5 Johnsen
6 Shakespeare
7 Shakspear
8 Shaekspir
9 Shakespar

4.4.1. sql

select an1.a_name as name1, an2.a_name as name2,
 SOUNDEX(an1.a_name) as Soundex_Name
 from author_names an1
 join author_names an2
 on (SOUNDEX(an1.a_name)=SOUNDEX(an2.a_name)
 and an1.a_name not like an2.a_name)

5. 查找与模式不匹配的文本

5.1. sql

select emp_id, text
  from employee_comment
EMP_ID     TEXT
---------- ------------------------------------------------------------
7369       126 Varnum, Edmore MI 48829, 989 313-5351
7499       1105 McConnell Court
           Cedar Lake MI 48812
           Home: 989-387-4321
           Cell: (237) 438-3333

5.2. 列出其中电话号码格式不正确的行

5.3. sql

select emp_id, text
from employee_comment
where regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')
  and regexp_like(
         regexp_replace(text,
            '[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}',''),
         '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')
    EMP_ID TEXT
---------- ------------------------------------------------------------
  7369     126 Varnum, Edmore MI 48829, 989 313-5351
  7844     989-387.5359
  9999     906-387-1698, 313-535.8886

6. 使用绝对中位差找出异常值

6.1. 值存在疑问的原因

6.1.1. 数据收集方式有问题

6.1.1.1. 记录值的仪表存在误差

6.1.2. 数据输入错误导致的

6.1.3. 数据生成时环境出现异常

6.1.3.1. 意味着数据点是正确的，但应谨慎根据数据得出任何结论

6.2. 检测异常数据的常用方法

6.2.1. 计算数据的标准偏差，并将超过 3 倍标准偏差（或其他类似距离）的数据点视为异常数据

6.2.2. 如果数据不符合正态分布，则这种方法可能错误地识别异常数据，而当数据分布不对称，或者如果你远离平均值，数据就不像正态分布那样变得稀疏时更是如此

6.3. 偏差是中值与各个值的绝对差

6.4. 绝对中位差是偏差的中值

6.5. Oracle

6.5.1. sql

with
Deviation (Deviation)
  as
(select abs(sal-median(sal))
from emp),
MAD (MAD) as
(select median(Deviation)
from Deviation )
select abs(sal-median)/MAD, sal, ename, job
FROM MAD join emp

6.6. SQL Server

6.6.1. sql

with median (median)
as
(select distinct percentile_cont(0.5) within group(order by sal)
        over()
from emp),
Deviation (Deviation)
  as
(Select abs(sal-median)
from emp join median on 1=1),
MAD (MAD) as
(select DISTINCT PERCENTILE_CONT(0.5) within group(order by deviation) over()
from Deviation )
select abs(sal-median)/MAD, sal, ename, job
from MAD join emp on 1=1

6.7. DB2

6.8. PostgreSQL

6.9. PERCENTILE_CONT 被视为聚合函数，而不是窗口函数

6.9.1. sql

with median (median)
as
(select percentile_cont(0.5) within group(order by sal)
from emp),
devtab (deviation)
  as
(select abs(sal-median)
from emp join median),
MedAbsDeviation (MAD) as
(select percentile_cont (0.5) within group(order by deviation)
from devtab)
select abs(sal-median)/MAD, sal, ename, job
FROM MedAbsDeviation join emp

6.10. MySQL

6.10.1. sql

with rank_tab (sal, rank_sal) as (
select sal, cume_dist() over (order by sal)
from emp),
inter as
(
select sal, rank_sal from rank_tab
where rank_sal＞=0.5
union
select sal, rank_sal from rank_tab
where rank_sal＜=0.5
)
,
medianSal (medianSal) as
(
select (max(sal)+min(sal))/2
from inter),
deviationSal (Sal,deviationSal) as
(select Sal,abs(sal-medianSal)
from emp join medianSal
on 1=1
)
,
distDevSal (sal,deviationSal,distDeviationSal) as
(
select sal,deviationSal,cume_dist() over (order by deviationSal)
from deviationSal
),
DevInter (DevInter, sal) as
(
select min(deviationSal), sal
from distDevSal
where distDeviationSal ＞= 0.5
union
select max(DeviationSal), sal
from distDevSal
where distDeviationSal ＜= 0.5
),
MAD (MedianAbsoluteDeviance) as
(
select abs(emp.sal-(min(devInter)+max(devInter))/2)
from emp join DevInter on 1=1
)
select emp.sal,MedianAbsoluteDeviance,
(emp.sal-deviationSal)/MedianAbsoluteDeviance
from (emp join MAD on 1=1)
         join deviationSal on emp.sal=deviationSal.sal

6.10.2. 没有提供函数 MEDIAN 或 PERCENTILE_CONT

6.10.3. 需要在 CTE 中使用两个子查询

6.11. 计算中值，然后计算这个中值与各个值的绝对偏差的中值，即绝对中位差

6.12. 使用查询来找出每个值相对于中值的偏差与绝对中位差的比值

6.13. 可以像使用标准偏差那样使用这些比值了

6.13.1. 如果一个值相对于中值的偏差是绝对中位差的 3 倍以上，就可以认为它是异常值

6.14. 优点

6.14.1. 即便数据不呈正态分布，它依然有效

6.14.2. 即便数据分布不平衡，绝对中位差给出的答案依然合理

7. 使用本福特法则查找反常数据

7.1. 检测不像异常值那样显而易见的反常数据的一种方式是查看数字位的出现频率，这种频率通常符合本福特法则

7.2. 本福特法则最常用于检测数据造假

7.2.1. 在数据集中人为地添加伪造的数字

7.2.2. 用于检测不符合预期规律的数据

7.3. 本福特法则

7.3.1. 计算数字位的期望分布

7.3.2. 将其与实际分布进行比较

7.4. sql

with
FirstDigits (FirstDigit)
as
(select left(cast(SAL as CHAR),1) as FirstDigit
        from emp),
TotalCount (Total)
as
 (select count(*)
  from emp),
ExpectedBenford (Digit,Expected)
as
  (select ID,(log10(ID + 1) - log10(ID)) as expected
    from t10
    where ID ＜ 10)
select count(FirstDigit),Digit,
coalesce(count(*)/Total,0) as ActualProportion,Expected
From FirstDigits
     Join TotalCount
     Right Join ExpectedBenford
     on FirstDigits.FirstDigit=ExpectedBenford.Digit
group by Digit
order by Digit

7.5. 最终的结果集包含 4 列数据，分别是第一位的预测频率、第一位的实际频率、本福特法则预测的前几位的频率，以及前几位的实际频率

8. SQL 不像专用包 SAS、统计编程语言 R 和 Python 统计库那样提供了完备的统计工具

9. SQL Server

9.1. PIVOT操作符

9.1.1. sql

DEPT_10    DEPT_20    DEPT_30    DEPT_40
------- ---------- ---------- ----------
      3          5          6          0

9.1.2. sql

select [10] as dept_10,
       [20] as dept_20,
       [30] as dept_30,
       [40] as dept_40
  from (select deptno, empno from emp) driver
 pivot (
    count(driver.empno)
    for driver.deptno in ( [10],[20],[30],[40] )
 ) as empPivot

9.1.2.1. 不使用CASE表达式或额外的连接操作

9.2. UNPIVOT操作符

9.2.1. sql

ACCOUNTING   RESEARCH      SALES OPERATIONS
---------- ---------- ---------- ----------
         3          5          6          0

9.2.2. sql

DNAME                 CNT
-------------- ----------
ACCOUNTING              3
RESEARCH                5
SALES                   6
OPERATIONS              0

9.2.3. sql

select DNAME, CNT
   from (
     select [ACCOUNTING] as ACCOUNTING,
            [SALES]      as SALES,
            [RESEARCH]   as RESEARCH,
            [OPERATIONS] as OPERATIONS
       from (
               select d.dname, e.empno
                 from emp e,dept d
                where e.deptno=d.deptno
            ) driver
      pivot (
        count(driver.empno)
        for driver.dname in ([ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS])
      ) as empPivot
 ) new_driver
 unpivot (cnt for dname in (ACCOUNTING,SALES,RESEARCH,OPERATIONS)
 ) as un_pivot