Oracle SQL-根据记录的日期与历史记录标记记录

这是我在论坛上的第一篇文章。通常我能够找到我需要的东西 - 但说实话 - 我不太确定如何针对该问题提出正确的问题。因此，如果论坛上已经有答案而我错过了，请接受我的歉意。

我通过 Benthic Software 在 Oracle 数据库中运行以下代码：

SELECT
T1."REGION"
, T1."COUNTRY"
, T1."IDNum"
, T1."CUSTOMER"
, T1."BUSSINESS"
, T3."FISCALYEARMONTH"
, T3."FISCALYEAR"
, SUM(T4."VALUE") 
,"HISTORICAL_PURCHASE_FLAG"

FROM
"DATABASE"."SALES" T4
, "DATABASE"."CUSTOMER" T1
, "DATABASE"."PRODUCT" T2
, "DATABASE"."TIME" T3

WHERE
T4."CUSTOMERID" = T1."CUSTOMERID"
AND T4."PRODUCTID" = T2."PRODUCTID"
AND T4."DATEID" = T3."DATEID"
AND T3."FISCALYEAR" IN ('2016')
AND T1."COUNTRY" IN ('ENGLAND', 'France')


GROUP BY
T1."REGION"
, T1."COUNTRY"
, T1."IDNum"
, T1."CUSTOMER"
, T1."BUSSINESS"
, T3."FISCALYEARMONTH"
, T3."FISCALYEAR"
;

该查询为我提供了有关交易的信息。正如您在上面看到的，我想添加一个名为“HISTORICAL_PURCHASE_FLAG”的列。

我希望查询采用 CUSTOMER 和 FISCALYEARMONTH。然后，我想检查过去两年内是否为客户登记了任何交易。

假设我得到以下结果：

LineNum REGION  COUNTRY IDNum   CUSTOMER            BUSSINESS       FISCALYEARMONTH FISCALYEAR  VALUE      HISTORICAL_PURCHASE_FLAG
1       Europe  ENGLAND 255     Abraxo Cleaner Co.  Chemicals       201605           2016       34,567.00   
2       Europe  FRANCE  123     Metal Trade          Heavy          201602           2016       12,500.00   
3       Europe  ENGLAND 255     Abraxo Cleaner Co.  Chemicals       201601           2016       8,400.00    

LineNum 1 显示注册于 201605 的 Abraxo Cleaner Co. 的交易。LineNum 3 也是 Abraxo Cleaner Co. 的交易，但注册于 201601。我需要查询执行的操作是将 LineNum 1 标记为“现有”。因为之前有一笔交易已登记。

另一方面，LineNum 3 是 Abraxo Cleaner Co. 首次注册交易，因此该行将被标记为“新”。

总而言之，我希望单独处理每一行数据。并检查是否有任何以前的 CUSTOMER & FISCALYEARMONTH - 24 个月的数据记录。

预先感谢您的帮助。

您可以使用LAG https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions070.htm功能：

SELECT
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "FISCALYEARMONTH"
, "FISCALYEAR"
, SUM("VALUE") 
, MAX(CASE WHEN to_date(prev_fym,'YYYYMM')  >= ADD_MONTHS (to_date("FISCALYEARMONTH",'YYYYMM'), -24) THEN 'Existing' 
           ELSE NULL END) "HISTORICAL_PURCHASE_FLAG"
FROM
(
    SELECT
    T1."REGION"
    , T1."COUNTRY"
    , T1."IDNum"
    , T1."CUSTOMER"
    , T1."BUSSINESS"
    , T3."FISCALYEARMONTH"
    , T3."FISCALYEAR"
    , T4."VALUE"
    , LAG ("FISCALYEARMONTH", 1) OVER (PARTITION BY T1."IDNum" ORDER BY T3."FISCALYEARMONTH" DESC) prev_fym 
    FROM
    "DATABASE"."SALES" T4
    , "DATABASE"."CUSTOMER" T1
    , "DATABASE"."PRODUCT" T2
    , "DATABASE"."TIME" T3
    WHERE
    T4."CUSTOMERID" = T1."CUSTOMERID"
    AND T4."PRODUCTID" = T2."PRODUCTID"
    AND T4."DATEID" = T3."DATEID"
    AND T1."COUNTRY" IN ('ENGLAND', 'France')
    AND T3."FISCALYEAR" IN ('2014','2015','2016')
)
WHERE "FISCALYEAR" IN ('2016')
GROUP BY
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "FISCALYEARMONTH"
, "FISCALYEAR"
;