sql - 将结果缩减为累积组

Question

有下表(描述对话):

 id | record_id  |  is_response  |         text         |
 ---+------------+---------------+----------------------+
 1  |     1      |      false    | first line of text   |
 2  |     1      |      true     | second line of text  |
 3  |     1      |      false    | third line of text   |
 4  |     1      |      true     | fourth line of text  |
 5  |     1      |      true     | fifth line of text   |
 6  |     2      |      false    | first line of text   |
 7  |     2      |      true     | second line of text  |
 8  |     2      |      false    | third line of text   |
 9  |     2      |      true     | fourth line of text  |
 10 |     2      |      true     | fifth line of text   |

我正在寻找一个 SQL 查询来输出以下内容:

  record_id |       in_text         |         out_text
  ----------+-----------------------+---------------------
       1    | first line of text    | second line of text
  ----------+-----------------------+---------------------
       1    | first line of text    | 
            | second line of text   | 
            | third line of text    | fourth line of text
  ----------+-----------------------+---------------------
       1    | first line of text    |  
            | second line of text   | 
            | third line of text    | 
            | fourth line of text   | fifth line of text
  ----------+-----------------------+---------------------
       2    | first line of text    | second line of text
  ----------+-----------------------+---------------------
       2    | first line of text    | 
            | second line of text   | 
            | third line of text    | fourth line of text
  ----------+-----------------------+---------------------
       2    | first line of text    | 
            | second line of text   | 
            | third line of text    | 
            | fourth line of text   | fifth line of text

表示每次 is_response 列为 true 时将文本列累积为 in_text 并将新行添加为 out_text.

行的顺序由 id 定义。

是否可以使用纯 SQL？怎么办？

Answer 1

在子查询中使用聚合函数 string_agg() 作为窗口函数:

SELECT record_id, in_text, out_text  
FROM  (
   SELECT record_id, text AS out_text, is_response
        , string_agg(text, E'\n')
          OVER (PARTITION BY record_id ORDER BY id
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS in_text
   FROM   tbl
   ) sub
WHERE  is_response;

这里的特殊功能是使用 ROWS 子句调整窗口框架。相关:

• Grouping based on sequence of rows

SQL Fiddle. (换行符在 sqlfiddle 中被转换为空格。)