Блог

У меня есть таблица, в которой перечислены все фрукты, купленные клиентом в магазине клиентом:

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

Я пытаюсь создать вывод, в котором показано, какой фрукт (fruit1 / fruit2, fruit3) был куплен клиентом, который помечен как apples из приведенной выше таблицы. Я знаю, case что оператор может быть применен только к одному столбцу, поэтому мне интересно, есть ли способ привлечь клиентов, которые купили apples .

Ожидаемый результат :

 cust_a,fruit1
cust_b,
cust_c,fruit3
  

Один из способов сделать это без написания сложного предложения WHERE и и, которое легко распространяется на большее количество столбцов) — преобразовать строку в JSON и выполнить итерацию по ключам результирующего значения JSON:

 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;
  

to_jsonb(t) - 'cust_name' создайте значение JSON со всеми столбцами из строки и удалите cust_name . Нет строгой необходимости удалять cust_name из JSON, поскольку маловероятно, что он содержит название фрукта, поэтому оно все равно никогда не будет возвращено.

jsonb_each_text() затем «выполняет итерацию» по всем столбцам и оставляет только те, которые содержат значение apples затем результат агрегируется обратно в список, разделенный запятыми, на всякий случай.

Со следующими примерными данными:

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

Приведенный выше запрос возвращает:

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

Однако правильная нормализация модели данных была бы гораздо лучшим решением.

Используя CASE с несколькими WHEN , вы можете получить ожидаемый результат:

 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
  

Вы можете создать тип, который хранит фрукт и его номер:

 CREATE TYPE num_fruit AS (
    num integer,
    fruit text
);
  

Учитывая этот тип, вы можете использовать unnest для расширения своих столбцов в строки (я не уверен, что это правильное выражение):

 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;
  

Вывод:

 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)
  

Теперь вы просто вводите SELECT строки в fruit и возвращаете num :

 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
  

Или:

 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}
  

Вы даже можете нормализовать свою таблицу (PK = cust_name num):

 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;