sql - 窗口函数 : PARTITION BY one column after ORDER BY another

Question

免责声明:显示的问题比我最初预期的要普遍得多。下面的示例取自另一个问题的解决方案。但现在我正在使用这个示例来解决更多问题 - 主要与时间序列相关(查看右侧栏中的“链接”部分)。

所以我试图首先更一般地解释这个问题:

---

我正在使用 PostgreSQL，但我确信这个问题也存在于其他支持 DBMS(MS SQL Server、Oracle 等)的窗口函数中。

---

Window functions可用于通过公共(public)属性或值将某些值组合在一起。例如，您可以按日期对行进行分组。然后您可以计算每个日期内的最大值或平均值或计算行数或其他任何内容。

这可以通过定义PARTITION 来实现。按日期分组适用于 PARTITION BY date_column。现在您想执行一项操作，该操作需要在您的组中进行特殊排序(计算行号或对列求和)。这可以通过 PARTITON BY date_column ORDER BY an_attribute_column 来完成。

现在考虑更精细的时间序列分辨率。如果你没有日期但有时间戳怎么办。那么你就不能再按时间列分组了。但尽管如此，按添加顺序分析数据可能很重要(时间戳可能是数据集的创建时间)。然后您意识到一些连续的行具有相同的值，并且您希望按这个公共(public)值对数据进行分组。但线索是这些行具有不同的时间戳。

这里的问题是您不能执行 PARTITION BY value_column。因为 PARTITION BY 强制先排序。因此，您的表将在分组之前按 value_column 排序，不再按时间戳排序。这会产生您意想不到的结果。

更一般地说:问题是确保特殊排序，即使有序列不是创建分区的一部分。

---

示例:

db<>fiddle

我有下表:

ts      val
100000  50
130100  30050
160100  60050 
190200  100
220200  30100 
250200  30100 
300000  300
500000  100
550000  1000  
600000  1000
650000  2000  
700000  2000
720000  2000
750000  300

我遇到了一个问题，我必须将 val 列的所有相关值分组。但我想在 ts 之前保留顺序。为此，我想为每个 val 组添加一个具有唯一 ID 的列

预期结果:

ts      val     group
100000  50      1
130100  30050   2
160100  60050   3
190200  100     4
220200  30100   5     \ same group
250200  30100   5     /
300000  300     6
500000  100     7
550000  1000    8     \ same group
600000  1000    8     /
650000  2000    9     \
700000  2000    9     | same group
720000  2000    9     /
750000  300     10

第一次尝试 是使用 rank 窗口函数，它可以正常完成这项工作:

SELECT 
    *,
    rank() OVER (PARTITION BY val ORDER BY ts)
FROM 
    test

但在这种情况下，这不起作用，因为 PARTITION BY 子句首先按其分区列(在本例中为 val)然后按其 对表进行排序code>ORDER BY 列。所以顺序是 val, ts 而不是 ts 的预期顺序。所以结果当然不是预期的。

ts       val     rank
100000   50      1
190200   100     1
500000   100     2
300000   300     1
750000   300     2
550000   1000    1
600000   1000    2
650000   2000    1
700000   2000    2
720000   2000    3
130100   30050   1
220200   30100   1
250200   30100   2
160100   60050   1

问题是:如何通过 ts 获取与订单相关的组 ID？

---

编辑:我在下面添加了自己的解决方案，但我对此感到非常不舒服。这似乎太复杂了。 我想知道是否有更好的方法来实现这个结果。

Answer 1

我自己想出了这个解决方案(希望别人能得到更好的):

demo:db<>fiddle

1. 按ts排序
2. 用 lag 窗口函数 ( https://www.postgresql.org/docs/current/static/tutorial-window.html ) 给出下一个 val 值
3. 检查下一个值和当前值是否相同。然后我可以打印出 0 或 1
4. 用有序的 SUM 对这些值求和。这会生成我正在寻找的组。他们对 val 列进行分组，但确保按 ts 列排序。

查询:

SELECT 
    *, 
    SUM(is_diff) OVER (ORDER BY ts) 
FROM (
    SELECT 
        *,
        CASE WHEN val = lag(val) over (order by ts) THEN 0 ELSE 1 END as is_diff
    FROM test 
)s

结果:

ts       val     is_diff   sum
100000   50      1         1
130100   30050   1         2
160100   60050   1         3
190200   100     1         4
220200   30100   1         5    \ group
250200   30100   0         5    /
300000   300     1         6
500000   100     1         7
550000   1000    1         8    \ group
600000   1000    0         8    /
650000   2000    1         9    \
700000   2000    0         9    | group
720000   2000    0         9    /
750000   300     1         10