oracle 计算多个记录属于一个ID

yduiuuwa  于 2023-11-17  发布在  Oracle
关注(0)|答案(4)|浏览(108)

我遇到了一个难题，想了很久也没有答案，非常感谢您的帮助！

问题：如何获取属于在过去5年内购买了牛奶或酸奶，并在随后15个月内购买了牛奶、酸奶或苹果的客户的所有记录？并按Customer_ID合并结果？

原始数据：
| 日期|项目|客户_ID|体积|
| --|--|--|--|
| 2021年1月1日|牛奶| 1345 | 2 |
| 2021年3月1日|苹果| 1345 | 2 |
| 2021年1月1日|牛奶| 8845 | 2 |
| 2023年1月1日|牛奶| 8845 | 2 |
| 二〇二一年十一月三十日|奶酪| 8845 | 1 |
目的数据表：
| 客户_ID|日期1|项目|体积|日期2|项目|体积|......这是一个好消息。|
| --|--|--|--|--|--|--|--|
| 1345 |2021年1月1日|牛奶| 2 |2021年3月1日|苹果| 2 ||
或简单
| 日期|项目|客户_ID|体积|
| --|--|--|--|
| 2021年1月1日|牛奶| 1345 | 2 |
| 2021年3月1日|苹果| 1345 | 2 |
对于第1步的分析是很好的。
我的尝试：

with t1 as (
   select 
      customer_id
      , date
      , item
      , sum(volume)
   from table
   where item in ('milk', 'yogurt')
     and date between to_date(20181030) and to_date(2023-10-30)
  ),

t2 as (
      select 
      customer_id
      , date
      , item
      , sum(volume)
   from table
   where item in ('milk', 'yogurt','apple')
    and date between to_date(20181030) and to_date(2023-10-30)
  )

select 
   a.customer_id
   , a.date as day1
   , a.item as item1
   , a.volume as v1
   , b.date as day2
   , b.item as item2
   , b.volume as v2
from t1 
    inner join t2 
         on t1.customer_id = t2.customer_id
            and (t1.date < t2.date and t1.date - t2.date < 456)

字符串
结果返回的是一个混乱的联合情况。日期被打乱了。一个客户有多条记录。如果该客户在接下来的15个月内多次购买苹果/牛奶/酸奶，则结果在一行中只返回一条记录。
你能告诉我出了什么问题吗？我该怎么做才能达到目的？谢谢！

oracle

4条答案

按热度按时间
l7mqbcuq

l7mqbcuq1#

这样的东西可能是你所追求的：

CREATE TABLE orig_data (
  purch_date, 
  item,
  cust_id,
  quantity) AS (
    SELECT TO_DATE ('2021-01-01', 'yyyy-mm-dd'),
           'Milk',
           1345,
           2
    FROM   dual UNION ALL
    SELECT TO_DATE ('2021-03-01', 'yyyy-mm-dd'),
           'Apple',
           1345,
           2
    FROM   dual UNION ALL
    SELECT TO_DATE ('2021-01-01', 'yyyy-mm-dd'),
           'Milk',
           8845,
           2
    FROM   dual UNION ALL
    SELECT TO_DATE ('2023-01-01', 'yyyy-mm-dd'),
           'Milk',
           8845,
           2
    FROM   dual UNION ALL
    SELECT TO_DATE ('2023-11-30', 'yyyy-mm-dd'),
           'Cheese',
           8845,
           1
    FROM   dual);

字符串
查询：

SELECT *
FROM   orig_data t1,
       orig_data t2
WHERE  UPPER (t1.item) IN ('MILK', 'YOGHURT')
AND    t1.purch_date BETWEEN (ADD_MONTHS (TRUNC (SYSDATE), -60)) AND TRUNC (SYSDATE)
AND    t2.cust_id = t1.cust_id
AND    UPPER (t2.item) IN ('MILK', 'YOGHURT', 'APPLE')
AND    t2.purch_date BETWEEN (t1.purch_date + 1) AND ADD_MONTHS (t1.purch_date, 15);

型
测试结果：

PURCH_DATE  ITEM    CUST_ID QUANTITY    PURCH_DATE  ITEM    CUST_ID QUANTITY
2021-01-01  Milk    1345    2           2021-03-01  Apple   1345    2

型

赞(0）分享  回复(0）举报  2023-11-17
6tqwzwtp

6tqwzwtp2#

为了扩展我之前的回答，并回答关于多次购买的问题，这里有一个样本数据，在原始数据的15个月内有2次购买，加上另一个比前2次超过15个月但比第3次不到15个月：

CREATE TABLE orig_data (
  purch_date, 
  item,
  cust_id,
  quantity) AS (
    SELECT TO_DATE ('2021-01-01', 'yyyy-mm-dd'),
           'Milk',
           1345,
           2
    FROM   dual UNION ALL
    SELECT TO_DATE ('2021-03-01', 'yyyy-mm-dd'),
           'Apple',
           1345,
           2
    FROM   dual UNION ALL
    SELECT TO_DATE ('2022-03-20', 'yyyy-mm-dd'),
           'Yoghurt',
           1345,
           1
    FROM   dual UNION ALL
        SELECT TO_DATE ('2023-05-07', 'yyyy-mm-dd'),
           'Milk',
           1345,
           3
    FROM   dual UNION ALL
    SELECT TO_DATE ('2021-01-01', 'yyyy-mm-dd'),
           'Milk',
           8845,
           2
    FROM   dual UNION ALL
    SELECT TO_DATE ('2023-01-01', 'yyyy-mm-dd'),
           'Milk',
           8845,
           2
    FROM   dual UNION ALL
    SELECT TO_DATE ('2023-11-30', 'yyyy-mm-dd'),
           'Cheese',
           8845,
           1
    FROM   dual);

字符串
完全相同的查询：

SELECT *
FROM   orig_data t1,
       orig_data t2
WHERE  UPPER (t1.item) IN ('MILK', 'YOGHURT')
AND    t1.purch_date BETWEEN (ADD_MONTHS (TRUNC (SYSDATE), -60)) AND TRUNC (SYSDATE)
AND    t2.cust_id = t1.cust_id
AND    UPPER (t2.item) IN ('MILK', 'YOGHURT', 'APPLE')
AND    t2.purch_date BETWEEN (t1.purch_date + 1) AND ADD_MONTHS (t1.purch_date, 15);

型
结果如下：

PURCH_DATE  ITEM    CUST_ID QUANTITY    PURCH_DATE  ITEM    CUST_ID QUANTITY
2021-01-01  Milk    1345    2           2021-03-01  Apple   1345    2
2021-01-01  Milk    1345    2           2022-03-20  Yoghurt 1345    1
2022-03-20  Yoghurt 1345    1           2023-05-07  Milk    1345    3

型

赞(0）分享  回复(0）举报  2023-11-17
0sgqnhkj

0sgqnhkj3#

如何获取过去5年中购买牛奶或酸奶的客户以及在接下来的15个月内购买牛奶、酸奶或苹果的客户的所有记录？并按Customer_ID合并结果？
语言有点混乱。如果你想得到：
1.每个customer_id的所有记录
1.如果在过去5年（60个月）内，他们购买了牛奶或酸奶，
1.如果在购买牛奶或酸奶后的15个月内，他们还购买了牛奶，酸奶或苹果。
然后你可以使用MATCH_RECOGNIZE：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY customer_id
  ORDER BY dt
  ALL ROWS PER MATCH
  PATTERN (^ any_items* milk_yoghurt any_items* milk_yoghurt_apple any_items* $)
  DEFINE
    milk_yoghurt AS  item IN ('Milk', 'Yoghurt')
                 AND dt >= ADD_MONTHS(TRUNC(SYSDATE), -60),
    milk_yoghurt_apple
                 AS  item IN ('Milk', 'Yoghurt', 'Apple')
                 AND dt <= ADD_MONTHS(TRUNC(milk_yoghurt.dt), 15)
)

字符串
其中，对于样本数据：

CREATE TABLE table_name (
  dt          DATE,
  Item        VARCHAR(10),
  Customer_ID INT,
  Volume      INT
);

INSERT INTO table_name (dt, Item,  Customer_ID, Volume)
SELECT DATE '2021-01-01', 'Milk',   1345, 2 FROM DUAL UNION ALL
SELECT DATE '2021-03-01', 'Apple',  1345, 2 FROM DUAL UNION ALL
SELECT DATE '2021-01-01', 'Milk',   8845, 2 FROM DUAL UNION ALL
SELECT DATE '2023-01-01', 'Milk',   8845, 2 FROM DUAL UNION ALL
SELECT DATE '2021-11-30', 'Cheese', 8845, 1 FROM DUAL

型
产出：
| 客户ID| DT|项目|体积|
| --|--|--|--|
| 1345 |2021-01-01 00：00：00|牛奶| 2 |
| 1345 |2021-03-01 00：00：00|苹果| 2 |
如果你不想使用MATCH_RECOGNIZE，那么你可以使用解析函数和条件聚合：

SELECT customer_id,
       dt,
       item,
       volume
FROM   (
  SELECT customer_id,
         dt,
         item,
         volume,
         COUNT(
           CASE
           WHEN dt >= ADD_MONTHS(TRUNC(SYSDATE), -60)
           AND  item IN ('Milk', 'Yoghurt')
           AND  EXISTS(
                  SELECT 1
                  FROM   table_name x
                  WHERE  x.dt >  t.dt
                  AND    x.dt <= ADD_MONTHS(t.dt, 15)
                  AND    x.customer_id = t.customer_id
                  AND    x.item IN ('Milk', 'Yoghurt', 'Apple')
                )
           THEN 1
           END
         ) OVER (PARTITION BY customer_id) AS num_matches
  FROM   table_name t
)
WHERE  num_matches > 0;

型
它的输出是一样的。
如果您只想要匹配的项目（而不是客户购买的所有项目），则：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY customer_id
  ORDER BY dt
  ALL ROWS PER MATCH
  PATTERN (milk_yoghurt {- any_items* -} milk_yoghurt_apple)
  DEFINE
    milk_yoghurt AS  item IN ('Milk', 'Yoghurt')
                 AND dt >= ADD_MONTHS(TRUNC(SYSDATE), -60),
    milk_yoghurt_apple
                 AS  item IN ('Milk', 'Yoghurt', 'Apple')
                 AND dt <= ADD_MONTHS(TRUNC(milk_yoghurt.dt), 15)
);

型
它为您的示例数据输出相同的结果，因为该客户没有购买任何其他项目。
fiddle

赞(0）分享  回复(0）举报  2023-11-17
whlutmcx

whlutmcx4#

在解释了期间条件后更新了答案。这里是一个具有更大样本数据集的选项（添加了3行-其中一行是Yoghurt），用于测试求和和多个日期。

WITH
    tbl (   A_DATE, ITEM, CUST_ID,  VOLUME ) AS
        (   Select  To_Date('2021-01-01', 'yyyy-mm-dd'), 'Milk', 1345, 2 From Dual Union All
    --  3 rows added
      Select    To_Date('2021-01-01', 'yyyy-mm-dd'), 'Milk', 1345, 1 From Dual Union All
      Select    To_Date('2021-03-03', 'yyyy-mm-dd'), 'Yoghurt', 1345, 7 From Dual Union All
      Select    To_Date('2021-06-06', 'yyyy-mm-dd'), 'Milk', 1345, 3 From Dual Union All
    --
            Select  To_Date('2021-03-01', 'yyyy-mm-dd'), 'Apple', 1345, 2 From Dual Union All 
            Select  To_Date('2021-01-01', 'yyyy-mm-dd'), 'Milk', 8845, 2 From Dual Union All
            Select  To_Date('2023-01-01', 'yyyy-mm-dd'), 'Milk', 8845, 2 From Dual Union All 
            Select  To_Date('2021-11-30', 'yyyy-mm-dd'), 'Cheese', 8845, 1 From Dual
        ),
  t1 AS
      ( Select  Min(A_DATE) "A_DATE",   ITEM, CUST_ID,  Sum(VOLUME) "VOLUME_1"
        From    tbl
        Where   Case When A_DATE Between ADD_MONTHS(To_Date('2023-10-30', 'yyyy-mm-dd'), -60) And To_Date('2023-10-30', 'yyyy-mm-dd') And ITEM In('Milk', 'Yoghurt') Then 'Y' Else 'N' End = 'Y' OR
                Case When A_DATE Between  A_DATE And ADD_MONTHS(To_Date('2023-10-30', 'yyyy-mm-dd'), 15) And ITEM In('Milk', 'Yoghurt', 'Apple') Then 'Y' Else 'N' End = 'Y'
        Group By ITEM, CUST_ID
      ), 
  t2 AS 
      ( Select  Min(A_DATE) "A_DATE",   ITEM, CUST_ID,  Sum(VOLUME) "VOLUME_2"
        From    tbl
        Where   Case When A_DATE Between ADD_MONTHS(To_Date('2023-10-30', 'yyyy-mm-dd'), -60) And To_Date('2023-10-30', 'yyyy-mm-dd') And ITEM In('Milk', 'Yoghurt') Then 'Y' Else 'N' End = 'Y' OR
                Case When A_DATE Between A_DATE And ADD_MONTHS(To_Date('2023-10-30', 'yyyy-mm-dd'), 15) And ITEM In('Milk', 'Yoghurt', 'Apple') Then 'Y' Else 'N' End = 'Y'
        Group By ITEM, CUST_ID
      ),
  t3 AS 
      ( Select  Min(A_DATE) "A_DATE",   ITEM, CUST_ID,  Sum(VOLUME) "VOLUME_3"
        From    tbl
        Where   Case When A_DATE Between ADD_MONTHS(To_Date('2023-10-30', 'yyyy-mm-dd'), -60) And To_Date('2023-10-30', 'yyyy-mm-dd') And ITEM In('Milk', 'Yoghurt') Then 'Y' Else 'N' End = 'Y' OR
                Case When A_DATE Between A_DATE And ADD_MONTHS(To_Date('2023-10-30', 'yyyy-mm-dd'), 15) And ITEM In('Milk', 'Yoghurt', 'Apple') Then 'Y' Else 'N' End = 'Y'
        Group By ITEM, CUST_ID
      )

字符串
主SQL -选项1 -每个客户一行

SELECT  CUST_ID_1 "CUST_ID", 
        A_DATE_1, ITEM_1, VOLUME_1,
        A_DATE_2, ITEM_2, VOLUME_2,          
        A_DATE_3, ITEM_3, VOLUME_3
        
FROM    ( Select  ROW_NUMBER() OVER(Partition By t1.CUST_ID Order By t1.CUST_ID) "RN", 
                  t1.A_DATE "A_DATE_1", t1.ITEM "ITEM_1", t1.CUST_ID "CUST_ID_1",   t1.VOLUME_1, 
                  t2.A_DATE "A_DATE_2", t2.ITEM "ITEM_2", t2.CUST_ID "CUST_ID_2",   t2.VOLUME_2,
                  t3.A_DATE "A_DATE_3", t3.ITEM "ITEM_3", t3.CUST_ID "CUST_ID_3",   t3.VOLUME_3
          From    t1
          Left Join t2 ON( t2.CUST_ID = t1.CUST_ID And t2.ITEM In('Milk', 'Yoghurt', 'Apple') And t2.A_DATE != t1.A_DATE And t2.ITEM != t1.ITEM)
          Left Join t3 ON( t3.CUST_ID = t1.CUST_ID And t3.ITEM In('Milk', 'Yoghurt', 'Apple') And t3.A_DATE != t1.A_DATE And t3.ITEM != t2.ITEM And t3.ITEM != t1.ITEM)
          Order By t1.CUST_ID, t1.A_DATE
      ) 
WHERE RN = 1 And ITEM_2 Is Not Null
ORDER BY CUST_ID, A_DATE_1
/*
   CUST_ID A_DATE_1  ITEM_1    VOLUME_1 A_DATE_2  ITEM_2    VOLUME_2 A_DATE_3  ITEM_3    VOLUME_3
---------- --------- ------- ---------- --------- ------- ---------- --------- ------- ----------
      1345 01-JAN-21 Milk             6 03-MAR-21 Yoghurt          7 01-MAR-21 Apple            2 */

型
提供的样本数据结果：

/*   
   CUST_ID A_DATE_1  ITEM_1   VOLUME_1 A_DATE_2  ITEM_2   VOLUME_2 A_DATE_3  ITEM_3   VOLUME_3
---------- --------- ------ ---------- --------- ------ ---------- --------- ------ ----------
      1345 01-JAN-21 Milk            2 01-MAR-21 Apple           2     
*/

型
主SQL -选项2 -导致多行

SELECT  A_DATE, ITEM, CUST_ID, VOLUME
FROM    (   SELECT  A_DATE, ITEM, CUST_ID, VOLUME, Count(Distinct ITEM) OVER(Partition By CUST_ID) "CNT"
            FROM    ( Select  A_DATE,   ITEM, CUST_ID, VOLUME_1 "VOLUME" From t1 Union All
                      Select  A_DATE,   ITEM, CUST_ID, VOLUME_2 From t2 Union All
                      Select  A_DATE,   ITEM, CUST_ID, VOLUME_3 From t3
                    ) 
            GROUP BY A_DATE, ITEM, CUST_ID, VOLUME
        )
WHERE CNT > 1
ORDER BY CUST_ID, A_DATE

/*
A_DATE    ITEM       CUST_ID     VOLUME
--------- ------- ---------- ----------
01-JAN-21 Milk          1345          6
01-MAR-21 Apple         1345          2
03-MAR-21 Yoghurt       1345          7 */

型
提供的样本数据结果：

/*
A_DATE    ITEM      CUST_ID     VOLUME
--------- ------ ---------- ----------
01-JAN-21 Milk         1345          2
01-MAR-21 Apple        1345          2  */

型

赞(0）分享  回复(0）举报  2023-11-17
我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

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