10 個(gè)不為人知的 SQL 技巧
2019-09-19 來源：raincent
從早期開始，編程語言設(shè)計(jì)者就有這樣的愿望：設(shè)計(jì)一種語言，在這種語言中，告訴機(jī)器我們想要的結(jié)果是什么(WHAT)，而不是如何(HOW)獲得結(jié)果。SQL 可以做到這點(diǎn)。在 SQL 中，我們不關(guān)心數(shù)據(jù)庫是如何檢索信息的，就可以得到結(jié)果。本文介紹了使用聲明式 SQL10 個(gè)不為人知的技巧。
介紹
為了理解這 10 個(gè) SQL 技巧的價(jià)值，首先需要了解下 SQL 語言的上下文。為什么我要在 Java 會(huì)議上討論 SQL 呢?(我可能是唯一一個(gè)在 Java 會(huì)議上討論 SQL 的了)下面講下為什么：
從早期開始，編程語言設(shè)計(jì)者就有這種的愿望：設(shè)計(jì)一種語言，在這種語言中，告訴機(jī)器我們想要的結(jié)果是什么(WHAT)，而不是如何(HOW)獲得結(jié)果。例如，在 SQL 中，我們告訴計(jì)算機(jī)我們要“連接”(聯(lián)接)用戶表和地址表，并查找居住在瑞士的用戶。我們不關(guān)心數(shù)據(jù)庫將如何檢索這些信息(比如，是先加載用戶表呢，還是先加載地址表?這兩個(gè)表是在嵌套循環(huán)中聯(lián)接呢，還是使用 hashmap 聯(lián)接?是先將所有數(shù)據(jù)加載到內(nèi)存中，然后再過濾出瑞士用戶呢，還是先加載瑞士地址?等等。)
與每個(gè)抽象一樣，我們?nèi)匀恍枰私鈹?shù)據(jù)庫背后的基本原理，以幫助數(shù)據(jù)庫在查詢時(shí)做出正確的決策。例如，做如下事情是有必要：
在表之間建立合適的外鍵(這能告訴數(shù)據(jù)庫每個(gè)地址都有一個(gè)對(duì)應(yīng)的用戶)
在搜索字段上添加索引：國家(這能告訴數(shù)據(jù)庫可以在 O(log N) 而不是 O(N) 的復(fù)雜度內(nèi)查找到特定的國家 )。
但是，一旦數(shù)據(jù)庫和應(yīng)用程序變得成熟之后，我們就可以把所有重要的元數(shù)據(jù)放在適當(dāng)?shù)奈恢蒙狭�，并且只需專注于業(yè)務(wù)邏輯即可。下面的 10 個(gè)技巧展示了，僅用幾行聲明式 SQL 就能編寫強(qiáng)大驚人功能的能力，它不僅可以生成簡單的輸出，也可以生成復(fù)雜的輸出。
1. 一切都是表
這是一條最微不足道的技巧，甚至不能說是真正的技巧，但它是全面理解 SQL 的基礎(chǔ)：一切都是表：當(dāng)我們看到這樣的 SQL 語句時(shí)：
SELECT *
FROM person
……我們很快就能在 FROM 子句中找到 person 表。很好，那是一張表。但我們能意識(shí)到整個(gè)語句也是一張表嗎?例如，我們可以這樣寫：
SELECT *
FROM (
SELECT *
FROM person
) t
現(xiàn)在，我們已經(jīng)創(chuàng)建了一張所謂的“派生表”，即 FROM 子句中的嵌套 SELECT 語句。
這是微不足道的，但是如果仔細(xì)想想，它是相當(dāng)優(yōu)雅的。我們還可以在某些數(shù)據(jù)庫(比如 PostgreSQL、SQL Server)中使用 VALUES() 構(gòu)造函數(shù)來創(chuàng)建臨時(shí)內(nèi)存表：
SELECT *
FROM (
VALUES(1),(2),(3)
) t(a)
臨時(shí)表就這樣產(chǎn)生了：
a
—
1
2
3
如果對(duì)應(yīng)的數(shù)據(jù)庫不支持該子句，則可以回到使用派生表上，比如，在 Oracle 中：
SELECT *
FROM (
SELECT 1 AS a FROM DUAL UNION ALL
SELECT 2 AS a FROM DUAL UNION ALL
SELECT 3 AS a FROM DUAL
) t
既然我們已經(jīng)看到了 VALUES() 和派生表實(shí)際上是相同的，那么從概念上，我們回顧一下 INSERT 語句，它有兩種類型：
-- SQL Server, PostgreSQL, some others:
INSERT INTO my_table(a)
VALUES(1),(2),(3);
-- Oracle, many others:
INSERT INTO my_table(a)
SELECT 1 AS a FROM DUAL UNION ALL
SELECT 2 AS a FROM DUAL UNION ALL
SELECT 3 AS a FROM DUAL
在 SQL 中，一切都是表。當(dāng)您在表中插入行時(shí)，實(shí)際上并不是插入單獨(dú)的行。是插入整張表。在大多數(shù)情況下，大部分人只是碰巧插入了一張單行表，因此沒有意識(shí)到 INSERT 真正做了什么。
一切都是表。在 PostgreSQL 中，甚至函數(shù)都是表：
SELECT *
FROM substring('abcde', 2, 3)
上面語句的結(jié)果是：
substring
———
bcd
如果你正在使用 Java 編程，那么可以使用 Java 8 Stream API 來做進(jìn)一步的類比�？紤]如下等價(jià)概念：
TABLE : Stream<Tuple<..>>
SELECT : map()
DISTINCT : distinct()
JOIN : flatMap()
WHERE / HAVING : filter()
GROUP BY : collect()
ORDER BY : sorted()
UNION ALL : concat()
在 Java 8 中，“一切都是流”(至少在你開始使用流時(shí)是這樣)。無論如何轉(zhuǎn)換流，例如，使用 map() 或 filter() 轉(zhuǎn)換，結(jié)果類型始終都是流。
我們寫了一篇完整的文章來更深入地解釋這一點(diǎn)，并將 Stream API 與 SQL 進(jìn)行了對(duì)比： Common SQL C lauses and Their Equivalents in Java 8 Streams
如果你正在尋找“更好的流”(即，具有更多 SQL 語義的流)，請(qǐng)查看 jOOλ，它一個(gè)將 SQL 窗口函數(shù)引入到 Java 中的開源庫。
2. 使用遞歸 SQL 生成數(shù)據(jù)
公共表表達(dá)式(Common Table Expressions ，CTE，在 Oracle 中也叫做子查詢分解)，它是在 SQL 中聲明變量的唯一方法(除了模糊 WINDOW 子句之外，WINDOW 子句也只有在 PostgreSQL 和 Sybase SQL 中可用)。
這是一個(gè)功能強(qiáng)大的概念。非常強(qiáng)大�？紤]如下聲明：
-- 表變量
WITH
t1(v1, v2) AS (SELECT 1, 2),
t2(w1, w2) AS (
SELECT v1 * 2, v2 * 2
FROM t1
)
SELECT *
FROM t1, t2
它的結(jié)果是：
v1 v2 w1 w2
-----------------
1 2 2 4
使用簡單的 WITH 子句，我們可以指定一系列表變量(請(qǐng)記�。阂磺卸际潜�)，這些變量甚至可以是相互依賴的。
這很容易理解。它已經(jīng)使得 CTE(Common Table Expressions)非常有用了，但是，真正了不起的是，它們還允許遞歸!考慮如下 PostgreSQL 示例：
WITH RECURSIVE t(v) AS (
SELECT 1 -- 種子行
UNION ALL
SELECT v + 1 -- 遞歸
FROM t
)
SELECT v
FROM t
LIMIT 5
它的結(jié)果是：
v
—
1
2
3
4
5
它是如何工作的呢?一旦你看懂了一些關(guān)鍵詞，它就相對(duì)容易了。我們定義了一個(gè)公共表表達(dá)式，它恰好有兩個(gè) UNION ALL 子查詢。
第一個(gè) UNION ALL 子查詢是我們通常所說的“種子行”。它“播種”(初始化)遞歸。它可以生成一行或多行，稍后我們將在這些行上遞歸。記�。阂磺卸际潜�，所以遞歸將發(fā)生在整張表上，而不是單個(gè)行 / 值上。
第二個(gè) UNION ALL 子查詢?cè)诎l(fā)生遞歸的地方。如果你仔細(xì)觀察，會(huì)發(fā)現(xiàn)它從 t 中選擇。也就是說，允許第二個(gè)子查詢從我們即將聲明的 CTE 中遞歸地選擇。因此，它還可以訪問使用它的 CTE 聲明的列 v。
在我們的示例中，我們使用行 (1) 對(duì)遞歸進(jìn)行種子處理，然后通過添加 v + 1 來進(jìn)行遞歸。最后通過設(shè)置 LIMIT 5 來終止遞歸(需要謹(jǐn)防潛在的無限遞歸 ，就像使用 Java 8 的流一樣)。
附注：圖靈完備
遞歸 CTE 使得 SQL:1999 圖靈完備，這意味著任何程序都可以用 SQL 編寫! (如果你夠瘋狂的話)
一個(gè)經(jīng)常出現(xiàn)在博客上的令人印象深刻的例子是：Mandelbrot 集，如 http://explainextended.com/2013/12/31/happy-new-year-5/ 所示。
WITH RECURSIVE q(r, i, rx, ix, g) AS (
SELECT r::DOUBLE PRECISION * 0.02, i::DOUBLE PRECISION * 0.02,
.0::DOUBLE PRECISION , .0::DOUBLE PRECISION, 0
FROM generate_series(-60, 20) r, generate_series(-50, 50) i
UNION ALL
SELECT r, i, CASE WHEN abs(rx * rx + ix * ix) &amp;lt;= 2 THEN rx * rx - ix * ix END + r,
CASE WHEN abs(rx * rx + ix * ix) &amp;lt;= 2 THEN 2 * rx * ix END + i, g + 1
FROM q
WHERE rx IS NOT NULL AND g &amp;lt; 99
)
SELECT array_to_string(array_agg(s ORDER BY r), '')
FROM (
SELECT i, r, substring(' .:-=+*#%@', max(g) / 10 + 1, 1) s
FROM q
GROUP BY i, r
) q
GROUP BY i
ORDER BY i
在 PostgreSQL 上運(yùn)行上面的代碼，我們將得到如下結(jié)果：
復(fù)制代碼 .-.:-.......==..*.=.::-@@@@@:::.:.@..*-. =. ...=...=...::+%.@:@@@@@@@@@@@@@+*#=.=:+-. ..- .:.:=::*....@@@@@@@@@@@@@@@@@@@@@@@@=@@.....::...:. .-.:-.......==..*.=.::-@@@@@:::.:.@..*-. =.
...=...=...::+%.@:@@@@@@@@@@@@@+*#=.=:+-. ..-
.:.:=::*....@@@@@@@@@@@@@@@@@@@@@@@@=@@.....::...:.
...*@@@@=.@:@@@@@@@@@@@@@@@@@@@@@@@@@@=.=....:...::.
.::@@@@@:-@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@..-:@=*:::.
.-@@@@@-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.=@@@@=..:
...@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@@@@@:..
....:-*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@::
.....@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-..
.....@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-:...
.--:+.@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@...
.==@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-..
..+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-#.
...=+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..
-.=-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..:
.*%:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@-
. ..:... ..-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
.............. ....-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@%@=
.--.-.....-=.:..........::@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..
..=:-....=@+..=.........@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:.
.:+@@::@==@-*:%:+.......:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.
::@@@-@@@@@@@@@-:=.....:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:
.:@@@@@@@@@@@@@@@=:.....%@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
.:@@@@@@@@@@@@@@@@@-...:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:-
:@@@@@@@@@@@@@@@@@@@-..%@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.
%@@@@@@@@@@@@@@@@@@@-..-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.
@@@@@@@@@@@@@@@@@@@@@::+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@+
@@@@@@@@@@@@@@@@@@@@@@:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..
@@@@@@@@@@@@@@@@@@@@@@-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.
印象是不是非常深刻?
3. 累計(jì)計(jì)算
這個(gè)博客有很多累計(jì)計(jì)算的示例。它們是學(xué)習(xí)高級(jí) SQL 最有教育意義的示例之一，因?yàn)橹辽儆惺畮追N方法可以實(shí)現(xiàn)累計(jì)計(jì)算。
在概念上，累計(jì)計(jì)算很容易理解。
在 Microsoft Excel 中，我們只需計(jì)算前兩個(gè)(或后兩個(gè))值的和(或差)，然后使用可用的十字光標(biāo)將該公式拉過整個(gè)電子表格。我們?cè)陔娮颖砀裰?ldquo;運(yùn)行”這個(gè)總數(shù)。即一個(gè)“累計(jì)”。
在 SQL 中，最好的方法是使用窗口函數(shù)，這也是該博客多次討論的另一主題。
窗口函數(shù)是一個(gè)功能強(qiáng)大的概念，一開始可能不太容易理解，但事實(shí)上，它們非常非常簡單：
窗口函數(shù)是在相對(duì)當(dāng)前行而言的一個(gè)子集上的聚合/排序，當(dāng)前行由 SELECT 轉(zhuǎn)換。
就是這樣簡單!
它本質(zhì)上的意思是，一個(gè)窗口函數(shù)可以對(duì)當(dāng)前行的“上”或“下”行執(zhí)行計(jì)算。然而，與普通的聚合和 GROUP BY 不同，它們不轉(zhuǎn)換行，這使得它們非常有用。
語法總結(jié)如下，個(gè)別部分是可選的：
function(...) OVER (
PARTITION BY ...
ORDER BY ...
ROWS BETWEEN ... AND ...
)
因此，我們可以使用任何類型的函數(shù)(稍后我們將介紹此類函數(shù)的示例)，后面緊跟其后的是 OVER() 子句，該子句指定窗口。即，這個(gè) OVER() 子句定義如下：
PARTITION ：窗口只考慮與當(dāng)前行在同一分區(qū)中的行
ORDER：窗口排序可以獨(dú)立于我們選擇的內(nèi)容
ROWS(或 RANGE )框架定義：窗口可以被限制在固定數(shù)量的行的“前面”和“后面”。
這就是窗口函數(shù)的全部功能。
那么它又是如何幫助我們累計(jì)計(jì)算的呢?考慮以下數(shù)據(jù)：
| ID | VALUE_DATE | AMOUNT | BALANCE |
|------|------------|--------|------------|
| 9997 | 2014-03-18 | 99.17 | 19985.81 |
| 9981 | 2014-03-16 | 71.44 | 19886.64 |
| 9979 | 2014-03-16 | -94.60 | 19815.20 |
| 9977 | 2014-03-16 | -6.96 | 19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |
假設(shè) BALANCE 是我們想從 AMOUNT 中計(jì)算出來的
直觀視覺上，我們可以立即看出以下情況是成立的：
因此，使用簡單的英語，任何余額都可以用以下偽 SQL 表示：
TOP_BALANCE – SUM(AMOUNT) OVER (
“all the rows on top of the current row”
)
在真正的 SQL 中，可以這樣寫：
SUM(t.amount) OVER (
PARTITION BY t.account_id
ORDER BY t.value_date DESC,
t.id DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING
)
說明：
分區(qū)計(jì)算每個(gè)銀行帳戶的總和，而不是整個(gè)數(shù)據(jù)集的總和
排序?qū)⒋_保事務(wù)在求和之前(在分區(qū)內(nèi))是有序的
在求和之前，rows 子句只考慮前面的行(在分區(qū)內(nèi)，給定順序)
所有這些都發(fā)生在內(nèi)存中的數(shù)據(jù)集上，該數(shù)據(jù)集由我們通過 FROM … WHERE 子句選擇出來，因此速度非�？�。
插曲
在我們開始討論其他精彩技巧之前，先考慮一下：我們已經(jīng)學(xué)習(xí)了
(遞歸)公共表表達(dá)式(CTE)
窗口函數(shù)
這兩個(gè)功能都是：
非常棒
功能極其強(qiáng)大
聲明式
SQL 標(biāo)準(zhǔn)的一部分
適用于大多數(shù)流行的 RDBMS(除了 MySQL)
非常重要的構(gòu)建塊
如果能從本文中得出什么結(jié)論，那就是我們應(yīng)該完全了解現(xiàn)代 SQL 的這兩個(gè)構(gòu)建塊。為什么呢?因?yàn)椋?/p>
4. 查找最大無間隔序列
Stack Overflow 有一個(gè)非常好的功能：徽章，它可以激勵(lì)人們盡可能長時(shí)間地呆在他們的網(wǎng)站上。
就規(guī)模而言，你可以看到我有多少徽章。
你要怎么計(jì)算這些徽章呢?讓我們看看“愛好者”和“狂熱者”。這些徽章是頒發(fā)給那些在他們平臺(tái)上連續(xù)停留一定時(shí)間的人。無論結(jié)婚紀(jì)念日或是妻子生日，你都必須登錄，否則計(jì)數(shù)器將再次從零開始。
當(dāng)我們進(jìn)行聲明式編程時(shí)，是不需要維護(hù)任何狀態(tài)和內(nèi)存計(jì)數(shù)器的�，F(xiàn)在，我們想用在線分析 SQL 的形式來表達(dá)這一點(diǎn)。即，考慮如下數(shù)據(jù)：
| LOGIN_TIME |
|---------------------|
| 2014-03-18 05:37:13 |
| 2014-03-16 08:31:47 |
| 2014-03-16 06:11:17 |
| 2014-03-16 05:59:33 |
| 2014-03-15 11:17:28 |
| 2014-03-15 10:00:11 |
| 2014-03-15 07:45:27 |
| 2014-03-15 07:42:19 |
| 2014-03-14 09:38:12 |
那沒什么用。我們從時(shí)間戳中刪除小時(shí)。這很簡單：
SELECT DISTINCT
cast(login_time AS DATE) AS login_date
FROM logins
WHERE user_id = :user_id
得到的結(jié)果是：
| LOGIN_DATE |
|------------|
| 2014-03-18 |
| 2014-03-16 |
| 2014-03-15 |
| 2014-03-14 |
現(xiàn)在，我們已經(jīng)學(xué)習(xí)了窗口函數(shù)，我們只需為每個(gè)日期添加一個(gè)簡單的行數(shù)即可：
SELECT
login_date,
row_number() OVER (ORDER BY login_date)
FROM login_dates
結(jié)果如下：
| LOGIN_DATE | RN |
|------------|----|
| 2014-03-18 | 4 |
| 2014-03-16 | 3 |
| 2014-03-15 | 2 |
| 2014-03-14 | 1 |
還是很容易的吧�，F(xiàn)在，如果我們不單獨(dú)選擇這些值，而是減去它們，會(huì)發(fā)生什么?
SELECT
login_date -
row_number() OVER (ORDER BY login_date)
FROM login_dates
將會(huì)得到如下結(jié)果：
| LOGIN_DATE | RN | GRP |
|------------|----|------------|
| 2014-03-18 | 4 | 2014-03-14 |
| 2014-03-16 | 3 | 2014-03-13 |
| 2014-03-15 | 2 | 2014-03-13 |
| 2014-03-14 | 1 | 2014-03-13 |
真的。很有趣。所以，14–1=13，15–2=13，16–3=13，但是 18–4=14。沒有人能比 Doge 說得更好了：
有一個(gè)關(guān)于這種行為的簡單示例：
ROW_NUMBER() 沒有間隔，這就是它的定義
但是，我們的數(shù)據(jù)有間隔
所以，當(dāng)我們從一個(gè)非連續(xù)日期的“gapful”序列中減去一個(gè)連續(xù)整數(shù)的“gapless”序列時(shí)，我們將得到連續(xù)日期中每個(gè)“gapless”子序列的相同日期，并且它是一個(gè)新的日期，其中日期序列是有間隔的。
嗯。
這意味著我們現(xiàn)在可以簡單地 GROUP BY 該任意日期值了：
SELECT
min(login_date), max(login_date),
max(login_date) -
min(login_date) + 1 AS length
FROM login_date_groups
GROUP BY grp
ORDER BY length DESC
我們做到了。最大的連續(xù)無間隔序列被找到了：
| MIN | MAX | LENGTH |
|------------|------------|--------|
| 2014-03-14 | 2014-03-16 | 3 |
| 2014-03-18 | 2014-03-18 | 1 |
完整的查詢?nèi)缦拢?/p>
ITH
login_dates AS (
SELECT DISTINCT cast(login_time AS DATE) login_date
FROM logins WHERE user_id = :user_id
),
login_date_groups AS (
SELECT
login_date,
login_date - row_number() OVER (ORDER BY login_date) AS grp
FROM login_dates
)
SELECT
min(login_date), max(login_date),
max(login_date) - min(login_date) + 1 AS length
FROM login_date_groups
GROUP BY grp
ORDER BY length DESC
最后，沒那么難吧?當(dāng)然，最主要的是有了這個(gè)想法，但是查詢本身真的非常簡單優(yōu)雅。沒有比這更簡潔的方法來實(shí)現(xiàn)一些命令式算法了。
5. 求序列的長度
在前面，我們看到了一系列連續(xù)的值。這很容易處理，因?yàn)槲覀兛梢詾E用整數(shù)的連續(xù)性。如果一個(gè)“序列”的定義不那么直觀，而且除此之外，幾個(gè)序列包含相同的值呢?考慮以下數(shù)據(jù)，其中 LENGTH 是要計(jì)算的每個(gè)序列的長度：
| ID | VALUE_DATE | AMOUNT | LENGTH |
|------|------------|--------|------------|
| 9997 | 2014-03-18 | 99.17 | 2 |
| 9981 | 2014-03-16 | 71.44 | 2 |
| 9979 | 2014-03-16 | -94.60 | 3 |
| 9977 | 2014-03-16 | -6.96 | 3 |
| 9971 | 2014-03-15 | -65.95 | 3 |
| 9964 | 2014-03-15 | 15.13 | 2 |
| 9962 | 2014-03-15 | 17.47 | 2 |
| 9960 | 2014-03-15 | -3.55 | 1 |
| 9959 | 2014-03-14 | 32.00 | 1 |
是的，你猜對(duì)了。 “序列”是由連續(xù)(按 ID 排序)行且具有相同的 SIGN(AMOUNT) 這一事實(shí)來定義的。再次檢查如下的數(shù)據(jù)格式：
| ID | VALUE_DATE | AMOUNT | LENGTH |
|------|------------|--------|------------|
| 9997 | 2014-03-18 | +99.17 | 2 |
| 9981 | 2014-03-16 | +71.44 | 2 |
| 9979 | 2014-03-16 | -94.60 | 3 |
| 9977 | 2014-03-16 | - 6.96 | 3 |
| 9971 | 2014-03-15 | -65.95 | 3 |
| 9964 | 2014-03-15 | +15.13 | 2 |
| 9962 | 2014-03-15 | +17.47 | 2 |
| 9960 | 2014-03-15 | - 3.55 | 1 |
| 9959 | 2014-03-14 | +32.00 | 1 |
我們?cè)趺醋瞿?很“簡單”，首先，我們?nèi)サ羲械脑胍簦⑻砑恿硪粋�(gè)行號(hào)：
SELECT
id, amount,
sign(amount) AS sign,
row_number()
OVER (ORDER BY id DESC) AS rn
FROM trx
它的結(jié)果是：
| ID | AMOUNT | SIGN | RN |
|------|--------|------|----|
| 9997 | 99.17 | 1 | 1 |
| 9981 | 71.44 | 1 | 2 |
| 9979 | -94.60 | -1 | 3 |
| 9977 | -6.96 | -1 | 4 |
| 9971 | -65.95 | -1 | 5 |
| 9964 | 15.13 | 1 | 6 |
| 9962 | 17.47 | 1 | 7 |
| 9960 | -3.55 | -1 | 8 |
| 9959 | 32.00 | 1 | 9 |
現(xiàn)在，接下來的目標(biāo)是生成如下表：
| ID | AMOUNT | SIGN | RN | LO | HI |
|------|--------|------|----|----|----|
| 9997 | 99.17 | 1 | 1 | 1 | |
| 9981 | 71.44 | 1 | 2 | | 2 |
| 9979 | -94.60 | -1 | 3 | 3 | |
| 9977 | -6.96 | -1 | 4 | | |
| 9971 | -65.95 | -1 | 5 | | 5 |
| 9964 | 15.13 | 1 | 6 | 6 | |
| 9962 | 17.47 | 1 | 7 | | 7 |
| 9960 | -3.55 | -1 | 8 | 8 | 8 |
| 9959 | 32.00 | 1 | 9 | 9 | 9 |
在該表中，我們希望將行號(hào)值復(fù)制到序列“末”端的“LO”和序列“頂”端的“HI”中。為此，我們將使用神奇的 LEAD() 和 LAG()。LEAD() 可以訪問當(dāng)前行向下的第 n 行，而 LAG() 可以訪問當(dāng)前行向上的第 n 行。例如：
SELECT
lag(v) OVER (ORDER BY v),
v,
lead(v) OVER (ORDER BY v)
FROM (
VALUES (1), (2), (3), (4)
) t(v)
上述查詢生成的結(jié)果：
太棒了!記住，使用窗口函數(shù)，你可以對(duì)相對(duì)于當(dāng)前行的子集執(zhí)行排序或聚合。在 LEAD() 和 LAG() 的情況下，只要給定當(dāng)前行的偏移量，就可以訪問相對(duì)于當(dāng)前行的單個(gè)行。這在很多情況下都很有用。
繼續(xù)我們的“LO”和“HI”示例，我們可以簡單地這樣寫：
SELECT
trx.*,
CASE WHEN lag(sign)
OVER (ORDER BY id DESC) != sign
THEN rn END AS lo,
CASE WHEN lead(sign)
OVER (ORDER BY id DESC) != sign
THEN rn END AS hi,
FROM trx
……我們將“前一個(gè)”sign (lag(sign)) 與“當(dāng)前”sign (sign) 進(jìn)行比較。如果它們不同，我們將行號(hào)放到“LO”中，因?yàn)樗切蛄械南陆纭?/p>
然后我們比較“下一個(gè)”sign (lead(sign)) 和“當(dāng)前”sign (sign)。如果它們不同，我們將行號(hào)放到“HI”中，因?yàn)樗切蛄械纳辖纭?/p>
最后，進(jìn)行一些無聊的 NULL 處理來以確保一切正常，這樣就完成了:
SELECT -- With NULL handling...
trx.*,
CASE WHEN coalesce(lag(sign)
OVER (ORDER BY id DESC), 0) != sign
THEN rn END AS lo,
CASE WHEN coalesce(lead(sign)
OVER (ORDER BY id DESC), 0) != sign
THEN rn END AS hi,
FROM trx
下一步。我們希望“LO”和“HI”出現(xiàn)在所有行中，而不僅僅是在序列的“下界”和“上界”上。例如，像這樣：
| ID | AMOUNT | SIGN | RN | LO | HI |
|------|--------|------|----|----|----|
| 9997 | 99.17 | 1 | 1 | 1 | 2 |
| 9981 | 71.44 | 1 | 2 | 1 | 2 |
| 9979 | -94.60 | -1 | 3 | 3 | 5 |
| 9977 | -6.96 | -1 | 4 | 3 | 5 |
| 9971 | -65.95 | -1 | 5 | 3 | 5 |
| 9964 | 15.13 | 1 | 6 | 6 | 7 |
| 9962 | 17.47 | 1 | 7 | 6 | 7 |
| 9960 | -3.55 | -1 | 8 | 8 | 8 |
| 9959 | 32.00 | 1 | 9 | 9 | 9 |
我們使用的特性至少在 Redshift、Sybase SQL Anywhere、DB2、Oracle 中是可用的。我們使用的是“IGNORE NULLS”子句，它可以傳遞給某些窗口函數(shù)：
SELECT
trx.*,
last_value (lo) IGNORE NULLS OVER (
ORDER BY id DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS lo,
first_value(hi) IGNORE NULLS OVER (
ORDER BY id DESC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS hi
FROM trx
有很多關(guān)鍵詞!但本質(zhì)都是一樣的。從任何給定的“當(dāng)前”行中，我們查看所有“之前的值”(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，但忽略所有空值。從之前的值中，我們?nèi)∽詈笠粋�(gè)值，這就是我們的新“LO”值。換句話說，我們?nèi)?ldquo;最近的前一個(gè)”“LO”值。
“HI”也是一樣的。從任何給定的“當(dāng)前”行中，我們查看所有“后續(xù)值”(ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)，但忽略所有空值。從隨后的值中，我們?nèi)〉谝粋�(gè)值，這是我們的新“HI”值。換言之，我們?nèi)?ldquo;最近的下一個(gè)”“HI”值。
使用幻燈片解釋如下：
100% 正確，加上點(diǎn)無聊的 NULL 調(diào)整：
SELECT -- With NULL handling...
trx.*,
coalesce(last_value (lo) IGNORE NULLS OVER (
ORDER BY id DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW), rn) AS lo,
coalesce(first_value(hi) IGNORE NULLS OVER (
ORDER BY id DESC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING), rn) AS hi
FROM trx
我們做最后一個(gè)步驟，記住清除一個(gè)個(gè)的錯(cuò)誤：
SELECT
trx.*,
1 + hi - lo AS length
FROM trx
我們做到了，結(jié)果如下：
| ID | AMOUNT | SIGN | RN | LO | HI | LENGTH|
|------|--------|------|----|----|----|-------|
| 9997 | 99.17 | 1 | 1 | 1 | 2 | 2 |
| 9981 | 71.44 | 1 | 2 | 1 | 2 | 2 |
| 9979 | -94.60 | -1 | 3 | 3 | 5 | 3 |
| 9977 | -6.96 | -1 | 4 | 3 | 5 | 3 |
| 9971 | -65.95 | -1 | 5 | 3 | 5 | 3 |
| 9964 | 15.13 | 1 | 6 | 6 | 7 | 2 |
| 9962 | 17.47 | 1 | 7 | 6 | 7 | 2 |
| 9960 | -3.55 | -1 | 8 | 8 | 8 | 1 |
| 9959 | 32.00 | 1 | 9 | 9 | 9 | 1 |
完整的查詢語句如下：
WITH
trx1(id, amount, sign, rn) AS (
SELECT id, amount, sign(amount), row_number() OVER (ORDER BY id DESC)
FROM trx
),
trx2(id, amount, sign, rn, lo, hi) AS (
SELECT trx1.*,
CASE WHEN coalesce(lag(sign) OVER (ORDER BY id DESC), 0) != sign
THEN rn END,
CASE WHEN coalesce(lead(sign) OVER (ORDER BY id DESC), 0) != sign
THEN rn END
FROM trx1
)
SELECT
trx2.*, 1
- last_value (lo) IGNORE NULLS OVER (ORDER BY id DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
+ first_value(hi) IGNORE NULLS OVER (ORDER BY id DESC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM trx2
嗯。這個(gè) SQL 開始變得有趣了!
準(zhǔn)備好接下來的學(xué)習(xí)了嗎?
6. SQL 的子集求和問題
這是我最喜歡的部分!
什么是子集求和問題呢?在這里找到一個(gè)有趣的解釋： https://xkcd.com/287
還有一個(gè)比較無聊的解釋： https://en.wikipedia.org/wiki/Subset_sum_problem
基本上，對(duì)于每一個(gè)總數(shù)…
| ID | TOTAL |
|----|-------|
| 1 | 25150 |
| 2 | 19800 |
| 3 | 27511 |
…我們希望找到盡可能“最佳”(即最接近的)的求和，它包括以下各項(xiàng)的任意組合：
| ID | ITEM |
|------|-------|
| 1 | 7120 |
| 2 | 8150 |
| 3 | 8255 |
| 4 | 9051 |
| 5 | 1220 |
| 6 | 12515 |
| 7 | 13555 |
| 8 | 5221 |
| 9 | 812 |
| 10 | 6562 |
由于我們內(nèi)心的數(shù)學(xué)處理速度很快，所以我們可以立即計(jì)算出這些是最好的求和：
| TOTAL | BEST | CALCULATION
|-------|-------|--------------------------------
| 25150 | 25133 | 7120 + 8150 + 9051 + 812
| 19800 | 19768 | 1220 + 12515 + 5221 + 812
| 27511 | 27488 | 8150 + 8255 + 9051 + 1220 + 812
如何用 SQL 要做到這一點(diǎn)呢?很簡單。只需要?jiǎng)?chuàng)建一個(gè)包含所有 2n possible sums 的 CTE，然后為每個(gè) TOTAL 找到最接近的一個(gè)即可：
-- All the possible 2N sums
WITH sums(sum, max_id, calc) AS (...)
-- Find the best sum per “TOTAL”
SELECT
totals.total,
something_something(total - sum) AS best,
something_something(total - sum) AS calc
FROM draw_the_rest_of_the_*bleep*_owl
當(dāng)你在讀這篇文章的時(shí)候，你可能會(huì)像我的朋友一樣:
不過，別擔(dān)心，解決方案也不是那么難(盡管由于算法的性質(zhì)，它無法執(zhí)行)：
WITH sums(sum, id, calc) AS (
SELECT item, id, to_char(item) FROM items
UNION ALL
SELECT item + sum, items.id, calc || ' + ' || item
FROM sums JOIN items ON sums.id < items.id
)
SELECT
totals.id,
totals.total,
min (sum) KEEP (
DENSE_RANK FIRST ORDER BY abs(total - sum)
) AS best,
min (calc) KEEP (
DENSE_RANK FIRST ORDER BY abs(total - sum)
) AS calc,
FROM totals
CROSS JOIN sums
GROUP BY totals.id, totals.total
在本文中，我將不解釋此解決方案的詳細(xì)信息，因?yàn)檫@個(gè)例子是從上一篇文章中選取的，你可以在這里查看:如何使用 SQL 查找最接近的子集和
希望你能愉悅地閱讀的相應(yīng)細(xì)節(jié)，但一定要回來查看其余 4 個(gè)技巧：
7. 設(shè)限的累計(jì)計(jì)算
到目前為止，我們已經(jīng)學(xué)習(xí)了如何使用窗口函數(shù)用 SQL 進(jìn)行“普通”的累計(jì)計(jì)算。那很容易�，F(xiàn)在，如果我們把累計(jì)計(jì)算限制在永遠(yuǎn)不低于零的情況下會(huì)怎么樣呢?實(shí)際上，我們是想要得到如下的計(jì)算：
| DATE | AMOUNT | TOTAL |
|------------|--------|-------|
| 2012-01-01 | 800 | 800 |
| 2012-02-01 | 1900 | 2700 |
| 2012-03-01 | 1750 | 4450 |
| 2012-04-01 | -20000 | 0 |
| 2012-05-01 | 900 | 900 |
| 2012-06-01 | 3900 | 4800 |
| 2012-07-01 | -2600 | 2200 |
| 2012-08-01 | -2600 | 0 |
| 2012-09-01 | 2100 | 2100 |
| 2012-10-01 | -2400 | 0 |
| 2012-11-01 | 1100 | 1100 |
| 2012-12-01 | 1300 | 2400 |
因此，當(dāng)減去 AMOUNT -20000 這個(gè)大的負(fù)數(shù)是，我們沒有顯示 -15550 這個(gè)實(shí)際的 TOTAL，而是顯示的 0。換句話說，用數(shù)據(jù)集表示如下：
| DATE | AMOUNT | TOTAL |
|------------|--------|-------|
| 2012-01-01 | 800 | 800 | GREATEST(0, 800)
| 2012-02-01 | 1900 | 2700 | GREATEST(0, 2700)
| 2012-03-01 | 1750 | 4450 | GREATEST(0, 4450)
| 2012-04-01 | -20000 | 0 | GREATEST(0, -15550)
| 2012-05-01 | 900 | 900 | GREATEST(0, 900)
| 2012-06-01 | 3900 | 4800 | GREATEST(0, 4800)
| 2012-07-01 | -2600 | 2200 | GREATEST(0, 2200)
| 2012-08-01 | -2600 | 0 | GREATEST(0, -400)
| 2012-09-01 | 2100 | 2100 | GREATEST(0, 2100)
| 2012-10-01 | -2400 | 0 | GREATEST(0, -300)
| 2012-11-01 | 1100 | 1100 | GREATEST(0, 1100)
| 2012-12-01 | 1300 | 2400 | GREATEST(0, 2400)
我們要怎么做呢?
確切地說。使用模糊的、特定于供應(yīng)商的 SQL。在本例中，我們使用的是 Oracle SQL
它是如何工作的?出奇的簡單!
只要在任何表后添加 MODEL ，就可以打開一個(gè)很棒的 SQL “蠕蟲罐頭”!
SELECT ... FROM some_table
-- 將此放在任意 table 的后面
MODEL ...
一旦我們把 MODEL 放在那里，就可以像 Microsoft Excel 一樣，在 SQL 語句中直接實(shí)現(xiàn)電子表格邏輯了。
以下三個(gè)條款是最有用也是使用最廣泛的(即每年地球上的任何人使用 1-2 次)：
MODEL
-- The spreadsheet dimensions
DIMENSION BY ...
-- The spreadsheet cell type
MEASURES ...
-- The spreadsheet formulas
RULES ...
這三個(gè)附加條款的含義最好再看下幻燈片的解釋。
DIMENSION BY 子句指定電子表格的維度。與 MS Excel 不同，Oracle 中可以包含任意數(shù)量的維度：
MEASURES 子句指定電子表格中每個(gè)單元格的可用值。與 MS Excel 不同，在 Oracle 中每個(gè)單元格可以有一個(gè)完整的元組，而不僅僅是單個(gè)值。
RULES 子句指定應(yīng)用于電子表格中每個(gè)單元格的公式。與 MS Excel 不同，這些規(guī)則 / 公式集中在一個(gè)地方，而不是放在每個(gè)單元格中：
這種設(shè)計(jì)使得 MODEL 比 MS Excel 更難使用，但如果你敢用的話，它的功能會(huì)更強(qiáng)大。整個(gè)查詢語句比較“瑣碎”，如下所示：
SELECT *
FROM (
SELECT date, amount, 0 AS total
FROM amounts
)
MODEL
DIMENSION BY (row_number() OVER (ORDER BY date) AS rn)
MEASURES (date, amount, total)
RULES (
total[any] = greatest(0,
coalesce(total[cv(rn) - 1], 0) + amount[cv(rn)])
)
在整個(gè)過程中它的功能非常強(qiáng)大，并附帶了 Oracle 自己的白皮書，所以請(qǐng)不要在本文中尋求進(jìn)一步解釋，請(qǐng)閱讀優(yōu)秀的白皮書：
http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf
8. 時(shí)間序列模式識(shí)別
如果你是從事欺詐檢測(cè)或在大型數(shù)據(jù)集上運(yùn)行實(shí)時(shí)分析的任何其他領(lǐng)域，時(shí)間序列模式識(shí)別對(duì)你來說肯定不是一個(gè)新術(shù)語。
如果我們回看下“序列長度”的數(shù)據(jù)集，我們可能希望在時(shí)間序列上為復(fù)雜事件生成一個(gè)觸發(fā)器，如下所示：
| ID | VALUE_DATE | AMOUNT | LEN | TRIGGER
|------|------------|---------|-----|--------
| 9997 | 2014-03-18 | + 99.17 | 1 |
| 9981 | 2014-03-16 | - 71.44 | 4 |
| 9979 | 2014-03-16 | - 94.60 | 4 | x
| 9977 | 2014-03-16 | - 6.96 | 4 |
| 9971 | 2014-03-15 | - 65.95 | 4 |
| 9964 | 2014-03-15 | + 15.13 | 3 |
| 9962 | 2014-03-15 | + 17.47 | 3 |
| 9960 | 2014-03-15 | + 3.55 | 3 |
| 9959 | 2014-03-14 | - 32.00 | 1 |
上述觸發(fā)器的規(guī)則是：
如果事件發(fā)生超過 3 次，則在第 3 次重復(fù)時(shí)觸發(fā)。
與前面的 MODEL 子句類似，我們可以使用添加到 Oracle 12c 中的 Oracle 特定的子句來執(zhí)行該操作：
SELECT ... FROM some_table
-- 將此放在任何 table 之后盡心模式匹配
-- table 的內(nèi)容
MATCH_RECOGNIZE (...)
MATCH_RECOGNIZE 最簡單的應(yīng)用程序包括以下子條款：
SELECT *
FROM series
MATCH_RECOGNIZE (
-- 模式匹配按此順序執(zhí)行
ORDER BY ...
-- 這些時(shí)模式匹配產(chǎn)生的列
MEASURES ...
-- 對(duì)行的簡短說明
-- 返回匹配結(jié)果
ALL ROWS PER MATCH
-- 要匹配的事件的“正則表達(dá)式”
PATTERN (...)
-- “什么是事件”的定義
DEFINE ...
)
這有些不可思議。讓我們看一些子句的實(shí)現(xiàn)示例：
SELECT *
FROM series
MATCH_RECOGNIZE (
ORDER BY id
MEASURES classifier() AS trg
ALL ROWS PER MATCH
PATTERN (S (R X R+)?)
DEFINE
R AS sign(R.amount) = prev(sign(R.amount)),
X AS sign(X.amount) = prev(sign(X.amount))
)
在此我們做了什么?
按照我們想要匹配事件的順序根據(jù) ID 對(duì)表進(jìn)行排序，比較容易。
然后指定我們所需的值作為結(jié)果。我們需要“MEASURE” trg，它被定義為分類器，也就是我們隨后將在 PATTERN 中使用的文本。另外，我們希望匹配所有行。
然后，我們指定一個(gè)類似于正則表達(dá)式的模式。在該模式中以“S”代表開始事件，隨后是可選事件“R”，它代表重復(fù)事件，“X”代表特殊事件 X，隨后的一個(gè)或多個(gè)“R”代表再次重復(fù)。如果整個(gè)模式匹配，我們得到 SRXR 或 SRXRR 或 SRXRRR，即 X 將位于序列長度 >=4 的第三位上。
最后，我們將 R 和 X 定義為相同的東西：當(dāng)前行的 SIGN(AMOUNT) 事件與前一行的 SIGN(AMOUNT) 事件相同時(shí)。我們不必定義“S”。“S”可以是任何其他行。
該查詢的結(jié)果輸出如下：
| ID | VALUE_DATE | AMOUNT | TRG |
|------|------------|---------|-----|
| 9997 | 2014-03-18 | + 99.17 | S |
| 9981 | 2014-03-16 | - 71.44 | R |
| 9979 | 2014-03-16 | - 94.60 | X |
| 9977 | 2014-03-16 | - 6.96 | R |
| 9971 | 2014-03-15 | - 65.95 | S |
| 9964 | 2014-03-15 | + 15.13 | S |
| 9962 | 2014-03-15 | + 17.47 | S |
| 9960 | 2014-03-15 | + 3.55 | S |
| 9959 | 2014-03-14 | - 32.00 | S |
我們可以在事件流中看到一個(gè)“X”。這正是我們所期望的。在序列長度 > 3 的事件(同一符號(hào))中的第三次重復(fù)。
太棒了!
因?yàn)槲覀儾⒉徽嬲P(guān)心“S”和“R”事件，所以我們可以刪除它們：
SELECT
id, value_date, amount,
CASE trg WHEN 'X' THEN 'X' END trg
FROM series
MATCH_RECOGNIZE (
ORDER BY id
MEASURES classifier() AS trg
ALL ROWS PER MATCH
PATTERN (S (R X R+)?)
DEFINE
R AS sign(R.amount) = prev(sign(R.amount)),
X AS sign(X.amount) = prev(sign(X.amount))
)
結(jié)果如下：
| ID | VALUE_DATE | AMOUNT | TRG |
|------|------------|---------|-----|
| 9997 | 2014-03-18 | + 99.17 | |
| 9981 | 2014-03-16 | - 71.44 | |
| 9979 | 2014-03-16 | - 94.60 | X |
| 9977 | 2014-03-16 | - 6.96 | |
| 9971 | 2014-03-15 | - 65.95 | |
| 9964 | 2014-03-15 | + 15.13 | |
| 9962 | 2014-03-15 | + 17.47 | |
| 9960 | 2014-03-15 | + 3.55 | |
| 9959 | 2014-03-14 | - 32.00 | |
多虧了 Oracle!
再說一次，不要指望我能比優(yōu)秀的 Oracle 白皮書更好地解釋這一點(diǎn)，如果你使用的是 Oracle 12c，我強(qiáng)烈建議你閱讀該白皮書：
http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1965433.pdf
9. 旋轉(zhuǎn)和非旋轉(zhuǎn)
如果你已經(jīng)讀過這篇文章了，那么下面的內(nèi)容將非常簡單：
以下是我們的數(shù)據(jù)，即演員、電影片名和電影評(píng)級(jí)：
| NAME | TITLE | RATING |
|-----------|-----------------|--------|
| A. GRANT | ANNIE IDENTITY | G |
| A. GRANT | DISCIPLE MOTHER | PG |
| A. GRANT | GLORY TRACY | PG-13 |
| A. HUDSON | LEGEND JEDI | PG |
| A. CRONYN | IRON MOON | PG |
| A. CRONYN | LADY STAGE | PG |
| B. WALKEN | SIEGE MADRE | R |
這就是我們所說的旋轉(zhuǎn)：
| NAME | NC-17 | PG | G | PG-13 | R |
|-----------|-------|-----|-----|-------|-----|
| A. GRANT | 3 | 6 | 5 | 3 | 1 |
| A. HUDSON | 12 | 4 | 7 | 9 | 2 |
| A. CRONYN | 6 | 9 | 2 | 6 | 4 |
| B. WALKEN | 8 | 8 | 4 | 7 | 3 |
| B. WILLIS | 5 | 5 | 14 | 3 | 6 |
| C. DENCH | 6 | 4 | 5 | 4 | 5 |
| C. NEESON | 3 | 8 | 4 | 7 | 3 |
觀察我們是如何按演員分組的，然后根據(jù)每個(gè)演員所演電影的評(píng)級(jí)來“旋轉(zhuǎn)”電影的數(shù)量。我們不是以“關(guān)系”的方式來顯示它(即每個(gè)組是一行)，而是將整體旋轉(zhuǎn)為每個(gè)組生成一列。我們可以這樣做，是因?yàn)槲覀兪孪戎浪锌赡艿慕M合。
非旋轉(zhuǎn)與此相反，從開始時(shí)，如果我們想要回到用“每個(gè)組一行”的形式表示，即：
| NAME | RATING | COUNT |
|-----------|--------|-------|
| A. GRANT | NC-17 | 3 |
| A. GRANT | PG | 6 |
| A. GRANT | G | 5 |
| A. GRANT | PG-13 | 3 |
| A. GRANT | R | 6 |
| A. HUDSON | NC-17 | 12 |
| A. HUDSON | PG | 4 |
其實(shí)很簡單。在 PostgreSQL 中，可以這樣做：
SELECT
first_name, last_name,
count(*) FILTER (WHERE rating = 'NC-17') AS "NC-17",
count(*) FILTER (WHERE rating = 'PG' ) AS "PG",
count(*) FILTER (WHERE rating = 'G' ) AS "G",
count(*) FILTER (WHERE rating = 'PG-13') AS "PG-13",
count(*) FILTER (WHERE rating = 'R' ) AS "R"
FROM actor AS a
JOIN film_actor AS fa USING (actor_id)
JOIN film AS f USING (film_id)
GROUP BY actor_id
我們可以將一個(gè)簡單的 FILTER 子句附加到聚合函數(shù)中，以便只計(jì)算一些數(shù)據(jù)。
在所有其他數(shù)據(jù)庫中，我們都可以這樣做：
SELECT
first_name, last_name,
count(CASE rating WHEN 'NC-17' THEN 1 END) AS "NC-17",
count(CASE rating WHEN 'PG' THEN 1 END) AS "PG",
count(CASE rating WHEN 'G' THEN 1 END) AS "G",
count(CASE rating WHEN 'PG-13' THEN 1 END) AS "PG-13",
count(CASE rating WHEN 'R' THEN 1 END) AS "R"
FROM actor AS a
JOIN film_actor AS fa USING (actor_id)
JOIN film AS f USING (film_id)
GROUP BY actor_id
這樣做的好處是，聚合函數(shù)通常只考慮非 NULL 值，所以如果我們將每個(gè)聚合都不感興趣的所有其他值都設(shè)為 NULL，那么我們也將得到相同的結(jié)果。
現(xiàn)在，如果你使用的是 SQL Server 或 Oracle，則可以使用內(nèi)置的 PIVOTt 或 UNPIVOT 子句。同樣，對(duì)于 MODEL 或 MATCH_RECOGNIZE 也是一樣，只需要在表后添加這個(gè)新關(guān)鍵字，就可以得到相同的結(jié)果：
-- PIVOTING
SELECT something, something
FROM some_table
PIVOT (
count(*) FOR rating IN (
'NC-17' AS "NC-17",
'PG' AS "PG",
'G' AS "G",
'PG-13' AS "PG-13",
'R' AS "R"
)
)
-- UNPIVOTING
SELECT something, something
FROM some_table
UNPIVOT (
count FOR rating IN (
"NC-17" AS 'NC-17',
"PG" AS 'PG',
"G" AS 'G',
"PG-13" AS 'PG-13',
"R" AS 'R'
)
)
很容易吧，下一個(gè)。
10. 濫用 XML 和 JSON
首先
JSON 只是一種具有較少特性和語法的 XML
現(xiàn)在，每個(gè)人都知道 XML 非常好。因此可以推論出：
JSON 不是那么好
不要使用 JSON。
現(xiàn)在我們已經(jīng)解決了這個(gè)問題，我們可以放心地忽略正在進(jìn)行的 JSON-in-the-database-hype (無論如何，在五年后，大多數(shù)人都會(huì)后悔的)，然后繼續(xù)討論最后一個(gè)例子。如何在數(shù)據(jù)庫中執(zhí)行 XML。
這就是我們想要做的：
我們希望解析給定原始的 XML 文檔，解套每個(gè)演員以逗號(hào)分隔的電影列表，并采用一對(duì)一的關(guān)系非規(guī)范性的表達(dá)演員 / 電影。
準(zhǔn)備好了。使用集合。這是個(gè)不錯(cuò)的主意。我們使用三個(gè) CTE：
-- PIVOTING
SELECT something, something
FROM some_table
PIVOT (
count(*) FOR rating IN (
'NC-17' AS "NC-17",
'PG' AS "PG",
'G' AS "G",
'PG-13' AS "PG-13",
'R' AS "R"
)
)
-- UNPIVOTING
SELECT something, something
FROM some_table
UNPIVOT (
count FOR rating IN (
"NC-17" AS 'NC-17',
"PG" AS 'PG',
"G" AS 'G',
"PG-13" AS 'PG-13',
"R" AS 'R'
)
)
在第一個(gè)例子中，我們只是解析了 XML。在 PostgreSQL 語法如下：
WITH RECURSIVE
x(v) AS (SELECT '
Bud
Spencer
God Forgives... I Don’t, Double Trouble, They Call Him Bulldozer
Terence
Hill
God Forgives... I Don’t, Double Trouble, Lucky Luke
'::xml),
actors(actor_id, first_name, last_name, films) AS (...),
films(actor_id, first_name, last_name, film_id, film) AS (...)
SELECT *
FROM films
很容易吧。
然后，我們使用一些 XPath 魔術(shù)，從 XML 結(jié)構(gòu)中提取單個(gè)值，并將它們放入列中：
WITH RECURSIVE
x(v) AS (SELECT '...'::xml),
actors(actor_id, first_name, last_name, films) AS (
SELECT
row_number() OVER (),
(xpath('//first-name/text()', t.v))[1]::TEXT,
(xpath('//last-name/text()' , t.v))[1]::TEXT,
(xpath('//films/text()' , t.v))[1]::TEXT
FROM unnest(xpath('//actor', (SELECT v FROM x))) t(v)
),
films(actor_id, first_name, last_name, film_id, film) AS (...)
SELECT *
FROM films
這也還是很容易的。
最后，只要使用一點(diǎn)遞歸正則表達(dá)式模式匹配的魔法，我們就完成了!
WITH RECURSIVE
x(v) AS (SELECT '...'::xml),
actors(actor_id, first_name, last_name, films) AS (...),
films(actor_id, first_name, last_name, film_id, film) AS (
SELECT actor_id, first_name, last_name, 1,
regexp_replace(films, ',.+', '')
FROM actors
UNION ALL
SELECT actor_id, a.first_name, a.last_name, f.film_id + 1,
regexp_replace(a.films, '.*' || f.film || ', ?(.*?)(,.+)?', '\1')
FROM films AS f
JOIN actors AS a USING (actor_id)
WHERE a.films NOT LIKE '%' || f.film
)
SELECT *
FROM films
我們總結(jié)下：
結(jié)論
本文展示的所有內(nèi)容都是聲明式的。而且比較容易。當(dāng)然，為了達(dá)到本次演講所展現(xiàn)的有趣效果，我使用了一些夸張的 SQL 語句，并且明確地稱之為“簡單”。但它一點(diǎn)都不簡單，你必須不斷練習(xí)使用 SQL。像許多其他語言一樣，但它有點(diǎn)難，因?yàn)椋?/p>
它的語法有時(shí)有點(diǎn)笨拙
聲明式思維并不容易。至少，它是非常與眾不同的
但是一旦你掌握了它，使用 SQL 進(jìn)行聲明式編程是非常值得的，因?yàn)槟阒恍枰枋鱿胍獜臄?shù)據(jù)庫獲得的結(jié)果，就可以用非常少的代碼來表達(dá)數(shù)據(jù)之間的復(fù)雜關(guān)系。
它是不是很棒?
原文鏈接：https://jaxenter.com/10-sql-tricks-that-you-didnt-think-were-possible-125934.html
標(biāo)簽： SQL
版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。
