交叉连接返回日期副本

Question

我应用了一个cross join来获得所需的行数(每computer和chip4行)。这很好，但是当我引入日期列时，我的交叉连接查询就会崩溃。

在介绍date列之前：

select t1.computer,t1.chip,transactions from generate_series (1,4) as transactions cross join 
(
select distinct computer,chip from the_table
)t1
order by 1,3

computer            chip            transactions
dell               intel                 1
dell               intel                 2
dell               intel                 3
dell               intel                 4
lenovo             samsung               1
lenovo             samsung               2
lenovo             samsung               3
lenovo             samsung               4

• 好到这部分！

当我添加一个date列时，查询就会爆炸，结果或多或少是重复的：

select t1.computer,t1.chip,t1.date_purchased,transactions from generate_series (1,4) as transactions cross join 
(
select distinct computer,chip,date_purchased from the_table
)t1
order by 1,3,4

computer            chip          date_purchased             transactions
dell               intel              5/11/21                     1
dell               intel              5/11/21                     2
dell               intel              5/11/21                     3
dell               intel              5/11/21                     4
dell               intel              5/12/21                     1
dell               intel              5/12/21                     2
dell               intel              5/12/21                     3
dell               intel              5/12/21                     4
dell               intel              5/13/21                     1
dell               intel              5/13/21                     2
dell               intel              5/13/21                     3
dell               intel              5/13/21                     4
lenovo             samsung            5/17/21                     1
lenovo             samsung            5/17/21                     2
lenovo             samsung            5/17/21                     3
lenovo             samsung            5/17/21                     4
lenovo             samsung            5/18/21                     1
lenovo             samsung            5/18/21                     2
lenovo             samsung            5/18/21                     3
lenovo             samsung            5/18/21                     4

我想得到的是：

computer            chip          date_purchased             transactions
dell               intel              5/11/21                     1
dell               intel              5/12/21                     2
dell               intel              5/13/21                     3
dell               intel               null                       4
lenovo             samsung            5/17/21                     1
lenovo             samsung            5/18/21                     2
lenovo             samsung             null                       3
lenovo             samsung             null                       4

如果列出的事务的date数据不可用，则返回date_purchased的null。

还有我想要的结果吗！

Answer 1

我推测，如果有可用的话，每台计算机/芯片组合需要四行不同的日期。如果是的话：

select computer, chip, tt.date_purchased, transactions
from generate_series (1, 4) gs(transactions) cross join 
     (select distinct computer, chip
      from the_table
     ) cc left join
     (select tt.*,
             row_number() over (partition by computer, chip order by date_purchased desc) as transactions
      from the_table tt
     ) tt
     using (computer, chip, transactions)
order by 1, 3, 4