Блог

Я хочу отобразить название дня на основе идентификатора, который находится в bigint[] в таблице, как показано ниже:

Таблица:

 create table tbl_days
(
day_ids bigint[]
);
 

Записи:

 insert into tbl_days values('{1,2}');
insert into tbl_days values('{1,2,3}');
insert into tbl_days values('{1,4}');
insert into tbl_days values('{4,7}');
insert into tbl_days values('{1,2,3,4,5,6,7}');
insert into tbl_days values('{2,4,7}');
 

Хотелось бы отобразить название дня для:

1 для понедельника, 2 для вторника. .. 7 для воскресенья.

Запрос 1: Использование replace() , которое занимает еще 3 секунды для получения основного результата запроса.

 select replace(replace(replace(replace(replace(replace(replace(day_ids::varchar,'1','Monday'),'2','Tuesday'),'3','Wednesday'),'4','Thursday'),'5','Friday'),'6','Saturday'),'7','Sunday') 
from tbl_days;
 

Запрос 2: Использование string_agg() , здесь проблема с заказом.

Шаг 1: добавьте дни во временную таблицу

 create temp table temp_days
(
id int,
days varchar
);

insert into temp_days values(1,'Monday'),(2,'Tuesday'),(3,'Wednesday'),(4,'Thursday'),(5,'Friday'),(6,'Saturday'),(7,'Sunday');
 

Шаг 2: объединение с основной таблицей

 select d.day_ids,string_agg(distinct t.days,',') 
from tbl_days d 
inner join temp_days t on t.id = any(d.day_ids)
group by d.day_ids
 

пошаговая демонстрация: db<>скрипка

 SELECT
    id,
    string_agg(                                               -- 4
        CASE day                                              -- 3
            WHEN 1 THEN 'Monday'
            WHEN 2 THEN 'Tuesday'
            WHEN 3 THEN 'Wednesday'
            WHEN 4 THEN 'Thursday'
            WHEN 5 THEN 'Friday'
            WHEN 6 THEN 'Saturday'
            WHEN 7 THEN 'Sunday'
        END,
        ','
        ORDER BY index_in_array                               -- 4
    ) 
FROM (
    SELECT
        *,
        row_number() OVER () as id                            -- 1
    FROM tbl_days
) s,
unnest(day_ids) WITH ORDINALITY as t(day, index_in_array)     -- 2
GROUP BY id
 

1. Для моего подхода вам нужна id колонка. Это создает единое целое. Если он у вас уже есть, вы можете проигнорировать этот шаг
2. unnest() расширяет массив на одну строку для каждого элемента. WITH ORDINALITY Предложение добавляет an index к записям, которые сохраняют позицию элемента в исходном массиве
3. Замените числа соответствующей строкой, используя CASE предложение
4. Повторно распределите дни недели по их исходным id дням. Порядок может быть обеспечен с помощью созданного в (2) index , который может быть использован в ORDER BY предложении агрегата