oracle 显示连续天数的交易数据

我在Oracle 11 G中有这个Transactions表。请不要评判我的问题。
表格包含列：

id (guid type)
sender_name
transaction_date
amount
sender_address
sender_phone
receiver_name

字符串
transaction_date列的格式类似于2014-11-30 04:26:29.000000。
我想要的是：显示Transactions表中的所有数据，其中sender_name在给定transaction_date between 01.01.2023 to 01.10.2023中连续5天发送超过1000个amount (sum(amount))。
即使，例如sender_name在4月连续5天发送了钱，并且在6月也连续5天发送了所有显示。
有人能告诉我怎么做吗？
样本数据：

CREATE TABLE transactions (id, sender_name, transaction_date, amount) AS
SELECT '1111', 'Alice', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '2222', 'Alice', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '3333', 'Alice', TIMESTAMP '2014-11-28 03:26:29', 1001 FROM DUAL UNION ALL
SELECT '4444', 'Alice', TIMESTAMP '2014-11-29 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '5555', 'Alice', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL

SELECT '6666', 'Betty', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '7777', 'Betty', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
  
SELECT '9999', 'Carol', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '8888', 'Carol', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '1234', 'Carol', TIMESTAMP '2014-11-28 03:26:29',  500 FROM DUAL UNION ALL
SELECT '4567', 'Carol', TIMESTAMP '2014-11-28 08:26:29',  501 FROM DUAL UNION ALL
SELECT '2345', 'Carol', TIMESTAMP '2014-11-29 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Carol', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '5678', 'Carol', TIMESTAMP '2014-12-01 05:26:29', 1001 FROM DUAL UNION ALL
  
SELECT '9999', 'Debra', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '8888', 'Debra', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '1234', 'Debra', TIMESTAMP '2014-11-28 03:26:29', 1001 FROM DUAL UNION ALL
SELECT '4567', 'Debra', TIMESTAMP '2014-11-28 08:26:29', 1001 FROM DUAL UNION ALL
SELECT '2345', 'Debra', TIMESTAMP '2014-11-30 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Debra', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '5678', 'Debra', TIMESTAMP '2014-12-01 05:26:29', 1001 FROM DUAL;

型
结果我想要Alice-s行的所有数据。因为他们连续5天发送的总量超过10000。如果我将连续5天改为超过5天，则显示Carol-s行。

由于您可以在一天内发生多个事务，因此您可以在每个sender_name的天数内使用DENSE_RANK分析函数来查找连续天数的组。然后对于每个组，您可以查找开始和结束日期，并仅筛选出有5天或更多天的行：

SELECT id,
       sender_name,
       transaction_date,
       amount
FROM   (
  SELECT id,
         sender_name,
         transaction_date,
         amount,
         MAX(TRUNC(transaction_date)) OVER (PARTITION BY sender_name, grp)
         - MIN(TRUNC(transaction_date)) OVER (PARTITION BY sender_name, grp)
         + 1
           AS num_days
  FROM   (
    SELECT id,
           sender_name,
           transaction_date,
           amount,
           TRUNC(transaction_date)
           - DENSE_RANK() OVER (
               PARTITION BY sender_name ORDER BY TRUNC(transaction_date)
             ) AS grp
    FROM   (
      SELECT id,
             sender_name,
             transaction_date,
             amount,
             SUM(amount) OVER (
               PARTITION BY sender_name, TRUNC(transaction_date)
             ) AS daily_amount
      FROM   transactions
    )
    WHERE  daily_amount > 1000
  )
)
WHERE  num_days >= 5
ORDER BY sender_name, transaction_date;

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

CREATE TABLE transactions (id, sender_name, transaction_date, amount) AS
SELECT '1111', 'Alice', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '2222', 'Alice', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '3333', 'Alice', TIMESTAMP '2014-11-28 03:26:29', 1001 FROM DUAL UNION ALL
SELECT '4444', 'Alice', TIMESTAMP '2014-11-29 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '5555', 'Alice', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL

SELECT '6666', 'Betty', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '7777', 'Betty', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
  
SELECT '9999', 'Carol', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '8888', 'Carol', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '1234', 'Carol', TIMESTAMP '2014-11-28 03:26:29',  500 FROM DUAL UNION ALL
SELECT '4567', 'Carol', TIMESTAMP '2014-11-28 08:26:29',  501 FROM DUAL UNION ALL
SELECT '2345', 'Carol', TIMESTAMP '2014-11-29 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Carol', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '5678', 'Carol', TIMESTAMP '2014-12-01 05:26:29', 1001 FROM DUAL UNION ALL
  
SELECT '9999', 'Debra', TIMESTAMP '2014-11-26 01:26:29', 1001 FROM DUAL UNION ALL
SELECT '8888', 'Debra', TIMESTAMP '2014-11-27 02:26:29', 1001 FROM DUAL UNION ALL
SELECT '1234', 'Debra', TIMESTAMP '2014-11-28 03:26:29', 1001 FROM DUAL UNION ALL
SELECT '4567', 'Debra', TIMESTAMP '2014-11-28 08:26:29', 1001 FROM DUAL UNION ALL
SELECT '2345', 'Debra', TIMESTAMP '2014-11-30 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Debra', TIMESTAMP '2014-11-30 05:26:29', 1001 FROM DUAL UNION ALL
SELECT '5678', 'Debra', TIMESTAMP '2014-12-01 05:26:29', 1001 FROM DUAL;

型
产出：
| ID|发送者名称|交易日期|量|
| --|--|--|--|
| 1111 |爱丽丝|2014-11-26 01：26：29.00000000| 1001 |
| 2222 |爱丽丝|2014-11-27 02：26：29.00000000| 1001 |
| 3333 |爱丽丝|2014-11-28 03：26：29.00000000| 1001 |
| 4444 |爱丽丝|2014-11-29 04：26：29.00000000| 1001 |
| 5555 |爱丽丝|2014-11-30 05：26：29.00000000| 1001 |
| 9999 |卡罗尔|2014-11-26 01：26：29.00000000| 1001 |
| 8888 |卡罗尔|2014-11-27 02：26：29.00000000| 1001 |
| 1234 |卡罗尔|2014-11-28 03：26：29.00000000| 500 |
| 4567 |卡罗尔|2014-11-28 08：26：29.00000000| 501 |
| 2345 |卡罗尔|2014-11-29 04：26：29.00000000| 1001 |
| 3456 |卡罗尔|2014-11-30 05：26：29.00000000| 1001 |
| 5678 |卡罗尔|2014-12-01 05：26：29.00000000| 1001 |
Oracle 11g fiddle
如果您使用的是Oracle 12或更高版本，则可以使用MATCH_RECOGNIZE大大简化解决方案：

SELECT *
FROM   (
  SELECT id,
         sender_name,
         transaction_date,
         amount
  FROM   (
    SELECT id,
           sender_name,
           transaction_date,
           amount,
           SUM(amount) OVER (
             PARTITION BY sender_name, TRUNC(transaction_date)
           ) AS daily_amount
    FROM   transactions
  )
  WHERE  daily_amount > 1000
)
MATCH_RECOGNIZE (
  PARTITION BY sender_name
  ORDER BY transaction_date
  ALL ROWS PER MATCH
  PATTERN (consecutive_days+ last_day)
  DEFINE
    consecutive_days AS NEXT(transaction_date) < TRUNC(transaction_date) + 2,
    last_day AS FIRST(TRUNC(transaction_date)) + 4 <= transaction_date
)

型
Oracle 18 fiddle的

oracle 显示连续天数的交易数据

1条答案

相关问题

热门标签

最新问答