Utilisation de CASE dans PostgreSQL pour affecter plusieurs colonnes à la fois

J'ai une instruction Postgres SELECT avec ces 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

Dans ma requête particulière, il y a 5 champs dont la sortie dépend si rtp.team_id = rtp.sub_team_id évalue true. Je répète encore et encore les instructions CASE avec la même condition.

Est-il possible de combiner ces expressions CASE pour basculer la sortie de plusieurs colonnes en un seul coup?

21
demandé sur Erwin Brandstetter 2012-12-05 02:44:20

2 réponses

1. Standard-SQL: {[8] } une seule ligne de valeurs

Vous pourriez LEFT JOIN une ligne de valeurs utilisant la condition (l'évaluant ainsi une fois). Ensuite, vous pouvez ajouter des valeurs de secours par colonne avec COALESCE().

Cette variante de syntaxe est plus courte et légèrement plus rapide avec plusieurs valeurs-particulièrement intéressante pour une condition coûteuse / longue:

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;

Puisque la table dérivée x se compose d'une ligne unique , se joignant sans plus les conditions sont très bien.

les conversions de type explicites sont nécessaires dans la sous-requête. J'utilise text dans l'exemple (qui est la valeur par défaut pour les littéraux de chaîne de toute façon). Utilisez vos types de données réels. Le raccourci syntaxique value::type est spécifique à Postgres, utilisez cast(value AS type) pour SQL standard.

Si la condition n'est pas TRUE, Toutes les valeurs de x sont nulles et COALESCE entrent en jeu.

Ou, puisque tous les candidats des valeurs du tableau rtd2, dans votre cas particulier, LEFT JOIN à rtd2 utilisation de la condition d'origine CASE et CROSS JOIN à une ligne avec des valeurs par défaut:

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;

Cela dépend des conditions de jointure et du reste de la requête.

2. Spécifique à PostgreSQL

2a. développer un tableau

Si vos différentes colonnes partagent même type de données, Vous pouvez utiliser un tableau dans une sous-requête et le développer dans le SELECT externe:

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;

Cela devient plus compliqué si les colonnes ne partagent pas le même type de données. Vous pouvez soit jeté tous à text (et éventuellement convertir de retour à l'extérieur SELECT), ou vous pouvez ...

2b. décomposer un type de ligne

, Vous pouvez utiliser un type composite (type de ligne) pour contenir des valeurs de différents types et simplement *-d'élargir l'extérieur SELECT. Dire que nous avons trois colonnes: text, integer et date. Pour utilisation répétée de , créez un type composite personnalisé:

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

Ou si le type d'un tableau de correspondances, vous pouvez simplement utiliser le nom de la table en composite type.

Ou si vous avez seulement besoin du type temporairement, vous pouvez créer un TEMPORARY TABLE, qui enregistre un type provisoire pour la durée de votre session:

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

Vous pouvez même le faire pour une transaction unique :

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

, Alors vous pouvez utiliser cette requête:

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;

Ou même juste (comme ci-dessus, plus simple, plus court, peut-être moins facile à comprendre):

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

L'expression CASE est évaluée une fois pour chaque colonne de cette façon. Si l'évaluation n'est pas anodin, l'autre variante avec une sous-requête sera plus rapide.

24
répondu Erwin Brandstetter 2016-06-02 15:26:50

Pas sûr que ce serait une amélioration, mais vous pourriez unir le SELECT d'une manière avec lui-même dans l'autre sens:

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

Les noms de colonne peuvent être omis en toute sécurité de la deuxième requête, en supposant que vous les sortez dans le même ordre que dans la première.

Vous pouvez faire de chacune d'elles une requête séparée en utilisant des expressions de table communes (Ctes). Si vous êtes inquiet à propos de cette modification de l'ordre, vous pouvez en faire une sous-requête et appliquer un ORDER BY autour d'elle.

0
répondu Daniel Lyons 2012-12-04 23:11:29