我正在寻找一个SQL查询,它可以确定一个人在没有吃饭的情况下最长的一段时间.理想情况下,输出看起来像
person periodstart periodend
每个人在哪里可以确定没有肉的最长时期
periodstart would be the time of the first non-meat meal
periodend would be the time of the first meat meal following.
下面的sql创建表和数据.
CREATE TABLE MEALS
(
PERSON VARCHAR2(20 BYTE),MEALTIME DATE,FOODTYPE VARCHAR2(20)
);
Insert into MEALS (PERSON,MEALTIME,FOODTYPE)
values ('Jane',to_date('04-JAN-15 06:09:09','DD-MON-RR HH24:MI:SS'),'fruit');
Insert into MEALS (PERSON,to_date('05-JAN-15 06:09:09','veg');
Insert into MEALS (PERSON,to_date('07-JAN-15 06:01:24','meat');
Insert into MEALS (PERSON,to_date('07-JAN-15 12:03:50',FOODTYPE)
values ('John',to_date('02-JAN-15 10:03:23',to_date('03-JAN-15 10:03:23',to_date('04-JAN-15 10:03:23',to_date('05-JAN-15 07:03:23',to_date('05-JAN-15 10:03:23',to_date('06-JAN-15 05:01:54',to_date('06-JAN-15 10:03:23',FOODTYPE)
values ('Mary',to_date('02-JAN-15 05:01:54',to_date('03-JAN-15 06:04:25',to_date('05-JAN-15 04:04:25',to_date('05-JAN-15 06:04:25',to_date('07-JAN-15 06:04:25','veg');
commit;
解决方法
这是一个间隙和岛屿问题,有各种方法来处理它.一种是使用
an analytic function effect/trick来查找每种类型的连续周期链：
select person,mealtime,foodtype,case when foodtype = 'meat' then 'Yes' else 'No' end as meat,dense_rank() over (partition by person,case when foodtype = 'meat' then 1 else 0 end order by mealtime)
- dense_rank() over (partition by person order by mealtime) as chain
from meals
order by person,mealtime;
‘链’假柱是基于这里的一个案例,因为你想要水果和蔬菜 – 或任何非肉类 – 处理相同.
然后,您可以使用它作为内部查询,从每个链中的第一餐开始查找每个肉类和非肉类时期的开始：
select person,meat,min(mealtime) as first_meal
from (
select person,case when foodtype = 'meat' then 1 else 0 end order by mealtime)
- dense_rank() over (partition by person order by mealtime) as chain
from meals
)
group by person,chain
order by person,min(mealtime);
PERSON MEAT FIRST_MEAL
-------------------- ---- ------------------
Jane No 04-JAN-15 06:09:09
Jane Yes 07-JAN-15 06:01:24
Jane No 07-JAN-15 12:03:50
John No 02-JAN-15 10:03:23
...
你希望这段时间能够覆盖第一顿非肉食到下一顿肉餐,所以你可以把它作为一个带有超前和滞后的内部查询来偷看任何一侧的行：对于一个蔬菜时期,你向前看,看看开始下一个肉食期;在肉食期间,你会看到他开始上一个蔬菜时期：
select person,case when meat = 'Yes' then lag(first_meal) over (partition by person
order by first_meal) else first_meal end as period_start,case when meat = 'No' then lead(first_meal) over (partition by person
order by first_meal) else first_meal end as period_end
from (
select person,min(mealtime) as first_meal
from (
select person,case when foodtype = 'meat' then 1 else 0 end order by mealtime)
- dense_rank() over (partition by person order by mealtime) as chain
from meals
)
group by person,chain
)
order by person,period_start;
PERSON MEAT PERIOD_START PERIOD_END
-------------------- ---- ------------------ ------------------
Jane No 04-JAN-15 06:09:09 07-JAN-15 06:01:24
Jane Yes 04-JAN-15 06:09:09 07-JAN-15 06:01:24
Jane No 07-JAN-15 12:03:50
John No 02-JAN-15 10:03:23 03-JAN-15 10:03:23
...
这有效地给了你重复,虽然我已经留下了’肉’标志,以使它在这一点上更清晰.假设您想忽略最新的开放期间,您只需要跳过这些并消除重复：
select person,period_start,period_end
from (
select person,case when meat = 'Yes' then lag(first_meal) over (partition by person
order by first_meal) else first_meal end as period_start,case when meat = 'No' then lead(first_meal) over (partition by person
order by first_meal) else first_meal end as period_end
from (
select person,min(mealtime) as first_meal
from (
select person,case when foodtype = 'meat' then 1 else 0 end order by mealtime)
- dense_rank() over (partition by person order by mealtime) as chain
from meals
)
group by person,chain
)
)
where meat = 'No'
and period_start is not null
and period_end is not null
order by person,period_start;
PERSON PERIOD_START PERIOD_END
-------------------- ------------------ ------------------
Jane 04-JAN-15 06:09:09 07-JAN-15 06:01:24
John 02-JAN-15 10:03:23 03-JAN-15 10:03:23
John 04-JAN-15 10:03:23 06-JAN-15 10:03:23
Mary 02-JAN-15 05:01:54 03-JAN-15 06:04:25
Mary 05-JAN-15 04:04:25 05-JAN-15 06:04:25
SQL Fiddle中间步骤全部完成.
姗姗来迟地意识到你只想要每个人最长的时间,你可以用另一层来获得：
select person,period_end,rank() over (partition by person order by period_end - period_start desc) as rnk
from (
...
)
where meat = 'No'
and period_start is not null
and period_end is not null
)
where rnk = 1
order by person,period_start;
PERSON PERIOD_START PERIOD_END
-------------------- ------------------ ------------------
Jane 04-JAN-15 06:09:09 07-JAN-15 06:01:24
John 04-JAN-15 10:03:23 06-JAN-15 10:03:23
Mary 02-JAN-15 05:01:54 03-JAN-15 06:04:25
