在具有特定值的连续行上定义窗口

Question

编辑2记住的解决方案是count( <GROUP-START-COND> or null ) over ( order by <SORTING> ) AS groupnr，当您拥有一个按<SORTING>排序的表时，新的行组由<GROUP-START-COND>发出信号，并且您需要一个计数器，该计数器随每组行的增加而增加，并且在整个组中保持不变。

原始问题

给出这样的桌子：

╔════════╤═══════╤═══════╤════════════════════════════════════...
║ linenr │ level │  key  │ value                              ...
╠════════╪═══════╪═══════╪════════════════════════════════════...
║      9 │     1 │ title │ Text processing umbrella: Parse / T...
║     10 │     1 │ tags  │ text-processing typesetting markdow...
║     11 │     1 │ about │ unified is an interface for process...
║     12 │     2 │ ...   │ and rehype, but it also allows for ...
║     13 │     1 │ note  │ EXAMPLE                            ...
║     16 │     1 │ tags  │ foo bar baz                        ...
║     17 │     1 │ tags  │ ctx/tag spaceships/orville         ...
...

我如何定义一个window，允许我对属于同一组的所有values进行恢复，其中一个组被定义为具有相邻的linenrs的行，其中第一行有level = 1和key，下面的行有level = 2和key = '...' (只使用这些条件中的一个就足够了)。

我正试图想出一个涉及over ( ... rows between current row ... )的公式，但被卡住了；也许先给组编号和组数的集合编号是一个很好的解决方案。

编辑--我意识到我的问题可能不是最清晰的，缺少一个有用的例子，也许更好地发布在dba.stackexchange.com上，因此这里有一个改进版本供您参考：

更新的问题

我有下表中的数据如下所示；linenrs是单调增长的，但不一定是连续的；当key字段包含一个省略号...，表示从上面继续的条目时：

create table source (
  linenr    integer unique not null,
  key       text not null,
  value     text );

insert into source values
  (  2, 'tags',  'a'          ),
  (  3, '...',   'b'          ),
  (  4, 'title', 'The Title'  ),
  (  5, 'note',  'this is'    ),
  (  6, '...',   'an EXAMPLE' ),
  (  8, 'title', 'over'       ),
  (  9, '...',   'three'      ),
  ( 10, '...',   'lines'      ),
  ( 11, 'about', 'grouping'   );

现在，我希望有一个视图，其中根据key字段的内容分配组号；组号不必是连续的，但是对于以...以外的键开头并在key是...的所有行中继续的每一组行来说，组号应该是不同的，如下所示：

╔════════╤═══════╤═══════╤════════════╗
║ linenr │ group │  key  │   value    ║
╠════════╪═══════╪═══════╪════════════╣
║      2 │     1 │ tags  │ a          ║
║      3 │     1 │ ...   │ b          ║
║      4 │     2 │ title │ The Title  ║
║      5 │     3 │ note  │ this is    ║
║      6 │     3 │ ...   │ an EXAMPLE ║
║      8 │     4 │ title │ over       ║
║      9 │     4 │ ...   │ three      ║
║     10 │     4 │ ...   │ lines      ║
║     11 │     5 │ about │ grouping   ║
╚════════╧═══════╧═══════╧════════════╝

我试图用windows /分区和tabibitosan模式来实现这一点，但是还没有想出任何可行的方法；而且，在前面的行中使用lag()并不能帮助使用...的多个连续行。在电子表格中，这是一件非常容易做到的事情，但在SQL中，我似乎不能引用当前查询的前一行，对吗？

对解决方案的讨论

事实证明，有一种解决方案非常简单，很伤人(不是自己想出来的)：

select
    linenr                              as linenr,
    key                                 as key,
    value                               as value,
    sum( rst ) over ( order by linenr ) as group_nr
  from ( select
    linenr,
    key,
    value,
    case when key != '...' then 1 end   as rst
  from source ) as x;

这是因为我们为启动组的行分配了一个1，而对其他行则分配了一个null；然后，对所有行(按正确的顺序)的sum()明将把null作为零对待，这将导致所有组起始行都得到一个新的group_id和所有后续行来保持这个计数。如果你知道如何..。

学分分配给用户McNets。

这同样可以在一个简短的和值得纪念的一行中完成，对于它，请参阅下面Erwin Brandstetter的编辑和答案。

编辑2评论者正确地抱怨我编辑的问题是一个真正的新问题。我认为其结果是，当解决一个棘手的问题时，人们应该尝试找到一个不会转动的特殊螺丝，并想出一个小规模的模型来突出这个特定的部分而忽略其他的部分。在这种情况下，用组号对行进行聚合对我来说并不困难，而是分配组号；此外，“定义一个窗口，允许我对所有值进行array_aggregate”并不是问题的一部分，它是我想象中的解决方案的一部分。

Answer 1

对最新(新)问题的答复：

SELECT *
     , count(key <> '...' OR NULL) OVER (ORDER BY linenr) AS grp
FROM   source;

参见dba.SE上的dupe：

• How can I assign group IDs depending on content?

对原问题的回答：

假设当前Postgres 10和linenr被定义为UNIQUE，这将实现您所描述的目标：

SELECT min(linenr) AS lines_from
     , max(linenr) AS lines_to
     , array_agg(value) AS value_arr
FROM  (
   SELECT linenr, level, value
        , count(level = 1 OR NULL) OVER (ORDER BY linenr) AS grp
        , row_number() OVER (ORDER BY linenr) - linenr    AS adjacent
   FROM   tbl
   ORDER  BY linenr
   ) sub
GROUP  BY grp, adjacent  -- same group, adjacent numbers
HAVING min(level) = 1    -- but only groups that start with level 1 
ORDER  BY lines_from;

具有扩展测试用例的SQL Fiddle。

相关的，有更多的解释：

• Select longest continuous sequence

关于count(level = 1 OR NULL)

• For absolute performance, is SUM faster or COUNT?

Answer 2

这可以用Tabibitosan方法来完成。

select array_agg(value) FROM
(
select t.*, row_number() OVER (ORDER BY linenr ) - 
       row_number() OVER (PARTITION BY CASE WHEN level = 2 and key = '...'
                          THEN 1 ELSE 0 END ORDER BY linenr ) as chg
FROM   Table1 as t
  ) as a 
  WHERE (level,key)  <> ( 2,'...')
  GROUP BY chg
  ORDER BY chg;