SQL 질문과 답변 게시판
![[레벨:9]](http://www.sqler.com/modules/point/icons/warrock_17x17/9.gif "포인트:171point (23%), 레벨:9/100"){kind=icon}
항상비기너|
logtime |
A |
B |
|
20100401000000 |
1 |
0 |
|
20100401000100 |
1 |
0 |
|
20100401000200 |
1 |
0 |
|
20100401000300 |
1 |
0 |
|
20100401000400 |
1 |
0 |
|
20100401000500 |
1 |
0 |
|
20100401000600 |
1 |
0 |
|
20100401000700 |
0 |
1 |
|
20100401000800 |
0 |
1 |
|
20100401000900 |
0 |
1 |
|
20100401001000 |
0 |
1 |
|
20100401001100 |
0 |
1 |
|
20100401001200 |
1 |
0 |
|
20100401001300 |
1 |
0 |
|
20100401001400 |
1 |
0 |
|
20100401001500 |
1 |
0 |
|
20100401001600 |
1 |
0 |
|
20100401001700 |
0 |
1 |
|
20100401001800 |
0 |
1 |
사용 DB는 SQL 2000입니다 .
1분마다에 Data가 쌓이고 있고,
A, B필드에는 가동(1), 비가동(0)을 나타내고 있다.
A, B는 동시에 1이 되지 않습니다.
일정한 시간 사이에 있는 Data에서 가동 비가동 변경점 시간을 구하고 싶습니다.
결과물은 아래와 같이 나타내고 싶습니다 .
|
가동 |
starttime |
endtime |
|
A |
20100401000000 |
20100401000600 |
|
B |
20100401000700 |
20100401001100 |
|
A |
20100401001200 |
20100401001600 |
|
B |
20100401001700 |
20100401001800 |
고수님들의 조언을 부탁드립니다.
감사합니다.
![[레벨:85]](http://www.sqler.com/modules/point/icons/warrock_17x17/85.gif "포인트:14501point (14%), 레벨:85/100"){kind=icon}
건우아빠
항상비기너님
혹 2000이신지요 .... 2000이시면 row_number()를 쓰시지 못하기 때문에 다른 방법으로 순서를 만드셔야 합니다.
![[레벨:30]](http://www.sqler.com/modules/point/icons/warrock_17x17/30.gif "포인트:1858point (47%), 레벨:30/100"){kind=icon}
원론IF OBJECT_ID('tbl') IS NOT NULL
DROP TABLE tbl
GO
CREATE TABLE tbl
(
logtime CHAR(14)
, A TINYINT
, B TINYINT
)
GO
INSERT INTO tbl
SELECT '20100401000000', 1, 0 UNION ALL
SELECT '20100401000100', 1, 0 UNION ALL
SELECT '20100401000200', 1, 0 UNION ALL
SELECT '20100401000300', 1, 0 UNION ALL
SELECT '20100401000400', 1, 0 UNION ALL
SELECT '20100401000500', 1, 0 UNION ALL
SELECT '20100401000600', 1, 0 UNION ALL
SELECT '20100401000700', 0, 1 UNION ALL
SELECT '20100401000800', 0, 1 UNION ALL
SELECT '20100401000900', 0, 1 UNION ALL
SELECT '20100401001000', 0, 1 UNION ALL
SELECT '20100401001100', 0, 1 UNION ALL
SELECT '20100401001200', 1, 0 UNION ALL
SELECT '20100401001300', 1, 0 UNION ALL
SELECT '20100401001400', 1, 0 UNION ALL
SELECT '20100401001500', 1, 0 UNION ALL
SELECT '20100401001600', 1, 0 UNION ALL
SELECT '20100401001700', 0, 1 UNION ALL
SELECT '20100401001800', 0, 1
GO
SELECT CASE a WHEN 1 THEN 'A' ELSE 'B' END AS 가동, MIN(logtime) AS Starttime, MAX(logtime) AS endtime
FROM (
SELECT a.a, a.b, a.logtime, COUNT(a.a) AS groupbycolumn
FROM (
SELECT logtime, a, b
FROM tbl
) a
LEFT OUTER JOIN
(
SELECT logtime, a, b
FROM tbl
) b
ON b.logtime < a.logtime
AND a.a = b.b
GROUP BY a.a, a.b, a.logtime
) c
GROUP BY a, groupbycolumn
ORDER BY MIN(logtime)
GO
-- http://www.sqler.com/138840 원리 참조..
with test
as (
select '20100401000000' logtime ,1 a,0 b union all
select '20100401000100' ,1 ,0 union all
select '20100401000200' ,1 ,0 union all
select '20100401000300' ,1 ,0 union all
select '20100401000400' ,1 ,0 union all
select '20100401000500' ,1 ,0 union all
select '20100401000600' ,1 ,0 union all
select '20100401000700' ,0 ,1 union all
select '20100401000800' ,0 ,1 union all
select '20100401000900' ,0 ,1 union all
select '20100401001000' ,0 ,1 union all
select '20100401001100' ,0 ,1 union all
select '20100401001200' ,1 ,0 union all
select '20100401001300' ,1 ,0 union all
select '20100401001400' ,1 ,0 union all
select '20100401001500' ,1 ,0 union all
select '20100401001600' ,1 ,0 union all
select '20100401001700' ,0 ,1 union all
select '20100401001800' ,0 ,1 ) , result
as (
select logtime
, a
, b
, (case when a = 1 then 'A' else 'B' end ) [가동]
, 100000000 + row_number() over ( partition by a order by logtime , a)
- (row_number() over ( order by logtime ))gidx
from test
)
select [가동] , min(logtime) starttime , max(logtime ) endtime
from result
group by gidx , [가동]
order by min(logtime)