通过不重复数学来优化 sql 查询

发布于 2024-08-31 14:38:55 字数 426 浏览 6 评论 0原文

这是 Oracle 中的查询，我试图从中删除多余的数学运算：

SELECT name,

     CASE 
       when nvl(num1,0) + nvl(num2,0) - nvl(num3,0) > 0
       THEN nvl(num1,0) + nvl(num2,0) - nvl(num3,0)
       ELSE 0
      END
      as result,
      .... from ....

How do I not重复上面的求和？

“结果”应包含上述表达式的 - 值 if > 0

                    -  0 if the value of expression is <= 0.

原文

Here is the query in Oracle I was trying to remove the redundant math operation from:

SELECT name,

     CASE 
       when nvl(num1,0) + nvl(num2,0) - nvl(num3,0) > 0
       THEN nvl(num1,0) + nvl(num2,0) - nvl(num3,0)
       ELSE 0
      END
      as result,
      .... from ....

How do I not repeat the summation above?

The 'result' should contain - value of the above expression if > 0

                    -  0 if the value of expression is <= 0.

分享到QQ

分享到微博

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

发布评论

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

闻呓 2024-09-07 14:38:55

在这种情况下，您可以将 CASE 表达式替换为：

GREATEST( nvl(num1,0) + nvl(num2,0) - nvl(num3,0), 0 )

OrbMan 的答案通常更有用。

In this case you could replace your CASE expression with:

GREATEST( nvl(num1,0) + nvl(num2,0) - nvl(num3,0), 0 )

OrbMan's answer is more generally useful though.

回复收藏 0 原文

银河中√捞星星 2024-09-07 14:38:55

使用派生表：

select name, nvl(CASE when sumall > 0 THEN sumall end, 0) as result
from (
    select nvl(num1,0) + nvl(num2,0) - nvl(num3,0) sumall, name
    from MyTable
) a

Use a derived table:

select name, nvl(CASE when sumall > 0 THEN sumall end, 0) as result
from (
    select nvl(num1,0) + nvl(num2,0) - nvl(num3,0) sumall, name
    from MyTable
) a

回复收藏 0 原文

星星的轨迹 2024-09-07 14:38:55

假设您出于性能原因想要摆脱数学计算，那么最好的方法（从 DBA 的角度来看）是创建另一个名为 result 的列，然后使用插入/更新触发器当行更改时，使用您想要的值自动填充它：

CASE 
   WHEN nvl(num1,0) + nvl(num2,0) - nvl(num3,0) > 0
   THEN nvl(num1,0) + nvl(num2,0) - nvl(num3,0)
   ELSE 0
END

这样，仅在需要时（在行创建/更改时）而不是在每次读取时进行计算。这可以分摊读取次数多于写入次数的数据库的操作成本（根据我的经验，这接近所有数据库）。

然后，您的查询变得简单且快得令人眼花缭乱：

select name, result from ...

此方法之所以有效，是因为：

绝大多数数据库的读取次数确实远远多于写入次数；并且
磁盘空间比 CPU grunt 便宜（将有关数据库性能的数据库问题数量与有关存储需求的数据库问题数量进行比较 - 前者远远超过后者）。
随着表变大，使用每行函数的数据库查询很少能很好地扩展。

当然，这一切都是假设 Oracle 有触发器。我自己就是一名 DB2 人员，但如果 Oracle 如此愚蠢以至于没有它们，我会感到惊讶。话又说回来，据我所知，也许 Oracle仍然无法区分 NULL 和空字符串，所以谁知道呢？ :-)

Assuming you want to get rid of the math for performance reasons, the best way to do this (from a DBA's point of view) is to create another column called result, then use an insert/update trigger to automatically populate it when a row changes, with the value you want:

CASE 
   WHEN nvl(num1,0) + nvl(num2,0) - nvl(num3,0) > 0
   THEN nvl(num1,0) + nvl(num2,0) - nvl(num3,0)
   ELSE 0
END

That way, the calculation is only done when required (on a row create/change) rather than on every single read. This amortises the cost of the operation on databases that are read more often than written (which, in my experience, is close to all of them).

Then your query becomes a simple and blindingly fast:

select name, result from ...

This method works because:

the vast majority of databases are indeed read far more often than they're written; and
disk space is cheaper than CPU grunt (compare the number of database questions regarding database performance against those regarding storage requirements - the former greatly outweigh the latter).
database queries with per-row functions rarely scale well as the tables get bigger.

This is all assuming Oracle has triggers of course. I'm a DB2 man myself but I'd be surprised if Oracle was so brain dead it didn't have them. Then again, for all I know, maybe Oracle still can't distinguish between NULL and an empty string, so who knows? :-)

回复收藏 0 原文

~没有更多了~