SQL查询：如何在Oracle中从顶部到该行检索最小值

发布于 2025-01-31 10:13:20 字数 1440 浏览 2 评论 0 原文

我需要帮助编写Oracle中的查询，以获取每行的最小值，将当前行金额与先前的最小值进行比较。

在另一个单词中，计算从顶部到该行的每行的最小值，最小功能的数据集是从第一行到当前行。

例如：检索每行的最小值（以前的，当前的）值，如下

等级	金额计算量	降低（以前，当前）
1	600	600
2	800	600
3	300	300
300	300	300
300	300	300
300	300 300	200 200 200
300 300 7	200	8 550
8	550	200

感谢提前灰

原文

I need help to write a query in Oracle to get minimum value for each row, comparing the current row amount with the previous minimum value.

In another word, calculate minimum value for each row from top to that row, dataset for minimum function is from the first row to the current row.

For example: retrieve Min(previous, current) value for each row as below

Rank	Amount	Calc Min (previous, current)
1	600	600
2	800	600
3	300	300
4	500	300
5	500	300
6	800	300
7	200	200
8	550	200

Thanks in Advance
Ash

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你的呼吸 2025-02-07 10:13:20

您正在通过来寻找分析函数。

select
  rank,
  amount,
  min(amount) over (order by rank) as min_amount_so_far
from mytable
order by rank;

You are looking for the analytic function MIN OVER.

select
  rank,
  amount,
  min(amount) over (order by rank) as min_amount_so_far
from mytable
order by rank;

回复收藏 0 原文

司马昭之心 2025-02-07 10:13:20

您也可以使用 match_regnize ：

SELECT rank, amount, min_amount
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY Rank
  MEASURES
    MIN(Amount) AS min_amount
  ALL ROWS PER MATCH
  PATTERN (^ any_row+ )
  DEFINE any_row AS 1 = 1
)

示例数据：输出：

CREATE TABLE table_name (Rank, Amount) AS
SELECT 1, 600 FROM DUAL UNION ALL
SELECT 2, 800 FROM DUAL UNION ALL
SELECT 3, 300 FROM DUAL UNION ALL
SELECT 4, 500 FROM DUAL UNION ALL
SELECT 5, 500 FROM DUAL UNION ALL
SELECT 6, 800 FROM DUAL UNION ALL
SELECT 7, 200 FROM DUAL UNION ALL
SELECT 8, 550 FROM DUAL;

输出：

等级量 min_Amount

1 600 600

2 800 600

3 300 300

4 500 300

5 500 300

6 800 300

7 200 200

8 550 200

在这里

You can also solve this using MATCH_RECOGNIZE:

SELECT rank, amount, min_amount
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY Rank
  MEASURES
    MIN(Amount) AS min_amount
  ALL ROWS PER MATCH
  PATTERN (^ any_row+ )
  DEFINE any_row AS 1 = 1
)

Which, for the sample data:

CREATE TABLE table_name (Rank, Amount) AS
SELECT 1, 600 FROM DUAL UNION ALL
SELECT 2, 800 FROM DUAL UNION ALL
SELECT 3, 300 FROM DUAL UNION ALL
SELECT 4, 500 FROM DUAL UNION ALL
SELECT 5, 500 FROM DUAL UNION ALL
SELECT 6, 800 FROM DUAL UNION ALL
SELECT 7, 200 FROM DUAL UNION ALL
SELECT 8, 550 FROM DUAL;

Outputs: