这是我的看法:
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
并丢弃不完整的对话。