SQL:DB2 iSeries 的窗口聚合函数 [英] SQL: windowed aggregate functions for DB2 iSeries
问题描述
count
不是 iSeries 上 SQL DB2 行分区的有效聚合函数吗?
Is count
not a valid aggregation function for row partitions for SQL DB2 on the iSeries?
此查询有效:
select ROW_NUMBER() over (partition by COL1, COL2 order by COL3 asc)
from MyTable
而且这个查询给出了一个语法错误:
And this query gives a syntax error:
select COUNT(1) over (partition by COL1, COL2)
from MyTable
错误信息指向partition
这个词前的括号:
The error message is pointing at the parenthesis before the word partition
:
[消息 SQL0401] 令牌 ( 不是有效令牌.有效令牌的部分列表是 , FROM INTO.
[Message SQL0401] Token ( is not a valid token. A partial list of valid tokens is , FROM INTO.
我知道我可以重写查询以避免行分区,但我想知道为什么这不起作用.
I'm aware I can rewrite the query to avoid the row partition, but I'd like to know why this isn't working.
推荐答案
不,COUNT() 不一样 函数类型为 ROW_NUMBER().
如果你想要每个 (col1,col2) 的行数,那么你可以简单地使用
If you want the number of rows per (col1,col2) then you could simply use
select COL1, COL2, count(*)
from MyTable
group by col1, col2
这篇关于SQL:DB2 iSeries 的窗口聚合函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!