时间戳

首页 > 解决方案 > 将包含子字符串的多行折叠成一行

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

问题描述

我有几行记录 ( 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不是，由于前缀 on close。26也不会折叠，因为query第二行中的字段不是第一行的子字符串（没有前缀）。

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

标签： sqlpostgresql

解决方案

演示：db<>小提琴

对于更一般的情况（例如“差异可能在第 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

主要思想在这里讨论。

A：该lag功能允许检查下一行的值。因此，如果phrase当前行的开头是phrase下一行的开头，则它们在同一组中。( current_row LIKE (next_row || '%'))。这是有效的，因为这些id组是按phrase文本（及其长度）排序的。

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

B：可以添加辅助变量并生成组。（有关更多详细信息，请参阅上面提供的链接）。

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

推荐阅读