😀 😥 👩🏿 SQL HowTo：使用窗口函数构建链 👌 🥜 💇🏻

有时，在分析数据时，会出现区分样本中的“链”的问题，即区分记录的有序序列，每个记录都满足特定条件。

这可以是记录本身数据的条件，也可以是相对于一个或多个先前记录的复杂表达式-例如，关闭时间样本之间的间隔长度。

当样本连接到自身时，或者使用“数据之外”的某些事实时，传统的解决方案为“自连接”提供了不同的选项-例如，记录必须具有严格定义的步骤（N + 1，“每天”，...））

第一种选择通常会导致记录数量呈二次方复杂性 ，这在大样本中是不可接受的，而第二种选择很容易在源数据中突然没有样本的情况下“崩溃” 。

但是此任务将帮助我们有效地解决PostgreSQL中的窗口函数。

任务：盘点别人的钱

考虑链的最简单情况-当连续性条件由记录本身的数据确定时。

不必单独执行所有其他操作。但是为了清楚起见，我将其分解为连续的步骤，最后我将展示优化的内容和方法。

假设我们有一家小型银行，可以维护表格中客户帐户的余额。收支交易一旦发生，该日期也会在当天结束时确定总帐单金额。

在漫长的新年假期之后，该银行决定奖励其客户-今年开户的每个人在没有“重置”该帐户 的最长连续时间内 ，还额外获得了平均每日余额的 1％。

这是我们“链”的连续性的标准。好吧，数据的排序将由余额的日期确定。

他们给我们带来了这样的CSV，并要求他们快速计算谁应该从银行获得多少慷慨的捐助：

date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5

只需注意一些在此数据上值得注意的事实：

01.01是个假期，银行没有工作。因此，没有客户记录当天余额的变化，但是他们的帐户中有钱。也就是说，每天迭代的“蛮力”算法将无法正常工作。
04.01 Alice没有执行任何操作，因此没有任何条目。但是在05.01之前，她帐户中的金额为非零-在分析中必须考虑到这一点。
我们对01.01-12.01进行了分析，但是在此期间结束时，爱丽丝的帐户余额为非零。我们还考虑了限制期限的需要。

CSV到表格

从CSV导入的最佳方法是使用COPY运算符。但是我们将尝试通过正则表达式来预热：

 CREATE TEMPORARY TABLE tbl AS SELECT to_date(prt[1], 'DD.MM.YYYY') dt , prt[2] client , prt[3]::numeric(32,2) balance FROM ( SELECT regexp_split_to_array(str, ';') prt FROM ( SELECT regexp_split_to_table( $$ date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5 $$ , E'\\n') str ) T WHERE str <> '' OFFSET 1 ) T;

从某种意义上说，这是一种“不诚实”的方法，因为它将无法正确地消化，例如，将分隔符屏蔽在了场的主体中。但对于大多数简单的应用程序-合适。

步骤1：修正申请条件

在我们的情况下，链连续性条件是非零余额。为了清楚起见，我们将其显示为单独的字段，以按客户的时间顺序排列：

 SELECT * , balance > 0 cond FROM tbl ORDER BY client, dt;

 dt | client | balance | cond ------------------------------------ 2020-01-01 |  | 150.00 | t 2020-01-02 |  | 100.00 | t 2020-01-03 |  | 200.00 | t 2020-01-05 |  | 0.00 | f 2020-01-06 |  | 50.00 | t 2020-01-08 |  | 0.00 | f 2020-01-09 |  | 0.00 | f 2020-01-10 |  | 5.00 | t 2020-01-01 |  | 100.00 | t 2020-01-02 |  | 150.00 | t 2020-01-08 |  | 200.00 | t 2020-01-09 |  | 0.00 | f

步骤2：计算缺失

请注意，鲍勃的金额没有从02.01更改为08.01。并且根据问题的情况，我们必须计算平均每日剩余时间-也就是说，我们需要有关这些“遗漏”天数的信息。或至少天数保持不变：

 coalesce(lead(dt) OVER(PARTITION BY client ORDER BY dt), '2020-01-12') - dt days

 dt | client | balance | cond | days ------------------------------------------- 2020-01-01 |  | 150.00 | t | 1 2020-01-02 |  | 100.00 | t | 1 2020-01-03 |  | 200.00 | t | 2 2020-01-05 |  | 0.00 | f | 1 2020-01-06 |  | 50.00 | t | 2 2020-01-08 |  | 0.00 | f | 1 2020-01-09 |  | 0.00 | f | 1 2020-01-10 |  | 5.00 | t | 2 2020-01-01 |  | 100.00 | t | 1 2020-01-02 |  | 150.00 | t | 6 2020-01-08 |  | 200.00 | t | 1 2020-01-09 |  | 0.00 | f | 3

使用lead（）窗口函数，我们从下一条记录开始按顺序学习日期，并通过合并限制了上一条记录的间隔。同时，他们使用了有用的属性，即PostgreSQL中两个日期之间的差返回它们之间的整数天数 。

作为几乎免费的奖金，我们获得了零余额记录的所有相同信息。但是，如果有很多行没有满足我们的条件，我们就不感兴趣了，那么在CASE下进行这样的计算就很有意义，以节省服务器资源。

步骤3：找到断点

我们感兴趣的每个链的起点是先前计算的条件的值相对于先前的记录发生变化的点。我们将使用lag（）函数找到这些点：

 lag(cond) OVER(PARTITION BY client ORDER BY dt) IS DISTINCT FROM cond chain_start

 dt | client | balance | cond | days | chain_start --------------------------------------------------------- 2020-01-01 |  | 150.00 | t | 1 | t 2020-01-02 |  | 100.00 | t | 1 | f 2020-01-03 |  | 200.00 | t | 2 | f 2020-01-05 |  | 0.00 | f | 1 | t 2020-01-06 |  | 50.00 | t | 2 | t 2020-01-08 |  | 0.00 | f | 1 | t 2020-01-09 |  | 0.00 | f | 1 | f 2020-01-10 |  | 5.00 | t | 2 | t 2020-01-01 |  | 100.00 | t | 1 | t 2020-01-02 |  | 150.00 | t | 6 | f 2020-01-08 |  | 200.00 | t | 1 | f 2020-01-09 |  | 0.00 | f | 3 | t

使用IS DISTINCT FROM运算符而不是<>，我们避免了将每个客户端的第一个记录与NULL比较的问题。相应地，值TRUE是新链的开始，FALSE是其延续的所有行。

步骤4：链接链接

要将数据分组到每个单独的链中，最简单的方法是为其所有记录分配相同的标识符 。链本身的序列号非常适合它。它完全等于在样本中发现的较高的链“起点”数目 。

可以通过布尔值总和的“窗口”求和（{boolean} ::整数）来计算它们，也可以通过对与count（*）FILTER匹配的记录数进行计数（在{boolean}条件下）。我们将使用第二个选项：

 count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid

 dt | client | balance | cond | days | chain_start | grpid ----------------------------------------------------------------- 2020-01-01 |  | 150.00 | t | 1 | t | 1 2020-01-02 |  | 100.00 | t | 1 | f | 1 2020-01-03 |  | 200.00 | t | 2 | f | 1 2020-01-06 |  | 50.00 | t | 2 | t | 2 2020-01-10 |  | 5.00 | t | 2 | t | 3 2020-01-01 |  | 100.00 | t | 1 | t | 1 2020-01-02 |  | 150.00 | t | 6 | f | 1 2020-01-08 |  | 200.00 | t | 1 | f | 1

在这一步，我们已经知道了每个链中所有链接的长度，我们不再需要“无趣的”记录，因此只需将它们过滤掉即可。

步骤5：放置链

要计算一条链中所有天的平均值，我们需要总天数和“积分”余额：

 SELECT client , min(dt) chain_dt , sum(days * balance) balance , sum(days) days FROM ... GROUP BY 1, grpid ORDER BY 1, grpid;

 client | chain_dt | balance | days -------------------------------------  | 2020-01-01 | 650.00 | 4  | 2020-01-06 | 100.00 | 2  | 2020-01-10 | 10.00 | 2  | 2020-01-01 | 1200.00 | 8

步骤6：寻找高点

使用DISTINCT ON，我们将为每个客户留下一条记录（最大天数）：

 SELECT DISTINCT ON(client) * FROM ... ORDER BY client, days DESC;

 client | chain_dt | balance | days -------------------------------------  | 2020-01-01 | 650.00 | 4  | 2020-01-01 | 1200.00 | 8

实际上，仅此而已...

我们结合并优化

摘要要求

 WITH step123 AS ( SELECT * , CASE WHEN cond THEN lag(cond) OVER(w) IS DISTINCT FROM cond END chain_start , CASE WHEN cond THEN coalesce(lead(dt) OVER(w), '2020-01-12') - dt END days FROM tbl , LATERAL(SELECT balance > 0 cond) T WINDOW w AS (PARTITION BY client ORDER BY dt) ) , step4 AS ( SELECT * , count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid FROM step123 WHERE cond ) SELECT DISTINCT ON(client) client , sum(days) OVER(w) days , min(dt) OVER(w) chain_dt , sum(days * balance) OVER(w) balance FROM step4 WINDOW w AS (PARTITION BY client, grpid) ORDER BY 1, 2 DESC;

在这里，我们结合并优化了前三个步骤：

LATERAL子查询使我们能够计算一个附加字段，而无需不必要地遍历选择并立即在函数中使用它
删除WINDOW下的常规定义有助于PostgreSQL避免进行双重排序以形成“窗口”并在一个WindowAgg节点中计算两个函数
CASE下的 “惰性” 功能计算减少了执行的操作数量

同样，我们结合了以下两个步骤。但是用于计算聚合（客户端，grpid）和唯一化（客户端，总和（天））的“窗口”顺序并不重合，因此，最后一个块中仍然有两个Sort节点-WindowAgg之前和Unique之前。

[看explain.tensor.ru]

我注意到，在对链进行编号时，首先满足WHERE条件 ，因此窗口函数生成的编号原来是连续的。

SQL HowTo：使用窗口函数构建链