PostgreSQL 窗口函数

March 22, 2020

原来除了 Modern C++、Modern CMake，我们还有 Modern SQL，真是佩服这种文艺复兴式的 branding。

窗口函数（Window Function）就是一个例子，它由 SQL:2003 引入，可以用来筛选结果集中与当前行存在指定关联的行。相比子查询，效率更高，用起来也更方便。

例如我们有一张去年全年每日收入的表 revenues，想根据这张表查一张报表，显示每季度总收入及其环比增长，就可以用窗口函数：

created_at	revenue
2019-01-01	123.45
2019-01-02	456.78
2019-01-03	420.00
...	...

SELECT
    date_part('quarter', created_at) AS quarter,
    sum(revenue) AS revenue,
    (sum(revenue) /
        lag(sum(revenue)) OVER ()) - 1 AS percentage
FROM revenues
GROUP BY 1
ORDER BY 1

得到的结果类似这样：

quarter	revenue	percentage
1	4530.50
2	4565.64	0.008
3	4933.01	0.080
4	4731.75	-0.041

SQL 中的 lag(sum(revenue)) OVER () 就是对窗口函数的调用了，其中 lag 函数就表示「上一条记录（季度）」。

窗口函数调用的特征是关键词 OVER

OVER 前的部分为窗口函数调用本身，用来指定针对窗口中内容的操作。既可以用 lag 这样专门的窗口函数，也可以用 sum 这种普通的聚合函数。

OVER 后的部分即为对窗口的定义，既可以是直接在括号里写出，也可以用稍后统一定义的窗口名，比如上面的 SQL 也可以写成：

SELECT
    date_part('quarter', created_at) AS quarter,
    sum(revenue) AS revenue,
    (sum(revenue) /
        lag(sum(revenue)) OVER w) - 1 AS percentage
FROM revenues
GROUP BY 1
WINDOW w AS ()
ORDER BY 1

窗口的定义

「窗口定义」中的窗口其实英文叫 Frame，即窗框。「窗口函数」中的窗口则是 Window，即窗户。没什么特别含义，应该就是叫着顺口、想着形象而已。

下面的例子里，我们用 PostgreSQL 的聚合函数 array_agg 列出窗口中有哪几行。

所有记录

括号中留空表示窗口中为结果中的所有行：

SELECT
    i,
    array_agg(i) OVER ()
FROM generate_series(0, 5) AS s(i)
ORDER BY 1

i	array_agg
0	0,1,2,3,4,5
1	0,1,2,3,4,5
2	0,1,2,3,4,5
3	0,1,2,3,4,5
4	0,1,2,3,4,5
5	0,1,2,3,4,5

我们可以看到每一行的对应窗口里，都包含了所有其它行。

相同分组

括号中还可以使用 PARTITION BY 指定分组的条件：

SELECT
    i,
    array_agg(i) OVER (
        PARTITION BY i % 2
    )
FROM generate_series(0, 5) AS s(i)
ORDER BY 1;

i	array_agg
0	0,2,4
1	1,3,5
2	0,2,4
3	1,3,5
4	0,2,4
5	1,3,5

我们可以看到每一行的对应窗口里，都包含了与它 i % 2 值相同的行。

指定范围

可以用 ROWS BETWEEN A AND B 来指定窗口中包含哪些行，例如：

SELECT
    i,
    array_agg(i) OVER (
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 
    )
FROM generate_series(0, 5) AS s(i)
ORDER BY 1;

i	array_agg
0	0,1,2,3,4,5
1	1,2,3,4,5
2	2,3,4,5
3	3,4,5
4	4,5
5	5

这里其实直接读 SQL 就明白了，是要求窗口从当前行开始，一直到最后一条记录结束。

排序

窗口定义里还可以用 ORDER BY 来排序，不过一旦排序，默认的范围就变成了 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRERNT ROW（从开头到当前行），如果不是想要的范围就需要显式指定。

SELECT
    i,
    array_agg(i) OVER (
        ORDER BY i DESC
    )
FROM generate_series(0, 5) AS s(i)
ORDER BY 1;

i	array_agg
0	5,4,3,2,1,0
1	5,4,3,2,1
2	5,4,3,2
3	5,4,3
4	5,4
5	5

常见窗口函数

lead 同一分组中，在当前行之后数的第 N 行
lag 同一分组中，在当前行之前的第 N 行
row_number 当前分组中的行号，从 1 开始
rank 当前分组中的排名，如果存在相同名次，会是 1、1、3、4……
dense_rank 当前分组中的排名，如果存在相同名次，会是 1、1、2、3……

参考

更新了博客的数学公式支持

March 12, 2020

这个 Typecho 博客搭成以来，就一直在用从这里下载到的 Markdown 插件，是对 PHP Markdown 的封装。

当年还不存在 XX-flavored Markdown 的概念，有些洁癖的我觉得坚持原生 Markdown 是最佳的选择，不够用时直接 HTML 来凑就好了。然而原生 Markdown 是没有数学公式支持的，如果用 $\TeX$ 语法，很难躲避代码被转义的命运。

单占一行的公式还可以手动用 HTML 块元素标签包裹，这样原生 Markdown 就不会对里面的内容转义了。但是行内的公式则不行，原生 Markdown 里没有任何办法禁止某些东西的转义（除了代码块，但是会把内容包裹在 <code> 里）。

所以最后我的选择是，把 $\TeX$ 代码作为代码块渲染以防止 Markdown 转义；然后强制让 MathJax 翻译 <code> 标签中的代码，显示公式。

这样做牺牲了「显示公式代码本身」的可能，不过够用了。

要解决的问题

时间一晃到了八年后的现在，翻翻以前的文章，参考链接里一个个都是 http，非常直观地给人一种「时代变了」的感觉。

如今 XX-flavored Markdown 已深入人心，在 Markdown 中插入数学公式似乎也形成了一些广为认可的写法。

位于行内时，多是使用 $公式代码$ 、$公式代码$ 的语法。
单独成块时，常见的则是 $$公式代码$$、\[公式代码\] 的语法。

是时候改一改这八年前的解决方案了。

被懒偷了去

首先想到的是 Typecho 早已原生支持 Markdown 文档，不需要再用插件实现了。要不先切换过去再搞？

然而，Typecho 原生的 Markdown 支持我非常不满意。因为它不允许内嵌任意 HTML，需要用 !!! 裹起来才行。放弃放弃。

于是看了看 PHP Markdown。虽然有 PHP Markdown Extra，但也已经是很多年前的东西，不再更新，更没有更新数学公式的可能。

所幸后来搜到了一个 PHP Markdown Extra with support for jsMath 仓库，非常开心，赶紧拿来替换了原先插件中得到 markdown.php。大体上是不错的，然而它有个 bug：行内的 $C_{ij} = \vec{u}_{rowi} \vec{v}_{colj}$ 还是被转义了，里面的一对下划线依旧被转成了 <em>，代码被破坏，导致公式转换失败。

解决方法

这样折腾了一圈以后非常绝望，甚至还想过换个静态网站生成器把博客重新搞一下的想法。

不过想想这个工程量，不禁还是摇摇头。既然是 bug，要不然我们来 Debug 一下？

不过那代码看着看着，忽然感觉 PHP Markdown 的代码也没有想象的那么复杂。于是干脆换回官方的 PHP Markdown Extra，在那上面依葫芦画瓢改了一通，很快搞定了 $ 和 $$ 的功能。

基本上就是模仿 Markdown 中代码块的语法。

行内 $\vec{a}$ 这么写。

$$
\begin{aligned}
S &= 成块的这么写 \\
  &= 就可以了
\end{aligned}
$$

具体补丁见这个 Gist，基于 PHP Markdown Extra 1.2.8。

下载到两者后，使用 patch markdown.php math.patch 就可以打上补丁使用了。

补丁后的版本中有个 MARKDOWN_MATH_CLASS 常量，非空时会为数学公式代码所在的 <span> 或者 <div> 加上对应的 class。这样就可以在 JS 脚本中找到具有这些 class 的节点，用 MathJax / KaTeX 做精准打击，不用怕正文中的普通单词因为夹在 $ 中间而被误认为是公式了。

以上。

使用 Conan 管理 C++ 依赖

March 7, 2020

现代 CMake 使用 C++ 依赖项时已经相对方便了。比如 find_package(OpenSSL 1.0 REQUIRED) 就可以自动在本地机器上查找已安装的与 OpenSSL 1.0 兼容的包，然后就可以直接 target_link_libraries(targetName PRIVATE OpenSSL::SSL) 使用，不必再手动写头文件和库文件配置。

要做到「本地机器上已安装」，macOS 和 Linux 系统都非常方便，大多数库都可以直接通过系统级包管理工具安装，而 Windows 上就稍显麻烦。我之前比较喜欢的是，比较复杂的库还是留着在各平台手动安装，简单的则使用 CMake 的 FetchContent 模块下载使用。但这样的缺点是每次重新生成工程时，都需要下载一遍依赖并编译。尤其最近国内的网络状态，HTTPS 连接 GitHub 经常超慢。所以想想还是用现成的包管理工具吧。

目前市面上流行的包管理工具中：vcpkg 虽然很多人用，但是微软那套逻辑我始终表示审美不能；Hunter 虽然是纯 CMake 解决方案，但是官方涵盖的库偶有不足；而 Conan 我一开始是不喜欢的，不但需要使用 Python 安装，而且它的前世 biicode 当年也风光过现在已经挂了。不过现在看来，Conan 似乎是这几个之中比较成熟好用的解决方案，至少，符合我的审美就是了。

安装

官方推荐用 Python 在虚拟环境里用 pip install conan 安装，可以随时保持最新。

我在 macOS 上使用 Homebrew 安装，可以少管理一个虚拟环境。感觉 Arch Linux 这样滚动更新的系统也可以直接使用系统包管理工具安装。

找到想要的库

想要使用 spdlog 时，首先搜索：

$ conan search spdlog -r conan-center
Existing package recipes:

spdlog/0.14.0@bincrafters/stable
spdlog/0.16.3@bincrafters/stable
spdlog/0.17.0@bincrafters/stable
spdlog/1.0.0@bincrafters/stable
spdlog/1.1.0@bincrafters/stable
spdlog/1.2.1@bincrafters/stable
spdlog/1.3.0@bincrafters/stable
spdlog/1.3.1@bincrafters/stable
spdlog/1.4.1@bincrafters/stable
spdlog/1.4.2
spdlog/1.4.2@bincrafters/stable
spdlog/1.5.0

命令行中的 -r conan-center 表示所要搜索的仓库，conan-center 是官方自带的默认仓库，如果你本地添加了多个仓库的话，也可以用 all 表示搜索所有仓库。不带这个选项时则是在本地的缓存中查找。

搜索结果中每一行都是一个可用的包的名称，使用 @user/channel 后缀的是完整的包命名方式。官方 conan-center 仓库中，最近的包都是通过 CI 自动构建二进制文件的，这些包使用纯 name/version 的命名方式。

想要知道某个版本/包的详情，可以使用这样的命令查看：

$ conan inspect spdlog/1.5.0

会列出一些信息和安装时的可选参数。

当然，你也可以直接在网站 https://conan.io/center/ 查找 conan-center 仓库中的包。

依赖的指定、安装、使用

一般使用名为 conanfile.txt 的纯文本文件指定依赖，格式类似 INI 文件。

[requires]
spdlog/1.5.0

[generators]
cmake_find_package

[requires] 部分很简单，列出你所需要依赖的包的名称即可。[generators] 部分指定所需要的「生成器」，可以生成与 CMake、SCons 等工具的对接文件。

使用 conan install /path/to/source-dir 可以安装依赖并生成对接文件，参数为包含 conanfile.txt 的目录。当然，这样做会把「对接文件」生成在当前目录，可以使用 -if 参数指定输出目录，推荐放在 CMake 的构建目录。

这样，Conan 就会把 1.5.0 版本的 spdlog 安装到自己管理的目录（一般是 ~/.conan），然后在输出目录输出一个 Findspdlog.cmake 文件。

CMakeLists.txt 中，要让 find_package 使用 Findspdlog.cmake 文件，把它所在的目录加入到 CMAKE_MODULE_PATH 中即可：

# 因为我们把 Findspdlog.cmake 输出到了构建目录
list(APPEND CMAKE_MODULE_PATH "${CMAKE_BINARY_DIR}")

# 按照正常方式搜索
find_package(spdlog REQUIRED)

# ...

# 这个生成器导出的目标是 package::package
target_link_libraries(targetName PRIVATE spdlog::spdlog)

当然，官方教程中使用的是 cmake 生成器，它不会生成 FindXXX.cmake，而是生成一个 conanbuildinfo.cmake，你需要在 CMakeLists.txt 中手动初始化：

include(${CMAKE_BINARY_DIR}/conanbuildinfo.cmake)
conan_basic_setup(TARGETS)

# ...

# 这个生成器导出的目标是 CONAN_PKG::package
target_link_libraries(targetName PRIVATE CONAN_PKG::spdlog)

这种方法把 Conan 显式写入了 CMake 配置里，我个人不是很喜欢。（但是 CONAN_PKG::package 的表述确实比 package::package 好一些，因为一些库官方提供的目标并不都是 package::package。）

其它零碎

构建版本

Conan 默认安装/构建的二进制是 Release 版本的。而 CMake 的默认构建方式则是 Debug。

所以，尤其在使用类似 MSVC 的编译器时，你可能需要手动指定安装 Debug 版：

$ conan install . -s build_type=Debug

当然你也可以试试 cmake_multi 或者 cmake_find_package_multi 生成器，可以同时安装 Debug 和 Release 版本。抑或是使用官方提供的CMake 集成，自己写脚本把 CMake 和 Conan 的构建类型同步起来。

包的参数

在使用 conanfile.txt 指定依赖时，还可以同时指定一些可选参数。比如指定使用 spdlog 的动态链接版本：

[options]
spdlog:shared=True

好了，这就是大致的 Conan 使用介绍。

真正上手，还请参阅官方文档 https://docs.conan.io/。

如何在 RESTful API 中表示批量操作

October 24, 2019

作为 RESTful API 的拥趸，最常被质疑的就是「批量操作很难 RESTful 起来」，似乎找不出对应批量操作的 HTTP 动词来。

早年间看文章，不少解决方案是先创建一个临时资源表示需要批量操作的资源，然后针对这个临时资源进行操作。将批量操作拆分到两个接口，多少有些削足适履的意思。

现在再看，情况就明朗起来了：用 PATCH 方法配合 JSON Patch 就能很好地表示批量操作。

PATCH 方法平时比较少用到，即便使用，一般也是以 JSON Merge Patch 格式更新单个资源的部分字段。例如修改一篇文章的发布时间和标题、删除广告：

PATCH /articles/42 HTTP/1.1
Content-Type: application/merge-patch+json

{
    "title": "Oops!",
    "published_at": "2019-01-02T03:04:05Z",
    "advertisement": null
}

相当于是给出了资源的部分表示，要求合并服务器上的表示与客户端所发送的表示。而 JSON Patch 格式的内容则是针对修改的结构化描述，例如上面的例子就会变成：

PATCH /articles/42 HTTP/1.1
Content-Type: application/json-patch+json

[
    {"op": "replace", "path": "/title", "value": "Oops!"},
    {"op": "replace", "path": "/published_at", "value": "2019-01-02T03:04:05Z"},
    {"op": "remove", "path": "/advertisement"}
]

其中 op 表示需要进行的操作，path 则是 JSON Pointer，用来指向 URL 所表示资源中的具体某个对象。数组中的多个操作依次进行。

使用 JSON Patch 来表示对单独资源的修改有些大材小用。不过如果将 JSON Patch 应用于合集资源，就可以很方便地表示所需的批量操作了。例如批量删除 ID 为 42 和 43 的文章，同时将 ID 为 45 的文章设为隐藏，并且新建一篇文章：

PATCH /articles HTTP/1.1
Content-Type: application/json-patch+json

[
    {"op": "remove", "path": "/42"},
    {"op": "remove", "path": "/43"},
    {"op": "replace", "path": "/45/visible", "value": false},
    {"op": "add", "path": "/-", "value": {
        "title": "Start Wars",
        "content": "A long time ago, in a country far, far away..."
    }}
]

甚至有些过于批量……

上面例子中，因为 PATCH 操作的 URL 资源是 /articles，所以 JSON Pointer /42 指向的就是 /articles/42 资源；同理 /45/visible 指向 /articles/45 资源的 visible 字段。而 /- 中的 - 则是 JSON Patch 中用来表示数组末尾的特殊索引。

这样一来，就可以名正言顺地对合集资源做 PATCH，进行批量操作了 😄

记一次有意义的调试

July 7, 2018

最近阳光明媚，天气晴朗，我为了更好地折腾理解 CMake，尝试把以前在 macOS 上写的一个 OpenGL 程序从 Makefile 改成 CMake 构建，顺带看看同样的东西在 Windows 上编译、运行效果如何。然而在 Windows 上用 MSVC 2015 构建后运行，发现场景里的东西都没有了，并且没有任何错误输出。

因为似乎坊间传言 Windows 对 OpenGL 的支持很迷，所以我的第一直觉是 GLSL 的兼容问题，不过这个角度似乎很难排查。

所以还是搞个最小的可重现问题的代码吧，于是从经典的「三角形」Hello World 开始很快地试了一下，发现在 Shader 里做矩阵乘法就有问题；进而发现只要不乘投影矩阵就没有大问题，至少场景里的东西还是可以显示出来的。

蛤？这是什么鬼？总不见得 glm::perspective 有移植性问题吧？

经过一番调试，终于找到了问题所在，结论让人哭笑不得：

我在代码里自己封装了摄像机类，初始化时需要设置投影矩阵所需的远近裁剪平面，相当于下面这样。

Camera(float far, float near)
    : far_{far}, near_{near}
{
    /* blah blah */
}

虽然在其它平台没什么问题，但 windows.h 里有这种东西：

#define far
#define near

于是代码就相当于变成了

Camera(float far, float near)
    : far_{}, near_{}
{
    /* blah blah */
}

把远近裁剪平面的两个变量都初始化成了 0，也难怪场景里什么东西都看不见了。

唉，写代码的时候忘记了 far / near 指针这种历史遗留爆点，不过还是要吐槽下 windows.h 如此暴力的解决方法。

以上，真是有意义的一天呀～

TimothyQiu's Blog

keep it simple stupid