结合 CONCAT 和 LAG 功能

Question

首先，这与一篇关于如何Oracle SQL的文章有关 - 根据记录的日期与历史记录标记记录，已成功解决。我决定打开一个新主题有两个原因：

1. 这是一个新问题（尽管它与前一个问题有关）。
2. 有人可能会发现原始帖子中的解决方案很有用，所以我不希望它改变。

我目前正在运行以下查询（由@Mottor提供）：

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 'New ' END) "HISTORICAL_PURCHASE_FLAG"
FROM
(
    SELECT
    T1."REGION"
    , T1."COUNTRY"
    , T1."IDNum"
    , T1."CUSTOMER"
    , T2."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"
;

结果，我得到了2016财年所有交易的列表。此外，LAG 功能允许我根据"IDNum"和"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    Existing   
2       Europe  FRANCE  123     Metal Trade         Heavy           201602           2016       12,500.00    Existing
3       Europe  ENGLAND 255     Abraxo Cleaner Co.  Mining          201601           2016       8,400.00     New    

我目前面临的问题是，一个客户实际上可以属于多个企业。如果你看上面的例子，你会注意到Abraxo Cleaner Co.属于化学和采矿。

我试图创建一个名为"CUSTOMER_BUSSINESS_KEY"的新列，它将是一个串联：CONCAT(T1."IDNum",T2."BUSSINESS") .

接下来，我想修改LAG函数如下：LAG ("FISCALYEARMONTH", 1) OVER (PARTITION BY "CUSTOMER_BUSSINESS_KEY" ORDER BY T3."FISCALYEARMONTH" DESC) prev_fym。这应该允许我根据新创建的列而不是"IDNum"将记录标记为"新"和"现有"。

问题是，当我运行查询时，我收到一条错误消息，指出：

ORA-00904："CUSTOMER_BUSSINESS_KEY"：标识符无效

我的理解是SQL找不到"CUSTOMER_BUSSINESS_KEY"的表名。不幸的是，我找不到解决方法。

Answer 1

不能在同一 Select 中使用别名，可以添加另一个派生表或改用计算：

SELECT
"REGION"
, "COUNTRY"
, "IDNum"
, "CUSTOMER"
, "BUSSINESS"
, "CUSTOMER_BUSSINESS_KEY"
, "FISCALYEARMONTH"
, "FISCALYEAR"
, SUM("VALUE") 
, MAX(CASE WHEN to_date(prev_fym,'YYYYMM')  >= ADD_MONTHS (to_date("FISCALYEARMONTH",'YYYYMM'), -24) THEN 'Existing' 
           ELSE 'New ' END) "HISTORICAL_PURCHASE_FLAG"
FROM
(
    SELECT
    T1."REGION"
    , T1."COUNTRY"
    , T1."IDNum"
    , T1."CUSTOMER"
    , T2."BUSSINESS"
    , CONCAT(T1."IDNum",T2."BUSSINESS") AS "CUSTOMER_BUSSINESS_KEY"
    , T3."FISCALYEARMONTH"
    , T3."FISCALYEAR"
    , T4."VALUE"
    , LAG ("FISCALYEARMONTH", 1)
      OVER (PARTITION BY CONCAT(T1."IDNum",T2."BUSSINESS")
            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"
;

但您也可以简单地在 PARTITION 中使用多个列：

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 'New ' END) "HISTORICAL_PURCHASE_FLAG"
FROM
(
    SELECT
    T1."REGION"
    , T1."COUNTRY"
    , T1."IDNum"
    , T1."CUSTOMER"
    , T2."BUSSINESS"
    , T3."FISCALYEARMONTH"
    , T3."FISCALYEAR"
    , T4."VALUE"
    , LAG ("FISCALYEARMONTH", 1)
      OVER (PARTITION BY T1."IDNum",T2."BUSSINESS"
            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"
;