介 紹

為了理解這 10 個 SQL 技巧的價值，首先需要了解下 SQL 語言的上下文。為什么我要在 Java 會議上討論 SQL 呢？(我可能是唯一一個在 Java 會議上討論 SQL 的了)下面講下為什么：

從早期開始，編程語言設計者就有這種的愿望：設計一種語言，在這種語言中，告訴機器我們想要的結(jié)果是什么(WHAT)，而不是如何(HOW)獲得結(jié)果。例如，在 SQL 中，我們告訴計算機我們要“連接”(聯(lián)接)用戶表和地址表，并查找居住在瑞士的用戶。我們不關心數(shù)據(jù)庫將如何檢索這些信息(比如，是先加載用戶表呢，還是先加載地址表？這兩個表是在嵌套循環(huán)中聯(lián)接呢，還是使用 hashmap 聯(lián)接？是先將所有數(shù)據(jù)加載到內(nèi)存中，然后再過濾出瑞士用戶呢，還是先加載瑞士地址？等等。)

與每個抽象一樣，我們?nèi)匀恍枰私鈹?shù)據(jù)庫背后的基本原理，以幫助數(shù)據(jù)庫在查詢時做出正確的決策。例如，做如下事情是有必要：

• 在表之間建立合適的外鍵(這能告訴數(shù)據(jù)庫每個地址都有一個對應的用戶)
• 在搜索字段上添加索引：國家(這能告訴數(shù)據(jù)庫可以在 O(log N) 而不是 O(N) 的復雜度內(nèi)查找到特定的國家 )。

但是，一旦數(shù)據(jù)庫和應用程序變得成熟之后，我們就可以把所有重要的元數(shù)據(jù)放在適當?shù)奈恢蒙狭?#xff0c;并且只需專注于業(yè)務邏輯即可。下面的 10 個技巧展示了，僅用幾行聲明式 SQL 就能編寫強大驚人功能的能力，它不僅可以生成簡單的輸出，也可以生成復雜的輸出。

1. 一切都是表

這是一條最微不足道的技巧，甚至不能說是真正的技巧，但它是全面理解 SQL 的基礎：一切都是表：當我們看到這樣的 SQL 語句時：

SELECT *FROM person

……我們很快就能在 FROM 子句中找到 person 表。很好，那是一張表。但我們能意識到整個語句也是一張表嗎？例如，我們可以這樣寫：

SELECT *FROM ( SELECT * FROM person) t

現(xiàn)在，我們已經(jīng)創(chuàng)建了一張所謂的“派生表”，即 FROM 子句中的嵌套 SELECT 語句。

這是微不足道的，但是如果仔細想想，它是相當優(yōu)雅的。我們還可以在某些數(shù)據(jù)庫(比如 PostgreSQL、SQL Server)中使用 VALUES() 構造函數(shù)來創(chuàng)建臨時內(nèi)存表：

SELECT *FROM ( VALUES(1),(2),(3)) t(a)

臨時表就這樣產(chǎn)生了：

a—123

如果對應的數(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 ALLSELECT 2 AS a FROM DUAL UNION ALLSELECT 3 AS a FROM DUAL

在 SQL 中，一切都是表。當您在表中插入行時，實際上并不是插入單獨的行。是插入整張表。在大多數(shù)情況下，大部分人只是碰巧插入了一張單行表，因此沒有意識到 INSERT 真正做了什么。

一切都是表。在 PostgreSQL 中，甚至函數(shù)都是表：

SELECT *FROM substring('abcde', 2, 3)

上面語句的結(jié)果是：

substring———bcd

如果你正在使用 Java 編程，那么可以使用 Java 8 Stream API 來做進一步的類比。考慮如下等價概念：

TABLE : Stream>SELECT : map()DISTINCT : distinct()JOIN : flatMap()WHERE / HAVING : filter()GROUP BY : collect()ORDER BY : sorted()UNION ALL : concat()

在 Java 8 中，“一切都是流”(至少在你開始使用流時是這樣)。無論如何轉(zhuǎn)換流，例如，使用 map() 或 filter() 轉(zhuǎn)換，結(jié)果類型始終都是流。

我們寫了一篇完整的文章來更深入地解釋這一點，并將 Stream API 與 SQL 進行了對比：Common SQL Clauses and Their Equivalents in Java 8 Streams

如果你正在尋找“更好的流”(即，具有更多 SQL 語義的流)，請查看 jOOλ，它一個將 SQL 窗口函數(shù)引入到 Java 中的開源庫。

2. 使用遞歸 SQL 生成數(shù)據(jù)

公共表表達式(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 vFROM tLIMIT 5

它的結(jié)果是：

v—12345

它是如何工作的呢？一旦你看懂了一些關鍵詞，它就相對容易了。我們定義了一個公共表表達式，它恰好有兩個 UNION ALL 子查詢。

第一個 UNION ALL 子查詢是我們通常所說的“種子行”。它“播種”(初始化)遞歸。它可以生成一行或多行，稍后我們將在這些行上遞歸。記住：一切都是表，所以遞歸將發(fā)生在整張表上，而不是單個行 / 值上。

第二個 UNION ALL 子查詢在發(fā)生遞歸的地方。如果你仔細觀察，會發(fā)現(xiàn)它從 t 中選擇。也就是說，允許第二個子查詢從我們即將聲明的 CTE 中遞歸地選擇。因此，它還可以訪問使用它的 CTE 聲明的列 v。

在我們的示例中，我們使用行 (1) 對遞歸進行種子處理，然后通過添加 v + 1 來進行遞歸。最后通過設置 LIMIT 5 來終止遞歸(需要謹防潛在的無限遞歸 ，就像使用 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) qGROUP BY iORDER BY i

在 PostgreSQL 上運行上面的代碼，我們將得到如下結(jié)果：

.-.:-.......==..*.=.::-@@@@@:::.:.@..*-. =. ...=...=...::+%.@:@@@@@@@@@@@@@+*#=.=:+-. ..- .:.:=::*....@@@@@@@@@@@@@@@@@@@@@@@@=@@.....::...:. ...*@@@@=.@:@@@@@@@@@@@@@@@@@@@@@@@@@@=.=....:...::. .::@@@@@:-@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@..-:@=*:::. .-@@@@@-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.=@@@@=..: ...@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@@@@@:.. ....:-*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:: .....@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-.. .....@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-:... .--:+.@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@... .==@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-.. ..+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-#. ...=+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.. -.=-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..: .*%:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@- . ..:... ..-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.............. ....-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@%@=.--.-.....-=.:..........::@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@....=:-....=@+..=.........@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:..:+@@::@==@-*:%:+.......:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.::@@@-@@@@@@@@@-:=.....:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:.:@@@@@@@@@@@@@@@=:.....%@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.:@@@@@@@@@@@@@@@@@-...:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:-:@@@@@@@@@@@@@@@@@@@-..%@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.%@@@@@@@@@@@@@@@@@@@-..-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.@@@@@@@@@@@@@@@@@@@@@::+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@+@@@@@@@@@@@@@@@@@@@@@@:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..@@@@@@@@@@@@@@@@@@@@@@-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.

印象是不是非常深刻？

3. 累計計算

這個博客 有很多累計計算的示例。它們是學習高級 SQL 最有教育意義的示例之一，因為至少有十幾種方法可以實現(xiàn)累計計算。

在概念上，累計計算很容易理解。

在 Microsoft Excel 中，我們只需計算前兩個(或后兩個)值的和(或差)，然后使用可用的十字光標將該公式拉過整個電子表格。我們在電子表格中“運行”這個總數(shù)。即一個“累計”。

在 SQL 中，最好的方法是使用 窗口函數(shù)，這也是該博客多次討論的另一主題。

窗口函數(shù)是一個功能強大的概念，一開始可能不太容易理解，但事實上，它們非常非常簡單：

窗口函數(shù)是在相對當前行而言的一個子集上的聚合／排序，當前行由 SELECT 轉(zhuǎn)換。

就是這樣簡單！

它本質(zhì)上的意思是，一個窗口函數(shù)可以對當前行的“上”或“下”行執(zhí)行計算。然而，與普通的聚合和 GROUP BY 不同，它們不轉(zhuǎn)換行，這使得它們非常有用。

語法總結(jié)如下，個別部分是可選的：

function(...) OVER ( PARTITION BY ... ORDER BY ... ROWS BETWEEN ... AND ...)

因此，我們可以使用任何類型的函數(shù)(稍后我們將介紹此類函數(shù)的示例)，后面緊跟其后的是 OVER() 子句，該子句指定窗口。即，這個 OVER() 子句定義如下：

• PARTITION ：窗口只考慮與當前行在同一分區(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 |

假設 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ū)⒋_保事務在求和之前(在分區(qū)內(nèi))是有序的
• 在求和之前，rows 子句只考慮前面的行(在分區(qū)內(nèi)，給定順序)

所有這些都發(fā)生在內(nèi)存中的數(shù)據(jù)集上，該數(shù)據(jù)集由我們通過 FROM .. WHERE 子句選擇出來，因此速度非常快。

插 曲

在我們開始討論其他精彩技巧之前，先考慮一下：我們已經(jīng)學習了

• (遞歸)公共表表達式(CTE)
• 窗口函數(shù)

這兩個功能都是：

• 非常棒
• 功能極其強大
• 聲明式
• SQL 標準的一部分
• 適用于大多數(shù)流行的 RDBMS(除了 MySQL)
• 非常重要的構建塊

如果能從本文中得出什么結(jié)論，那就是我們應該完全了解現(xiàn)代 SQL 的這兩個構建塊。為什么呢？因為：

4. 查找最大無間隔序列

Stack Overflow 有一個非常好的功能：徽章，它可以激勵人們盡可能長時間地呆在他們的網(wǎng)站上。

就規(guī)模而言，你可以看到我有多少徽章。

你要怎么計算這些徽章呢？讓我們看看“愛好者”和“狂熱者”。這些徽章是頒發(fā)給那些在他們平臺上連續(xù)停留一定時間的人。無論結(jié)婚紀念日或是妻子生日，你都必須登錄，否則計數(shù)器將再次從零開始。

當我們進行聲明式編程時，是不需要維護任何狀態(tài)和內(nèi)存計數(shù)器的。現(xiàn)在，我們想用在線分析 SQL 的形式來表達這一點。即，考慮如下數(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_dateFROM loginsWHERE user_id = :user_id

得到的結(jié)果是：

| LOGIN_DATE ||------------|| 2014-03-18 || 2014-03-16 || 2014-03-15 || 2014-03-14 |

現(xiàn)在，我們已經(jīng)學習了窗口函數(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 |

還是很容易的吧。現(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 說得更好了：

有一個關于這種行為的簡單示例：

ROW_NUMBER() 沒有間隔，這就是它的定義

但是，我們的數(shù)據(jù)有間隔

所以，當我們從一個非連續(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 lengthFROM login_date_groupsGROUP BY grpORDER BY length DESC

我們做到了。最大的連續(xù)無間隔序列被找到了：

| MIN | MAX | LENGTH ||------------|------------|--------|| 2014-03-14 | 2014-03-16 | 3 || 2014-03-18 | 2014-03-18 | 1 |

完整的查詢?nèi)缦?#xff1a;

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 lengthFROM login_date_groupsGROUP BY grpORDER BY length DESC

最后，沒那么難吧？當然，最主要的是有了這個想法，但是查詢本身真的非常簡單優(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)サ羲械脑胍?#xff0c;并添加另一個行號：

SELECT id, amount, sign(amount) AS sign, row_number() OVER (ORDER BY id DESC) AS rnFROM 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)在，接下來的目標是生成如下表：

| 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 |

在該表中，我們希望將行號值復制到序列“末”端的“LO”和序列“頂”端的“HI”中。為此，我們將使用神奇的 LEAD() 和 LAG()。LEAD() 可以訪問當前行向下的第 n 行，而 LAG() 可以訪問當前行向上的第 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ù)，你可以對相對于當前行的子集執(zhí)行排序或聚合。在 LEAD() 和 LAG() 的情況下，只要給定當前行的偏移量，就可以訪問相對于當前行的單個行。這在很多情況下都很有用。

繼續(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)) 與“當前”sign (sign) 進行比較。如果它們不同，我們將行號放到“LO”中，因為它是序列的下界。

然后我們比較“下一個”sign (lead(sign)) 和“當前”sign (sign)。如果它們不同，我們將行號放到“HI”中，因為它是序列的上界。

最后，進行一些無聊的 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 hiFROM trx

有很多關鍵詞！但本質(zhì)都是一樣的。從任何給定的“當前”行中，我們查看所有“之前的值”(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，但忽略所有空值。從之前的值中，我們?nèi)∽詈笠粋€值，這就是我們的新“LO”值。換句話說，我們?nèi)　白罱那耙粋€”“LO”值。

“HI”也是一樣的。從任何給定的“當前”行中，我們查看所有“后續(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 hiFROM trx

我們做最后一個步驟，記住清除一個個的錯誤：

SELECT trx.*, 1 + hi - lo AS lengthFROM 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 開始變得有趣了！

準備好接下來的學習了嗎？

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ù)學處理速度很快，所以我們可以立即計算出這些是最好的求和：

| 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 sumsWITH 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 calcFROM draw_the_rest_of_the_*bleep*_owl

當你在讀這篇文章的時候，你可能會像我的朋友一樣:

不過，別擔心，解決方案也不是那么難(盡管由于算法的性質(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 totalsCROSS JOIN sumsGROUP BY totals.id, totals.total

在本文中，我將不解釋此解決方案的詳細信息，因為這個例子是從上一篇文章中選取的，你可以在這里查看:如何使用 SQL 查找最接近的子集和

希望你能愉悅地閱讀的相應細節(jié)，但一定要回來查看其余 4 個技巧：

7. 設限的累計計算

到目前為止，我們已經(jīng)學習了如何使用窗口函數(shù)用 SQL 進行“普通”的累計計算。那很容易。現(xià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 |

因此，當減去 AMOUNT -20000 這個大的負數(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)

我們要怎么做呢？

確切地說。使用模糊的、特定于供應商的 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 子句指定應用于電子表格中每個單元格的公式。與 MS Excel 不同，這些規(guī)則 / 公式集中在一個地方，而不是放在每個單元格中：

這種設計使得 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 自己的白皮書，所以請不要在本文中尋求進一步解釋，請閱讀優(yōu)秀的白皮書：

http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

8. 時間序列模式識別

如果你是從事欺詐檢測或在大型數(shù)據(jù)集上運行實時分析的任何其他領域，時間序列模式識別對你來說肯定不是一個新術語。

如果我們回看下“序列長度”的數(shù)據(jù)集，我們可能希望在時間序列上為復雜事件生成一個觸發(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ā)。

與前面的 MODEL 子句類似，我們可以使用添加到 Oracle 12c 中的 Oracle 特定的子句來執(zhí)行該操作：

SELECT ... FROM some_table-- 將此放在任何 table 之后盡心模式匹配-- table 的內(nèi)容MATCH_RECOGNIZE (...)

MATCH_RECOGNIZE 最簡單的應用程序包括以下子條款：

SELECT *FROM seriesMATCH_RECOGNIZE ( -- 模式匹配按此順序執(zhí)行 ORDER BY ... -- 這些時模式匹配產(chǎn)生的列 MEASURES ... -- 對行的簡短說明 -- 返回匹配結(jié)果 ALL ROWS PER MATCH -- 要匹配的事件的“正則表達式” PATTERN (...) -- “什么是事件”的定義 DEFINE ...)

這有些不可思議。讓我們看一些子句的實現(xiàn)示例：

SELECT *FROM seriesMATCH_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 對表進行排序，比較容易。
• 然后指定我們所需的值作為結(jié)果。我們需要“MEASURE” trg，它被定義為分類器，也就是我們隨后將在 PATTERN 中使用的文本。另外，我們希望匹配所有行。
• 然后，我們指定一個類似于正則表達式的模式。在該模式中以“S”代表開始事件，隨后是可選事件“R”，它代表重復事件，“X”代表特殊事件 X，隨后的一個或多個“R”代表再次重復。如果整個模式匹配，我們得到 SRXR 或 SRXRR 或 SRXRRR，即 X 將位于序列長度>=4 的第三位上。
• 最后，我們將 R 和 X 定義為相同的東西：當前行的 SIGN(AMOUNT) 事件與前一行的 SIGN(AMOUNT) 事件相同時。我們不必定義“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 |

我們可以在事件流中看到一個“X”。這正是我們所期望的。在序列長度> 3 的事件(同一符號)中的第三次重復。

太棒了！

因為我們并不真正關心“S”和“R”事件，所以我們可以刪除它們：

SELECT id, value_date, amount, CASE trg WHEN 'X' THEN 'X' END trgFROM seriesMATCH_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ù)量。我們不是以“關系”的方式來顯示它(即每個組是一行)，而是將整體旋轉(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 創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎

總結(jié)

以上是生活随笔為你收集整理的sqlserver拼接sql插入table_10个SQL技巧的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。