如何获取组中最新日期对应的值来填充oraclesql中的空值？

e1xvtsh3  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(322)

我在oracle sql中有下表：

+-------------------+--------------+---------------+-----------------+-------------------+--------+
| OWNER_CHANGE_DATE |     CAR      | LICENSE_PLATE |   OWNER_NAME    | COLOR_CHANGE_DATE | COLOR  |
+-------------------+--------------+---------------+-----------------+-------------------+--------+
| 1-Jan-20          | Ford F150    | 3892A84       | John Doe        | 2-Feb-20          | red    |
| 1-Jan-20          | Ford F150    | 3892A84       | John Doe        | 21-Mar-20         | orange |
| 1-Jan-20          | Ford F150    | 3892A84       | John Doe        | 22-Mar-20         | green  |
| 23-Mar-20         | Ford F150    | 3892A84       | Jane Doe        | 23-Mar-20         | blue   |
| 23-Mar-20         | Ford F150    | 3892A84       | Jane Doe        | 24-Mar-20         | pink   |
| 23-Mar-20         | Ford F150    | 3892A84       | Jane Doe        | 25-Mar-20         | purple |
| 23-Mar-20         | Ford F150    | 3892A84       | Jane Doe        | 26-Mar-20         | brown  |
| 2-Jun-20          | Ford F150    | 3892A84       | Jack Doe        | (null)            | (null) |
| 12-Jul-20         | Ford F150    | 3892A84       | Steve Doe       | (null)            | (null) |
| 1-Jan-19          | Toyota Camry | 894A839       | Adam Baker      | 1-Jan-19          | red    |
| 1-Jan-19          | Toyota Camry | 894A839       | Adam Baker      | 5-Jan-19          | blue   |
| 3-Feb-19          | Toyota Camry | 894A839       | Frank Evans     | 14-Feb-19         | red    |
| 19-Apr-19         | Toyota Camry | 894A839       | Clark Davis     | (null)            | (null) |
| 11-Aug-19         | Toyota Camry | 894A839       | Joe Bryant      | (null)            | (null) |
| 1-Oct-19          | Toyota Camry | 894A839       | Jennifer Smith  | (null)            | (null) |
| 30-Nov-19         | Toyota Camry | 894A839       | William Johnson | (null)            | (null) |
+-------------------+--------------+---------------+-----------------+-------------------+--------+

请注意，有六行 NULL 的值 COLOR_CHANGE_DATE 以及 COLOR . 我想用基于汽车的最新颜色和日期填充这些值。例如，一旦我填写 NULL 值，我希望生成的表如下所示：

+-------------------+--------------+---------------+-----------------+-------------------+--------+
| OWNER_CHANGE_DATE |     CAR      | LICENSE_PLATE |   OWNER_NAME    | COLOR_CHANGE_DATE | COLOR  |
+-------------------+--------------+---------------+-----------------+-------------------+--------+
| 1-Jan-20          | Ford F150    | 3892A84       | John Doe        | 2-Feb-20          | red    |
| 1-Jan-20          | Ford F150    | 3892A84       | John Doe        | 21-Mar-20         | orange |
| 1-Jan-20          | Ford F150    | 3892A84       | John Doe        | 22-Mar-20         | green  |
| 23-Mar-20         | Ford F150    | 3892A84       | Jane Doe        | 23-Mar-20         | blue   |
| 23-Mar-20         | Ford F150    | 3892A84       | Jane Doe        | 24-Mar-20         | pink   |
| 23-Mar-20         | Ford F150    | 3892A84       | Jane Doe        | 25-Mar-20         | purple |
| 23-Mar-20         | Ford F150    | 3892A84       | Jane Doe        | 26-Mar-20         | brown  |
| 2-Jun-20          | Ford F150    | 3892A84       | Jack Doe        | 26-Mar-20         | brown  |
| 12-Jul-20         | Ford F150    | 3892A84       | Steve Doe       | 26-Mar-20         | brown  |
| 1-Jan-19          | Toyota Camry | 894A839       | Adam Baker      | 1-Jan-19          | red    |
| 1-Jan-19          | Toyota Camry | 894A839       | Adam Baker      | 5-Jan-19          | blue   |
| 3-Feb-19          | Toyota Camry | 894A839       | Frank Evans     | 14-Feb-19         | red    |
| 19-Apr-19         | Toyota Camry | 894A839       | Clark Davis     | 14-Feb-19         | red    |
| 11-Aug-19         | Toyota Camry | 894A839       | Joe Bryant      | 14-Feb-19         | red    |
| 1-Oct-19          | Toyota Camry | 894A839       | Jennifer Smith  | 14-Feb-19         | red    |
| 30-Nov-19         | Toyota Camry | 894A839       | William Johnson | 14-Feb-19         | red    |
+-------------------+--------------+---------------+-----------------+-------------------+--------+

请注意 NULL 六行的值已用最新值填充 COLOR_CHANGE_DATE 以及 COLOR 对于每个 CAR . 也就是说，为了 Ford F150 ，的 NULL 值将替换为最新值 COLOR_CHANGE_DATE = 26-Mar-20 以及 COLOR = brown .
可能有无限数量的所有者没有相应的 COLOR 以及 COLOR_CHANGE_DATE s。如果所有者没有 COLOR 以及 COLOR_CHANGE_DATE s、 那我需要填写表格 NULL 最新值 COLOR_CHANGE_DATE 以及 COLOR 每辆车。
您能告诉我如何在oraclesql中执行此操作吗？
为了方便起见，下面是生成示例表的sql查询。

with a as (
            select to_date('1/1/2020 11:51', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   ,  'Ford F150' as car   
                   , '3892A84' as license_plate 
                   , 'John Doe' as owner_name 
                   , to_date('2/2/2020 12:43', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'red' color
                   from dual
            union
            select to_date('1/1/2020 11:51', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Ford F150' as car
                   , '3892A84' as license_plate
                   , 'John Doe' as owner_name
                   , to_date('3/21/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'orange' color
                   from dual
            union
            select to_date('1/1/2020 11:51', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Ford F150' as car
                   , '3892A84' as license_plate
                   , 'John Doe' as owner_name
                   , to_date('3/22/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'green' color
                   from dual
            union
            select to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Ford F150' as car
                   , '3892A84' as license_plate
                   , 'Jane Doe' as owner_name
                   , to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'blue' color
                   from dual
            union
            select to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Ford F150' as car
                   , '3892A84' as license_plate
                   , 'Jane Doe' as owner_name
                   , to_date('3/24/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'pink' color
                   from dual
            union
            select to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Ford F150' as car
                   , '3892A84' as license_plate
                   , 'Jane Doe' as owner_name
                   , to_date('3/25/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'purple' color
                   from dual
            union
            select to_date('3/23/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Ford F150' as car
                   , '3892A84' as license_plate
                   , 'Jane Doe' as owner_name
                   , to_date('3/26/2020 8:14', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'brown' color
                   from dual
            union
            select to_date('6/2/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Ford F150' as car
                   , '3892A84' as license_plate
                   , 'Jack Doe' as owner_name
                   , null color_change_date
                   , null color
                   from dual
            union
            select to_date('7/12/2020 8:14', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Ford F150' as car
                   , '3892A84' as license_plate
                   , 'Steve Doe' as owner_name
                   , null color_change_date
                   , null color
                   from dual
            union
            select to_date('1/1/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Toyota Camry' as car
                   , '894A839' as license_plate
                   , 'Adam Baker' as owner_name
                   , to_date('1/1/2019 11:51', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'red' color
                   from dual
            union
            select to_date('1/1/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Toyota Camry' as car
                   , '894A839' as license_plate
                   , 'Adam Baker' as owner_name
                   , to_date('1/5/2019 11:51', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'blue' color
                   from dual
            union
            select to_date('2/3/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Toyota Camry' as car
                   , '894A839' as license_plate
                   , 'Frank Evans' as owner_name
                   , to_date('2/14/2019 11:51', 'MM/DD/YYYY HH24:MI') color_change_date
                   , 'red' color
                   from dual
            union
            select to_date('4/19/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Toyota Camry' as car
                   , '894A839' as license_plate
                   , 'Clark Davis' as owner_name
                   , null color_change_date
                   , null color
                   from dual
            union
            select to_date('8/11/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Toyota Camry' as car
                   , '894A839' as license_plate
                   , 'Joe Bryant' as owner_name
                   , null color_change_date
                   , null color
                   from dual
            union
            select to_date('10/1/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Toyota Camry' as car
                   , '894A839' as license_plate
                   , 'Jennifer Smith' as owner_name
                   , null color_change_date
                   , null color
                   from dual
            union
            select to_date('11/30/2019 10:27', 'MM/DD/YYYY HH24:MI') as owner_change_date
                   , 'Toyota Camry' as car
                   , '894A839' as license_plate
                   , 'William Johnson' as owner_name
                   , null color_change_date
                   , null color
                   from dual
                )
select *
from a
order by car, owner_change_date, color_change_date

任何帮助都将不胜感激！

sqlwindow-functionsDateoracle

1条答案

按热度按时间
ijnw1ujt

ijnw1ujt1#

oracle支持 ignore nulls 在窗口函数中，您只需执行以下操作：

select 
    a.*,
    coalesce(
        color,
        lag(color) ignore nulls over(partition by car order by color_change_date) 
    ) color2
from a
赞(0）分享  回复(0）举报  2021-07-24
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com