SQL 组表按 "leading rows"没有 pl/sql

Question

我有这个包含两列的表格(简短示例)

1 a
2 a
3 a3
4 a
5 a
6 a6
7 a
8 a8
9 a

我想将它们分组/划分为由前导“a”分隔的组，理想情况下添加这样的另一列，这样我就可以轻松地处理这些组。

1 a  0
2 a  0
3 a3 3
4 a  3
5 a  3
6 a6 6
7 a  6
8 a8 8
9 a  8

问题是表的设置是动态的，所以我不能使用静态滞后或前导函数，任何想法如何在 postgres 9.5 版中没有 pl/sql 的情况下做到这一点

Answer 1

假设前导部分是单个字符。因此，表达式 right(data, -1) 用于提取组名。适应您的实际前缀。

该解决方案使用两个 window functions , 不能嵌套。所以我们需要一个子查询或 CTE。

SELECT id, data
     , COALESCE(first_value(grp) OVER (PARTITION BY grp_nr ORDER BY id), '0') AS grp
FROM (
   SELECT *, NULLIF(right(data, -1), '') AS grp
        , count(NULLIF(right(data, -1), '')) OVER (ORDER BY id) AS grp_nr
   FROM   tbl
   ) sub;

准确地产生您想要的结果。

NULLIF(right(data, -1), '') 获取有效的组名或 NULL 如果没有。

count() 只计算非空值，因此我们对子查询中的每个新组都获得更高的计数。

在外部查询中，我们将每个 grp_nr 的第一个 grp 值作为组名，并默认为 '0' 和 COALESCE第一个没有名字的组(到目前为止有一个 NULL 作为组名)。

我们也可以使用 min() 或 max() 作为外部窗口函数，因为只有 一个 非空值无论如何每个分区。 first_value() 可能是最便宜的，因为行已经排序。

注意组名 grp 是数据类型 text。如果这些是干净(且可靠)的整数，您可能希望转换为整数。