sql - 查询以删除列中字符串的第一个和第二个连字符之间的所有内容

Question

我正在尝试根据自定义字段“ci_ku”检索记录。对于“ci_ku”的相同值，我们将有多个“l1m_visits”，我想检索“l1mvisits”的最小值对于每个“ci_ku”。我想通过删除 ku 中第一个和第二个下划线 (-) 之间的字符串来获取 ci_ku，如何编写查询以在此处获取 ci_ku

示例数据:

<表类="s-表"><头>ku项目l1m_visits<正文>1234-5678-HIJK1234一个1234-9012-HIJK1234B56457-12456-DF-GH-TC56457D

预期输出:

<表类="s-表"><头>kuci_kul1m_visits<正文>1234-5678-HIJK1234-HIJK一个56457-12456-DF-GH-TC56457-DF-GH-TCD

已尝试以下查询:

WITH tab_with_ci_ku AS (
   select split(ku, '-', 3)ivm_arr,

           l1m_visits,
           last_refresh_date
    FROM db.scema.table
), ranked_visits AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY CONCAT(ivm_arr[2],item) as ci_sku ORDER BY l1m_visits) AS rn
    FROM tab_with_ci_ku
)
SELECT sku,ci_ku
FROM ranked_visits
WHERE rn = 1

并面临以下错误:

 mismatched input 'ci_ku'. Expecting: 'ALTER', 'ANALYZE', 'CALL', 'COMMENT', 'COMMIT', 'CREATE', 'DEALLOCATE', 'DELETE', 'DENY', 'DESC', 'DESCRIBE', 'DROP', 'EXECUTE', 'EXPLAIN', 'GRANT', 'INSERT', 'MERGE', 'PREPARE', 'REFRESH', 'RESET', 'REVOKE', 'ROLLBACK', 'SET', 'SHOW', 'START', 'TRUNCATE', 'UPDATE', 'USE', <query>

Answer 1

我会使用正则表达式替换来处理 ci_ku 要求。请注意，此替换应该首先发生，因为对 ROW_NUMBER 的调用取决于它。

WITH tab_with_ci_ku AS (
    SELECT *, REGEXP_REPLACE(ku, '([^-]+)-[^-]+-(.*)', '$1-$2') AS ci_ku
    FROM db.schema.table
),
ranked_visits AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ci_ku ORDER BY l1m_visits) AS rn
    FROM tab_with_ci_ku
)

SELECT ku, ci_ku, l1m_visits
FROM ranked_visits
WHERE rn = 1;

这里是正则表达式的解释:

• ([^-]+) 匹配并捕获$1中的第一个词
• - 匹配文字连字符
• [^-]+ 匹配第二项(在替换中排除)
• - 匹配文字连字符
• (.*) 匹配并捕获 $2 剩余的 SKU

然后我们替换为 $1-$2 以有效拼接出第二项。