在PostgreSQL中使用CASE一次性影响多列。

Question

我有一个Postgres的SELECT语句，其中包含以下表达式:

,CASE WHEN (rtp.team_id = rtp.sub_team_id)
 THEN 'testing'
 ELSE TRIM(rtd2.team_name)
 END AS testing_testing
,CASE WHEN (rtp.team_id = rtp.sub_team_id)
 THEN 'test example'
 ELSE TRIM(rtd2.normal_data)
 END AS test_response
,CASE WHEN (rtp.team_id = rtp.sub_team_id)
 THEN 'test example #2'
 ELSE TRIM(rtd2.normal_data_2)
 END AS another_example

在我的查询中，有5个字段的输出取决于rtp.team_id = rtp.sub_team_id是否为真。我一遍又一遍地重复使用相同条件的CASE语句。

我能否以某种方式组合这些CASE表达式，以便一次切换多个列的输出？

- Elliot B.

Answer 1

1. 标准SQL: LEFT JOIN 连接单行值

您可以使用条件（从而仅评估一次）LEFT JOIN 行值。然后，您可以使用COALESCE() 为每列添加备用值。

这种语法变体对于多个值而言更短小且稍微快一些 - 尤其是对于一个昂贵/冗长的条件而言更有趣：

SELECT COALESCE(x.txt1, trim(r2.team_name))     AS testing_testing
     , COALESCE(x.txt2, trim(r2.normal_data))   AS test_response
     , COALESCE(x.txt3, trim(r2.normal_data_2)) AS another_example
FROM   rtp
JOIN   rtd2 r2 ON <unknown condition> -- missing context in question
LEFT   JOIN (
   SELECT 'testing'::text         AS txt1
        , 'test example'::text    AS txt2
        , 'test example #2'::text AS txt3
   ) x ON rtp.team_id = rtp.sub_team_id;

由于派生表x只包含一个行，因此在没有进一步条件的情况下进行连接是可以的。

在子查询中需要进行显式类型转换。在示例中我使用了text（无论如何这是字符串字面量的默认值）。请使用实际的数据类型。语法快捷方式value::type是针对Postgres的，对于标准SQL，请使用cast(value AS type)。

如果条件不是TRUE，则x中的所有值都为NULL，并且COALESCE会起作用。

或者，由于您特定情况下的所有候选值都来自表rtd2，因此可以使用原始的CASE条件进行LEFT JOIN到rtd2表，并使用默认值交叉连接到一行：

SELECT COALESCE(trim(r2.team_name),     x.txt1) AS testing_testing
     , COALESCE(trim(r2.normal_data),   x.txt2) AS test_response
     , COALESCE(trim(r2.normal_data_2), x.txt3) AS another_example
FROM   rtp
LEFT   JOIN rtd2 r2 ON <unknown condition>  -- missing context in question
                   AND rtp.team_id = rtp.sub_team_id
CROSS  JOIN (
   SELECT 'testing'::text         AS txt1
        , 'test example'::text    AS txt2
        , 'test example #2'::text AS txt3
   ) x;

这取决于连接条件和查询的其余部分。

2. 专属于PostgreSQL

2a. 扩展一个数组

如果你的各个列共享相同的数据类型，你可以在子查询中使用一个数组，并在外部SELECT中扩展它：

SELECT x.combo[1], x.combo[2], x.combo[3]
FROM  (
   SELECT CASE WHEN rtp.team_id = rtp.sub_team_id
            THEN '{test1,test2,test3}'::text[]
            ELSE ARRAY[trim(r2.team_name)
                     , trim(r2.normal_data)
                     , trim(r2.normal_data_2)]
          END AS combo
   FROM   rtp
   JOIN   rtd2 r2 ON <unknown condition>
   ) x;

如果列没有共享相同的数据类型，则情况会变得更加复杂。您可以将它们全部转换为 text (并在外部的 SELECT 中进行必要的转换)，或者...

2b. 分解行类型

您可以使用自定义的复合类型 (行类型) 来保存各种类型的值，并在外部的 SELECT 中使用 *-展开它。假设我们有三个列: text, integer 和 date。对于 重复使用，创建一个自定义的复合类型:

CREATE TYPE my_type AS (t1 text, t2 int, t3 date);

或者，如果现有表的类型匹配，您可以将表名直接用作复合类型。

或者，如果您只需要临时类型，可以创建一个TEMPORARY TABLE，在您的会话期间注册一个临时类型：

CREATE TEMP TABLE my_type (t1 text, t2 int, t3 date);

您甚至可以为单个交易执行此操作：

CREATE TEMP TABLE my_type (t1 text, t2 int, t3 date) ON COMMIT DROP;

然后您可以使用这个查询：

SELECT (x.combo).*  -- parenthesis required
FROM  (
   SELECT CASE WHEN rtp.team_id = rtp.sub_team_id
             THEN ('test', 3, now()::date)::my_type  -- example values
             ELSE (r2.team_name
                 , r2.int_col
                 , r2.date_col)::my_type
          END AS combo
   FROM   rtp
   JOIN   rtd2 r2 ON <unknown condition>
   ) x;

甚至可以只是（与上述相同，更简单，更短，可能不太容易理解）：

SELECT (CASE WHEN rtp.team_id = rtp.sub_team_id
           THEN ('test', 3, now()::date)::my_type
           ELSE (r2.team_name, r2.int_col, r2.date_col)::my_type
        END).*
FROM   rtp
JOIN   rtd2 r2 ON <unknown condition>;

CASE表达式将对每一列进行一次评估。如果评估不是微不足道的，则使用子查询的另一种变体将更快。

CASE表达式会逐列进行一次评估。如果该评估不是繁琐的话，使用一个子查询的变体会更快。

- Erwin Brandstetter

---

什么更快：多个CASE WHEN还是LEFT JOIN？ - Eugen Konkov

在这种情况下，使用带有LEFT JOIN rtd2的版本可以避免在条件不满足时从rdt2中读取数据，因此它会更便宜。如果不是因为这个，使用一个简单的等式检查的CASE非常便宜 - 即使更冗长 - 而其他（开销更大）的替代方案只有涉及多个表达式时才能竞争。如果有疑问，请运行测试。（并请在此处报告您的发现。） - Erwin Brandstetter

Answer 2

不确定它是否会有所改善，但您可以将SELECT沿一条路与自身联合，另一条路上也这么做：

SELECT 
  ...,
  'testing' AS testing_testing,
  'test example' AS test_response,
  'test example #2' AS another_example, ...
FROM ...
WHERE rtp.team_id = rtp.sub_team_id AND ...
UNION 
SELECT
  ...,
  TRIM(rtd2.team_name) AS testing_testing,
  TRIM(rtd2.normal_data) AS test_response,
  TRIM(rtd2.normal_data_2) AS another_example, ...
WHERE rtp.team_id <> rtp.sub_team_id AND ...;

假设您在第二个查询中按照与第一个相同的顺序提取列名，则可以安全地省略列名。

您可能希望使用公共表达式（CTE）将每个查询分开。如果您担心这会改变顺序，您可以将其作为子查询，并在其周围应用ORDER BY。

- Daniel Lyons