主页 / dba / 问题 / 206884
Accepted
Vérace
Asked: 2018-05-17 03:35:06 +0800 CST

应用一长串 LIKE 模式的最佳方法？

  • 772

作为这个问题的后续，我有自己的一个。

最初的问题涉及使用CASE大于 100 个选项的语句，并且该语句必须在 4 个地方使用 - 所以显然 SQL 很麻烦。OP 的问题涉及 SQL Server 2012，但是我的问题是关于 PostgreSQL。

在我的回答中，我建议使用 aVIEW作为“一站式”解决方案——即声明VIEW一次，在任何地方使用它——这也适用于未来的任何查询及其任何变体。

另一位发帖人 (@AndriyM) 建议使用 aCROSS APPLY来解决问题，这是另一种解决方案。PostgreSQL 语法是JOIN LATERAL

然后，我在原始答案中添加了 CTE（公用表表达式）作为另一种可能的解决方案。

因此，OP 现在有 5 个选项：

  1. CASE
  2. VIEW
  3. JOIN LATERAL（CROSS APPLY对于 SQL 服务器）
  4. CTE
  5. Separate table

我排除了更改基础数据的选项，因为在这个论坛中，顾问/DBA/程序员经常不允许更改基础数据 - 也使答案更有趣！

显然，CASE具有 > 100 个选项 (x4) 的表达式非常繁琐和复杂 - 但是什么时候使用是个好主意CASE，在什么时候它会变成减号而不是加号？

在我看来（不仅仅是因为这是我的答案！），aVIEW是最佳解决方案 - 它很简单，适用于所有 RDBMS，并且是永久性的，并且如果 OP 希望修改查询，它将适用于现在和将来的所有查询.

该JOIN LATERAL构造也可以作为一种派生表工作，这几乎就是 aCTE也是。它们都可以在同一个查询中使用。

5 种方法中哪一种更好/最好，技术（易用性、速度、查询计划优化）在什么时候倾向于特定解决方案？

postgresql performance

1 个回答

  1. Best Answer

    我会在子查询中使用翻译表。演示（Postgres 10+）：LATERAL

    CREATE TABLE ac_translate (
   ord_nr int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
 , like_pattern text NOT NULL
 , target text NOT NULL
);

INSERT INTO ac_translate(like_pattern, target) VALUES 
   ('AIR NEW Z%'       , 'AIR NEW ZEALAND')  -- rows in order of precedence!
 , ('AIR BP%'          , 'AIR BP')
 , ('ADDICTION ADVICE%', 'ADDICTION ADVICE')
 , ('AIA%'             , 'AIA')
;

    看：

    操纵ord_nr以调整优先级。

    询问：

    SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;

    或者使用相关的子查询：

    SELECT COALESCE(
        (SELECT a1.target
         FROM   ac_translate a1
         WHERE  ac.accountname LIKE a1.like_pattern
         ORDER  BY a1.ord_nr
         LIMIT  1), ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS sum_amount
FROM   account_code ac
GROUP BY 1;

    这很容易处理，将一长串选项保留在代码之外，并将其放入可以正确维护的表中。并且速度适中。

    我们不能轻易使用普通的LEFT JOIN ac_translate，因为CASE遍历模式以返回单一的第一个匹配。我们不能仅仅加入一个集合，如果一个模式是另一个模式的前缀，它可能会返回多个匹配项，例如“AIR%”和“AIR N%”。所以我们使用翻译表中的排序号来优先考虑子查询中的匹配。

    引用问题中的ELSE子句解析为原始值。这是在这里实现的。基本上，这结合了前两个答案的优点。COALESCE

    最重要的是，我GROUP BY 1以另一种方式避免重复冗长的表达式（这里实际上不再需要）。看：

    速度

    由于 Postgres 被迫按顺序遍历所有行并评估LIKE表达式，因此性能会随着转换表中的行数而下降。如果这还不够快，我们需要index support，但表达式不是“sargable” - 我们需要索引的表达式在运算符的右侧，并且没有COMMUTATORfor LIKE。细节：

    不过，有一个解决方法。我的示例要求模式至少有 3 个前导字符（3是我的任意选择）。在转换表中添加一个CHECK约束以强制执行此规则，并在前导三元组上添加一个表达式索引：

    CREATE INDEX ac_translate_left_idx ON ac_translate (left(like_pattern, 3));

    调整查询：

    SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  left(ac.accountname, 3) = left(a1.like_pattern, 3)
   AND    ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;

    在翻译表中有足够的行（以及有利的估计和成本设置）后，Postgres 将使用非常快速的索引扫描将其缩小到少数候选者（如果有的话），并仅使用表达式过滤其余部分。LIKE应该缩放就好了。我将EXPLAIN输出添加到小提琴中作为概念证明：

    db<>在这里摆弄

    • 6

相关问题