甲骨文 11g。7天内输出数据-解网

问：

Oracle 11g 中有一张表，例如：Transactions

CREATE TABLE transactions (id, sender_name, transaction_date, amount) AS
SELECT '1111', 'Alice', TIMESTAMP '2023-01-03 01:26:29', 700 FROM DUAL UNION ALL
SELECT '2222', 'Alice', TIMESTAMP '2023-01-05 04:26:29', 450 FROM DUAL UNION ALL
SELECT '3333', 'Alice', TIMESTAMP '2023-01-08 18:26:29', 1000 FROM DUAL UNION ALL
SELECT '4444', 'Alice', TIMESTAMP '2023-01-09 04:26:29', 200 FROM DUAL UNION ALL

SELECT '6666', 'Betty', TIMESTAMP '2023-03-12 15:26:29', 845 FROM DUAL UNION ALL
SELECT '7777', 'Betty', TIMESTAMP '2023-03-19 11:00:29', 300 FROM DUAL UNION ALL
  
SELECT '9999', 'Carol', TIMESTAMP '2023-08-26 01:26:29', 100 FROM DUAL UNION ALL
SELECT '8888', 'Carol', TIMESTAMP '2023-08-29 02:26:29', 501 FROM DUAL UNION ALL
SELECT '1234', 'Carol', TIMESTAMP '2023-09-10 03:26:29',  500 FROM DUAL UNION ALL
SELECT '4567', 'Carol', TIMESTAMP '2023-09-11 08:26:29',  501 FROM DUAL UNION ALL
SELECT '2345', 'Carol', TIMESTAMP '2023-09-17 04:26:29', 1001 FROM DUAL UNION ALL
SELECT '3456', 'Carol', TIMESTAMP '2023-09-30 05:26:29', 700 FROM DUAL UNION ALL
SELECT '5678', 'Carol', TIMESTAMP '2023-10-01 05:26:29', 200 FROM DUAL;

对此深表歉意，但我需要两个选项来输出数据:)））

选择：显示客户在第一次汇款后七天内汇款总额超过> 1000笔的交易。在这种情况下，结果应该是：所有 s 个交易，因为第一笔付款是 and 直到，而她在这些天的交易总数是。并且所有的交易也都有相同的逻辑。对于 Carol 事务 10.09、11.09 和 17.09(sender_name)(amount)transaction_dateAlice03.01within seven days09.01amountmore than 1000Betty

2.选项：是否可以确定一周的开始（从周一开始）并显示一周内（周一至周日）进行的客户交易，总金额超过 1000（一周内的总金额）？如果是，那么该怎么做。

SQL 预言机 oracle11g

你什么都没试过吗？您至少应该能够获得每个人的第一笔交易。然后，您可以再次从表中进行选择，并将其限制为从（？）第一笔交易开始/之后的日期，直到六天后。然后看看你得到的总和。请向我们展示您到目前为止所得到的，并告诉我们您被困在哪里。

0赞 Thorsten Kettner 10/30/2023

我不明白你为什么选择卡罗尔的 10.09、11.09 和 17.09 的交易。她的第一笔交易是在 8 月 26 日，所以 9 月份的交易无关紧要。

0赞 Saidvali 10/31/2023

她的第一笔交易是在8月26日，但这次交易后7天内的总金额不超过1000

0赞 Thorsten Kettner 10/31/2023

正确，所以卡罗尔一定不在结果中，因为她不符合标准（“第一次transaction_date后 7 天内金额> 1000”）。9月份的交易与此无关。你为什么提到他们？无论是否进行 9 月份的交易，决策都必须相同。

答：

1赞 MT0 10/29/2023 #1

对于选项 1，您可以使用带有范围窗口的分析函数，首先对过去 7 天的金额进行总和，然后在第二遍中计算未来一周中有多少行的行的先前计算的总数超过 1000，并仅筛选到这些行：

SELECT id,
       sender_name,
       transaction_date,
       amount
FROM   (
  SELECT id,
         sender_name,
         transaction_date,
         amount,
         COUNT(CASE WHEN previous_weekly_amount > 1000 THEN 1 END) OVER (
             PARTITION BY sender_name
             ORDER BY transaction_date
             RANGE BETWEEN INTERVAL '0 00:00:00.000000000' DAY TO SECOND PRECEDING
                       AND INTERVAL '6 23:59:59.999999999' DAY TO SECOND FOLLOWING
         ) AS has_weekly_total_over_1000
  FROM   (
    SELECT id,
           sender_name,
           transaction_date,
           amount,
           SUM(amount) OVER (
             PARTITION BY sender_name
             ORDER BY transaction_date
             RANGE BETWEEN INTERVAL '6 23:59:59.999999999' DAY TO SECOND PRECEDING
                       AND INTERVAL '0 00:00:00.000000000' DAY TO SECOND FOLLOWING
           ) AS previous_weekly_amount
    FROM   transactions
  )
)
WHERE  has_weekly_total_over_1000 > 0;

对于示例数据，输出：

编号	SENDER_NAME	TRANSACTION_DATE	量
1111	爱丽丝	2023-01-03 01:26:29.000000000	700
2222	爱丽丝	2023-01-05 04:26:29.000000000	450
3333	爱丽丝	2023-01-08 18:26:29.000000000	1000
4444	爱丽丝	2023-01-09 04:26:29.000000000	200
6666	贝蒂	2023-03-12 15:26:29.000000000	845
7777	贝蒂	2023-03-19 11:00:29.000000000	300
1234	颂歌	2023-09-10 03:26:29.000000000	500
4567	颂歌	2023-09-11 08:26:29.000000000	501
2345	颂歌	2023-09-17 04:26:29.000000000	1001

选项 2 更简单，因为您只需要使用分析函数并按发件人和 ISO 周进行聚合：

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, 'IW'))
           AS weekly_amount
  FROM   transactions
)
WHERE  weekly_amount > 1000;

对于示例数据，输出：

编号	SENDER_NAME	TRANSACTION_DATE	量
1111	爱丽丝	2023-01-03 01:26:29.000000000	700
2222	爱丽丝	2023-01-05 04:26:29.000000000	450
3333	爱丽丝	2023-01-08 18:26:29.000000000	1000
4567	颂歌	2023-09-11 08:26:29.000000000	501
2345	颂歌	2023-09-17 04:26:29.000000000	1001

小提琴

@Saidvali 如果您想查看正在发生的事情，请运行子查询并查看生成的数据。是的，如果将限制更改为那么，将发生这种情况，因为排除的行不符合该阈值。Carol 的行不包括在内，因为该行超出了 7 天窗口（1 小时 1 毫秒），因此在 7 天窗口内只有 1001 的总数。>11452023-09-10 03:26:292023-09-17 04:26:29

0赞 Saidvali 11/3/2023

很棒的@MT0，非常感谢

0赞 Saidvali 11/20/2023

请再问一个问题。在第一个选项中，我想再添加一个表。桌子。事实上，在表中我还有一列。我们现在使用的，无论货币如何，我们都会得到金额的总和。因此，我想首先转换（计算）汇率，例如以美元为单位，然后将它们相加。示例值为 400，值为 EUR。我将从表格中获取 EUR 的汇率并进行 400 / 0.92 的计算并得到 434.8。如果是美元，请不要碰。你能帮忙吗？CurrencyRatetransactionscurrencyamountamountcurrencyCurrencyRateamountcurrency

0赞 MT0 11/20/2023

@Saidvali 在表格中使用 a，然后乘以或除以，如果需要，请使用表达式来检查货币是什么（或者表格中的美元兑美元汇率可能为 1）。JOINCurrencyRateCASE

上一个：通过 CONNECT 脚本命令创建的连接在未连接的情况下断开连接 - ORACLE

下一个：显示连续天数的交易数据

甲骨文 11g。7天内输出数据

Oracle 11g. Output data within seven days

评论

评论