主页 / dba / 问题 / 316619
Accepted
AJ AJ
Asked: 2022-09-09 05:03:19 +0800 CST

如何仅删除数组中相邻的重复项？

  • 772

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

["","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;

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

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

postgresql aggregate

2 个回答

  1. Best Answer

    单个子查询应该这样做：

    SELECT ARRAY (
   SELECT page_type
   FROM  (
      SELECT event_time, page_type
           , lag(page_type, 1, '') OVER (ORDER BY event_time) AS last_page_type
      FROM   tes
      WHERE  page_type <> ''
      ) sub
   WHERE  page_type <> last_page_type
   ORDER  BY event_time
   )
 AS page_type_journey_unique;

    小提琴

    立即消除 null 和空字符串WHERE page_type <> ''。看：

    然后page_type使用窗口函数获取上一个，默认lag()放置。''这种方式last_page_type永远不可能null（并且空字符串''在刚刚被消除后不会与现有值发生冲突）。看：

    因此，我们可以在外部使用普通的<>（不是更昂贵的）来识别具有新页面类型的行。IS DISTINCT FROMSELECT

    将结果集提供给 ARRAY 构造函数。最简单最便宜的。看：

    • 1

  2. 如前所述，您似乎可以只使用LAG窗口函数，并结合数组聚合。

    如果您想在合并重复项之前和之后查看，可以使用ARRAY_AGG带有和不带有 a 的聚合FILTER

    SELECT
  ARRAY_AGG(tes.page_type ORDER BY tes.event_time)
      FILTER (WHERE tes.page_type <> tes.last_page_type) AS consecutives_combined,
  ARRAY_AGG(tes.page_type ORDER BY tes.event_time) AS all_values
FROM (
    SELECT
      *,
      LAG(page_type, 1, '') OVER (ORDER BY tes.event_time) AS last_page_type
    FROM tes
    WHERE tes.page_type <> ''
) tes;

    db<>小提琴

    • 0

相关问题