时间戳

首页 > 解决方案 > 在 Oracle 的 SQL 中选择唯一的行和上一个值

sql - 在 Oracle 的 SQL 中选择唯一的行和上一个值

问题描述

我正在使用一个有时重复相同数据的表，我想查询它以获取最新值和它之前的值。表是这样的：

+-------------+----------------+------+-------+
| Item Number | Effective Date | Cost | Price |
+-------------+----------------+------+-------+
|      1      |   01/01/2020   | 8.00 | 11.00 |
|      1      |   01/01/2020   | 8.00 | 10.50 |
|      2      |   09/22/2020   | 6.25 |  6.50 |
|      1      |   01/01/2020   | 8.00 | 10.50 |
|      1      |   05/07/2019   | 7.00 | 10.50 |
|      1      |   03/12/2018   | 6.00 | 10.50 |
|      2      |   03/12/2018   | 6.00 |  6.50 |
|      2      |   03/12/2018   | 6.00 |  6.50 |
|      1      |   01/01/2020   | 7.00 | 10.50 |
|      1      |   08/01/2016   | 5.25 | 10.50 |
+-------------+----------------+------+-------+

我试图让查询结果列出最近成本的日期，日期和成本是它之前的日期，并忽略所有重复的数据，如下所示：

+-------------+---------------+---------------+--------------+--------------+
| Item Number | Previous Date | Previous Cost | Current Date | Current Cost |
+-------------+---------------+---------------+--------------+--------------+
|      1      |   05/07/2019  |     7.00      |  01/01/2020  |     8.00     |
|      2      |   03/12/2018  |     6.00      |  09/22/2020  |     6.50     |
+-------------+---------------+---------------+--------------+--------------+

我一直在为滞后和分区而苦苦挣扎，但是，我仍然得到这样的重复：

+-------------+---------------+---------------+--------------+--------------+
| Item Number | Previous Date | Previous Cost | Current Date | Current Cost |
+-------------+---------------+---------------+--------------+--------------+
|      1      |   01/20/2020  |     8.00      |  01/01/2020  |     8.00     |
+-------------+---------------+---------------+--------------+--------------+

感谢您的任何想法！

标签： sqloracleduplicates

解决方案

我可以为您建议以下方法。

with
    date_t as
    (
        select 1 as item_number, to_date('01/01/2020','mm/dd/yyyy') as effective_date, 8.00 as cost, 11.00 as price from dual union all
        select 1 as item_number, to_date('01/01/2020','mm/dd/yyyy') as effective_date, 8.00 as cost, 10.50 as price from dual union all
        select 2 as item_number, to_date('09/22/2020','mm/dd/yyyy') as effective_date, 6.25  as cost, 6.50 as price from dual union all
        select 1 as item_number, to_date('01/01/2020','mm/dd/yyyy') as effective_date, 8.00 as cost, 10.50 as price from dual union all
        select 1 as item_number, to_date('05/07/2019','mm/dd/yyyy') as effective_date, 7.00 as cost, 10.50 as price from dual union all
        select 1 as item_number, to_date('03/12/2018','mm/dd/yyyy') as effective_date, 6.00 as cost, 10.50 as price from dual union all
        select 2 as item_number, to_date('03/12/2018','mm/dd/yyyy') as effective_date, 6.00  as cost, 6.50 as price from dual union all
        select 2 as item_number, to_date('03/12/2018','mm/dd/yyyy') as effective_date, 6.00  as cost, 6.50 as price from dual union all
        select 1 as item_number, to_date('01/01/2020','mm/dd/yyyy') as effective_date, 7.00 as cost, 10.50 as price from dual union all
        select 1 as item_number, to_date('08/01/2016','mm/dd/yyyy') as effective_date, 5.25 as cost, 10.50 as price from dual 
    )
select 
    item_number,
    lag(effective_date) over (partition by item_number order by effective_date) as previous_date,
    lag(cost) over (partition by item_number order by effective_date) as previous_cost,
    effective_date as current_date,
    cost as current_cost
from    
    (
        select
            item_number,
            effective_date,
            max(cost) as cost
        from
            date_t  
        group by
            item_number,
            effective_date
        order by effective_date desc
    ) t

输出将如下所示。

ITEM_NUMBER PREVIOUS_DATE   PREVIOUS_COST   CURRENT_DATE    CURRENT_COST
----------- -------------   -------------   ------------     ----------- 
1             null          null            01.08.2016       5.25
1             01.08.2016    5.25            12.03.2018       6
1             12.03.2018    6               07.05.2019       7
1             07.05.2019    7               01.01.2020       8
2             null          null            12.03.2018       6
2             12.03.2018    6               22.09.2020       6.25

注意：我在 Oracle 中的另一种日期格式是dd.mm.yyyy.

推荐阅读