sql - 如何构造子查询/CTE 以替换嵌套聚合函数？

Question

我试图找出“amt”值中有多少百分比属于每个“百分比”桶。

示例数据:

amt | pct
5   | .5
10  | .6
15  |  1
20  | .8

期望的结果:

pctAMT | PCTrange
.3     | <65
.4     | 75-84
.3     | 100+

我试过使用嵌套聚合函数，也尝试过将总计用作单个值。但是，我遇到了错误。我认为我必须将查询重新构造为 CTE 或子查询，但我不确定如何进行。

SELECT SUM(amt)/(SUM(amt) OVER()) AS pctAMT,
CASE WHEN percent < .65 then '  <65'
            WHEN .65 <= percent AND percent <.75  then '65-74'
            WHEN .75 <= percent AND percent <.85  then '75-84'
            WHEN .85 <= percent AND percent <.95  then '85-94'
            WHEN .95 <= percent AND percent <1.00 then '95-99'
            WHEN .100 <= percent  then ' 100+'
        END PCTrange
FROM table
WHERE date = TO_DATE('2016-04-01','YYYY-MM-DD')
GROUP BY PCTrange
ORDER BY PCTrange;

ERROR [HY000] ERROR:  Attribute LN_HIST.LN_FNCL_RPTG_UPB must be GROUPed or used in an aggregate function

Answer 1

我希望是这样的:

SELECT (CASE WHEN percent < 0.65 then '  <65'
             WHEN percent < 0.75 then '65-74'
             WHEN percent < 0.85 then '75-84'
             WHEN percent < 0.95 then '85-94'
             WHEN percent < 1.00 then '95-99'
             ELSE ' 100+'
        END) as PCTrange,
       SUM(amt)/ SUM(SUM(amt)) OVER () AS pctAMT
FROM table
WHERE date = '2016-04-01'
GROUP BY PCTrange
ORDER BY PCTrange;

重要的变化是 SUM(SUM()) —— 相加除法。

我还简化了 CASE 表达式。这些子句按顺序进行评估，因此您可以利用它。日期比较也更简单。