这是对我之前发布的问题的轻微修改:如何在 Oracle 中按组填写缺失的日期 https://stackoverflow.com/questions/63002363/how-do-i-fill-in-missing-dates-by-group-in-oracle
我在 Oracle SQL 中有下表:
+-----------+-------+-------+
| Date | Color | Count |
+-----------+-------+-------+
| 16-Jan-20 | blue | 7 |
| 19-Jan-20 | blue | 32 |
| 23-Jan-20 | blue | 16 |
| 28-Jan-20 | blue | 24 |
| 31-Jan-20 | blue | 41 |
| 2-Feb-20 | blue | 11 |
| 28-Jan-20 | red | 1 |
| 3-Feb-20 | red | 5 |
| 6-Feb-20 | red | 11 |
| 11-Feb-20 | red | 4 |
| 15-Feb-20 | red | 6 |
+-----------+-------+-------+
我想做的是为每一个color
,填写缺失的日期从2020年1月1日到2020年2月29日。这样做时,我想使用COUNT
列的方式如下:
- Get the
COUNT
for a COLOR
以前向填充的方式。例如,在我的表中,缺少 2020 年 1 月 17 日BLUE
。我将填写COUNT
2020 年 1 月 17 日为 7,因为这是该颜色的最新可用计数。同样,对于 2020 年 1 月 22 日,BLUE
,我就填32COUNT
.
- 如果没有
COUNT
给定日期之前存在的值,然后填写COUNT
最早的值COUNT
可用值。例如,2020 年 1 月 2 日,COUNT
值将为 7BLUE
.
结果表如下:
+-----------+-------+-------+
| Date | Color | Count |
+-----------+-------+-------+
| 1-Jan-20 | blue | 7 |
| 2-Jan-20 | blue | 7 |
| 3-Jan-20 | blue | 7 |
| 4-Jan-20 | blue | 7 |
| 5-Jan-20 | blue | 7 |
| 6-Jan-20 | blue | 7 |
| 7-Jan-20 | blue | 7 |
| 8-Jan-20 | blue | 7 |
| 9-Jan-20 | blue | 7 |
| 10-Jan-20 | blue | 7 |
| 11-Jan-20 | blue | 7 |
| 12-Jan-20 | blue | 7 |
| 13-Jan-20 | blue | 7 |
| 14-Jan-20 | blue | 7 |
| 15-Jan-20 | blue | 7 |
| 16-Jan-20 | blue | 7 |
| 17-Jan-20 | blue | 7 |
| 18-Jan-20 | blue | 7 |
| 19-Jan-20 | blue | 32 |
| 20-Jan-20 | blue | 32 |
| 21-Jan-20 | blue | 32 |
| 22-Jan-20 | blue | 32 |
| 23-Jan-20 | blue | 16 |
| 24-Jan-20 | blue | 16 |
| 25-Jan-20 | blue | 16 |
| 26-Jan-20 | blue | 16 |
| 27-Jan-20 | blue | 16 |
| 28-Jan-20 | blue | 24 |
| 29-Jan-20 | blue | 24 |
| 30-Jan-20 | blue | 24 |
| 31-Jan-20 | blue | 41 |
| 1-Feb-20 | blue | 41 |
| 2-Feb-20 | blue | 11 |
| 3-Feb-20 | blue | 11 |
| 4-Feb-20 | blue | 11 |
| 5-Feb-20 | blue | 11 |
| 6-Feb-20 | blue | 11 |
| 7-Feb-20 | blue | 11 |
| 8-Feb-20 | blue | 11 |
| 9-Feb-20 | blue | 11 |
| 10-Feb-20 | blue | 11 |
| 11-Feb-20 | blue | 11 |
| 12-Feb-20 | blue | 11 |
| 13-Feb-20 | blue | 11 |
| 14-Feb-20 | blue | 11 |
| 15-Feb-20 | blue | 11 |
| 16-Feb-20 | blue | 11 |
| 17-Feb-20 | blue | 11 |
| 18-Feb-20 | blue | 11 |
| 19-Feb-20 | blue | 11 |
| 20-Feb-20 | blue | 11 |
| 21-Feb-20 | blue | 11 |
| 22-Feb-20 | blue | 11 |
| 23-Feb-20 | blue | 11 |
| 24-Feb-20 | blue | 11 |
| 25-Feb-20 | blue | 11 |
| 26-Feb-20 | blue | 11 |
| 27-Feb-20 | blue | 11 |
| 28-Feb-20 | blue | 11 |
| 29-Feb-20 | blue | 11 |
| 1-Jan-20 | red | 1 |
| 2-Jan-20 | red | 1 |
| 3-Jan-20 | red | 1 |
| 4-Jan-20 | red | 1 |
| 5-Jan-20 | red | 1 |
| 6-Jan-20 | red | 1 |
| 7-Jan-20 | red | 1 |
| 8-Jan-20 | red | 1 |
| 9-Jan-20 | red | 1 |
| 10-Jan-20 | red | 1 |
| 11-Jan-20 | red | 1 |
| 12-Jan-20 | red | 1 |
| 13-Jan-20 | red | 1 |
| 14-Jan-20 | red | 1 |
| 15-Jan-20 | red | 1 |
| 16-Jan-20 | red | 1 |
| 17-Jan-20 | red | 1 |
| 18-Jan-20 | red | 1 |
| 19-Jan-20 | red | 1 |
| 20-Jan-20 | red | 1 |
| 21-Jan-20 | red | 1 |
| 22-Jan-20 | red | 1 |
| 23-Jan-20 | red | 1 |
| 24-Jan-20 | red | 1 |
| 25-Jan-20 | red | 1 |
| 26-Jan-20 | red | 1 |
| 27-Jan-20 | red | 1 |
| 28-Jan-20 | red | 1 |
| 29-Jan-20 | red | 1 |
| 30-Jan-20 | red | 1 |
| 31-Jan-20 | red | 1 |
| 1-Feb-20 | red | 1 |
| 2-Feb-20 | red | 1 |
| 3-Feb-20 | red | 5 |
| 4-Feb-20 | red | 5 |
| 5-Feb-20 | red | 5 |
| 6-Feb-20 | red | 11 |
| 7-Feb-20 | red | 11 |
| 8-Feb-20 | red | 11 |
| 9-Feb-20 | red | 11 |
| 10-Feb-20 | red | 11 |
| 11-Feb-20 | red | 4 |
| 12-Feb-20 | red | 4 |
| 13-Feb-20 | red | 4 |
| 14-Feb-20 | red | 4 |
| 15-Feb-20 | red | 6 |
| 16-Feb-20 | red | 6 |
| 17-Feb-20 | red | 6 |
| 18-Feb-20 | red | 6 |
| 19-Feb-20 | red | 6 |
| 20-Feb-20 | red | 6 |
| 21-Feb-20 | red | 6 |
| 22-Feb-20 | red | 6 |
| 23-Feb-20 | red | 6 |
| 24-Feb-20 | red | 6 |
| 25-Feb-20 | red | 6 |
| 26-Feb-20 | red | 6 |
| 27-Feb-20 | red | 6 |
| 28-Feb-20 | red | 6 |
| 29-Feb-20 | red | 6 |
+-----------+-------+-------+
请注意,数量不定COLOR
s. The COLOR_COUNT
也会发生难以预料的变化。
有人可以告诉我如何在 Oracle SQL 中执行此操作吗?
任何帮助将不胜感激!
为了方便您使用,下面是在Oracle中生成表的sql查询:
with tbl as (
select to_date('1/28/2020 09:29', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color, 1 color_count from dual union
select to_date('2/3/2020 07:04', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color, 5 color_count from dual union
select to_date('2/6/2020 12:11', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color, 11 color_count from dual union
select to_date('2/11/2020 17:15', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color, 4 color_count from dual union
select to_date('2/15/2020 03:46', 'MM/DD/YYYY HH24:MI') as color_date, 'red' as color, 6 color_count from dual union
select to_date('1/16/2020 14:52', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 7 color_count from dual union
select to_date('1/19/2020 22:30', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 32 color_count from dual union
select to_date('1/23/2020 05:17', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 16 color_count from dual union
select to_date('1/28/2020 18:35', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 24 color_count from dual union
select to_date('1/31/2020 15:38', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 41 color_count from dual union
select to_date('2/2/2020 16:01', 'MM/DD/YYYY HH24:MI') as color_date, 'blue' as color, 11 color_count from dual
)
select *
from tbl
order by color, color_date