在PostgreSQL中使用CASE一次影响多个列 [英] Using CASE in PostgreSQL to affect multiple columns at once

问题描述

我有一个Postgres SELECT 语句，这些表达式是：

  ，CASE WHEN（rtp.team_id = rtp.sub_team_id）
然后'testing'
 ELSE TRIM（rtd2.team_name）
 END as testing_testing 
，CASE WHEN（rtp.team_id = rtp.sub_team_id）
然后'测试示例'
 ELSE TRIM（rtd2.normal_data）
以test_response 
为例，当案例（rtp.team_id = rtp.sub_team_id）
然后'测试示例＃2'
 ELSE TRIM（rtd2.normal_data_2）
以另一个示例
  

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

有什么方法可以将这些组合 CASE 表达式可一次切换多个列的输出？

解决方案

1。标准SQL：左键一行值

您可以 左联接 使用条件（从而评估一次）。然后，您可以使用 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 <未知条件> ; -缺少相关上下文
 LEFT JOIN（
选择'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（值AS类型）。 / p>

如果条件不是 TRUE ，则 x 为NULL，并加入 COALESCE 。

或，因为所有候选值来自表 rtd2 在您的特定情况下， LEFT JOIN 到 rtd2 使用原始 CASE 条件和 CROSS JOIN 到具有默认值的行：

 选择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> -缺少相关上下文
 AND rtp.team_id = rtp.sub_team_id 
 CROSS JOIN（
选择'testing':: text AS txt1 
，'test example':: text AS txt2 
，'测试示例＃2':: text AS txt3 
）x; 
  

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

2。 PostgreSQL专用

2a。展开数组

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

  SELECT x.combo [ 1]，x.combo [2]，x.combo [3] 
 FROM（
在rtp.team_id = rtp.sub_team_id 
时选择情况然后'{test1，test2，test3}' :: text [] 
 ELSE ARRAY [trim（r2.team_name）
，trim（r2.normal_data）
，trim（r2.normal_data_2）] 
以组合$ b结尾$ b从rtp 
加入rtd2 r2 ON<未知条件> 
）x; 
  

如果列不共享相同的数据类型，则会变得更加复杂。您可以将它们全部转换为 text （并可以选择在外部的 SELECT 中转换），也可以..

2b。分解行类型

您可以使用自定义复合类型（行类型）来保存各种类型的值，然后简单地*扩展它在外部的 SELECT 。假设我们有三列：文本，整数和日期 。为了重复使用，创建一个自定义的复合类型：

 创建类型my_type（t1文本，t2 int ，t3日期）； 
  

或如果现有表的类型匹配，则可以使用

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

  CREATE TEMP TABLE my_type（t1文本，t2 int，t3日期）; 
  

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

 创建温度表my_type（t1文本，t2 int，t3日期）在提交时； 
  

然后您可以使用以下查询：

  SELECT（x.combo）。*-需要括号
 FROM（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 
以组合
从rtp 
加入rtd2 r2 ON<未知条件> 
）x结束x; 
  

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

  SELECT（当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< <未知条件> 
  

CASE 表达式每次计算一次这种方式列。如果求值不平凡，则带有子查询的其他变体会更快。

I have a Postgres SELECT statement with these expressions:

,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

In my particular query there are 5 fields whose output depends on whether rtp.team_id = rtp.sub_team_id evaluates true. I'm repeating CASE statements with the same condition over and over.

Is there any way I can combine these CASE expressions to toggle the output of multiple columns in one shot?

解决方案

1. Standard-SQL: LEFT JOIN a single row of values

You could LEFT JOIN a row of values using the condition (thereby evaluating it once). Then you can add fallback values per column with COALESCE().

This syntax variant is shorter and slightly faster with multiple values - especially interesting for an expensive / lengthy condition:

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;

Since the derived table x consists of a single row, joining without further conditions is fine.

Explicit type casts are necessary in the subquery. I use text in the example (which is the default for string literals anyway). Use your actual data types. The syntax shortcut value::type is Postgres-specific, use cast(value AS type) for standard SQL.

If the condition is not TRUE, all values in x are NULL, and COALESCE kicks in.

Or, since all candidate values come from table rtd2 in your particular case, LEFT JOIN to rtd2 using the original CASE condition and CROSS JOIN to a row with default values:

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;

It depends on the join conditions and the rest of the query.

2. PostgreSQL-specific

2a. Expand an array

If your various columns share the same data type, you can use an array in a subquery and expand it in the outer 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;

It gets more complicated if the columns don't share the same data type. You can either cast them all to text (and optionally convert back in the outer SELECT), or you can ...

2b. Decompose a row type

You can use a custom composite type (row type) to hold values of various types and simply *-expand it in the outer SELECT. Say we have three columns: text, integer and date. For repeated use, create a custom composite type:

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

Or if the type of an existing table matches, you can just use the table name as composite type.

Or if you only need the type temporarily, you can create a TEMPORARY TABLE, which registers a temporary type for the duration of your session:

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

You could even do this for a single transaction:

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

Then you can use this query:

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;

Or even just (same as above, simpler, shorter, maybe less easy to understand):

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>;

The CASE expression is evaluated once for every column this way. If the evaluation is not trivial, the other variant with a subquery will be faster.

这篇关于在PostgreSQL中使用CASE一次影响多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！