informix 中有没有row_num()over() 这样的函数啊
想问下 informix 中有没有row_num()over(partition by ) 这样的函数啊
作用是：分组排序建序号
类似的函数也行 ，或者给我个思路 怎么实现这样的函数 我自己写也可以，谢谢
查看其它 2 个回答光洋山的回答
Select date, store_name, sum(dollars) as sales_dols,
rank () over (partition by date order by sales_dols desc) as date_rank
from period, store, sales
where period.perkey = sales.perkey
and store.storekey = sales.storekey
and state = “CA”
group by date, store_name
order by date;
DATE STORE_NAME SALES_DOL DATE_RANK
2012-01-02 Beaches Brew 785.55 1
2012-01-02 Roasters, Los Gatos 762.25 2
2012-01-02 Los Gatos Roasting Company 636.25 3
2012-01-02 Cupertino Coffee Supply 634.00 4
2012-01-02 Instant Coffee 457.75 5
2012-01-03 Instant Coffee 713.75 1
2012-01-03 Los Gatos Roasting Company 633.25 2
….. …. …. ..
2012-01-04 Instant Coffee 1031.50 1
2012-01-04 Los Gatos Roasting Company 613.95 2
…… …. …. ..
Select row_number () over () as row,
dimension,
measure,
avg (measure) over (partition by dimension
order by measure
rows between current row and 2 following)
as OLAP_AVG
from ….
聚集函数
AVG Function
Overview of COUNT Functions
COUNT(*) function
COUNT DISTINCT and COUNT UNIQUE functions
COUNT column Function
MAX Function
MIN Function
SUM Function
RANGE Function
STDEV Function
VARIANCE Function
Error Checking in ESQL/C
Summary of Aggregate Function Behavior
User-Defined Aggregates
Aggregate expressions in grid queries
OLAP 窗口函数表达式
OLAP numbering function expression
OLAP 排名函数表达式
LAG and LEAD functions
RANK function
DENSE_RANK function
PERCENT_RANK function
CUME_DIST function
NTILE function
OLAP 聚集函数表达式
FIRST_VALUE function
LAST_VALUE function
RATIO_TO_REPORT function
OLAP window aggregate functions
OVER clause for OLAP window expressions
如何生成 rowid,row number ,row_number
select (row_number() over (partition by 1 order by tabname))::int rowid,
tabname, tabid
from systables
where tabid>1;
rowid tabname tabid
1 GL_COLLAT 90
2 GL_CTYPE 91
3 VERSION 99
4 bson_table 104
5 sysaggrega 46
6 sysams 40
7 sysattrtyp 31
8 sysautoloc 64
9 sysblobs 20
10 syscasts 36
11 syschecks 13
12 syscolattr 34
13 syscolauth 5
14 syscoldepe 15
15 syscolumns 2
16 sysconstra 11
17 sysdefault 14
select (row_number() over (partition by 1 order by tabname,tabid))::int rowid,
tabname, tabid
from systables
where tabid>1;
Select date, store_name, sum(dollars) as sales_dols,
rank () over (partition by date order by sales_dols desc) as date_rank
from period, store, sales
where period.perkey = sales.perkey
and store.storekey = sales.storekey
and state = “CA”
group by date, store_name
order by date;