在Oracle SQL中使用OVER(按...进行分区)时如何计算总计 [英] How to calculate Grand Totals when using OVER (partition by ...) using Oracle SQL

问题描述

我希望那里的Oracle SQL专家可以对此提供帮助.我有一个包含以下各列的视图:

I am hoping the Oracle SQL experts out there can help with this. I have a view that has the following columns:

MONTH (DATE)
JOB_GROUP (VARCHAR2)
PEOPLE (NUMBER)

我的视图当前如下所示:

My view currently looks like this:

SELECT
DISTINCT JOB_GROUP,
SUM(PEOPLE) OVER (PARTITION BY MONTH || JOB_GROUP) AS CURRENT_PEOPLE
SUM(PEOPLE) OVER (PARTITION BY MONTH || JOB_GROUP) / '100' AS RATIO
FROM MY_VIEW
WHERE MONTH = '01-DEC-2013'
ORDER BY RATIO DESC

"100"来自我顶点页面上的一个隐藏项目，该项目拉出了当月的Worker1，但是对于本示例，我只是使用"100". 查询结果出来像这样.

The '100' comes from a hidden item on my apex page that pulls out the current month's Worker1, but for this example, I am just using the '100'. The results of the query come out something like this.

JOB_GROUP CURRENT_PEOPLE RATIO 
WORKER1       100        1.0
WORKER2       80         0.8
WORKER3       50         0.5

我要查找的GRAND总计是查询结果底部的CURRENT PEOPLE AND RATIO.我将其放入APEX Classic报告中.在此示例中将为230和2.3.由于我使用的是OVER(分区)方法，因此我无法使用任何类型的GROUP BY.

The GRAND totals I am looking for are for CURRENT PEOPLE AND RATIO at the bottom of the query result. I will be putting this into an APEX Classic report. In this example would be 230 and 2.3. Since I am using the OVER (partition) method, I have not been able to use any sort of GROUP BY.

感谢您可能有的任何建议..谢谢

Appreciate any advice you may have..thank you

推荐答案

您不需要解析函数.您可以通过常规聚合来做到这一点:

You don't need analytic functions. You can do this with regular aggregation:

SELECT JOB_GROUP,
       SUM(PEOPLE) AS CURRENT_PEOPLE
       SUM(PEOPLE) / '100' AS RATIO
FROM MY_VIEW
WHERE MONTH = '01-DEC-2013'
GROUP BY ROLLUP (OB_GROUP)
ORDER BY RATIO DESC;

group by中不需要月份，因为您只选择一个月.

The month isn't needed in the group by, because you are selecting only one month.

这篇关于在Oracle SQL中使用OVER(按...进行分区)时如何计算总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！