如何计算Oracle数据库的增加/减少百分比？

发布于 2024-12-10 00:09:51 字数 404 浏览 2 评论 0原文

我有一个包含 3 行值的表：

Row1 : 5
Row2 : 8
Row3 : 9

我希望计算 Row2 与 Row1、Row3 与 Row2 相比的增加或减少百分比，所以我将拥有此表：计算％的公式为：[Row(n+1) - Row(n)] / Row(2)

     Value   Percentage 
Row1 : 5         -
Row2 : 8        60% (increase compared to Row1) | (8-5)/5
Row3 : 9        12.5%(increase compared to Row2)| (9-8)/8

请建议方法或解决方案。

谢谢大家。

原文

I have a table with 3 rows contain values :

Row1 : 5
Row2 : 8
Row3 : 9

I wish to calculate the percentage of increase or decrease of : Row2 compared to Row1 , Row3 to Row2 so i would have this table :
The formula to calculate % is : [Row(n+1) - Row(n)] / Row(2)

     Value   Percentage 
Row1 : 5         -
Row2 : 8        60% (increase compared to Row1) | (8-5)/5
Row3 : 9        12.5%(increase compared to Row2)| (9-8)/8

Please suggest the way or a solution .

Thanks all.

分享到QQ

分享到微博

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

发布评论

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

靑春怀旧 2024-12-17 00:09:51

您需要使用 Oracle 分析函数 LAG：

您的查询将类似于：

SELECT ((value - lagv)/lagv) * 100
  FROM (
        SELECT value,
               LAG(value) OVER(ORDER BY <ordering_column>) as lagv
          FROM table1
);

为了测试我运行：

WITH t AS (SELECT 1 as rnum,
                  5 AS value FROM DUAL
           UNION
           SELECT 2 as rnum,
                  8 AS value FROM DUAL
           UNION
           SELECT 3 as rnum,
                  9 AS value FROM DUAL
          ) 
SELECT ((value - lagv)/lagv) * 100 AS Percentage
  FROM (
        SELECT value,
               LAG(value) OVER(ORDER BY rnum) as lagv
          FROM t
);

它返回：

Row  Percentage
  1           
  2          60
  3        12.5

由于您需要为 LAG 函数指定顺序，所以我创建了 rnum 列，我假设您的表有某种排序您可以使用排序列（否则您如何知道要比较哪些行？）。

希望这会有所帮助...

编辑：此链接对于了解 Oracle 的 LEAD 和 LAG 功能非常有用。 http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php

You need to use the Oracle Analytical function LAG:

Your query will look something like:

SELECT ((value - lagv)/lagv) * 100
  FROM (
        SELECT value,
               LAG(value) OVER(ORDER BY <ordering_column>) as lagv
          FROM table1
);

To test I ran:

WITH t AS (SELECT 1 as rnum,
                  5 AS value FROM DUAL
           UNION
           SELECT 2 as rnum,
                  8 AS value FROM DUAL
           UNION
           SELECT 3 as rnum,
                  9 AS value FROM DUAL
          ) 
SELECT ((value - lagv)/lagv) * 100 AS Percentage
  FROM (
        SELECT value,
               LAG(value) OVER(ORDER BY rnum) as lagv
          FROM t
);

It returned:

Row  Percentage
  1           
  2          60
  3        12.5

As you need to specify an order for the LAG function I have created the rnum column, I assume your table has some sort of ordering column you can use, (otherwise how do you know which rows to compare?).

Hope this helps...

EDIT: This link is pretty useful for learning about Oracle's LEAD and LAG functions. http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php

回复收藏 0 原文

脸赞 2024-12-17 00:09:51

SET @previousRow=0;

SELECT currentRowValue, 
       @previousRowValue:= (SELECT COALESCE(currentRowValue, COUNT(0)) 
                        FROM tableName 
                        WHERE id< t.id 
                        LIMIT 1) AS previousRowValue,
COALESCE((currentRowValue - @previousRowValue) /  @previousRowValue, 0) AS Percentage 

FROM tableName t;

SET @previousRow=0;

SELECT currentRowValue, 
       @previousRowValue:= (SELECT COALESCE(currentRowValue, COUNT(0)) 
                        FROM tableName 
                        WHERE id< t.id 
                        LIMIT 1) AS previousRowValue,
COALESCE((currentRowValue - @previousRowValue) /  @previousRowValue, 0) AS Percentage 

FROM tableName t;

回复收藏 0 原文

~没有更多了~