主页 / dba / 问题 / 197125
Accepted
John Frazer
Asked: 2018-02-06 13:22:16 +0800 CST

如何根据内容分配组 ID？

  • 772

我有下表，其中的数据如下所示；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字段的内容分配组号；组编号不必是连续的，但对于以除 以外的键开头...并继续通过 is 的所有行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   ║
╚════════╧═══════╧═══════╧════════════╝

我尝试用窗口/分区和 tabibitosan 模式来做这件事，但还没有想出任何有效的方法；此外，在前一行使用 withlag()对多行连续行 with 没有帮助...。在电子表格中，这是一件非常容易做到的事情，但在 SQL 中，我似乎无法引用当前查询的前一行，可以吗？

postgresql window-functions

2 个回答

  1. Best Answer
    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'   );

    您可以使用一个简单的 CASE 来获取它。

    select linenr, key, value,
       case when key <> '...' then 1 end as rst
from   source;

    亚麻布 | 钥匙 | 价值 | 首先
-----: | :---- | :-------- | ---：
     2 | 标签 | 一个 | 1个
     3 | ... | 乙 | 无效的
     4 | 标题 | 标题 | 1个
     5 | 注意 | 这是 | 1个
     6 | ... | 一个例子 | 无效的
     8 | 标题 | 结束 | 1个
     9 | ... | 三 | 空
    10 | ... | 线条 | 无效的
    11 | 关于 | 分组 | 1个

    现在 SUM(rst) 忽略空值并执行此操作。

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

    亚麻布 | 钥匙 | 价值 | 组
-----: | :---- | :-------- | --:
     2 | 标签 | 一个 | 1个
     3 | ... | 乙 | 1个
     4 | 标题 | 标题 | 2个
     5 | 注意 | 这是 | 3个
     6 | ... | 一个例子 | 3个
     8 | 标题 | 结束 | 4个
     9 | ... | 三 | 4个
    10 | ... | 线条 | 4个
    11 | 关于 | 分组 | 5个

    dbfiddle在这里

    • 2

  2. 使用单个窗口函数更便宜、更简单：

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

    dbfiddle在这里

    有关的：

    • 2

相关问题