sql - 将包含子字符串的多行折叠成一行

Question

我有几行记录 (id, query,count)，我想通过观察多行上的 query 来折叠这些记录。我想保留具有最长 query 的行和折叠行的 count 字段的总和。

示例输入:

24, que, 2
24, querie, 1
24, queries, 1
25, term1, 3
25, term1+term2, 11
25, term1+term2+term3, 1
26, inventory, 5
26, issues, 10
27, close, 1
27, sclosed, 2
28, abcde, 2
28, abcfe, 2

要求的输出:

24, queries, 4
25, term1+term2+term3, 15
26, inventory, 5
26, issues, 10
27, close, 1
27, sclosed, 2
28, abcde, 2
28, abcfe, 2

我只是在子字符串的特殊情况之后:24,25 被折叠但是 27 不是，由于 close 上的前缀>。 26 也不会折叠，因为第二行中的 query 字段不是第一行的子字符串(没有前缀)。

编辑:添加了 id 28，这是不应折叠记录的另一种情况。

Answer 1

demo:db<>fiddle

对于更一般的情况(例如，“差异可能在第 10 个字母” 或 “一行只有一个字符”)，您需要确定正确的群体。因此有必要对照下一行检查行:“当前行是下一行的开头吗？”

对于像 substring 这样的东西，你可以在开头检查一个特殊的长度(“将所有以相同 3 个字母开头的文本分组” 但是如果你没有3 个字母？或者区别在后面的某个地方？)

这就是我借助 lag 窗口函数 (https://www.postgresql.org/docs/current/static/tutorial-window.html) 计算特殊组的原因:

SELECT 
    max(id) as id,                                        -- C
    max(phrase) as phrase,
    sum("count") as count
FROM (
    SELECT 
        *,
        SUM(is_diff) OVER (ORDER BY id, phrase) as ranked -- B
    FROM (
        SELECT
            *,
            -- A: 
            CASE WHEN phrase LIKE (lag(phrase) over (order by id, phrase)) || '%' THEN 0 ELSE 1 END as is_diff
        FROM phrases 
    )s
) s
GROUP BY ranked
ORDER BY ranked

讨论主要思想here .

A:lag 函数允许检查下一行的值。因此，如果当前行的 phrase 是下一行 phrase 的开头，则它们属于同一组。 (current_row LIKE(next_row || '%'))。这是有效的，因为 id 组是按 phrase 文本(及其长度)排序的。

如果行不兼容，辅助变量设置为 1，否则设置为 0。

B:可以添加辅助变量并生成组。 (有关详细信息，请参阅上面提供的链接)。

C:剩下的就是根据新生成的组值进行简单分组。