甲骨文RATIO_TO_REPORT和汇总

问题描述：

SELECT HOME_DEPT, LINECODE, SUM(TOTAL_HRS), 
     TO_CHAR ( 
     100*RATIO_TO_REPORT(SUM(TOTAL_HRS)) 
     OVER (PARTITION BY HOME_DEPT), 
     '990.00L', 'NLS_CURRENCY=%' 
     ) PCT_JOB 
     FROM TABLE 
     GROUP BY HOME_DEPT, LINECODE ORDER BY HOME_DEPT ASC, PCT_JOB DESC;

以上Oracle查询工作，并产生结果，例如：甲骨文RATIO_TO_REPORT和汇总

DEPT1 LINECODE1 100 50% 
DEPT1 LINECODE2 50 25% 
DEPT1 LINECODE3 50 25% 

DEPT2 LINECODE1 20 12.5% 
DEPT2 LINECODE2 20 12.5% 
DEPT2 LINECODE3 20 12.5% 
DEPT2 LINECODE4 20 12.5% 
DEPT2 LINECODE5 20 12.5% 
DEPT2 LINECODE6 20 12.5% 
DEPT2 LINECODE7 20 12.5% 
DEPT2 LINECODE8 20 12.5%

现在我想每个部门汇总得到的结果是这样的：

DEPT1 LINECODE1 100 50% 
DEPT1 LINECODE2 50 25% 
DEPT1 LINECODE3 50 25% 
DEPT1   200 100% <--- desired 

DEPT2 LINECODE1 20 12.5% 
DEPT2 LINECODE2 20 12.5% 
DEPT2 LINECODE3 20 12.5% 
DEPT2 LINECODE4 20 12.5% 
DEPT2 LINECODE5 20 12.5% 
DEPT2 LINECODE6 20 12.5% 
DEPT2 LINECODE7 20 12.5% 
DEPT2 LINECODE8 20 12.5% 
DEPT2   160 100.0% <--- desired

我有尝试了各种各样的东西，例如GROUPING SETS，但是我的PCT_JOB值是错误的。

我认为你需要，做了汇总外部查询到这个包起来。 – FrustratedWithFormsDesigner 2012-04-17 17:47:50

你是对的。我得到它的工作，但现在我想要下降的百分比，但在所有下降百分比后列出每个部门的汇总。但由于汇总是100％，因此首先列出。 – jeff 2012-04-17 18:15:08

我认为你可以做类似'... ORDER BY CASE当PCT = 100 THEN 1 ELSE 0 AS PCT END'不知道如果语法是100％，但我敢肯定它*可能有一个表达式在订单条款中。 – FrustratedWithFormsDesigner 2012-04-17 18:18:11

答

有一个功能GROUPING这是documented here。这个函数会告诉你给定的行是否是“超集合”行。然后您可以根据该行的值进行订购。

请注意，我对PCT_JOB列的定义有点混乱，因为我正在分组正常行和“超集合”行。

下面的SQL查询应该给你你需要的东西。

select 
    home_dept, 
    linecode, 
    total_hrs, 
    to_char(
    100 * ratio_to_report(total_hrs) over (partition by home_dept, linecode_group), 
    '990.00L', 'NLS_CURRENCY=%') as pct_job 
from (
    SELECT 
    HOME_DEPT, 
    LINECODE, 
    SUM(TOTAL_HRS) as total_hrs, 
    grouping(linecode) as linecode_group 
    FROM my_TABLE 
    GROUP BY grouping sets ((home_dept), (HOME_DEPT, LINECODE)) 
) 
ORDER BY HOME_DEPT ASC, linecode_group, pct_job desc

而且，我已经使用分组这里设置，但有可能只使用：

GROUP BY home_dept, rollup(LINECODE)

一个SQLFiddle is available这将让你看到一个例子。

谢谢，好吧，以便您的查询工作。 Mine（above）在应用FrustratedWithFormsDesigner对外部查询进行包装的建议后工作。但是这两个查询都没有按降序提供pct_job，而是最后汇总。请参阅上面我的问题的评论。我一直在玩的情况下，但我不能让它匹配'100.00％' – jeff 2012-04-17 21:39:42

好吧，我得到了ORDER BY CASE的工作。我不得不将TO_CHAR更改为TRUNC，以便WHEN子句处理一个数字而不是一个字符。 – jeff 2012-04-17 21:46:27

糟糕。忘了更新。这是正确的SQLFiddle页面，但没有在答案。现在修复。请不要在订单中使用CASE，而是使用GROUPING而不是CASE可能会导致问题，因为如果您对给定部门只有一行，因为订购可能是非确定性的 – 2012-04-17 22:00:16

答

这是一个不需要外部查询的答案。它是基于麦克·迈尔斯的答案，但现在行代码的分组的PARTITION BY子句中，也内ORDER BY子句指定：

SELECT 
    home_dept, 
    linecode, 
    SUM(total_hrs) AS total_hrs, 
    TO_CHAR(100 * RATIO_TO_REPORT (SUM(total_hrs)) 
      OVER (PARTITION BY home_dept, GROUPING(linecode)), 
      '990.00L', 'NLS_CURRENCY=%') AS pct_job 
FROM my_table 
GROUP BY GROUPING SETS ((home_dept), (home_dept, linecode)) 
ORDER BY home_dept, GROUPING(linecode), pct_job DESC

上面的代码使用GROUP BY GROUPING SETS但下面的GROUP BY也将工作：

GROUP BY home_dept, ROLLUP(linecode)

而且FYI：原来的问题希望linecodes通过他们的百分比降序排列，而不是由线路码值（即行代码名称）排序....但在给定的例子，结果“按百分比递减”看起来与“按名称递增”相同。如果有人想看到名升序linecodes无论百分比，可以通过线顺序更改为这样：

ORDER BY home_dept, linecode

谢谢。我会在不久的将来重新访问这个应用程序，然后可以测试您的查询。 – jeff 2014-08-05 20:47:41

甲骨文RATIO_TO_REPORT和汇总

相关推荐