这是我正在使用的表格。表名为 phone_log
caller_id recipient_id call_start_time
1 2 2012-04-19 09:00:00
2 3 2012-04-19 17:00:00
1 2 2012-04-19 23:00:00
... ... ...
我需要找出谁在某一天给同一个人打了第一个和最后一个电话。我不知道从哪里开始。感谢对此的任何建议。
我想要的输出如下所示:
caller_id recipient_id call_start_time
1 2 2012-04-19
一种方法使用first_value()
:
select distinct caller_id, first_recipient_id
from (select pl.*,
first_value(recipient_id) over (partition by caller_id, date(call_start_time) order by call_start_time) as first_recipient_id,
first_value(recipient_id) over (partition by caller_id, date(call_start_time) order by call_start_time desc) as last_recipient_id
from phone_log pl
) pl
where first_recipient_id = last_recipient_id;
这使用诸如date()
之类的函数,该函数从日期/时间中提取日期。 日期/时间函数因数据库而异。