초단위로 생성되는 속도정보를 저장하는 테이블이 있습니다. 가끔은 데이터가 생성되지 않는 경우도 있습니다.
이 테이블에서 속도값이 100보다 큰 경우가 3초 이상을 지속하는 경우의 건수와
이 경우에 해당하는 초단위 시간의 합을 구해야 하는데요 ..
CREATE TABLE Tbl_Speed (
[Seq] [int] NULL,
[DateTime] [char](12) NULL,
[Speed] [smallint] NOT NULL
)
insert into Tbl_Speed (Seq, DateTime, Speed) values (1, '140610120001', 96);
insert into Tbl_Speed (Seq, DateTime, Speed) values (2, '140610120002', 101);
insert into Tbl_Speed (Seq, DateTime, Speed) values (3, '140610120003', 102);
insert into Tbl_Speed (Seq, DateTime, Speed) values (4, '140610120004', 97);
insert into Tbl_Speed (Seq, DateTime, Speed) values (5, '140610120005', 99);
insert into Tbl_Speed (Seq, DateTime, Speed) values (6, '140610120006', 101);
insert into Tbl_Speed (Seq, DateTime, Speed) values (7, '140610120007', 103);
insert into Tbl_Speed (Seq, DateTime, Speed) values (8, '140610120008', 104);
insert into Tbl_Speed (Seq, DateTime, Speed) values (9, '140610120009', 99);
insert into Tbl_Speed (Seq, DateTime, Speed) values (10, '140610120010', 97);
insert into Tbl_Speed (Seq, DateTime, Speed) values (11, '140610120012', 103);
insert into Tbl_Speed (Seq, DateTime, Speed) values (12, '140610120013', 105);
insert into Tbl_Speed (Seq, DateTime, Speed) values (13, '140610120014', 101);
insert into Tbl_Speed (Seq, DateTime, Speed) values (14, '140610120015', 99);
insert into Tbl_Speed (Seq, DateTime, Speed) values (15, '140610120016', 99);
위의 데이터로 부터 최종 결과는 이렇게 되어야 겠네요.
1. 속도가 100 보다 큰 경우가 3초이상 지속하는 경우의 건수 : 2
=> '140610120006' ~ '140610120008' 와 '140610120012' ~ '140610120014'의 경우 총 2건
2. 이런 조건에 만족하는 초단위 시간의 합 : 6
=> '140610120006' , '140610120007', '140610120008', '140610120012', '140610120013', '140610120014' 의 경우 총 6초
에휴, 이 쿼리 정말 감이 안잡히네요. 고수님들의 조언 부탁드립니다.
즐거운 주말 되세요 ~
Comment 4
-
향지
2014.06.16 17:02
-
향지
2014.06.16 17:08
그리고 기준이 '2000-01-01' 이고 datediff는 int형으로 출력하기때문에 대충 +- 60년정도만 계산 가능합니다.
그 이상 가실거면 시간 단위를 '년월일' , '시분초' 등으로 나눠서 두개를 비교하는 식으로 계산하시기 바랍니다.
-
등빛
2014.07.16 17:01
와... 이렇게하면되네요 SEQ 값 붙어있는걸가지고 DATEDIFF 를 이용해 나온값이랑 SEQ를 뺀값을 같을 같은그룹으로 묶어서 하네요 ㄷㄷ
많이배우고갑니다
-
sqlnovice
2014.06.17 13:21
향지님. 고맙습니다.
아직, 원리는 잘 모르겠지만 열심히 분석해 보겠습니다.
즐거운 시간 보내세요 ...
날짜를 문자로 저장할 경우에는 다음 값이 바로 다음 초인지 계산하기 힘들어서
datetime 형식으로 바꿔서 계산했습니다.
select MIN(dt) as dt_start
, MAX(dt) as dt_end
, COUNT(ck) as during
from
(
select seq, diff, diff-seq as ck
, COUNT(seq) over (partition by diff-seq) as during
, dt -- 확인용
from
(
select ROW_NUMBER() over (order by seq) as seq
, DATEDIFF(SECOND, '2000-01-01', dt) as diff
, dt -- 확인용
from
(
select seq
, CONVERT(DATETIME, '20'+LEFT([datetime], 6)+' '
+SUBSTRING([datetime], 7, 2)+':'
+SUBSTRING([datetime], 9, 2)+':'
+SUBSTRING([datetime], 11, 2)) as dt
from Tbl_Speed
WHERE Speed > 100
) as a
) as a
) as a
where during>=3 -- 3초이상
group by ck
여기서 필요하신 부분 있으면 수정하시면 될 것 같습니다.