问题背景:
stage表有app_id,stage_id,submission_time等数据项。要求对该表的指定app_id的所有stage_id ,按submission_time 从小到大排序,并且相邻的两项进行做差。
另一个描述如下:
对同一个app_id的所有stage_id,按submission_time排序,第一个stage_id的submission_interval=0;
第二个stage_id的submission_interval=(第二个stage_id的submission_time-第一个stage_id的submission_time),也就是,第二个stage_id到第一个stage_id的时间间隔。。。依次类推。
select a.app_id, a.stage_id, a.submission_time, count(*)
子句:(利用增加一项的方法,增加了rownum字段)
select a.app_id, a.stage_id, a.submission_time, count(*) as rownum
子句:(利用增加一项的方法,增加了rownum字段)
select a.app_id, a.stage_id, a.submission_time, count(*) as rownum
from stage a ,stage b where a.app_id="XXXXXXX" and b.app_id=a.app_id
and
a.submission_time > b.submission_time
group by a.app_id, a.stage_id , a.submission_time
说明:按submission_time排序,submission_time项是不相同的。
表a 和 表b(同一个表) 进行笛卡尔积,选择
a的每一项submission_time 大于 b的submission_time所有项分别有
0,1,2.。。。。。。。。。。。。
所以count(*) as rownum的序号可以作为submission_time的排序。
语法形式:
select * from A right join B on 条件 group by 项。
select B.submission_time-A.submission_time from
(select a.app_id, a.stage_id, a.submission_time, count(*) as rownum from stage a ,stage b where a.app_id="XXXXXXXXXX" and b.app_id=a.app_id and a.submission_time > b.submission_time group by a.app_id, a.stage_id , a.submission_time ) as A
right join
(select a.app_id, a.stage_id, a.submission_time, count(*) as rownum from stage a ,stage b where a.app_id="XXXXXXXXXX" and b.app_id=a.app_id and a.submission_time > b.submission_time group by a.app_id, a.stage_id , a.submission_time ) as B
on A.rownum+1=B.rownum
on A.rownum+1=B.rownum
group by B.rownum ;