如表
id appid date
1 30 2007-03-11...
2 30 2007-03-12...
3 30 2007-03-13...
4 31 2007-03-24...
5 31 2007-03-12...
6 31 2007-03-13... 要得到的结果是:
1 30 2007-03-11...
5 31 2007-03-12...
就是以 appid 为组中的时间最小的行.
id appid date
1 30 2007-03-11...
2 30 2007-03-12...
3 30 2007-03-13...
4 31 2007-03-24...
5 31 2007-03-12...
6 31 2007-03-13... 要得到的结果是:
1 30 2007-03-11...
5 31 2007-03-12...
就是以 appid 为组中的时间最小的行.
/*
id appid date
1 30 2007-03-11...
2 30 2007-03-12...
3 30 2007-03-13...
4 31 2007-03-24...
5 31 2007-03-12...
6 31 2007-03-13...
*/
输出结果
1 1 30 2007-3-11
2 4 31 2007-3-14
select
first_value(id) over(partition by appid order by date) as id,
first_value(appid) over(partition by appid order by date) as appid,
first_value(date) over(partition by appid order by date) as date
from 表
first_value(id) over(partition by appid order by date) as id,
first_value(date) over(partition by appid order by date) as date,
appid as appid
from 表
select * from test where (to_char(appid) || to_char(date_time,'yyyy mm dd')) in
( select min(to_char(appid) || to_char(date_time,'yyyy mm dd')) from test group by appid);