Oracle SQL: sum( case when then amount else 0 END) OVER (partition by...) = 无法得到正确的 GROUP BY 语句

发布于 2024-12-02 09:15:23 字数 292 浏览 1 评论 0原文

我正在尝试选择几个不同的总和，其中之一是OVER（按column_also_in_select_plan 分区）。

但是，我似乎永远无法正确使用 GROUP BY 语句。

示例：

Select 1, 2, 3, sum(4) over (partition by 3), sum(case when 6 = etc...) 
FROM table
Where filters
GROUP BY ?

感谢您的任何提示:)

原文

I'm trying to select several different sums, one of them being OVER (Partition by column_also_in_select_plan).

However I cannot seem to ever be able to get the GROUP BY statement right.

Example:

Select 1, 2, 3, sum(4) over (partition by 3), sum(case when 6 = etc...) 
FROM table
Where filters
GROUP BY ?

Thanks for any tips :)

分享到QQ

分享到微博

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

发布评论

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

滥情哥ㄟ 2024-12-09 09:15:23

同时使用窗口函数进行聚合并没有多大意义，所以你感到困惑我并不感到惊讶。在上面的示例中，您可能希望将窗口移动到外部查询，即：

select 1, 2, 3, sum(sum4) over(partition by 3), ...
from (
      select 1, 2, 3, sum(4) as sum4
      from table
      where filters
      group by 1, 2, 3
) x

It doesn't really make much sense to be doing aggregation and using window functions at the same time, so I'm not surprised you're confused. In the above example, you probably want to move the windowing to an outer query, that is:

select 1, 2, 3, sum(sum4) over(partition by 3), ...
from (
      select 1, 2, 3, sum(4) as sum4
      from table
      where filters
      group by 1, 2, 3
) x

回复收藏 0 原文

~没有更多了~