有一张表:dengji_hotel;
id (id号)
name (旅客姓名)
rz_date(入住时间)
hotel_name(旅馆名称)
hotel_room(房间号) 这张表有几百万的数据:
如何高效率的检索出:
在40分钟内,住在同一旅馆并同一房间的数据
下面可以统计同一天内,那40分钟内呢?怎么改?
select * from dengji_hotel where rowid in (
select rid from (
select count(id)over(partition by trunc(rz_date),hotel_name,hotel_room)as rank_id, rowid as rid from dengji_hotel
)
where rank_id >= 2
)
id (id号)
name (旅客姓名)
rz_date(入住时间)
hotel_name(旅馆名称)
hotel_room(房间号) 这张表有几百万的数据:
如何高效率的检索出:
在40分钟内,住在同一旅馆并同一房间的数据
下面可以统计同一天内,那40分钟内呢?怎么改?
select * from dengji_hotel where rowid in (
select rid from (
select count(id)over(partition by trunc(rz_date),hotel_name,hotel_room)as rank_id, rowid as rid from dengji_hotel
)
where rank_id >= 2
)
select rid from (
select count(id)over(partition by trunc(rz_date),hotel_name,hotel_room)as rank_id, rowid as rid from dengji_hotel where rz_date<sysdate-40/1440
)
where rank_id >= 2
)
sysdate就是坐标,你可以把它替换成你需要的时间点为坐标
,这两个时间间隔不超过40分钟,则满足要求;
假如一条记录rz_date为2006-08-11 20:16:25,另一个为2006-08-11 21:30:23
,这两个时间间隔超过40分钟,则不满足要求;
这个2006-08-11 20:16:25就是你的坐标
select rid from (
select count(id)over(partition by trunc(rz_date),hotel_name,hotel_room)as rank_id, rowid as rid from dengji_hotel and where rz_date>to_date(2006-08-11 20:16:25) rz_date<to_date(2006-08-11 20:16:25)+40/1440
)
where rank_id >= 2
)
select rid from (
select count(id)over(partition by trunc(rz_date),hotel_name,hotel_room)as rank_id, rowid as rid from dengji_hotel and where rz_date>to_date(2006-08-11 20:16:25) rz_date<to_date(2006-08-11 20:16:25)+40/1440
)
where rank_id >= 2
)
坐标不固定,可以使用通配符号&。比如select * from dengji_hotel where rowid in (
select rid from (
select count(id)over(partition by trunc(rz_date),hotel_name,hotel_room)as rank_id, rowid as rid from dengji_hotel and where rz_date>to_date(&1) rz_date<to_date(&1)+40/1440
)
where rank_id >= 2
)按照提示输入坐标时间即可
select rid from (
select count(id)over(partition by trunc(rz_date),hotel_name,hotel_room)as rank_id, rowid as rid from dengji_hotel and where rz_date>to_date(&1,'yyyymmdd hh24:mi:ss') rz_date<to_date(&1,'yyyymmdd hh24:mi:ss')+40/1440
)
where rank_id >= 2
)
2006-12-06 11:23:04
2006-12-06 11:26:01
2006-12-06 11:33:04
2006-12-06 18:59:09
2006-12-06 19:01:11即可
从12点15分开始的四十分钟内
从10点18分开始的四十分钟内
......
你可以说:从今天任意时间开始四十分钟内,别人如何定位?
select rid from (
select count(id)over(partition by trunc(rz_date),hotel_name,hotel_room)as rank_id, rowid as rid from dengji_hotel where sysdate-rz_date>=40
)
where rank_id >= 2
)这个是给出到目前时间(sysdate)为止,入住时间超过40分钟的记录。
至于别的时间,还是要给你参考坐标点。替换sysdate即可。
lz我只能理解你的意思到这一步了。
select t1.* from dengji_hotel t2
where exists (
select * from dengji_hotel t2
where t2.hotel_name = t1.hotel_name
and t2.hotel_room = t2.hotel_room
and ( (t2.rz_date > t1.rz_date - 40min)
or(t2.rz_date < t1.rz_date + 40min)
)