显示连续天数的交易数据-解网

问：

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

表包含列：

id (guid type)
sender_name
transaction_date
amount
sender_address
sender_phone
receiver_name

列的格式类似于。transaction_date2014-11-30 04:26:29.000000

我想要的是：显示表中的所有数据，其中那些连续 1000 天发送超过 5 个数据的人在给定的 .Transactionssender_nameamount (sum(amount))transaction_date between 01.01.2023 to 01.10.2023

即使，例如，在4月连续5天汇款，在6月也连续5天汇款，显示全部。sender_name

有人能告诉我该怎么做吗？

示例数据：

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;

结果我想要 -s 行的所有数据。由于总金额，他们连续 5 天发送超过 10000 个。如果我将连续 5 天更改为 5 天以上，则显示 -s 行。AliceCarol

SQL 预言机 oracle11g

请编辑问题并包含一个最小的可重现示例：示例数据的 and 语句;您对解决方案的尝试;您的查询的问题/错误的详细信息（即完整的错误消息以文本形式，而不是图像形式）;以及示例数据的预期输出。仅仅说明“列别名有问题”并不能告诉我们问题出在哪里，以及是否是您正在使用的 IDE 的问题，或者 Oracle 是否返回异常，或者是否是其他原因。CREATE TABLEINSERT

0赞 MSS 10/27/2023

@MT0我编辑了问题并尝试了您的代码。IDE有问题吗？

0赞 MSS 10/27/2023

并尝试了另一个IDE仍然是相同的问题

0赞 MT0 10/27/2023

至于您的编辑 - 您尚未在内联视图上定义别名，并且表别名仅在您定义该别名的最内层查询中相关（并在不需要时停止使用带引号的标识符）。从我的问题的评论中看到小提琴，它显示了如何添加别名。"a"

答：

3赞 MT0 10/27/2023 #1

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

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;

输出：

编号	SENDER_NAME	TRANSACTION_DATE	量
1111	爱丽丝	2014-11-26 01:26:29.000000000	1001
2222	爱丽丝	2014-11-27 02:26:29.000000000	1001
3333	爱丽丝	2014-11-28 03:26:29.000000000	1001
4444	爱丽丝	2014-11-29 04:26:29.000000000	1001
5555	爱丽丝	2014-11-30 05:26:29.000000000	1001
9999	颂歌	2014-11-26 01:26:29.000000000	1001
8888	颂歌	2014-11-27 02:26:29.000000000	1001
1234	颂歌	2014-11-28 03:26:29.000000000	500
4567	颂歌	2014-11-28 08:26:29.000000000	501
2345	颂歌	2014-11-29 04:26:29.000000000	1001
3456	颂歌	2014-11-30 05:26:29.000000000	1001
5678	颂歌	2014-12-01 05:26:29.000000000	1001

Oracle 11g 小提琴

如果您使用的是 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 小提琴

@MSS 如答案中链接的小提琴所示，该代码在 Oracle 11g 中有效。我不使用 dbForge Studio，我“不明白你”，因为答案使用有效的 Oracle 语法并且有效。如果您在使用 IDE 时遇到问题，那么我建议您尝试使用支持 Oracle 使用的语法的其他 IDE。

0赞 MSS 10/27/2023

好的，非常感谢@MT0。让我尝试使用不同的 IDE

0赞 MSS 10/27/2023

还有一件事。例如，如果我有很多列，我应该把它们都写在每列上吗？TransactionsSelect

0赞 MT0 10/27/2023

@MSS 您可以为表和每个内联视图指定一个别名，然后如果您不想将它们全部写出，则可以使用（但您只需要写出它们一次，然后复制/粘贴每个嵌套的子查询）。SELECT alias.*, other_columns FROM table_name alias

上一个：甲骨文 11g。7天内输出数据

下一个：SQL查询返回一个unix时间来表示一天

显示连续天数的交易数据

Display transactions data with consecutive days

评论

评论