--先找到档期
select * from hv_item_info a where a.report_begdate > '2016-07-28' and a.report_begdate < '2016-07-29';
--找轮次
select * from media_item_wheel_info a where a.item_id = 46088 and a.rule_id = 0215 order by a.begin_date;
--按键情况
select * from media_user_dial_info a where a.item_id = 46088 and a.item_wheel_id in ('74419');
--中奖人数
select * from media_prizerl_info a where a.item_id = 46088 and a.item_wheel_id in ('74419');
--开始前在线人数
select * from (
select t.callingnbr,
t.callednbr,
t.flag,
lead(t.flag, 1, null) over(partition by t.callingnbr, t.callednbr order by t.logdate) nextflag,
t.logdate,
lead(t.logdate, 1, null) over(partition by t.callingnbr, t.callednbr order by t.logdate)
from MEDIA_USER_ONLINE_OFFLINE_LOG t
where t.callednbr in ('12590484666', '12590484667')
and t.flag <> 'DD'
and t.logdate >= to_Date('20160728105000', 'yyyymmddhh24miss')
and t.logdate <= to_Date('20160728113739', 'yyyymmddhh24miss')) a
where a.flag = 'on' and nextflag is null;
--结束时的在线人数
select * from (
select t.callingnbr,
t.callednbr,
t.flag,
lead(t.flag, 1, null) over(partition by t.callingnbr, t.callednbr order by t.logdate) nextflag,
t.logdate,
lead(t.logdate, 1, null) over(partition by t.callingnbr, t.callednbr order by t.logdate)
from MEDIA_USER_ONLINE_OFFLINE_LOG t
where t.callednbr in ('12590484666', '12590484667')
and t.flag <> 'DD'
and t.logdate >= to_Date('20160728105000', 'yyyymmddhh24miss')
and t.logdate <= to_Date('20160728113749', 'yyyymmddhh24miss')) a
where a.flag = 'on' and nextflag is null;