oracle - 如何通过更改计数值在 Oracle 中按组填写缺失的日期
问题描述
这是对我之前发布的一个问题的轻微修改： 如何在 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按以下方式使用该列：
- 以向前填充的方式获取
COUNTfor aCOLOR。例如，在我的表中， 2020 年 1 月 17 日是缺失的BLUE。我将在 2020 年 1 月 17 日填写COUNT7，因为它是该颜色的最新可用计数。同样，对于 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 |
+-----------+-------+-------+
请注意，将有无限数量的COLORs。也会发生不可预测的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
解决方案
而已：
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
)
-- get first and last dates:
,first_record(color_date,color,color_count, end_date) as (
select
trunc(min(color_date),'yyyy'), -- 1st of Jan of min(color_date) or use own hardcoded date
min(color) keep(dense_rank first order by color_date),
min(color_count) keep(dense_rank first order by color_date),
last_day(max(color_date)) as end_date -- last dat of max(color_date) or you can replace it with own hardcoded date
from tbl
)
-- add them into the data from tbl:
,data as (
select color_date,color,color_count from first_record
union
select * from tbl
)
-- generating all dates:
,dates_generator as (
select color_date+N as color_date from first_record, xmltable('0 to 100000' columns N int path '.') x
where x.n <= end_date-color_date
)
-- simple left join:
select
g.color_date
,nvl(d.color, lag(color ignore nulls) over(order by g.color_date)) as color
,nvl(d.color_count, lag(color_count ignore nulls) over(order by g.color_date)) as color_count
from dates_generator g
left join data d
on g.color_date=d.color_date
/
推荐阅读
- python - 我什么时候应该使用 pyspark saveAsTable？
- kotlin - Kotlin / 我需要传递给 init: (index: Int)->Contact?
- javascript - 按下 Tab 键时输入元素上的调度事件在 JavaScript 中不起作用
- amazon-web-services - 在 aws lambda 上使用 opensearch-py 进行 opensearch 身份验证
- jenkins-pipeline - 每次 bitbucket 提交的 Bitbucket 标记
- firefox - Firefox 不再通过 ssh -D 隧道工作
- graph - 在进行图形查询时如何匹配 Neo4j 中的方括号？
- c# - 在解决方案中全局使用可能的 Linq 结果数据存储的良好做法是什么
- python - Xpath python包含值
- javascript - V-model 不会在复选框列表更改时更新