如果你經(jīng)常需要寫大量的 SQL腳本 來(lái)進(jìn)行數(shù)據(jù)分析工作，那你可能值得擁有這款神器:

https://github.com/taojy123/sqlx

SQLx 意為 SQL Extension，強(qiáng)大的 SQL 語(yǔ)法拓展，目標(biāo)是打造 "易讀易寫 方便維護(hù)" 的 SQL 腳本。

應(yīng)用場(chǎng)景

假設(shè)有一張商品價(jià)目表(product)，每天價(jià)格變動(dòng)的商品都會(huì)更新報(bào)價(jià)。

例如，蘋果的最新價(jià)格為 10 元, 因?yàn)樘O果最新的一次報(bào)價(jià)是在 20191211, 當(dāng)時(shí)價(jià)格為 10 元。

現(xiàn)在要求通過(guò) sql 統(tǒng)計(jì)出 20191212 這天的平均價(jià)格 比 20191209 那天漲了多少 ?

正常情況下我們可能會(huì)寫出這樣的 sql

SELECT
    a1.avg_price AS `20191209 平均價(jià)格`,
    a2.avg_price AS `20191212 平均價(jià)格`,
    (a2.avg_price - a1.avg_price) AS `漲價(jià)金額`
FROM
    (
        -- 求出各類別 20191209 前最后一次報(bào)價(jià)的平均價(jià)格
        SELECT
            avg(product.price) AS avg_price
        FROM
            (
                -- 求出各商品在 20191209 前最后一次報(bào)價(jià)的日期
                SELECT
                    name,
                    max(date) AS max_date
                FROM
                    product
                WHERE
                    date <= '20191209'
                GROUP BY
                    name
            ) AS t1
        LEFT JOIN product
        ON t1.name = product.name AND t1.max_date = product.date
    ) AS a1
LEFT JOIN
    (
        -- 再求出各類別 20191212 前最后一次報(bào)價(jià)的平均價(jià)格
        SELECT
            avg(product.price) AS avg_price
        FROM
            (
                -- 先求出各商品在 20191212 前最后一次報(bào)價(jià)的日期
                SELECT
                    name,
                    max(date) AS max_date
                FROM
                    product
                WHERE
                    date <= '20191212'
                GROUP BY
                    name
            ) AS t2
        LEFT JOIN product
        ON t2.name = product.name AND t2.max_date = product.date
    ) AS a2
ON true

得到統(tǒng)計(jì)結(jié)果如下:

傳統(tǒng)做法雖然得到的結(jié)果是正確的，但同時(shí)暴露出以下問(wèn)題:

1. 子查詢多層嵌套，代碼可讀性極低
2. t1 t2 兩個(gè)子查詢內(nèi)容基本一致，也就說(shuō)我們要維護(hù)兩處相同的代碼
3. a1 a2 兩個(gè)子查詢也基本一致，并且其中相同的注釋我們要寫兩遍，感覺(jué)太"蠢"了
4. 這只是個(gè)很簡(jiǎn)單的示例，在實(shí)際工作中，針對(duì)更復(fù)雜的統(tǒng)計(jì)需求，代碼的復(fù)雜度將會(huì)以指數(shù)形式遞增

下面看看如何使用 sqlx 來(lái)解決上述問(wèn)題:

func product_max_date(day)
    -- 子查詢: 統(tǒng)計(jì)出各個(gè)商品在 {day} 前最后一次報(bào)價(jià)的日期
    (
        SELECT
            name,
            max(date) AS max_date
        FROM
            product
        WHERE
            date <= '{day}'
        GROUP BY
            name
    )
end

func date_avg_price(day):
    -- 子查詢: 統(tǒng)計(jì)出 {day} 這天各個(gè)類別的平均價(jià)格
    (
        SELECT
            avg(product.price) AS avg_price
        FROM
            {product_max_date($day)} AS t1
        LEFT JOIN product 
        ON t1.name = product.name AND t1.max_date = product.date
    )
end

SELECT
    a1.avg_price AS `20191209 平均價(jià)格`,
    a2.avg_price AS `20191212 平均價(jià)格`,
    (a2.avg_price - a1.avg_price) AS `漲價(jià)金額`
FROM
    {date_avg_price(20191209)} AS a1
LEFT JOIN 
    {date_avg_price(20191212)} AS a2
ON true

優(yōu)勢(shì)非常明顯:

1. 核心代碼是一段短小的 SELECT，外加兩個(gè)子查詢的定義就搞定了，代碼邏輯清晰，可讀性高
2. a1 a2 使用類似 函數(shù) 的概念進(jìn)行封裝，通過(guò)傳入不同的參數(shù)來(lái)生成不同的子查詢內(nèi)容
3. 相同邏輯的代碼片段只需要寫一遍，大大降低了代碼維護(hù)的工作量
4. 使用 sqlx 提供的編譯工具或插件，可快速編譯成 sql 代碼，在數(shù)據(jù)庫(kù)中執(zhí)行結(jié)果一致

如何使用

先看一下 sqlx 的基本語(yǔ)法介紹，很簡(jiǎn)單 5 分鐘就看明白學(xué)會(huì)了。

接下來(lái)就開(kāi)始編寫你的 sqlx 腳本吧，保存文件時(shí)拓展名設(shè)為 .sqlx

然后下載 sqlx 的編譯工具，如果你使用 Windows 64位系統(tǒng) 可以直接下載 sqlx.exe 。雙擊運(yùn)行，即可將當(dāng)前目錄下的 sqlx 腳本文件一鍵編譯為 sql。

如果你使用 Sublime Text 編輯器，可以搜索下載 Sqlx Builder 插件來(lái)使用，更加方便。