postgresql - 每组动态交叉加入

Question

我有以下 postgres 表：

|---------------------|------------------|
|        Group        |      Product     |
|---------------------|------------------|
|          A          |         P1       |
|---------------------|------------------|
|          A          |         P2       |
|---------------------|------------------|
|          B          |         Q1       |
|---------------------|------------------|
|          B          |         Q2       |
|---------------------|------------------|
|          B          |         Q3       |
|---------------------|------------------|
|          C          |         R1       |
|---------------------|------------------|
|          C          |         R2       |
|---------------------|------------------|

我想编写一个输出以下内容的查询：

|---------------------|
|        Array        |
|---------------------|
|      {P1,Q1,R1}     |
|---------------------|
|      {P1,Q2,R1}     |
|---------------------|
|      {P1,Q3,R1}     |
|---------------------|
|      {P1,Q1,R2}     |
|---------------------|
|      {P1,Q2,R2}     |
|---------------------|
|      {P1,Q3,R2}     |
|---------------------|
|      {P2,Q1,R1}     |
|---------------------|
|      {P2,Q2,R1}     |
|---------------------|
|      {P2,Q3,R1}     |
|---------------------|
|      {P2,Q1,R2}     |
|---------------------|
|      {P2,Q2,R2}     |
|---------------------|
|      {P2,Q3,R2}     |
|---------------------|

这意味着源数据需要产生 12 (2x3x2) 行。每组的组数和产品数是可变的。

标签： postgresql

Answer 1

这是递归的：

with recursive groupnums as (
  select distinct grp from gp
), numbering as (
  select grp, row_number() over (order by grp) gnum
    from groupnums
), numbered as (
  select n.gnum, gp.grp, gp.product
    from numbering n
    join gp on gp.grp = n.grp
), exploded as (
  select gnum, grp, product, array[product] as parray
    from numbered
   where gnum = 1
  union all
  select n.gnum, n.grp, n.product, e.parray||n.product
    from numbered n
    join exploded e on e.gnum = n.gnum - 1
)
select parray
  from exploded
 where gnum = (select max(gnum) from numbering)
 order by parray;

   parray   
------------
 {P1,Q1,R1}
 {P1,Q1,R2}
 {P1,Q2,R1}
 {P1,Q2,R2}
 {P1,Q3,R1}
 {P1,Q3,R2}
 {P2,Q1,R1}
 {P2,Q1,R2}
 {P2,Q2,R1}
 {P2,Q2,R2}
 {P2,Q3,R1}
 {P2,Q3,R2}
(12 rows)