带 sum 的窗口函数返回过大的数字

发布于 2025-01-12 10:40:21 字数 784 浏览 0 评论 0原文

我很困惑为什么一个简单的求和窗口函数返回以下输出，请注意“运行总计”值：

我的查询是：

select month,
       "total sales", 
       sum("total sales") over (order by "month number" asc) as "running total"
from
(
    select to_char(date, 'Month') as month,
           sum(sale_price) as "total sales",
           extract(MONTH from date) as "month number"
    from sales
    group by month, "month number"
) as sub_sales
order by "month number" asc;

我的输入数据如下：

我希望这样的结果是“运行总计”列返回 sale_price 的累积总和。

原文

I'm confused about why a simple sum window function is returning the below output, note the "running total" value:

My query is:

select month,
       "total sales", 
       sum("total sales") over (order by "month number" asc) as "running total"
from
(
    select to_char(date, 'Month') as month,
           sum(sale_price) as "total sales",
           extract(MONTH from date) as "month number"
    from sales
    group by month, "month number"
) as sub_sales
order by "month number" asc;

My input data is the following:

I would expect this to work out such that the "running total" column returns a cumulative sum of sale_price.

分享到QQ

分享到微博

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

发布评论

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

梦里的微风 2025-01-19 10:40:21

这解决了这个问题，基于 @AdrianKlaver 评论：

select month,
       "total sales", 
       sum("total sales") over (order by "month number" asc)::int as "running total"
from
(
    select to_char(date, 'Month') as month,
           sum(sale_price) as "total sales",
           extract(MONTH from date) as "month number"
    from sales
    group by month, "month number"
) as sub_sales
order by "month number" asc;

但不确定为什么运行总计会以这种方式格式化？

This solved the issue, based on @AdrianKlaver comment:

select month,
       "total sales", 
       sum("total sales") over (order by "month number" asc)::int as "running total"
from
(
    select to_char(date, 'Month') as month,
           sum(sale_price) as "total sales",
           extract(MONTH from date) as "month number"
    from sales
    group by month, "month number"
) as sub_sales
order by "month number" asc;

Not sure why the running total would be formatted that way, though?

回复收藏 0 原文

~没有更多了~