根据 bigint[] 数据类型的自定义 ID 获取日期名称

Question

我想根据 id(在表格中 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

Answer 1

逐步演示：db<>fiddle

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子句向记录添加一个index，以保存元素在原始数组中的位置
3. 使用CASE子句将数字替换为相关字符串
4. 按原始id重新聚合工作日。可以通过使用 in (2) 创建的index来确保顺序，该 可以在聚合的ORDER BY子句中使用