1. 關聯子查詢 1.1. 關聯子查詢和自連接在很多時候都是等價的 1.2. 使用SQL進行行間比較時，發揮主要作用的技術是關聯子查詢，特別是與自連接相結合的“自關聯子查詢” 1.3. 缺點 1.3.1. 代碼的可讀性不好 1.3.1.1. 特別是在計算累計值和移動平均值的例題里，與聚合一起使用後， ...
1. 關聯子查詢
1.1. 關聯子查詢和自連接在很多時候都是等價的
1.2. 使用SQL進行行間比較時，發揮主要作用的技術是關聯子查詢，特別是與自連接相結合的“自關聯子查詢”
1.3. 缺點
-
1.3.1. 代碼的可讀性不好
- 1.3.1.1. 特別是在計算累計值和移動平均值的例題里，與聚合一起使用後，其內部處理過程非常難理解
-
1.3.2. 性能不好
- 1.3.2.1. 特別是在SELECT子句里使用標量子查詢時，性能可能會變差
2. 增長、減少、維持現狀
2.1. 使用基於時間序列的表進行時間序列分析
2.2. 示例
- 2.2.1. --求與上一年營業額一樣的年份(1)：使用關聯子查詢
SELECT year, sale
FROM Sales S1
WHERE sale = (SELECT sale
FROM Sales S2
WHERE S2.year = S1.year -1)
ORDER BY year;
-
2.2.2. S2.year = S1.year -1這個條件起到了將要比較的數據偏移一行的作用
-
2.2.3. --求與上一年營業額一樣的年份(2)：使用自連接
SELECT S1.year, S1.sale
FROM Sales S1,
Sales S2
WHERE S2.sale = S1.sale
AND S2.year = S1.year -1
ORDER BY year;
3. 用列表展示與上一年的比較結果
3.1. 示例
- 3.1.1. --求出是增長了還是減少了，抑或是維持現狀(1)：使用關聯子查詢
SELECT S1.year, S1.sale,
CASE WHEN sale =
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year -1) THEN'→'--持平
WHEN sale ＞
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year -1) THEN'↑'--增長
WHEN sale ＜
(SELECT sale
FROM Sales S2
WHERE S2.year = S1.year -1) THEN'↓'--減少
ELSE'—'END AS var
FROM Sales S1
ORDER BY year;
- 3.1.2. --求出是增長了還是減少了，抑或是維持現狀(2)：使用自連接查詢（最早的年份不會出現在結果里）
SELECT S1.year, S1.sale,
CASE WHEN S1.sale = S2.sale THEN'→'
WHEN S1.sale ＞ S2.sale THEN'↑'
WHEN S1.sale ＜ S2.sale THEN'↓'
ELSE'—'END AS var
FROM Sales S1, Sales S2
WHERE S2.year = S1.year -1
ORDER BY year;
4. 時間軸有間斷時
4.1. 和過去最臨近的時間進行比較
4.2. 示例
- 4.2.1. --查詢與過去最臨近的年份營業額相同的年份
SELECT year, sale
FROM Sales2 S1
WHERE sale =
(SELECT sale
FROM Sales2 S2
WHERE S2.year =
(SELECT MAX(year) --條件2：在滿足條件1的年份中，年份最早的一個
FROM Sales2 S3
WHERE S1.year ＞ S3.year)) --條件1：與該年份相比是過去的年份
ORDER BY year;
- 4.2.2. 自連接版本
SELECT S1.year AS year,
S1.year AS year
FROM Sales2 S1, Sales2 S2
WHERE S1.sale = S2.sale
AND S2.year = (SELECT MAX(year)
FROM Sales2 S3
WHERE S1.year ＞ S3.year)
ORDER BY year;
- 4.2.3. --求每一年與過去最臨近的年份之間的營業額之差(1)：結果里不包含最早的年份
SELECT S2.year AS pre_year,
S1.year AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff
FROM Sales2 S1, Sales2 S2
WHERE S2.year = (SELECT MAX(year)
FROM Sales2 S3
WHERE S1.year ＞ S3.year)
ORDER BY now_year;
- 4.2.4. --求每一年與過去最臨近的年份之間的營業額之差(1)：結果里不包含最早的年份
SELECT S2.year AS pre_year,
S1.year AS now_year,
S2.sale AS pre_sale,
S1.sale AS now_sale,
S1.sale - S2.sale AS diff
FROM Sales2 S1, Sales2 S2
WHERE S2.year = (SELECT MAX(year)
FROM Sales2 S3
WHERE S1.year ＞ S3.year)
ORDER BY now_year;
- 4.2.5. 使用極值函數時會發生排序
5. 移動累計值和移動平均值
5.1. 示例
- 5.1.1. --求累計值：使用視窗函數
SELECT prc_date, prc_amt,
SUM(prc_amt) OVER (ORDER BY prc_date) AS onhand_amt
FROM Accounts;
-
5.1.2. 引入視窗函數的目的原本就是解決這類問題，因此這裡的代碼非常簡潔
- 5.1.2.1. 如果選用的資料庫支持視窗函數，也可以考慮使用視窗函數
-
5.1.3. 從性能方面來看，表的掃描和數據排序也都只進行了一次
- 5.1.3.1. 依賴於具體的資料庫的
-
5.1.4. --求累計值：使用馮·諾依曼型遞歸集合
SELECT prc_date, A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date ＞= A2.prc_date ) AS onhand_amt
FROM Accounts A1
ORDER BY prc_date;
- 5.1.5. --求移動累計值(1)：使用視窗函數
SELECT prc_date, prc_amt,
SUM(prc_amt) OVER (ORDER BY prc_date
ROWS 2 PRECEDING) AS onhand_amt
FROM Accounts;
- 5.1.6. --求移動累計值(2)：不滿3行的時間區間也輸出
SELECT prc_date, A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date ＞= A2.prc_date
AND (SELECT COUNT(＊)
FROM Accounts A3
WHERE A3.prc_date
BETWEEN A2.prc_date AND A1.prc_date ) ＜= 3 )
AS mvg_sum
FROM Accounts A1
ORDER BY prc_date;
-
5.1.7. A3.prc_date在以A2.prc_date為起點，以A1.prc_date為終點的區間內移動
-
5.1.8. --移動累計值(3)：不滿3行的區間按無效處理
SELECT prc_date, A1.prc_amt,
(SELECT SUM(prc_amt)
FROM Accounts A2
WHERE A1.prc_date ＞= A2.prc_date
AND (SELECT COUNT(＊)
FROM Accounts A3
WHERE A3.prc_date
BETWEEN A2.prc_date AND A1.prc_date ) ＜= 3
HAVING COUNT(＊) =3) AS mvg_sum --不滿3行數據的不顯示
FROM Accounts A1
ORDER BY prc_date;
5.2. 基本思路是使用馮·諾依曼型遞歸集合
6. 查詢重疊的時間區間
6.1. 示例
- 6.1.1. --求重疊的住宿期間
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS
(SELECT ＊
FROM Reservations R2
WHERE R1.reserver ＜＞ R2.reserver --與自己以外的客人進行比較
AND ( R1.start_date BETWEEN R2.start_date AND R2.end_date
--條件(1)：自己的入住日期在他人的住宿期間內
OR R1.end_date BETWEEN R2.start_date AND R2.end_date));
--條件(2)：自己的離店日期在他人的住宿期間內
- 6.1.2. --升級版：把完全包含別人的住宿期間的情況也輸出
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS
(SELECT ＊
FROM Reservations R2
WHERE R1.reserver ＜＞ R2.reserver
AND ( ( R1.start_date BETWEEN R2.start_date
AND R2.end_date
OR R1.end_date BETWEEN R2.start_date
AND R2.end_date)
OR ( R2.start_date BETWEEN R1.start_date
AND R1.end_date
AND R2.end_date BETWEEN R1.start_date
AND R1.end_date)));
