sql - 用最后的结果填写表格

我有一个包含以下信息的表：

id | amount |   date   | customer_id
 1 |  0.00  | 11/12/17 | 1
 2 | 54.00  | 11/12/17 | 1
 3 | 60.00  | 02/12/18 | 1
 4 |  0.00  | 01/18/17 | 2
 5 | 14.00  | 03/12/17 | 2
 6 | 24.00  | 02/22/18 | 2
 7 |  0.00  | 09/12/16 | 3
 8 | 74.00  | 10/01/17 | 3

我需要它看起来是这样的：

ranked_id | id | amount |   date   | customer_id
        1 |  1 |  0.00  | 11/12/17 | 1
        2 |  2 | 54.00  | 11/12/17 | 1
        3 |  3 | 60.00  | 02/12/18 | 1
        4 |  3 | 60.00  | 02/12/18 | 1
        5 |  3 | 60.00  | 02/12/18 | 1
        6 |  3 | 60.00  | 02/12/18 | 1
        7 |  3 | 60.00  | 02/12/18 | 1
        8 |  4 |  0.00  | 01/18/17 | 2
        9 |  5 | 14.00  | 03/12/17 | 2
       10 |  6 | 24.00  | 02/22/18 | 2
       11 |  6 | 24.00  | 02/22/18 | 2
       12 |  6 | 24.00  | 02/22/18 | 2
       13 |  6 | 24.00  | 02/22/18 | 2
       14 |  6 | 24.00  | 02/22/18 | 2
       15 |  7 |  0.00  | 09/12/16 | 3
       16 |  8 | 74.00  | 10/01/17 | 3
       17 |  8 | 74.00  | 10/01/17 | 3
       18 |  8 | 74.00  | 10/01/17 | 3
       19 |  8 | 74.00  | 10/01/17 | 3
       20 |  8 | 74.00  | 10/01/17 | 3
       21 |  8 | 74.00  | 10/01/17 | 3

我知道有一些分区和排名（在排名的id上），但我不知道如何重复最后一行7次。

最佳答案

正如@Gordon Linoff建议的那样，可以使用generate_series（）函数与不同的customer_id交叉来生成下面T1中所需的所有行。然后在T2（也在下面）中，row_number函数用于生成到outer join to from t1的顺序值以及customer_id。
从这里开始，当没有原始数据要加入时，只需要获得每个客户id的最后一个值，这就是case语句和分析第一个值函数的来源。由于PostgreSQL缺少一个忽略空指令，所以我无法得到解析函数，所以我用降序排序的方法，只在没有其他数据存在的情况下返回解析值。

with t1 as (
select distinct
       dense_rank() over (order by customer_id, generate_series) ranked_id
     , customer_id
     , generate_series
  from table1
  cross join generate_series(1,7)
), t2 as (
  select row_number() over (partition by customer_id order by id) rn
       , table1.*
    from table1
)
select t1.ranked_id
     , case when t2.customer_id is not null
            then t2.id
            else  first_value(t2.id)
                 over (partition by t1.customer_id
                       order by id desc nulls last)
       end id
     , case when t2.customer_id is not null
            then t2.amount
            else  first_value(t2.amount)
                 over (partition by t1.customer_id
                       order by id desc nulls last)
       end amount
     , case when t2.customer_id is not null
            then t2.date
            else  first_value(t2.date)
                 over (partition by t1.customer_id
                       order by id desc nulls last)
       end date
     , t1.customer_id
  from t1
  left join t2
    on t2.customer_id = t1.customer_id
   and t2.id = t1.generate_series
 order by ranked_id;

这里有一个SQL Fiddle演示代码。