从日期列中为所有客户Oracle获取错误的下一个日期

小编典典

从日期列中为所有客户Oracle获取错误的下一个日期

sql

这是我的NM_CUST_APPLIANCE_HISTORY表（对于custoner_id = 96）。

Customer_id   | Last_effective_date | Present_quentity
--------------+---------------------+-----------------
    96        | 2009-12-20          | 10
    96        | 2014-11-18          | 12
    96        | 2015-11-26          | 14

我执行查询以获取下一行的start_date和立即日期作为单个客户（customer_id = 96）的end_date。

  SELECT NM.CUSTOMER_ID customer_id,
     NM.LATEST_EFFECTIVE_DATE start_date,
     NVL (
        CASE
           WHEN nm.LATEST_EFFECTIVE_DATE IS NULL
           THEN
              TO_DATE ('12/12/9999', 'dd/mm/yyyy')
           ELSE
              FIRST_VALUE (
                 nm.LATEST_EFFECTIVE_DATE)
              OVER (ORDER BY nm.LATEST_EFFECTIVE_DATE
                    RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
        END,
        TO_DATE ('12/12/9999', 'dd/mm/yyyy'))
        end_date,
     NM.PRESENT_QUANTITY PRESENT_quantity
FROM nm_cust_appliance_history nm
WHERE NM.APPLIANCE_INFO_ID = 10484 
AND NM.CUSTOMER_ID = 96
ORDER BY customer_id, start_date;

结果就如我所愿。像下面这样：

Customer_id | START_DATE |  END_DATE  | PRESENT_QUANTITY
------------+------------+------------+-----------------
    96      | 2009-12-20 | 2014-11-18 |      10
    96      | 2014-11-18 | 2015-11-26 |      12
    96      | 2015-11-26 | 9999-12-12 |      14

但是，当我为所有客户执行此查询（从查询中删除NM.CUSTOMER_ID =
96）时，它给我相同的START_DATE和END_DATE，并且end_date在下面添加了一天，就像......我也为您提供了我的查询输出的快照并用
红色框标出该客户结果…

  SELECT NM.CUSTOMER_ID customer_id,
     NM.LATEST_EFFECTIVE_DATE start_date,
     NVL (
        CASE
           WHEN nm.LATEST_EFFECTIVE_DATE IS NULL
           THEN
              TO_DATE ('12/12/9999', 'dd/mm/yyyy')
           ELSE
              FIRST_VALUE (
                 nm.LATEST_EFFECTIVE_DATE)
              OVER (ORDER BY nm.LATEST_EFFECTIVE_DATE
                    RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
        END,
        TO_DATE ('12/12/9999', 'dd/mm/yyyy'))
        end_date,
     NM.PRESENT_QUANTITY PRESENT_quantity
FROM nm_cust_appliance_history nm
WHERE NM.APPLIANCE_INFO_ID = 10484
--AND NM.CUSTOMER_ID = 96
ORDER BY customer_id, start_date;

结果是：

Customer_id   | START_DATE  |  END_DATE  | Present_quentity
--------------+-------------+------------+-----------------
    74        | 2008-10-26  | 2008-10-27 |  5
 >  96        | 2009-12-20  | 2009-12-21 | 10
 >  96        | 2014-11-18  | 2014-11-19 | 12
 >  96        | 2015-11-26  | 2015-11-27 | 14
   100        | 2009-01-07  | 2009-01-09 |  7

我希望所有客户的结果都像单个客户的结果一样。我该如何解决我的问题？帮我一个

阅读 115

2021-05-16

共1个答案

小编典典

您的window子句正在查看last_effective_dates所有数据。您需要添加一个partition by子句以将其限制为当前客户：

  OVER (PARTITION BY nm.CUSTOMER_ID
        ORDER BY nm.LATEST_EFFECTIVE_DATE
        RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)

所以：

  SELECT NM.CUSTOMER_ID customer_id,
     NM.LATEST_EFFECTIVE_DATE start_date,
     NVL (
        CASE
           WHEN nm.LATEST_EFFECTIVE_DATE IS NULL
           THEN
              TO_DATE ('12/12/9999', 'dd/mm/yyyy')
           ELSE
              FIRST_VALUE (
                 nm.LATEST_EFFECTIVE_DATE)
              OVER (PARTITION BY nm.CUSTOMER_ID
                    ORDER BY nm.LATEST_EFFECTIVE_DATE
                    RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
        END,
        TO_DATE ('12/12/9999', 'dd/mm/yyyy'))
        end_date,
     NM.PRESENT_QUANTITY PRESENT_quantity
FROM nm_cust_appliance_history nm
WHERE NM.APPLIANCE_INFO_ID = 10484
ORDER BY customer_id, start_date;

如果您需要运行多个应用程序，appliance_info_id则还需要将其添加到其中partition by clause。

使用虚拟的额外记录来模拟您通过CTE提供的内容：

with nm_cust_appliance_history(appliance_info_id, customer_id, latest_effective_date, present_quantity) as (
  select 10484, 96, date '2009-12-20', 10 from dual
  union all select 10484, 96, date '2014-11-18', 12 from dual
  union all select 10484, 96, date '2015-11-26', 14 from dual
  union all select 10484, 42, date '2009-12-21', 15 from dual
)

您原来的查询得到：

CUSTOMER_ID START_DATE END_DATE   PRESENT_QUANTITY
----------- ---------- ---------- ----------------
         42 2009-12-21 2014-11-18               15
         96 2009-12-20 2009-12-21               10
         96 2014-11-18 2015-11-26               12
         96 2015-11-26 9999-12-12               14

上面的分区查询得到：

CUSTOMER_ID START_DATE END_DATE   PRESENT_QUANTITY
----------- ---------- ---------- ----------------
         42 2009-12-21 9999-12-12               15
         96 2009-12-20 2014-11-18               10
         96 2014-11-18 2015-11-26               12
         96 2015-11-26 9999-12-12               14

2021-05-16