5개 연속 행에 대한 연산 쿼리

이번에도 하나의 문제에 대한 다양한 형태의 쿼리들입니다.

 

다음과 같은 형태의 입력 데이터가 있을 때,

 

다음과 같은 결과를 얻으려고 합니다.

 

 

자.. 좋은 방법을 생각해 보시고, 다음의 여러 사람들의 답을 살펴보시기 바랍니다.

 

테스트용 데이터 생성하기 

USE TEMPDB GO   IF EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = 'T' AND type = 'U')         DROP TABLE T GO     CREATE TABLE T (     id  int primary key,     V1  int,     V2  int ) INSERT INTO T SELECT 1, 50, 20 UNION ALL SELECT 2, 60, 30 UNION ALL SELECT 3, 70, 40 UNION ALL SELECT 4, 45, 25 UNION ALL SELECT 5, 55, 35 UNION ALL SELECT 6, 65, 15 UNION ALL SELECT 7, 75, 35 UNION ALL SELECT 8, 90, 60 UNION ALL SELECT 9, 45, 32 GO   SELECT * FROM T /* id      V1      V2 ----------- 1       50      20 2       60      30 3       70      40 4       45      25 5       55      35 6       65      15 7       75      35 8       90      60 9       45      32 */

 

 

 

[방법 1]

-------------------------------------------------------------------------------------------- --Louis -------------------------------------------------------------------------------------------- SELECT T.id, (SELECT MAX(v1) FROM T AS t2                 WHERE T.id BETWEEN t2.id -2 AND t2.id + 2                 HAVING COUNT(*) = 5)              , (SELECT MIN(v2) FROM T AS t2                 WHERE T.id BETWEEN t2.id -2 AND t2.id + 2                 HAVING COUNT(*) = 5) FROM   T GROUP BY T.id GO

 

 

[방법 2]

-------------------------------------------------------------------------------------------- --Steve Join -------------------------------------------------------------------------------------------- SELECT   T1.id,   CASE WHEN COUNT(*) = 5 THEN MAX(T2.V1) END AS V1,   CASE WHEN COUNT(*) = 5 THEN MIN(T2.V2) END AS V2 FROM T AS T1 JOIN T AS T2 ON T2.id BETWEEN T1.id - 2 AND T1.id + 2 GROUP BY T1.id GO

 

 

[방법 3]

-------------------------------------------------------------------------------------------- --Steve Cross Join -------------------------------------------------------------------------------------------- SELECT   MIN(CASE WHEN Five.N = 0 THEN id END) AS id,   CASE WHEN COUNT(*) = 5 THEN MAX(V1) END AS V1,   CASE WHEN COUNT(*) = 5 THEN MIN(V2) END AS V2 FROM T CROSS JOIN (   SELECT -2 AS N UNION ALL SELECT -1 UNION ALL SELECT 0   UNION ALL SELECT 1 UNION ALL SELECT 2 ) AS Five GROUP BY id+N HAVING COUNT(CASE WHEN Five.N = 0 THEN 1 END) > 0 GO

 

 

더 좋은 방법 있으시면 답글~^^


-->

select a.id,b.v1,b.v2
from t a
cross apply
(
select case when count(*) = 5 then max(V1 ) end as v1
,case when count(*) = 5 then min( V2) end as v2
from t
where id between a.id - 2 and a.id + 2
) b