將列值與Oracle中的前一個記錄進行比較 -有解無憂

我的oracle表資料如下。

金額。 FEB-2021MAR-2021 2000APR-2021

2000JUN-20212000

JUL-2021 2000

Org_ ID	Product_ID	訂單月份	Order_Month
101	201	2000
101	201	2000
101	201
101	201	1500	101	201
101	202	101	202

我們需要比較之前的金額值，并找到與Product_ID不匹配的金額記錄。

我的輸出應該像下面這樣。我試著用滯后法，但找不到解決辦法。誰能提供如何解決這個問題的意見呢？

。金額。。 1500

1500

JUN-2021 to JUL-20211500

Org_ID	Product_ID	訂單月份	Order_Month
101	201	2021年1月至2021年3月	2000
101	201	101	201	101	202

uj5u.com熱心網友回復：

你可以嘗試以下方法

SELECT
    "Org_ID", 
    "Product_ID", 
    CONCAT(
         CONCAT(Order_Month_Group,' to ')。
        TO_CHAR(MAX(actual_date),'Mon-YYY')
    ) as Order_Month, 
    "金額"
FROM (
    SELECT[/span
        t1.*,
        
        LAG(
             "Order_Month",
             CASE WHEN continued=0 THEN 0 ELSE seq_num1 END
            , "Order_Month") OVER (
                PARTITION BY "Org_ID", "Product_ID", "Amount"
                ORDER BY actual_date
        ) as Order_Month_Group
    FROM (
        SELECT[/span 
            t.*,
            TO_DATE(t. "Order_Month",'MON-YYY') as actual_date,
            ROW_NUMBER() OVER (
                PARTITION BY t. "Org_ID",t. "Product_ID",t. "Amount"
                ORDER BY TO_DATE("Order_Month",'MON-YYY')
            ) as seq_num,
            CASE  
                WHEN t. "Amount" = LAG(t. "Amount",1, t. "Amount") OVER (
                                      PARTITION BY t. "Org_ID",t. "Product_ID"
                                      ORDER BY TO_DATE("Order_Month",'Mon-YYY')
                                  ) THEN1 
                ELSE 0 
            END as continued
        FROM 
            my_oracle_table t
    ) t1
) t2
GROUP BY "Org_ID", "Product_ID", Order_Month_Group," Amount"
ORDER BY MIN(actual_date)

或者

SELECT
    "Org_ID", 
    "Product_ID", 
    CONCAT(
         CONCAT(TO_CHAR(MIN(actual_date),'MON-YYY'),' to ')。
        TO_CHAR(MAX(actual_date),'MON-YYY')
    ) as Order_Month, 
    "金額"
FROM (
    SELECT[/span
        t1.*,
        SUM(continued) OVER ( ORDER BY actual_date) as GRP
    FROM (
        SELECT[/span 
            t.*,
            TO_DATE("Order_Month",'MON-YYY') as actual_date,
            CASE> 
                WHEN t. "Amount" = LAG(t. "Amount",1, t. "Amount") OVER (
                                      PARTITION BY t. "Org_ID",t. "Product_ID"
                                      ORDER BY TO_DATE("Order_Month",'Mon-YYY')
                                  ) THEN0
                ELSE 1 
            END as continued
        FROM 
            my_oracle_table t
    ) t1
) t2
GROUP BY "Org_ID", "Product_ID", grp, "Amount"
ORDER BY MIN(actual_date)

查看DB Fiddle的演示

讓我知道這是否對你有用。

uj5u.com熱心網友回復：

這是一種空白和孤島問題。在這種情況下，最簡單的解決方案可能是行數的差異。下面假設order_month實際上是一個字串（而不是一個日期）：

select org_id, product_id, amount,
       min（order_month）, max（order_month）。
from (select t.*,
             row_number() over (partition by org_id。product_id order by to_date(order_month, 'mon-yyy') as seqnum,
             row_number() over (partition by org_id。product_id, amount order by to_date(order_month, 'mon-yyy') as seqnum_2
      from t
     ) t
group by org_id, product_id, amount, (seqnum - seqnm_2) 。

為什么這樣做，解釋起來有點棘手。然而，如果你看一下子查詢的結果，你會發現這兩個值之間的差異在相鄰的月份是不變的。

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/325021.html

標籤：

上一篇：如何在JSON_DATAGUIDE中獲得帶有鍵的值（動態）？

下一篇：根據gitdescribe的輸出重新構建make目標