根据其他表中的匹配选择分区

Question

具有下表（conversations）：

 id | record_id  |  is_response  |         text         |
 ---+------------+---------------+----------------------+
 1  |     1      |      false    | in text 1            |
 2  |     1      |      true     | response text 3      |
 3  |     1      |      false    | in text 2            |
 4  |     1      |      true     | response text 2      |
 5  |     1      |      true     | response text 3      |
 6  |     2      |      false    | in text 1            |
 7  |     2      |      true     | response text 1      |
 8  |     2      |      false    | in text 2            |
 9  |     2      |      true     | response text 3      |
 10 |     2      |      true     | response text 4      |

另一个帮助表（responses）：

 id |         text         |
 ---+----------------------+
 1  | response text 1      |
 2  | response text 2      |
 3  | response text 4      |

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

  record_id |       context
  ----------+-----------------------+---------------------
       1    | in text 1 response text 3 in text 2 response text 2
  ----------+-----------------------+---------------------
       2    | in text 1 response text 1
  ----------+-----------------------+---------------------
       2    | in text 2 response text 3 response text 4

所以，每一次is_response是true和text是 在 响应表， 汇总
对话上下文到这一点，忽略不池中的响应结束谈话的一部分。

在上面的示例中，活动 响应文本 为record_id1中的 3 。

我尝试了以下复杂的SQL，但有时会中断将文本汇总错误的情况：

with context as(
    with answers as (

       SELECT record_id, is_response, id as ans_id
        , max(id)
          OVER (PARTITION BY record_id ORDER BY id
          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS previous_ans_id
       FROM (select * from conversations where text in (select text from responses)) ans
       ),
     lines as (
      select answers.record_id, con.id, COALESCE(previous_ans_id || ',' || ans_id, '0') as block, con.text as text from answers, conversations con where con.engagement_id = answers.record_id and ((previous_ans_id is null and con.id <= ans_id) OR (con.id > previous_ans_id and con.id <= ans_id)) order by engagement_id, id asc
      )

      select record_id, block,replace(trim(both ' ' from string_agg(text, E' ')) ,'  ',' ') ctx from lines group by record_id, block order by record_id,block
      )

select * from context

我敢肯定有更好的方法。

Answer 1

这是我的看法：

SELECT
    record_id,
    string_agg(text, ' ' ORDER BY id) AS context
FROM (
    SELECT
        *,
        coalesce(sum(incl::integer) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS grp
    FROM (
        SELECT *, is_response AND text IN (SELECT text FROM responses) as incl
        FROM conversations
         ) c
     ) c1
GROUP BY record_id, grp
HAVING bool_or(incl)
ORDER BY max(id);

这将扫描表conversations一次，但是我不确定它的性能是否会比您的解决方案更好。基本思想是使用窗口函数来计算同一记录中的前几行如何结束对话。然后，我们可以使用该数字进行分组，record_id并丢弃不完整的对话。