SELECT length , MIN(dt) dt , MAX(v1) v1 , MAX(v2) v2 , MAX(v3) v3 , MAX(v4) v4 , AVG(CASE WHEN rn <= 5 THEN v3 END) avg5_v3_asc , AVG(CASE WHEN cnt - rn < 5 THEN v3 END) avg5_v3_desc FROM (SELECT m.length, m.dt , m.v1, m.v2, m.v3, m.v4 , COUNT(CASE WHEN m.dt >= s.dt THEN 1 END) rn , COUNT(*) cnt FROM cams_data m , cams_data s WHERE m.vcode = 'TTest7' AND m.wdate = '20150625' AND m.vcode = s.vcode AND m.wdate = s.wdate AND m.length = s.length -- AND m.dt >= s.dt GROUP BY m.length, m.dt , m.v1, m.v2, m.v3, m.v4 ) a GROUP BY length ;
데이터가 2500개 정도 되면 몇분씩 걸리는데, 좀더 빠른 방법이 없을까요..
SELECT length , MIN(dt) dt , MAX(v1) v1 , MAX(v2) v2 , MAX(v3) v3 , MAX(v4) v4 , AVG(CASE WHEN rn1 <= 5 THEN v3 END) avg5_v3_asc , AVG(CASE WHEN rn2 <= 5 THEN v3 END) avg5_v3_desc FROM (SELECT length, dt , v1, v2, v3, v4 , @rn := CASE @len WHEN length THEN @rn + 1 ELSE 1 END rn1 , cnt - @rn + 1 rn2 , @len := length FROM (SELECT a.length, a.dt , a.v1, a.v2, a.v3, a.v4 , b.cnt FROM (SELECT length, dt , v1, v2, v3, v4 FROM cams_data WHERE vcode = 'TTest7' AND wdate = '20150625' ) a , (SELECT length , COUNT(*) cnt FROM cams_data WHERE vcode = 'TTest7' AND wdate = '20150625' GROUP BY length ) b , (SELECT @len := '', @rn := 0) c WHERE a.length = b.length ORDER BY length, dt ) a ) a GROUP BY length ;