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