Oracle SQL -基于日志包括/排除特定行

fcwjkofz  于 2023-10-16  发布在  Oracle
关注(0)|答案(1)|浏览(71)

数据（仅限客户和产品的一种组合）

| 客户ID|产品ID|订单ID|订单日期|订单是否已完成？|
| --|--|--|--|--|
| 123123 | 4444 | 701 |二〇二二年九月十三日| 0 |
| 123123 | 4444 | 702 |二〇二二年十一月十五日| 0 |
| 123123 | 4444 | 703 |二〇二二年十一月十六日| 0 |
| 123123 | 4444 | 704 |二〇二三年一月三日| 0 |
| 123123 | 4444 | 705 |二〇二三年一月三十一日| 0 |
| 123123 | 4444 | 706 |二〇二三年二月六日| 1 |
| 123123 | 4444 | 707 |二〇二三年二月二十八日| 1 |
| 123123 | 4444 | 708 |二〇二三年三月二十九日| 1 |
| 123123 | 4444 | 709 |二〇二三年四月二十五日| 1 |
| 123123 | 4444 | 710 |二〇二三年七月五日| 0 |
| 123123 | 4444 | 711 |二〇二三年七月十二日| 0 |
| 123123 | 4444 | 712 |二〇二三年七月十八日| 0 |
| 123123 | 4444 | 713 |二〇二三年八月一日| 1 |
| 123123 | 4444 | 714 |二〇二三年八月十五日| 1 |

What's needed

添加一个新列，该列将用于根据以下逻辑包括/排除行：
1.如果'订单已完成？' = 1 -->则'1'（即包括在内）
1.如果'订单已完成？' = 0且该订单是在完成订单之前或之后的30天内发出的-->则'0'（即排除）
1.如果'订单已完成？' = 0且该订单不是在完成订单之前或之后的30天内下的且是在未完成订单之前的30天内下的-->则'0'（即排除）
1.否则“1”
因此，新列（名为“包括？'）应该看起来像这样：
| 客户ID|产品ID|订单ID|订单日期|订单是否已完成？|包括？|
| --|--|--|--|--|--|
| 123123 | 4444 | 701 |二〇二二年九月十三日| 0 | 1 |
| 123123 | 4444 | 702 |二〇二二年十一月十五日| 0 | 0 |
| 123123 | 4444 | 703 |二〇二二年十一月十六日| 0 | 1 |
| 123123 | 4444 | 704 |二〇二三年一月三日| 0 | 1 |
| 123123 | 4444 | 705 |二〇二三年一月三十一日| 0 | 0 |
| 123123 | 4444 | 706 |二〇二三年二月六日| 1 | 1 |
| 123123 | 4444 | 707 |二〇二三年二月二十八日| 1 | 1 |
| 123123 | 4444 | 708 |二〇二三年三月二十九日| 1 | 1 |
| 123123 | 4444 | 709 |二〇二三年四月二十五日| 1 | 1 |
| 123123 | 4444 | 710 |二〇二三年七月五日| 0 | 0 |
| 123123 | 4444 | 711 |二〇二三年七月十二日| 0 | 0 |
| 123123 | 4444 | 712 |二〇二三年七月十八日| 0 | 0 |
| 123123 | 4444 | 713 |二〇二三年八月一日| 1 | 1 |
| 123123 | 4444 | 714 |二〇二三年八月十五日| 1 | 1 |
如何完成上述逻辑中的步骤2和步骤3？
我创建了多个CTE，但结果不是很准确，而且性能也非常广泛。

oracle

1条答案

按热度按时间
5ktev3wc

5ktev3wc1#

这里有两个变体，它们都不是与预期结果完全匹配的，但也许这些变体可以帮助您实现所需的条件。

CREATE TABLE my_table (
    Customer_ID NUMBER,
    Product_ID NUMBER,
    Order_ID NUMBER,
    Order_Date DATE,
    Order_Fulfilled NUMBER
);
INSERT ALL
  INTO my_table VALUES (123123, 4444, 701, TO_DATE('Sep 13, 2022', 'Mon dd, yyyy'), 0)
  INTO my_table VALUES (123123, 4444, 702, TO_DATE('Nov 15, 2022', 'Mon dd, yyyy'), 0)
  INTO my_table VALUES (123123, 4444, 703, TO_DATE('Nov 16, 2022', 'Mon dd, yyyy'), 0)
  INTO my_table VALUES (123123, 4444, 704, TO_DATE('Jan 3, 2023', 'Mon dd, yyyy'), 0)
  INTO my_table VALUES (123123, 4444, 705, TO_DATE('Jan 31, 2023', 'Mon dd, yyyy'), 0)
  INTO my_table VALUES (123123, 4444, 706, TO_DATE('Feb 6, 2023', 'Mon dd, yyyy'), 1)
  INTO my_table VALUES (123123, 4444, 707, TO_DATE('Feb 28, 2023', 'Mon dd, yyyy'), 1)
  INTO my_table VALUES (123123, 4444, 708, TO_DATE('Mar 29, 2023', 'Mon dd, yyyy'), 1)
  INTO my_table VALUES (123123, 4444, 709, TO_DATE('Apr 25, 2023', 'Mon dd, yyyy'), 1)
  INTO my_table VALUES (123123, 4444, 710, TO_DATE('Jul 5, 2023', 'Mon dd, yyyy'), 0)
  INTO my_table VALUES (123123, 4444, 711, TO_DATE('Jul 12, 2023', 'Mon dd, yyyy'), 0)
  INTO my_table VALUES (123123, 4444, 712, TO_DATE('Jul 18, 2023', 'Mon dd, yyyy'), 0)
  INTO my_table VALUES (123123, 4444, 713, TO_DATE('Aug 1, 2023', 'Mon dd, yyyy'), 1)
  INTO my_table VALUES (123123, 4444, 714, TO_DATE('Aug 15, 2023', 'Mon dd, yyyy'), 1)
SELECT 1 FROM DUAL;
14 rows affected
SELECT
      Customer_ID
    , Product_ID
    , Order_ID
    , Order_Date
    , Order_Fulfilled
    , CASE 
        WHEN Order_Fulfilled = 1
            THEN 1
        WHEN Order_Fulfilled = 0
            AND (
                EXISTS (
                    SELECT 1
                    FROM my_table t2
                    WHERE t2.Customer_ID = t.Customer_ID
                        AND t2.Product_ID = t.Product_ID
                        AND t2.Order_Fulfilled = 1
                        AND t2.Order_Date BETWEEN t.Order_Date - 30 AND t.Order_Date + 30
                    )
                )
            THEN 0
        WHEN Order_Fulfilled = 0
            AND (
                EXISTS (
                    SELECT 1
                    FROM my_table t3
                    WHERE t3.Customer_ID = t.Customer_ID
                        AND t3.Product_ID = t.Product_ID
                        AND t3.Order_Fulfilled = 0
                        AND t3.Order_Date BETWEEN t.Order_Date - 30 AND t.Order_Date
                    )
                )
            THEN 0
        ELSE 1
        END AS Include
FROM my_table t
ORDER BY
      Customer_ID
    , Product_ID
    , Order_Date;

| 客户ID|产品编号|ORDER_ID|订单日期|订单_已完成|包括|
| --|--|--|--|--|--|
| 123123 | 4444 | 701 |22年9月13日| 0 | 0 |
| 123123 | 4444 | 702 |2022年11月15日| 0 | 0 |
| 123123 | 4444 | 703 |22年11月16日| 0 | 0 |
| 123123 | 4444 | 704 |2023年1月3日| 0 | 0 |
| 123123 | 4444 | 705 |23年1月31日| 0 | 0 |
| 123123 | 4444 | 706 |23年2月6日| 1 | 1 |
| 123123 | 4444 | 707 |23年2月28日| 1 | 1 |
| 123123 | 4444 | 708 |23年3月29日| 1 | 1 |
| 123123 | 4444 | 709 |23年4月25日| 1 | 1 |
| 123123 | 4444 | 710 |2023年7月5日| 0 | 0 |
| 123123 | 4444 | 711 |23年7月12日| 0 | 0 |
| 123123 | 4444 | 712 |23年7月18日| 0 | 0 |
| 123123 | 4444 | 713 |23年8月1日| 1 | 1 |
| 123123 | 4444 | 714 |23年8月15日| 1 | 1 |

SELECT
      Customer_ID
    , Product_ID
    , Order_ID
    , Order_Date
    , Order_Fulfilled
    , CASE 
        WHEN Order_Fulfilled = 1
            THEN '1'
        WHEN Order_Fulfilled = 0 AND SUM(CASE WHEN Order_Fulfilled = 1 THEN 1 ELSE 0 END)
          OVER (
                PARTITION BY Customer_ID, Product_ID 
                ORDER BY Order_Date
                ROWS BETWEEN 30 PRECEDING AND 30 FOLLOWING
                ) > 0
            THEN '0'
        WHEN Order_Fulfilled = 0 AND SUM(CASE WHEN Order_Fulfilled = 0 THEN 1 ELSE 0 END) 
           OVER (
                PARTITION BY Customer_ID, Product_ID 
                ORDER BY Order_Date
                ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING
                ) > 0
            THEN '0'
        ELSE '1'
        END AS Include
FROM my_table
ORDER BY Customer_ID
    , Product_ID
    , Order_Date;

| 客户ID|产品编号|ORDER_ID|订单日期|订单_已完成|包括|
| --|--|--|--|--|--|
| 123123 | 4444 | 701 |22年9月13日| 0 | 0 |
| 123123 | 4444 | 702 |2022年11月15日| 0 | 0 |
| 123123 | 4444 | 703 |22年11月16日| 0 | 0 |
| 123123 | 4444 | 704 |2023年1月3日| 0 | 0 |
| 123123 | 4444 | 705 |23年1月31日| 0 | 0 |
| 123123 | 4444 | 706 |23年2月6日| 1 | 1 |
| 123123 | 4444 | 707 |23年2月28日| 1 | 1 |
| 123123 | 4444 | 708 |23年3月29日| 1 | 1 |
| 123123 | 4444 | 709 |23年4月25日| 1 | 1 |
| 123123 | 4444 | 710 |2023年7月5日| 0 | 0 |
| 123123 | 4444 | 711 |23年7月12日| 0 | 0 |
| 123123 | 4444 | 712 |23年7月18日| 0 | 0 |
| 123123 | 4444 | 713 |23年8月1日| 1 | 1 |
| 123123 | 4444 | 714 |23年8月15日| 1 | 1 |
fiddle

赞(0）分享  回复(0）举报  2023-10-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com