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

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

1. select max(HeadCount) as HighestJobHeadCount from
2. (select job,count(empno) as HeadCount
3. from emp
4. group by job) head_count_tab

复制代码

3. 通用表表达式

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

3.1.1. 让 SQL 支持递归

3.2. CTE 的可读性更高

3.2.1. sql

1. with head_count_tab (job,HeadCount) as
2. (select job,count(empno)
3. from emp
4. group by job)
5. select max(HeadCount) as HighestJobHeadCount
6. from head_count_tab

复制代码

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

3.3.1. sql

1. with recursive workingTable (fibNum, NextNumber, index1)
2. as
3. (select 0,1,1
4. union all
5. select fibNum+nextNumber,fibNUm,index1+1
6. from anchor
7. where index1＜20)
8. 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

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

复制代码

4.4.1.  sql

1. select an1.a_name as name1, an2.a_name as name2,
2. SOUNDEX(an1.a_name) as Soundex_Name
3. from author_names an1
4. join author_names an2
5. on (SOUNDEX(an1.a_name)=SOUNDEX(an2.a_name)
6. and an1.a_name not like an2.a_name)

复制代码

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

5.1. sql

1. select emp_id, text
2.   from employee_comment
3. EMP_ID     TEXT
4. ---------- ------------------------------------------------------------
5. 7369       126 Varnum, Edmore MI 48829, 989 313-5351
6. 7499       1105 McConnell Court
7.            Cedar Lake MI 48812
8.            Home: 989-387-4321
9.            Cell: (237) 438-3333

复制代码

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

5.3. sql

1. select emp_id, text
2. from employee_comment
3. where regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')
4.   and regexp_like(
5.          regexp_replace(text,
6.             '[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}',''),
7.          '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')
8.     EMP_ID TEXT
9. ---------- ------------------------------------------------------------
10.   7369     126 Varnum, Edmore MI 48829, 989 313-5351
11.   7844     989-387.5359
12.   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

1. with
2. Deviation (Deviation)
3.   as
4. (select abs(sal-median(sal))
5. from emp),
6. MAD (MAD) as
7. (select median(Deviation)
8. from Deviation )
9. select abs(sal-median)/MAD, sal, ename, job
10. FROM MAD join emp

复制代码

6.6. SQL Server

6.6.1. sql

1. with median (median)
2. as
3. (select distinct percentile_cont(0.5) within group(order by sal)
4.         over()
5. from emp),
6. Deviation (Deviation)
7.   as
8. (Select abs(sal-median)
9. from emp join median on 1=1),
10. MAD (MAD) as
11. (select DISTINCT PERCENTILE_CONT(0.5) within group(order by deviation) over()
12. from Deviation )
13. select abs(sal-median)/MAD, sal, ename, job
14. from MAD join emp on 1=1

复制代码

6.7. DB2

6.8. PostgreSQL

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

6.9.1. sql

1. with median (median)
2. as
3. (select percentile_cont(0.5) within group(order by sal)
4. from emp),
5. devtab (deviation)
6.   as
7. (select abs(sal-median)
8. from emp join median),
9. MedAbsDeviation (MAD) as
10. (select percentile_cont (0.5) within group(order by deviation)
11. from devtab)
12. select abs(sal-median)/MAD, sal, ename, job
13. FROM MedAbsDeviation join emp

复制代码

6.10. MySQL

6.10.1. sql

1. with rank_tab (sal, rank_sal) as (
2. select sal, cume_dist() over (order by sal)
3. from emp),
4. inter as
5. (
6. select sal, rank_sal from rank_tab
7. where rank_sal＞=0.5
8. union
9. select sal, rank_sal from rank_tab
10. where rank_sal＜=0.5
11. )
12. ,
13. medianSal (medianSal) as
14. (
15. select (max(sal)+min(sal))/2
16. from inter),
17. deviationSal (Sal,deviationSal) as
18. (select Sal,abs(sal-medianSal)
19. from emp join medianSal
20. on 1=1
21. )
22. ,
23. distDevSal (sal,deviationSal,distDeviationSal) as
24. (
25. select sal,deviationSal,cume_dist() over (order by deviationSal)
26. from deviationSal
27. ),
28. DevInter (DevInter, sal) as
29. (
30. select min(deviationSal), sal
31. from distDevSal
32. where distDeviationSal ＞= 0.5
33. union
34. select max(DeviationSal), sal
35. from distDevSal
36. where distDeviationSal ＜= 0.5
37. ),
38. MAD (MedianAbsoluteDeviance) as
39. (
40. select abs(emp.sal-(min(devInter)+max(devInter))/2)
41. from emp join DevInter on 1=1
42. )
43. select emp.sal,MedianAbsoluteDeviance,
44. (emp.sal-deviationSal)/MedianAbsoluteDeviance
45. from (emp join MAD on 1=1)
46.          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

1. with
2. FirstDigits (FirstDigit)
3. as
4. (select left(cast(SAL as CHAR),1) as FirstDigit
5.         from emp),
6. TotalCount (Total)
7. as
8. (select count(*)
9.   from emp),
10. ExpectedBenford (Digit,Expected)
11. as
12.   (select ID,(log10(ID + 1) - log10(ID)) as expected
13.     from t10
14.     where ID ＜ 10)
15. select count(FirstDigit),Digit,
16. coalesce(count(*)/Total,0) as ActualProportion,Expected
17. From FirstDigits
18.      Join TotalCount
19.      Right Join ExpectedBenford
20.      on FirstDigits.FirstDigit=ExpectedBenford.Digit
21. group by Digit
22. order by Digit

复制代码

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

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

9. SQL Server

9.1. PIVOT操作符

9.1.1. sql

1. DEPT_10    DEPT_20    DEPT_30    DEPT_40
2. ------- ---------- ---------- ----------
3.       3          5          6          0

复制代码

9.1.2. sql

1. select [10] as dept_10,
2.        [20] as dept_20,
3.        [30] as dept_30,
4.        [40] as dept_40
5.   from (select deptno, empno from emp) driver
6. pivot (
7.     count(driver.empno)
8.     for driver.deptno in ( [10],[20],[30],[40] )
9. ) as empPivot

复制代码

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

9.2. UNPIVOT操作符

9.2.1. sql

1. ACCOUNTING   RESEARCH      SALES OPERATIONS
2. ---------- ---------- ---------- ----------
3.          3          5          6          0

复制代码

9.2.2. sql

1. DNAME                 CNT
2. -------------- ----------
3. ACCOUNTING              3
4. RESEARCH                5
5. SALES                   6
6. OPERATIONS              0

复制代码

9.2.3.  sql

1. select DNAME, CNT
2.    from (
3.      select [ACCOUNTING] as ACCOUNTING,
4.             [SALES]      as SALES,
5.             [RESEARCH]   as RESEARCH,
6.             [OPERATIONS] as OPERATIONS
7.        from (
8.                select d.dname, e.empno
9.                  from emp e,dept d
10.                 where e.deptno=d.deptno
11.             ) driver
12.       pivot (
13.         count(driver.empno)
14.         for driver.dname in ([ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS])
15.       ) as empPivot
16. ) new_driver
17. unpivot (cnt for dname in (ACCOUNTING,SALES,RESEARCH,OPERATIONS)
18. ) as un_pivot

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！