AJ AJ's questions

在这个问题中，我想到了使用array_position

CREATE Temp TABLE foo (
    id    integer PRIMARY KEY,
      name  text[] NOT NULL);
INSERT INTO foo (id, name) VALUES
    (1, array[NULL,'c7b74ea7a0ee2924ca6f2fb4f90ac378',NULL]);
INSERT INTO foo (id, name) VALUES 
    (2, array[NULL,NULL,NULL,NULL,NULL,'4bf4b5c141a361e4eac28b405b31baeb',NULL,'4bf4b5c141a361e4eac28b405b31baeb',NULL]); 

select array_position(d.name,(array_agg( x.v) FILTER (WHERE x.v IS NOT NULL))[1])
from foo d
  cross join lateral unnest(d.name) as x(v)
group by d.name;

返回正确答案：

| "array_position"|
|:---------------:|
|      2          |
|      6          |

有没有更好的功能可以用于此？

在聚合一个数组时，我需要删除空字符串，然后组合所有相邻的相同值。例如：

["","product","product","","product","","product","product","","product","product","","product","","","collection","product","","","product","product","","collection","order","checkout",""]

应该变成：

["product","collection","product","collection","order","checkout"]

我有一个带有 4 个嵌套选择的工作查询：

SELECT array_agg( page_type_unique_pre) FILTER (WHERE page_type_unique_pre != '')
                                        OVER   (ORDER BY event_time) AS page_type_journey_unique

FROM  (
   SELECT CASE WHEN lag(last_page_type) OVER (ORDER BY event_time) LIKE '%' || page_type || '%' THEN ''
               ELSE page_type END AS page_type_unique_pre
        , page_type
        , event_time
   FROM  (
      SELECT string_agg(page_type, ',') OVER (ORDER BY event_time) AS page_type_journey
           , first_value(page_type) OVER (PARTITION BY last_page_type_partition ORDER BY event_time) AS last_page_type
           , page_type
           , event_time
      FROM  (
         SELECT
         sum(CASE WHEN page_type IS NULL OR page_type = ''  THEN 0 ELSE 1 END) OVER (ORDER BY event_time) AS last_page_type_partition,
         page_type,
         event_time
         FROM (
            SELECT * FROM tes
            ) a
         ) b
      ) c
   ) d;

请参阅此小提琴中的测试用例。

我确定有更好的方法来实现这一目标吗？