1
votes

Postgres - Recherche d'une valeur particulière dans les colonnes d'une table

J'ai un tableau qui liste tous les fruits achetés par un client dans un magasin par client:

cust_a,fruit1
cust_b,
cust_c,fruit3

J'essaie de créer une sortie où montre quel fruit (fruit1 / fruit2, fruit3 ) a été acheté par un client marqué comme pommes dans le tableau ci-dessus. Je sais que la déclaration case ne peut être appliquée que sur une seule colonne, je me demande donc s'il existe un moyen d'obtenir des clients qui ont acheté des apples .

Résultat attendu:

| cust_name | fruit1 | fruit2  | fruit3 |
|-----------|--------|---------|--------|
| cust_a    | apples | oranges | pears  |
| cust_b    | pears  |         |        |
| cust_c    |        |         | apples |

postgresql sql

6 commentaires

SELECT cust_name, CASE WHEN fruit1 = 'pommes' THEN 'fruit1' WHEN fruit2 = 'pommes' THEN 'fruit2' ... END

C'est un mauvais modèle de données pour commencer.

@ 404, merci je ne sais pas que nous pourrions étendre la déclaration de cas de cette façon.

@a_horse_with_no_name, je sais que c'est un mauvais modèle, mais j'essaie simplement de travailler avec les données disponibles. Merci quand même pour le conseil..

Que se passe-t-il si quelqu'un achète des pommes, des pommes, des poires? Voulez-vous fruit1 ou fruit1, fruit2?

@Astrogat, je recherche actuellement uniquement des pommes achetées. Je voudrais savoir comment cela se fait, mais j'ai peur que cela dépasse le cadre de cette question.

3 Réponses :

0
votes

En utilisant CASE avec plusieurs WHEN , vous pouvez obtenir le résultat attendu:

DECLARE FruitName VARCHAR(50) := 'apples';

SELECT cust_name,
       CASE WHEN fruit1 = FruitName THEN 'fruit1'
            WHEN fruit2 = FruitName THEN 'fruit2'
            WHEN fruit3 = FruitName THEN 'fruit3'
       ELSE '' END AS fruit

1 commentaires

Maintenant c'est du code postgres mais vous ne pouvez pas faire de DECLARE à moins que vous n'écriviez en plpgsql.

3
votes

Une façon de le faire sans écrire une clause WHERE compliquée et qui s'étend facilement à plus de colonnes) consiste à convertir la ligne en JSON et à parcourir les clés de la valeur JSON résultante:

cust_name | string_agg   
----------+--------------
cust_a    | fruit1       
cust_b    |              
cust_c    | fruit3       
cust_d    | fruit1,fruit3

to_jsonb (t) - 'cust_name' crée une valeur JSON avec toutes les colonnes de la ligne et supprime le cust_name . Il n'est pas strictement nécessaire de supprimer cust_name du JSON car il est peu probable qu'il contienne un nom de fruit, il ne sera donc jamais retourné de toute façon.

jsonb_each_text () code> puis "itère" sur toutes les colonnes et ne laisse que celles qui contiennent la valeur pommes le résultat est ensuite regroupé dans une liste séparée par des virgules au cas où.

Avec l'exemple suivant data:

create table the_table (cust_name text, fruit1 text, fruit2 text, fruit3 text)
insert into the_table
values 
  ('cust_a', 'apples', 'oranges', 'pears'),
  ('cust_b', 'pears', null, null),
  ('cust_c', null,  null, 'apples'),
  ('cust_d', 'apples',  null, 'apples');

La requête ci-dessus renvoie:

select t.cust_name, string_agg(r.field, ',')
from the_table t
  left join lateral jsonb_each_text(to_jsonb(t) - 'cust_name') as r(field, fruit) 
                 on r.fruit = 'apples'
group by t.cust_name;

Normaliser correctement le modèle de données serait cependant une bien meilleure solution.

0 commentaires

0
votes

Vous pouvez créer un type qui stocke un fruit et son numéro:

WITH t AS (
    SELECT cust_name, unnest(ARRAY[ROW(1, fruit1), ROW(2, fruit2), ROW(3, fruit3)]::num_fruit[]) as nf FROM the_table
) SELECT cust_name, num(nf), fruit(nf) FROM t WHERE fruit(nf) IS NOT NULL;

Étant donné ce type, vous pouvez utiliser unnest pour développer vos colonnes en lignes (I je ne suis pas sûr que la bonne expression):

WITH t AS (
    SELECT cust_name, unnest(ARRAY[ROW(1, fruit1), ROW(2, fruit2), ROW(3, fruit3)]::num_fruit[]) as nf FROM customer_fruits
) SELECT cust_name, array_agg('fruit' || num(nf)) as 'fruits' FROM t WHERE fruit(nf) = 'apples' GROUP BY 1;

cust_name | fruits
------------------
cust_c    | {fruit3}
cust_a    | {fruit1}
cust_d    | {fruit1, fruit3}

Sortie:

WITH t AS (
    SELECT cust_name, unnest(ARRAY[ROW(1, fruit1), ROW(2, fruit2), ROW(3, fruit3)]::num_fruit[]) as nf FROM customer_fruits
) SELECT cust_name, 'fruit' || num(nf) as 'fruit' FROM t WHERE fruit(nf) = 'apples';

cust_name | fruit
------------------
cust_a    | fruit1
cust_c    | fruit3
cust_d    | fruit1
cust_d    | fruit3

Maintenant, il vous suffit de SÉLECTIONNEZ les lignes sur le fruit et renvoyez le num:

cust_name | nf
-----------------------
cust_a    | (1,apples)
cust_a    | (2,oranges)
cust_a    | (3,pears)
cust_b    | (1,pears)
cust_b    | (2,)
cust_b    | (3,)
cust_c    | (1,)
cust_c    | (2,)
cust_c    | (3,apples)
cust_d    | (1,apples)
cust_d    | (2,)
cust_d    | (3,apples)

Ou:

XXX

Vous pouvez même normaliser votre table (PK = cust_name + num):

CREATE TABLE customer_fruits (cust_name text, fruit1 text, fruit2 text, fruit3 text);
INSERT INTO customer_fruits VALUES 
  ('cust_a', 'apples', 'oranges', 'pears'),
  ('cust_b', 'pears', NULL, NULL),
  ('cust_c', NULL, NULL, 'apples'),
  ('cust_d', 'apples', NULL, 'apples');


SELECT cust_name, unnest(ARRAY[ROW(1, fruit1), ROW(2, fruit2), ROW(3, fruit3)]::num_fruit[]) as nf FROM customer_fruits;

0 commentaires