PostgreSQL文本数组扁平化：将GUID数组转为层级编号列

需要将planogram表中的shelves文本列扁平化为coolershelf和shelfposition两列。当前使用的SQL执行后得到含GUID与连续编号的结果，但期望得到分组层级的表格：每组的shelfposition从0开始重新编号，GUID替换为连续的分组序号。

SELECT s.CoolerShelf,
       s.ShelfPosition,
 FROM planogram
  CROSS JOIN LATERAL UNNEST(string_to_array(shelves, ','))
      WITH ORDINALITY s(CoolerShelf,ShelfPosition)

WITH split_shelves AS (
    SELECT 
        unnest(string_to_array(shelves, ',')) AS cooler_guid,
        generate_subscripts(string_to_array(shelves, ','), 1) AS original_pos
    FROM planogram
),
grouped_shelves AS (
    SELECT 
        cooler_guid,
        original_pos,
        DENSE_RANK() OVER (ORDER BY cooler_guid) AS coolershelf
    FROM split_shelves
)
SELECT 
    coolershelf,
    ROW_NUMBER() OVER (PARTITION BY coolershelf ORDER BY original_pos) - 1 AS shelfposition
FROM grouped_shelves
ORDER BY coolershelf, shelfposition;

逻辑说明 #

1. 拆分与记录位置：通过string_to_array拆分shelves列，结合unnest和generate_subscripts获取每个GUID项及其在原始数组中的位置，保证顺序不混乱。
2. 生成分组序号：用DENSE_RANK()给不同的GUID分配连续的分组编号（如1、2），替换原有的GUID。
3. 组内重新编号：在每个分组内，用ROW_NUMBER() - 1生成从0开始的shelfposition序号，最后按分组和位置排序得到目标结果。

内容的提问来源于stack exchange，提问作者Pete Florenzano