?
?
從早期開始，編程語言設(shè)計者就有這樣的愿望：設(shè)計一種語言，在這種語言中，告訴機器我們想要的結(jié)果是什么(WHAT)，而不是如何(HOW)獲得結(jié)果。SQL 可以做到這點。在 SQL 中，我們不關(guān)心數(shù)據(jù)庫是如何檢索信息的，就可以得到結(jié)果。本文介紹了使用聲明式 SQL10 個不為人知的技巧。
介紹
為了理解這 10 個 SQL 技巧的價值，首先需要了解下 SQL 語言的上下文。為什么我要在 Java 會議上討論 SQL 呢?(我可能是唯一一個在 Java 會議上討論 SQL 的了)下面講下為什么：
?
?
從早期開始，編程語言設(shè)計者就有這種的愿望：設(shè)計一種語言，在這種語言中，告訴機器我們想要的結(jié)果是什么(WHAT)，而不是如何(HOW)獲得結(jié)果。例如，在 SQL 中，我們告訴計算機我們要“連接”(聯(lián)接)用戶表和地址表，并查找居住在瑞士的用戶。我們不關(guān)心數(shù)據(jù)庫將如何檢索這些信息(比如，是先加載用戶表呢，還是先加載地址表?這兩個表是在嵌套循環(huán)中聯(lián)接呢，還是使用 hashmap 聯(lián)接?是先將所有數(shù)據(jù)加載到內(nèi)存中，然后再過濾出瑞士用戶呢，還是先加載瑞士地址?等等。)
與每個抽象一樣，我們?nèi)匀恍枰私鈹?shù)據(jù)庫背后的基本原理，以幫助數(shù)據(jù)庫在查詢時做出正確的決策。例如，做如下事情是有必要：
在表之間建立合適的外鍵(這能告訴數(shù)據(jù)庫每個地址都有一個對應(yīng)的用戶)
在搜索字段上添加索引：國家(這能告訴數(shù)據(jù)庫可以在 O(log N) 而不是 O(N) 的復(fù)雜度內(nèi)查找到特定的國家 )。
但是，一旦數(shù)據(jù)庫和應(yīng)用程序變得成熟之后，我們就可以把所有重要的元數(shù)據(jù)放在適當(dāng)?shù)奈恢蒙狭耍⑶抑恍鑼Ｗ⒂跇I(yè)務(wù)邏輯即可。下面的 10 個技巧展示了，僅用幾行聲明式 SQL 就能編寫強大驚人功能的能力，它不僅可以生成簡單的輸出，也可以生成復(fù)雜的輸出。
1. 一切都是表
這是一條最微不足道的技巧，甚至不能說是真正的技巧，但它是全面理解 SQL 的基礎(chǔ)：一切都是表：當(dāng)我們看到這樣的 SQL 語句時：
SELECT * FROM person
……我們很快就能在 FROM 子句中找到 person 表。很好，那是一張表。但我們能意識到整個語句也是一張表嗎?例如，我們可以這樣寫：
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)建臨時內(nèi)存表：
SELECT * FROM ( ??VALUES(1),(2),(3) ) t(a)
臨時表就這樣產(chǎn)生了：
a — 1 2 3
如果對應(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() 和派生表實際上是相同的，那么從概念上，我們回顧一下 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ù)情況下，大部分人只是碰巧插入了一張單行表，因此沒有意識到 INSERT 真正做了什么。
一切都是表。在 PostgreSQL 中，甚至函數(shù)都是表：
SELECT * FROM substring('abcde', 2, 3)
上面語句的結(jié)果是：
substring ——— bcd
如果你正在使用 Java 編程，那么可以使用 Java 8 Stream API 來做進(jìn)一步的類比?？紤]如下等價概念：
TABLE????????? : Stream
在 Java 8 中，“一切都是流”(至少在你開始使用流時是這樣)。無論如何轉(zhuǎn)換流，例如，使用 map() 或 filter() 轉(zhuǎn)換，結(jié)果類型始終都是流。
我們寫了一篇完整的文章來更深入地解釋這一點，并將 Stream API 與 SQL 進(jìn)行了對比： Common SQL C lauses and Their Equivalents in Java 8 Streams
如果你正在尋找“更好的流”(即，具有更多 SQL 語義的流)，請查看 jOOλ，它一個將 SQL 窗口函數(shù)引入到 Java 中的開源庫。
2. 使用遞歸 SQL 生成數(shù)據(jù)
公共表表達(dá)式(Common Table Expressions ，CTE，在 Oracle 中也叫做子查詢分解)，它是在 SQL 中聲明變量的唯一方法(除了模糊 WINDOW 子句之外，WINDOW 子句也只有在 PostgreSQL 和 Sybase SQL 中可用)。
這是一個功能強大的概念。非常強大?？紤]如下聲明：
-- 表變量 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 子句，我們可以指定一系列表變量(請記住：一切都是表)，這些變量甚至可以是相互依賴的。
這很容易理解。它已經(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)鍵詞，它就相對容易了。我們定義了一個公共表表達(dá)式，它恰好有兩個 UNION ALL 子查詢。
第一個 UNION ALL 子查詢是我們通常所說的“種子行”。它“播種”(初始化)遞歸。它可以生成一行或多行，稍后我們將在這些行上遞歸。記?。阂磺卸际潜恚赃f歸將發(fā)生在整張表上，而不是單個行 / 值上。
第二個 UNION ALL 子查詢在發(fā)生遞歸的地方。如果你仔細(xì)觀察，會發(fā)現(xiàn)它從 t 中選擇。也就是說，允許第二個子查詢從我們即將聲明的 CTE 中遞歸地選擇。因此，它還可以訪問使用它的 CTE 聲明的列 v。
在我們的示例中，我們使用行 (1) 對遞歸進(jìn)行種子處理，然后通過添加 v + 1 來進(jìn)行遞歸。最后通過設(shè)置 LIMIT 5 來終止遞歸(需要謹(jǐn)防潛在的無限遞歸 ，就像使用 Java 8 的流一樣)。
附注：圖靈完備
遞歸 CTE 使得 SQL:1999 圖靈完備，這意味著任何程序都可以用 SQL 編寫! (如果你夠瘋狂的話)
一個經(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) <= 2 THEN rx * rx - ix * ix END + r, ???????????????CASE WHEN abs(rx * rx + ix * ix) <= 2 THEN 2 * rx * ix END + i, g + 1 ??FROM q ??WHERE rx IS NOT NULL AND g < 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 上運行上面的代碼，我們將得到如下結(jié)果：
復(fù)制代碼 .-.:-.......==..*.=.::-@@@@@:::.:.@..*-. =. ...=...=...::+%.@:@@@@@@@@@@@@@+*#=.=:+-. ..- .:.:=::*....@@@@@@@@@@@@@@@@@@@@@@@@=@@.....::...:. ???????????????????????????? .-.:-.......==..*.=.::-@@@@@:::.:.@..*-.???????? =. ???????????????????????????? ...=...=...::+%.@:@@@@@@@@@@@@@+*#=.=:+-.????? ..-? ???????????????????????????? .:.:=::*....@@@@@@@@@@@@@@@@@@@@@@@@=@@.....::...:. ???????????????????????????? ...*@@@@=.@:@@@@@@@@@@@@@@@@@@@@@@@@@@=.=....:...::. ????????????????????????????? .::@@@@@:-@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@..-:@=*:::. ????????????????????????????? .-@@@@@-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.=@@@@=..: ????????????????????????????? ...@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@@@@@:.. ???????????????????????????? ....:-*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@::? ??????????????????????????? .....@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-..? ????????????????????????? .....@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-:...? ???????????????????????? .--:+.@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@...? ???????????????????????? .==@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-..? ???????????????????????? ..+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-#.? ???????????????????????? ...=+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.. ???????????????????????? -.=-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..: ??????????????????????? .*%:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@- ?.??? ..:...?????????? ..-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ..............??????? ....-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@%@= .--.-.....-=.:..........::@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.. ..=:-....=@+..=.........@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:. .:+@@::@==@-*:%:+.......:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@. ::@@@-@@@@@@@@@-:=.....:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@: .:@@@@@@@@@@@@@@@=:.....%@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ .:@@@@@@@@@@@@@@@@@-...:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:- :@@@@@@@@@@@@@@@@@@@-..%@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@. %@@@@@@@@@@@@@@@@@@@-..-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@. @@@@@@@@@@@@@@@@@@@@@::+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@+ @@@@@@@@@@@@@@@@@@@@@@:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.. @@@@@@@@@@@@@@@@@@@@@@-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.?
印象是不是非常深刻?
3. 累計計算
這個博客有很多累計計算的示例。它們是學(xué)習(xí)高級 SQL 最有教育意義的示例之一，因為至少有十幾種方法可以實現(xiàn)累計計算。
在概念上，累計計算很容易理解。
?
?
在 Microsoft Excel 中，我們只需計算前兩個(或后兩個)值的和(或差)，然后使用可用的十字光標(biāo)將該公式拉過整個電子表格。我們在電子表格中“運行”這個總數(shù)。即一個“累計”。
在 SQL 中，最好的方法是使用窗口函數(shù)，這也是該博客多次討論的另一主題。
窗口函數(shù)是一個功能強大的概念，一開始可能不太容易理解，但事實上，它們非常非常簡單：
窗口函數(shù)是在相對當(dāng)前行而言的一個子集上的聚合/排序，當(dāng)前行由 SELECT 轉(zhuǎn)換。
就是這樣簡單!
它本質(zhì)上的意思是，一個窗口函數(shù)可以對當(dāng)前行的“上”或“下”行執(zhí)行計算。然而，與普通的聚合和 GROUP BY 不同，它們不轉(zhuǎn)換行，這使得它們非常有用。
語法總結(jié)如下，個別部分是可選的：
function(...) OVER ( ??PARTITION BY ... ??ORDER BY ... ??ROWS BETWEEN ... AND ... )
因此，我們可以使用任何類型的函數(shù)(稍后我們將介紹此類函數(shù)的示例)，后面緊跟其后的是 OVER() 子句，該子句指定窗口。即，這個 OVER() 子句定義如下：
PARTITION ：窗口只考慮與當(dāng)前行在同一分區(qū)中的行
ORDER：窗口排序可以獨立于我們選擇的內(nèi)容
ROWS(或 RANGE )框架定義：窗口可以被限制在固定數(shù)量的行的“前面”和“后面”。
這就是窗口函數(shù)的全部功能。
那么它又是如何幫助我們累計計算的呢?考慮以下數(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 中計算出來的
直觀視覺上，我們可以立即看出以下情況是成立的：
?
?
因此，使用簡單的英語，任何余額都可以用以下偽 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ū)計算每個銀行帳戶的總和，而不是整個數(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ù)
這兩個功能都是：
非常棒
功能極其強大
聲明式
SQL 標(biāo)準(zhǔn)的一部分
適用于大多數(shù)流行的 RDBMS(除了 MySQL)
非常重要的構(gòu)建塊
如果能從本文中得出什么結(jié)論，那就是我們應(yīng)該完全了解現(xiàn)代 SQL 的這兩個構(gòu)建塊。為什么呢?因為：
?
?
4. 查找最大無間隔序列
Stack Overflow 有一個非常好的功能：徽章，它可以激勵人們盡可能長時間地呆在他們的網(wǎng)站上。
?
?
就規(guī)模而言，你可以看到我有多少徽章。
你要怎么計算這些徽章呢?讓我們看看“愛好者”和“狂熱者”。這些徽章是頒發(fā)給那些在他們平臺上連續(xù)停留一定時間的人。無論結(jié)婚紀(jì)念日或是妻子生日，你都必須登錄，否則計數(shù)器將再次從零開始。
當(dāng)我們進(jìn)行聲明式編程時，是不需要維護(hù)任何狀態(tài)和內(nèi)存計數(shù)器的。現(xiàn)在，我們想用在線分析 SQL 的形式來表達(dá)這一點。即，考慮如下數(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 |
那沒什么用。我們從時間戳中刪除小時。這很簡單：
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ù)，我們只需為每個日期添加一個簡單的行數(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)在，如果我們不單獨選擇這些值，而是減去它們，會發(fā)生什么?
SELECT ??login_date - ??row_number() OVER (ORDER BY login_date) FROM login_dates
將會得到如下結(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 說得更好了：
?
?
有一個關(guān)于這種行為的簡單示例：
ROW_NUMBER() 沒有間隔，這就是它的定義
但是，我們的數(shù)據(jù)有間隔
所以，當(dāng)我們從一個非連續(xù)日期的“gapful”序列中減去一個連續(xù)整數(shù)的“gapless”序列時，我們將得到連續(xù)日期中每個“gapless”子序列的相同日期，并且它是一個新的日期，其中日期序列是有間隔的。
嗯。
這意味著我們現(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)然，最主要的是有了這個想法，但是查詢本身真的非常簡單優(yōu)雅。沒有比這更簡潔的方法來實現(xiàn)一些命令式算法了。
5. 求序列的長度
在前面，我們看到了一系列連續(xù)的值。這很容易處理，因為我們可以濫用整數(shù)的連續(xù)性。如果一個“序列”的定義不那么直觀，而且除此之外，幾個序列包含相同的值呢?考慮以下數(shù)據(jù)，其中 LENGTH 是要計算的每個序列的長度：
| 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 |
是的，你猜對了。 “序列”是由連續(xù)(按 ID 排序)行且具有相同的 SIGN(AMOUNT) 這一事實來定義的。再次檢查如下的數(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 |
我們怎么做呢?很“簡單”，首先，我們?nèi)サ羲械脑胍簦⑻砑恿硪粋€行號：
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 |
在該表中，我們希望將行號值復(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ù)，你可以對相對于當(dāng)前行的子集執(zhí)行排序或聚合。在 LEAD() 和 LAG() 的情況下，只要給定當(dāng)前行的偏移量，就可以訪問相對于當(dāng)前行的單個行。這在很多情況下都很有用。
繼續(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
……我們將“前一個”sign (lag(sign)) 與“當(dāng)前”sign (sign) 進(jìn)行比較。如果它們不同，我們將行號放到“LO”中，因為它是序列的下界。
然后我們比較“下一個”sign (lead(sign)) 和“當(dāng)前”sign (sign)。如果它們不同，我們將行號放到“HI”中，因為它是序列的上界。
最后，進(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)∽詈笠粋€值，這就是我們的新“LO”值。換句話說，我們?nèi) 白罱那耙粋€”“LO”值。
“HI”也是一樣的。從任何給定的“當(dāng)前”行中，我們查看所有“后續(xù)值”(ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)，但忽略所有空值。從隨后的值中，我們?nèi)〉谝粋€值，這是我們的新“HI”值。換言之，我們?nèi) 白罱南乱粋€”“HI”值。
使用幻燈片解釋如下：
?
?
100% 正確，加上點無聊的 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
我們做最后一個步驟，記住清除一個個的錯誤：
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
?
嗯。這個 SQL 開始變得有趣了!
準(zhǔn)備好接下來的學(xué)習(xí)了嗎?
6. SQL 的子集求和問題
這是我最喜歡的部分!
什么是子集求和問題呢?在這里找到一個有趣的解釋： https://xkcd.com/287
還有一個比較無聊的解釋： https://en.wikipedia.org/wiki/Subset_sum_problem
基本上，對于每一個總數(shù)…
| ID | TOTAL | |----|-------| |? 1 | 25150 | |? 2 | 19800 | |? 3 | 27511 |
…我們希望找到盡可能“最佳”(即最接近的)的求和，它包括以下各項的任意組合：
| 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é)處理速度很快，所以我們可以立即計算出這些是最好的求和：
| TOTAL |? BEST | CALCULATION |-------|-------|-------------------------------- | 25150 | 25133 | 7120 + 8150 + 9051 + 812 | 19800 | 19768 | 1220 + 12515 + 5221 + 812 | 27511 | 27488 | 8150 + 8255 + 9051 + 1220 + 812
如何用 SQL 要做到這一點呢?很簡單。只需要創(chuàng)建一個包含所有 2n possible sums 的 CTE，然后為每個 TOTAL 找到最接近的一個即可：
-- 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)你在讀這篇文章的時候，你可能會像我的朋友一樣:
?
?
不過，別擔(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ì)信息，因為這個例子是從上一篇文章中選取的，你可以在這里查看:如何使用 SQL 查找最接近的子集和
希望你能愉悅地閱讀的相應(yīng)細(xì)節(jié)，但一定要回來查看其余 4 個技巧：
7. 設(shè)限的累計計算
到目前為止，我們已經(jīng)學(xué)習(xí)了如何使用窗口函數(shù)用 SQL 進(jìn)行“普通”的累計計算。那很容易?，F(xiàn)在，如果我們把累計計算限制在永遠(yuǎn)不低于零的情況下會怎么樣呢?實際上，我們是想要得到如下的計算：
| 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 這個大的負(fù)數(shù)是，我們沒有顯示 -15550 這個實際的 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 ，就可以打開一個很棒的 SQL “蠕蟲罐頭”!
SELECT ... FROM some_table ?? -- 將此放在任意 table 的后面 MODEL ...
一旦我們把 MODEL 放在那里，就可以像 Microsoft Excel 一樣，在 SQL 語句中直接實現(xiàn)電子表格邏輯了。
以下三個條款是最有用也是使用最廣泛的(即每年地球上的任何人使用 1-2 次)：
MODEL ??-- The spreadsheet dimensions ??DIMENSION BY ... ???? ??-- The spreadsheet cell type ??MEASURES ... ???? ??-- The spreadsheet formulas ??RULES ...
這三個附加條款的含義最好再看下幻燈片的解釋。
DIMENSION BY 子句指定電子表格的維度。與 MS Excel 不同，Oracle 中可以包含任意數(shù)量的維度：
?
?
MEASURES 子句指定電子表格中每個單元格的可用值。與 MS Excel 不同，在 Oracle 中每個單元格可以有一個完整的元組，而不僅僅是單個值。
?
?
RULES 子句指定應(yīng)用于電子表格中每個單元格的公式。與 MS Excel 不同，這些規(guī)則 / 公式集中在一個地方，而不是放在每個單元格中：
?
?
這種設(shè)計使得 MODEL 比 MS Excel 更難使用，但如果你敢用的話，它的功能會更強大。整個查詢語句比較“瑣碎”，如下所示：
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)]) ??)
在整個過程中它的功能非常強大，并附帶了 Oracle 自己的白皮書，所以請不要在本文中尋求進(jìn)一步解釋，請閱讀優(yōu)秀的白皮書：
http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf
8. 時間序列模式識別
如果你是從事欺詐檢測或在大型數(shù)據(jù)集上運行實時分析的任何其他領(lǐng)域，時間序列模式識別對你來說肯定不是一個新術(shù)語。
如果我們回看下“序列長度”的數(shù)據(jù)集，我們可能希望在時間序列上為復(fù)雜事件生成一個觸發(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ù)時觸發(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 ... ?? ? -- 這些時模式匹配產(chǎn)生的列 ? MEASURES ... ?? ? -- 對行的簡短說明 ? -- 返回匹配結(jié)果 ? ALL ROWS PER MATCH ?? ? -- 要匹配的事件的“正則表達(dá)式” ? PATTERN (...) ?? ? -- “什么是事件”的定義 ? DEFINE ... )?
這有些不可思議。讓我們看一些子句的實現(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 對表進(jìn)行排序，比較容易。
然后指定我們所需的值作為結(jié)果。我們需要“MEASURE” trg，它被定義為分類器，也就是我們隨后將在 PATTERN 中使用的文本。另外，我們希望匹配所有行。
然后，我們指定一個類似于正則表達(dá)式的模式。在該模式中以“S”代表開始事件，隨后是可選事件“R”，它代表重復(fù)事件，“X”代表特殊事件 X，隨后的一個或多個“R”代表再次重復(fù)。如果整個模式匹配，我們得到 SRXR 或 SRXRR 或 SRXRRR，即 X 將位于序列長度 >=4 的第三位上。
最后，我們將 R 和 X 定義為相同的東西：當(dāng)前行的 SIGN(AMOUNT) 事件與前一行的 SIGN(AMOUNT) 事件相同時。我們不必定義“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 |
我們可以在事件流中看到一個“X”。這正是我們所期望的。在序列長度 > 3 的事件(同一符號)中的第三次重復(fù)。
太棒了!
因為我們并不真正關(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 白皮書更好地解釋這一點，如果你使用的是 Oracle 12c，我強烈建議你閱讀該白皮書：
http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1965433.pdf
9. 旋轉(zhuǎn)和非旋轉(zhuǎn)
如果你已經(jīng)讀過這篇文章了，那么下面的內(nèi)容將非常簡單：
以下是我們的數(shù)據(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ù)每個演員所演電影的評級來“旋轉(zhuǎn)”電影的數(shù)量。我們不是以“關(guān)系”的方式來顯示它(即每個組是一行)，而是將整體旋轉(zhuǎn)為每個組生成一列。我們可以這樣做，是因為我們事先知道所有可能的組合。
非旋轉(zhuǎn)與此相反，從開始時，如果我們想要回到用“每個組一行”的形式表示，即：
| 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 |
其實很簡單。在 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
我們可以將一個簡單的 FILTER 子句附加到聚合函數(shù)中，以便只計算一些數(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 值，所以如果我們將每個聚合都不感興趣的所有其他值都設(shè)為 NULL，那么我們也將得到相同的結(jié)果。
現(xiàn)在，如果你使用的是 SQL Server 或 Oracle，則可以使用內(nèi)置的 PIVOTt 或 UNPIVOT 子句。同樣，對于 MODEL 或 MATCH_RECOGNIZE 也是一樣，只需要在表后添加這個新關(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' ??) )
很容易吧，下一個。
10. 濫用 XML 和 JSON
首先
?
?
JSON 只是一種具有較少特性和語法的 XML
現(xiàn)在，每個人都知道 XML 非常好。因此可以推論出：
JSON 不是那么好
不要使用 JSON。
現(xiàn)在我們已經(jīng)解決了這個問題，我們可以放心地忽略正在進(jìn)行的 JSON-in-the-database-hype (無論如何，在五年后，大多數(shù)人都會后悔的)，然后繼續(xù)討論最后一個例子。如何在數(shù)據(jù)庫中執(zhí)行 XML。
這就是我們想要做的：
?
?
我們希望解析給定原始的 XML 文檔，解套每個演員以逗號分隔的電影列表，并采用一對一的關(guān)系非規(guī)范性的表達(dá)演員 / 電影。
準(zhǔn)備好了。使用集合。這是個不錯的主意。我們使用三個 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' ??) )
在第一個例子中，我們只是解析了 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)中提取單個值，并將它們放入列中：
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
這也還是很容易的。
最后，只要使用一點遞歸正則表達(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 語句，并且明確地稱之為“簡單”。但它一點都不簡單，你必須不斷練習(xí)使用 SQL。像許多其他語言一樣，但它有點難，因為：
它的語法有時有點笨拙
聲明式思維并不容易。至少，它是非常與眾不同的
但是一旦你掌握了它，使用 SQL 進(jìn)行聲明式編程是非常值得的，因為你只需要描述想要從數(shù)據(jù)庫獲得的結(jié)果，就可以用非常少的代碼來表達(dá)數(shù)據(jù)之間的復(fù)雜關(guān)系。
它是不是很棒?
原文鏈接：https://jaxenter.com/10-sql-tricks-that-you-didnt-think-were-possible-125934.html
