sql - 分析函数 SUM 返回具有相同值的窗口的平均值

Question

我试图编写一个查询，导致给定值的运行总和。但是，当应用 SUM 作为分析函数时，我得到了一个窗口内的平均值结果。

示例：考虑以下查询：

with tbl as 
(
select 'steve' "NAME", 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 90 val from dual UNION ALL
select 'peter' "NAME", 450 val from dual UNION ALL
select 'hary' "NAME", 2772 val from dual UNION ALL
select 'may' "NAME", 2227.5 val from dual UNION ALL
select 'tom' "NAME", 500 val from dual UNION ALL
select 'sia' "NAME", 20000 val from dual
)
select name, val,
        sum(val) over (order by name) running_sum
from tbl;

结果：

我真正想要的是

我使用 ROWNUM 得到的：

with tbl as 
(
select 'steve' "NAME", 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 90 val from dual UNION ALL
select 'peter' "NAME", 450 val from dual UNION ALL
select 'hary' "NAME", 2772 val from dual UNION ALL
select 'may' "NAME", 2227.5 val from dual UNION ALL
select 'tom' "NAME", 500 val from dual UNION ALL
select 'sia' "NAME", 20000 val from dual
)
select name, val,
        sum(val) over (order by rownum) running_sum
from tbl;

第一个结果中显示的 peter 的 running_sum 实际上是 peter 的总 running_sum 的平均值。分析函数正在考虑为 peter 提供一个窗口，因为我在窗口子句中包含了“NAME”。但是为什么查询会导致窗口的平均值而不是运行总和？

标签： sqloracleanalytic-functions

Answer 1

为什么查询导致窗口的平均值而不是运行总和？

这不是窗口的平均值。在您的rownum版本中，它显示的四个值是 6270、6540、6630 和 7080 - 平均为 6630。

这是运行总和，但可能与您预期的不太一样，并且显示输出的顺序有点模糊了您实际应用的逻辑。

您可以通过订购输出来查看您看到的数字来自哪里：

with tbl as 
(
select 'steve' "NAME", 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 90 val from dual UNION ALL
select 'peter' "NAME", 450 val from dual UNION ALL
select 'hary' "NAME", 2772 val from dual UNION ALL
select 'may' "NAME", 2227.5 val from dual UNION ALL
select 'tom' "NAME", 500 val from dual UNION ALL
select 'sia' "NAME", 20000 val from dual
)
select name, val,
        sum(val) over (order by name) running_sum
from tbl
order by name;

NAME         VAL RUNNING_SUM
----- ---------- -----------
hary        2772        2772
john        4000        6772
may       2227.5      8999.5
peter        450     10079.5
peter        270     10079.5
peter        270     10079.5
peter         90     10079.5
sia        20000     30079.5
steve       2000     32079.5
tom          500     32579.5

您可以从窗口子句评估它们的顺序看到运行总计现在是有意义的。的所有四个值peter都包含在这些行中的每一行的运行总数中 - 因为这就是 - 中的全部内容order by- 并且将 450+270+270+90=1080 的总数添加到前一个名称的总数 8999.5 中。

peter您可以通过包含基于行的窗口子句为每一行获取不同的值：

with tbl as 
(
select 'steve' "NAME", 2000 val from dual UNION ALL
select 'john' "NAME", 4000 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 270 val from dual UNION ALL
select 'peter' "NAME", 90 val from dual UNION ALL
select 'peter' "NAME", 450 val from dual UNION ALL
select 'hary' "NAME", 2772 val from dual UNION ALL
select 'may' "NAME", 2227.5 val from dual UNION ALL
select 'tom' "NAME", 500 val from dual UNION ALL
select 'sia' "NAME", 20000 val from dual
)
select name, val,
        sum(val) over (order by name
          rows between unbounded preceding and current row) running_sum
from tbl;

NAME         VAL RUNNING_SUM
----- ---------- -----------
hary        2772        2772
john        4000        6772
may       2227.5      8999.5
peter        450      9449.5
peter        270      9719.5
peter        270      9989.5
peter         90     10079.5
sia        20000     30079.5
steve       2000     32079.5
tom          500     32579.5

评估相同名称的行的顺序仍然不确定，因为该分析子句中没有关于如何打破关系的说明。

整个结果现在是隐式排序的（至少在今天，使用 CTE 以及我的版本和我的优化器的决定），这可能不是你想要的；但是，order by无论如何，如果订单对您很重要，无论它是什么，您都应该有一个明确的。