如何选择每组的前两行，并在一列中计算它们之间的差异

Question

我有一个这样的表

ID_WE   ID_B    ID_WO   RDATA   RSIZE
11111   22      1   1998-10-01  14
11111   22      2   1998-09-30  17
11111   23      1   1998-10-01  23
11112   22      1   1998-09-30  14

ID_WE、ID_B和ID_WO为主键。对于每一个组合id-we, id_b, ID_WO都很少。每个ID_WO都有大量的读取，读取信息在RDATA和RSIZE

我需要像那样获取表格

ID_WE   ID_B    ID_WO   DAYS    DIF
11111   22      1       1       0

DIF是给定ID_WO上两次读RSIZE的差值;DAYS是最后两次读取间隔的天数

它可能需要一些group by和max(rdata)来计算天数和大小的差异。我真搞不懂怎么得到那样的结果。我将感谢任何提示如何得到所需的结果。

Answer 1

试试这个

SELECT MIN(ID_WE) ID_WE,ID_B,ID_WO,DAYS,DIF
FROM
(
     SELECT MIN(ID_WE) ID_WE,MIN(ID_B) ID_B,MIN(ID_WO) ID_WO, 
            MAX(RDATA)-Min(RDATA) DAYS,MAX(RSIZE)-Min(RSIZE) DIF   
     FROM TABLE1
     GROUP BY ID_WE,ID_B
)AS G
GROUP BY ID_WE

Answer 2

代替TABLE1，我建立了一些东西来获取最后两个元素:

SELECT MIN(ID_WE) ID_WE,ID_B,ID_WO,DAYS,DIF
FROM
 (
 SELECT MIN(d.ID_WE) ID_WE,MIN(d.ID_B) ID_B,MIN(d.ID_WO) ID_WO, 
        MAX(d.rdata)-Min(d.rdata) DAYS,MAX(d.RSIZE)-Min(d.RSIZE) DIF   
 FROM 
     (  select t.id_we, t.id_b, t.id_wo, t.max from 
       (( select b.id_we, b.id_b, b.id_wo, max(b.rdata) as max
            from test b
            where b.rdata NOT IN
                (select max(a.rdata)
                from test a
                group by a.id_we, a.id_b, a.id_wo
                )
            group by b.id_we, b.id_b, b.id_wo
        ) union
        (
          select e.id_we, e.id_b, e.id_wo, max(e.rdata)
          from test e
          group by e.id_we, e.id_b, e.id_wo
        )  
       ) t
        ) t4
        join test d on t4.id_we = d.id_we and d.id_b = t4.id_b and d.id_wo = t4.id_wo and d.rdata = t4.max
   GROUP BY t4.ID_WE,t4.ID_B
)AS G
GROUP BY ID_WE;

Answer 3

感谢大家的评论和回答，让我走上了正确的思考轨道。我终于弄明白了。在Baron Schwartz博客的帮助下(如果有人需要在SQL中选择每组的第一行/最小行/最大行，这是有用的资源)。

我得到最大和第二大的RDATA，并在查询中添加相应的RSIZE的数据，最大(FOO):

select oo.id_wej,oo.id_ob,oo.id_wo, oo.odata,oo.od
from (select id_wej,id_ob,id_wo,odata from odczyty
where odata = (select max(odata) from odczyty o where o.id_wej=odczyty.id_wej and o.id_ob=odczyty.id_ob and o.id_wo=odczyty.id_wo)) as x
inner join odczyty oo on oo.id_wej=x.id_wej and oo.id_ob=x.id_ob and oo.id_wo=x.id_wo and oo.odata=x.odata
order by id_wej,id_ob,id_wo)as ok
on od.id_wej=ok.id_wej and od.id_ob=ok.id_ob and od.id_wo=ok.id_wo

和第二大(BAR):

select o.id_we,o.id_b,o.id_wo, o.rdata,o.rsize
from (select id_we,id_b,id_wo,rdata from odczyty
where rdata =(select max(rdata)
from odczyty o2
where o2.id_we=odczyty.id_we and o2.id_b=odczyty.id_b
and o2.id_wo=odczyty.id_wo and
rdata <(select max(rdata) from odczyty o3
where o3.id_we=o2.id_we and
o3.id_b=o2.id_b and o3.id_wo=o2.id_wo))) as x
inner join odczyty o on o.id_we=x.id_we and o.id_b=x.id_b and o.id_wo=x.id_wo and o.rdata=x.rdata

使用这两个查询，我做了这样的:

   select od.id_wej,od.id_ob,od.id_wo, ok.odata-od.odata as days,ok.od-od.od as dif
from(BAR)as od  inner join
(FOO)as ok
on od.id_wej=ok.id_wej and od.id_ob=ok.id_ob and od.id_wo=ok.id_wo
order by id_wej, id_ob, id_wo

为了可读性，我用FOO和BAR替换了插入的查询。