自荐项目，SQL 语法增强工具，Sqlx

[taojy123]

项目推荐

• 项目地址：https://github.com/taojy123/sqlx

• 类别：其它（sql）

• 项目后续更新计划：
  目前语法还不支持 if 嵌套和 for 循环嵌套，后续会开发支持嵌套语法；另外目前 sqlx 已经做了 Sublime Text 插件，后续可能还会开发 VS Code 插件。

• 项目描述：
  强大的 SQL 语法拓展，目标是打造 "易读易写 方便维护" 的 sql 脚本，sqlx 意为 SQL Extension

• 推荐理由：
  适用于经常需要写复杂的 sql 查询做统计的同学，其实这也是本人在工作中的痛点，所以做了此工具，并希望同遇到相同困惑的同学分享。

• 插件支持：
  sqlx 已被 Sublime Text 官方收录，使用 Sublime Text 的同学可以直接搜索安装 Sqlx Builder 插件，并通过编辑器的 build 操作快捷的编译 sqlx 脚本。

• 插件项目地址：https://github.com/taojy123/SublimeText-Sqlx

• 应用场景示例：

假设有一张商品价目表(product)，每天价格变动的商品都会更新报价。

例如，苹果的最新价格为 10 元, 因为苹果最新的一次报价是在 20191211, 当时价格为 10 元。

name(商品名称)	price(价格)	date(报价日期)
苹果	15	20191208
香蕉	18	20191208
橘子	12	20191208
香蕉	16	20191209
橘子	11	20191209
苹果	11	20191210
橘子	13	20191210
苹果	10	20191211
香蕉	22	20191211
橘子	14	20191212

现在要求通过 sql 统计出 20191212 这天的平均价格 比 20191209 那天涨了多少 ?

正常情况下我们可能会写出这样的 sql

SELECT
    a1.avg_price AS `20191209 平均价格`,
    a2.avg_price AS `20191212 平均价格`,
    (a2.avg_price - a1.avg_price) AS `涨价金额`
FROM
    (
        -- 求出各类别 20191209 前最后一次报价的平均价格
        SELECT
            avg(product.price) AS avg_price
        FROM
            (
                -- 求出各商品在 20191209 前最后一次报价的日期
                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 前最后一次报价的平均价格
        SELECT
            avg(product.price) AS avg_price
        FROM
            (
                -- 先求出各商品在 20191212 前最后一次报价的日期
                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

得到统计结果如下:

20191209 平均价格	20191212 平均价格	涨价金额
14.0000	15.3333	1.3333

传统做法虽然得到的结果是正确的，但同时暴露出以下问题:

1. 子查询多层嵌套，代码可读性极低
2. t1 t2 两个子查询内容基本一致，也就说我们要维护两处相同的代码
3. a1 a2 两个子查询也基本一致，并且其中相同的注释我们要写两遍，感觉太"蠢"了
4. 这只是个很简单的示例，在实际工作中，针对更复杂的统计需求，代码的复杂度将会以指数形式递增

下面看看如何使用 sqlx 来解决上述问题:

func product_max_date(day)
    -- 子查询: 统计出各个商品在 {day} 前最后一次报价的日期
    (
        SELECT
            name,
            max(date) AS max_date
        FROM
            product
        WHERE
            date <= '{day}'
        GROUP BY
            name
    )
end

func date_avg_price(day):
    -- 子查询: 统计出 {day} 这天各个类别的平均价格
    (
        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 平均价格`,
    a2.avg_price AS `20191212 平均价格`,
    (a2.avg_price - a1.avg_price) AS `涨价金额`
FROM
    {date_avg_price(20191209)} AS a1
LEFT JOIN 
    {date_avg_price(20191212)} AS a2
ON true

优势非常明显:

1. 核心代码是一段短小的 SELECT，外加加上两个子查询的定义就搞定了，代码逻辑清晰，可读性高
2. a1 a2 使用类似 函数 的概念进行封装，通过传入不同的参数来生成不同的子查询内容
3. 相同逻辑的代码片段只需要写一遍，大大降低了代码维护的工作量
4. 使用 sqlx 提供的编译工具或插件，可快速编译成 sql 代码，在数据库中执行结果一致