分组和划分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屋！