Oracle SQL-根据记录的日期和历史记录标记记录 [英] Oracle SQL- Flag records based on record's date vs history

问题描述

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

This is my 1st post on the forum. Usually I was able to find what I needed - but to tell the truth - I am not really sure how to ask a correct question to the issue. Therefore, please accept my apologies if there already is an answer on the forum and I missed it.

我正在通过Benthic软件在Oracle数据库中运行以下代码:

I am running the following code in an Oracle database via Benthic Software:

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"的列.

This query provides me with information on transactions. As you can see above, I would like to add a column named "HISTORICAL_PURCHASE_FLAG".

我希望查询采用CUSTOMER和FISCALYEARMONTH.然后，我想检查过去2年内是否有为客户注册的交易.

I would like the query to take CUSTOMER and FISCALYEARMONTH. Then, I would like to check if there are any transactions registered for the CUSTOMER, up to 2 years in the past.

所以可以说我得到以下结果:

So lets say I get the following result:

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显示了Abraxo Cleaner Co.在201605上注册的交易.LineNum3也是Abraxo Cleaner Co.但在201601上注册的交易.我需要查询做的是将LineNum 1标记为现有".因为有先前的交易记录.

LineNum 1 shows transaction for Abraxo Cleaner Co. registered on 201605. And LineNum 3 is also for Abraxo Cleaner Co. but registered on 201601. What I would need the query to do, is to flag LineNum 1 as 'Existing'. Because there was a previous transaction registered.

另一方面，LineNum 3是第一次为Abraxo Cleaner Co.注册交易，因此该行将被标记为新建".

On the other hand, LineNum 3 was the first time transactions was registered for Abraxo Cleaner Co. so the line would be flagged as 'New'.

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

To sum up, I would like for each row of data to be treated individually. And to check if there are any previous records of data for CUSTOMER & FISCALYEARMONTH - 24 months.

预先感谢您的帮助.

推荐答案

您可以使用 LAG 函数:

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"
;

这篇关于Oracle SQL-根据记录的日期和历史记录标记记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！