PostgreSQL查询将数组拆分为行 [英] Postgresql Query to split the array into rows

问题描述

我正在运行一个查询，该查询正在为我创建带有以下详细信息的视图

I am running a query which is creating a view for me with following details

id      name       brand_id
1        E1          {3,4}
2        E2          {5,7,8}
3        E4          {1}

我想将brand_id的记录分成相等的行数。因此，上面的视图应如下所示：

I want to split the records for brand_id into equal number of rows. Hence the above view should look like:

id      name       brand_id
1        E1          {3}
1        E1          {4}
2        E2          {5}
2        E2          {7}
2        E2          {8}
3        E4          {1}

在这里，通过将记录的创建日期与品牌日期相匹配，从子查询中计算出brand_id

Over here the brand_id is calculated from a subquery by matching the creation date of record with the date of brand

SQL查询：

CREATE OR REPLACE VIEW %I AS 
    SELECT row_number() over(),
    id,
    name,              
    (select array(select id
                  from brand b
                  where status = true and (i.creation_date = b.creation_date)
                  order by b asc) ) as brand_id
  FROM events i
  group by id order by id

推荐答案

最简单的方法可能是：

CREATE VIEW some_name AS
    SELECT i.id, i.name, b.id AS brand_id
    FROM events i
    JOIN brand b USING (creation_date)
    WHERE b.status
    ORDER BY 1;

这篇关于PostgreSQL查询将数组拆分为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！