BigQuery中基于条件保留字符串重叠部分的实现咨询

现有数据表 #

table_1 #

table_2 #

需求与期望结果 #

需要按store_no和store_loc匹配的门店分组，提取每组内product字段的公共重叠文本部分，移除各记录独有的非重叠内容。最终期望结果如下：

示例说明：同一门店（store_no=5678、store_loc=PAA）下的两个product分别为skates 42和skates black red，公共重叠部分是skates，因此这两条记录的product统一保留为skates；仅含单条记录的门店直接保留原product内容。

实现方案（以MySQL为例） #

核心逻辑是先按门店分组计算出该组内product的公共文本，再将原表中的product替换为对应组的公共文本（单条记录门店不处理）。

完整SQL代码 #

WITH grouped_products AS (
    -- 按门店分组，合并该门店下所有product
    SELECT 
        t1.store_no,
        t1.store_loc,
        GROUP_CONCAT(t2.product SEPARATOR '|') AS product_list
    FROM table_1 t1
    JOIN table_2 t2 ON t1.ID = t2.ID
    GROUP BY t1.store_no, t1.store_loc
),
common_text AS (
    -- 计算每组product的最长公共单词前缀（按空格拆分单词）
    SELECT 
        store_no,
        store_loc,
        SUBSTRING_INDEX(
            SUBSTRING_INDEX(product_list, '|', 1),
            ' ',
            -- 找到所有公共单词的最大数量
            (SELECT MAX(n)
             FROM (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) numbers
             WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(product_list, '|', 1), ' ', numbers.n)
                   = SUBSTRING_INDEX(SUBSTRING_INDEX(product_list, '|', -1), ' ', numbers.n)
            )
        ) AS common_product
    FROM grouped_products
)
-- 关联回原表，替换product字段
SELECT 
    t2.ID,
    t2.client_no,
    t2.client_name,
    CASE 
        -- 门店下有多条记录时用公共文本，否则保留原内容
        WHEN (SELECT COUNT(*) FROM table_1 t WHERE t.store_no = t1.store_no AND t.store_loc = t1.store_loc) > 1 
        THEN ct.common_product
        ELSE t2.product
    END AS product
FROM table_1 t1
JOIN table_2 t2 ON t1.ID = t2.ID
LEFT JOIN common_text ct ON t1.store_no = ct.store_no AND t1.store_loc = ct.store_loc;

关键说明 #

• 这里以“空格分隔的单词”为单位提取公共部分，若需要更细粒度的字符级公共子串，可自定义SQL函数实现最长公共子串提取。
• 若使用其他数据库（如PostgreSQL），可将GROUP_CONCAT替换为STRING_AGG，并调整对应的字符串处理语法。

内容的提问来源于stack exchange，提问作者Ola