分组和划分Netezza [英] Grouping and Dividing Netezza
问题描述
我有以下查询,该查询将sum(rev)列中的每一行除以该列的总和.
I have the following query that divides each row in the sum(rev) column by the sum of the column.
在下面的示例中sum(rev)列的总和为23193.divide列的派生为:sum(rev)/sum(rev)中的row
For the following example the sum of sum(rev) column is 23193.The Divide column is derived by : row in sum(rev)/sum(rev)
select date,id,sum(rev),
NULLIF(rev,0) / sum(rev) over() as Divide
from test
where month(date) = 11
and year(date) = 2012
and day(date) = 02
and id = 'Client1'
group by date,id,rev
having sum(rev) <> 0
order by date
date id sum(rev) Divide
2012-11-02 00:00:00 Client1 1562.00 0.067348
2012-11-02 00:00:00 Client1 1.00 0.000043
2012-11-02 00:00:00 Client1 4689.00 0.202173
2012-11-02 00:00:00 Client1 267.00 0.011512
2012-11-02 00:00:00 Client1 16674.00 0.718924
有2个问题
1.)当注释了day(date)条件时,检索到的值是错误的.它在Divide Computation中没有给出正确的值
1.) When the day(date) condition is commented the values retrieved are wrong.It does not give the correct value in the Divide Computation
date sum(rev) Divide
2012-11-02 00:00:00 1.00 0.000002
2012-11-02 00:00:00 267.00 0.000412
2012-11-02 00:00:00 1562.00 0.002412
2012-11-02 00:00:00 4689.00 0.007241
2012-11-02 00:00:00 16674.00 0.025749
2.)我想按日期分组.由于我们只有2012年2月11日的记录,所以每天必须只有一行记录
2.) I want to group by date.So since we have records only for 2-11-2012 there must be only one line of record for each day
请帮助解决这两个错误
引用:通过除以和来查找列值列
推荐答案
如果要按date
分组,然后将每日总数除以总计,则可以这样操作:
If you want to group by date
and divide the daily totals by the grand total, you could do it like this:
SELECT
date,
SUM(rev) AS total,
SUM(rev) / SUM(SUM(rev)) OVER () AS portion
FROM test
GROUP BY
date
;
也就是说,SUM() OVER ()
的参数应该是有效的表达式,并且rev
在此GROUP BY查询中不是有效的表达式,因为rev
未包含在GROUP BY中.但是您可以(并且应该)使用SUM(rev)
作为参数,它将按预期工作.
That is, the argument of SUM() OVER ()
should be a valid expression, and rev
isn't a valid expression in this GROUP BY query, because rev
isn't included in GROUP BY. But you can (and should) use SUM(rev)
as an argument, and it will work as expected.
如果要为单独的客户端获得单独的结果,请在窗口SUM()的GROUP BY子句中添加id
,在窗口SUM()的OVER子句中添加PARTITION BY id
,如下所示:
If you want separate results for separate clients, add id
to the GROUP BY clause and add PARTITION BY id
to the OVER clause of the window SUM(), like this:
SELECT
date,
id,
SUM(rev) AS total,
SUM(rev) / SUM(SUM(rev)) OVER (PARTITION BY id) AS portion
FROM test
GROUP BY
date,
id
;
详细了解OVER子句在手册中.
Read more about the OVER clause in the manual.
这篇关于分组和划分Netezza的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!