궁금한게 있어 질문드립니다.
약 30만개 이상의 데이터를 1분 단위마나 평균치를 내는 쿼리를 작성하였습니다.
테이블 정보는
시간 | 데이터1 | 데이터 2 | 데이터 3 |
2012-01-01 00:00:12 | 1 | 2 | 3 |
2012-01-01 00:00:25 | 2 | 2 | 3 |
2012-01-01 00:00:55 | 2 | 21 | 4 |
2012-01-01 00:01:11 | 1213 | 2 | 3 |
2012-01-01 00:01:31 | 23 | 21 | 42 |
2012-01-01 00:02:01 | 45 | 33 | 123 |
라는 데이터들이 계속 쌓이고있습니다.
그래서 저는 1분단위의 데이터 평균치를 보고 싶어
select left (convert (varchar, 시간, 120), 16), avg(데이터1), avg(데이터2), avg(데이터3) from test
group by left (convert (varchar, 시간, 120), 16)
라고 하였는데.
데이터 자체는 잘 나오는데 문제는 날짜의 순서가 약간 변경된다는 것 입니다.
간단하게 보여드리자면
2012-01-01 00:01
2012-01-01 00:02
2012-01-01 00:03
2012-01-01 00:05
2012-01-01 00:04
2012-01-01 00:06
2012-01-01 00:07
2012-01-01 00:08
같이 시간의 순서가 맞지 않습니다.
해결 방법 자체는 order by 를 쓰면 간단해 지는데.
왜 데이터 시간의 순서가 맞지 않는지 궁금합니다. (CPU 사용량을 최대한 줄이기 위해 order by를 최대한 안쓰고 싶습니다.)
혹시 처음 입력된 데이터의 시간이 잘못 되었다 해서 확인을 하여도 원본 데이터는 정상적으로 시간기록이 되어있습니다.
약 1000개 가량의 데이터로 테스트를 해봐도 정상적으로 나오는데 20만 개의 데이터가 넘어가면 이렇게 되는거 같습니다.
이게 저만 그러는 것 인지 아니면 MSSQL 자체의 버그성 오류인지 궁금합니다.
답변 부탁드리겠습니다.
Comment 7
-
처리짱
2013.12.12 12:09
-
바람의취객
2013.12.12 14:21
저도 그렇게 알고있지만
dtTimeStamp AxisLoad
2012-11-16 00:01 2.53333333333333
2012-11-16 00:02 1.98333333333333
2012-11-16 00:03 2.04
2012-11-16 00:04 2.5
2012-11-16 00:05 2.33333333333333
2012-11-16 00:06 3.2
2012-11-16 00:07 1.46
2012-11-16 00:08 0.55
2012-11-16 00:10 0
2012-11-16 00:17 2.6
2012-11-16 00:19 2.1
2012-11-16 00:22 2.51666666666667
2012-11-16 00:25 2.15
2012-11-16 00:27 2.26666666666667
2012-11-16 00:28 1.71666666666667
2012-11-16 00:37 2.4
2012-11-16 00:39 2.65
2012-11-16 00:47 2.98333333333333
2012-11-16 00:49 2.41666666666667
2012-11-16 00:50 1.8
2012-11-16 00:57 2.78
2012-11-16 00:59 0.4
2012-11-16 01:01 0
2012-11-16 01:03 0
2012-11-16 01:04 0
2012-11-16 01:05 0.0666666666666667
2012-11-16 01:06 1.13333333333333
2012-11-16 01:08 1.9
2012-11-16 01:09 2.04285714285714
2012-11-16 01:10 2.3
2012-11-16 01:11 2.3
2012-11-16 01:13 2.68
2012-11-16 01:14 2.04285714285714
2012-11-16 01:15 1.88
2012-11-16 01:16 2.55
2012-11-16 01:19 0.133333333333333
2012-11-16 01:22 0
2012-11-16 01:46 0.15
2012-11-16 01:48 2.68333333333333
2012-11-16 01:49 2.01666666666667
2012-11-16 01:50 2.31666666666667
2012-11-16 01:52 1.06666666666667
2012-11-16 01:57 0
2012-11-16 02:01 0
2012-11-16 02:04 0.8
2012-11-16 02:06 1.73333333333333
2012-11-16 02:10 2.53333333333333
2012-11-16 02:12 2.56666666666667
2012-11-16 02:13 2.61666666666667
2012-11-16 02:14 1.86666666666667
2012-11-16 02:15 2.31666666666667
2012-11-16 02:17 2.62857142857143
2012-11-16 02:18 1.94
2012-11-16 02:19 1.3
2012-11-16 02:24 2.45
2012-11-16 02:29 2.18333333333333
2012-11-16 02:31 1.95
2012-11-16 02:36 2.26666666666667
2012-11-16 02:40 2.31666666666667
2012-11-16 02:41 2.1
2012-11-16 02:42 2.63333333333333
2012-11-16 02:43 2
2012-11-16 02:45 2.58333333333333
2012-11-16 02:47 2.16666666666667
2012-11-16 02:48 2.36666666666667
2012-11-16 02:51 2.11666666666667
2012-11-16 02:54 2.26666666666667
2012-11-16 02:56 1.4
2012-11-16 02:59 3.05
2012-11-16 03:00 2.13333333333333
2012-11-16 03:01 2.48333333333333
2012-11-16 03:04 2.58333333333333
2012-11-16 03:06 2.26666666666667
2012-11-16 03:09 2.1
2012-11-16 03:12 2.01666666666667
2012-11-16 03:15 1.76666666666667
2012-11-16 03:17 2.01666666666667
2012-11-16 03:18 2.15
2012-11-16 03:20 2.6
2012-11-16 03:21 2.73333333333333
2012-11-16 03:23 2.8
2012-11-16 03:24 2.01666666666667
2012-11-16 03:26 2.26666666666667
2012-11-16 03:29 2.05
2012-11-16 03:30 2.08333333333333
2012-11-16 03:31 2.45
2012-11-16 03:34 2.05
2012-11-16 03:36 1.96666666666667
2012-11-16 03:39 1.7
2012-11-16 03:40 2.51666666666667
2012-11-16 03:41 1.96666666666667
2012-11-16 03:42 1.91666666666667
2012-11-16 03:43 2.73333333333333
2012-11-16 03:45 2.65
2012-11-16 03:46 1.93333333333333
2012-11-16 03:47 2.63333333333333
2012-11-16 03:48 2.01666666666667
2012-11-16 03:49 2.06666666666667
2012-11-16 03:50 2.4
2012-11-16 03:51 1.81666666666667
2012-11-16 03:54 1.45
2012-11-16 03:59 0.216666666666667
2012-11-16 04:00 0.0333333333333333
2012-11-16 04:01 0
2012-11-16 04:04 0
2012-11-16 04:06 0
2012-11-16 04:22 1
2012-11-16 04:23 1.9
2012-11-16 04:25 2.26666666666667
2012-11-16 04:27 1.78333333333333
2012-11-16 04:28 2.56666666666667
2012-11-16 04:30 2.2
2012-11-16 04:31 2.26666666666667
2012-11-16 04:33 2.95714285714286
2012-11-16 04:34 1.88
2012-11-16 04:36 2.14285714285714
2012-11-16 04:39 1.98333333333333
2012-11-16 04:50 2.56666666666667
2012-11-16 04:51 2.05
2012-11-16 04:52 2.06666666666667
2012-11-16 04:53 2.46666666666667
2012-11-16 04:54 1.8
2012-11-16 04:55 2.58571428571429
2012-11-16 04:56 2.8
2012-11-16 04:59 1.04285714285714
2012-11-16 05:00 1.1
2012-11-16 00:00 1.7
2012-11-16 00:09 0.166666666666667
보시는 바와 같이 2012-11-16 00:00대의 값이 밑에내려와 있습니다.
원본 데이터 쿼리 에는 순서가 정확하게 되어있고요.
쿼리는
-- Number를@Num라고선언
declare @Num varchar(10)
-- UPDATE Time을@month_date 라고선언
declare @month_date varchar(30)
-- 로봇코드명을@Robot_Name라고선언
declare @Robot_Name varchar(40)
-- 로봇의대수를@Robot_No라고선언
declare @Robot_No int
-- 테이블명을@Table_Name라고선언
declare @Table_Name varchar(50)
-- exec프로시저사용을위한함수@strQuery를선언
declare @strQuery varchar(1200)
set @month_date = left ((select dtTimeStamp from HRMS_Diagnostics.dbo.UPDATE_TIME
where tbName =('tbAxisLoadContinueHis')), 10)
set @Robot_Name = 'dbo.tbAxisLoadContinueHis_R'
Set @Num = '155'
set @Robot_No = 164
--로봇대수만큼반복문실행
while @Num <= @Robot_No
begin
set @Table_Name = @Robot_Name + RIGHT('000' + CAST(@Num AS VARCHAR), 3)
set @strQuery = --'insert into HRMS_Diagnostics.'+@Table_Name+'_Bak '+
'select left (convert(VARCHAR, dtTimeStamp , 120),16),
avg(fLoadContinue_1), min(fLoadContinue_1), max(fLoadContinue_1),
avg(fLoadContinue_2), min(fLoadContinue_2), max(fLoadContinue_2),
avg(fLoadContinue_3), min(fLoadContinue_3), max(fLoadContinue_3),
avg(fLoadContinue_4), min(fLoadContinue_4), max(fLoadContinue_4),
avg(fLoadContinue_5), min(fLoadContinue_5), max(fLoadContinue_5),
avg(fLoadContinue_6), min(fLoadContinue_6), max(fLoadContinue_6),
avg(fLoadContinue_7), min(fLoadContinue_7), max(fLoadContinue_7),
avg(fLoadContinue_8), min(fLoadContinue_8), max(fLoadContinue_8),
avg(fLoadContinue_9), min(fLoadContinue_9), max(fLoadContinue_9)
from '+'HRMS_HMC.'+@Table_Name +' where dtTimeStamp <= '''+@month_date+''''
+' group by left (convert(VARCHAR, dtTimeStamp , 120), 16)'
--+' order by left (convert(VARCHAR, dtTimeStamp , 120), 16)'
set @Num = @Num + 1
exec(@strQuery)
end
order by를 사용하지 않은 상태입니다.
-
따로 order by를 지정하지 않았다면 스켄한 순서대로 데이터가 나올 것 같습니다.
힙을 스켄하였다면 데이터가 쌓인 순서대로...
클러스터드 인덱스를 스켄하였다면 클러스터드 인덱스 키 순으로...
인덱스를 스켄하였다면 인덱스 키 순으로...
그리고 데이터가 실제로 쌓이는 순서는
힙일 경우 순서대로, B-TREE의 경우에는 클러스터드 인덱스 순으로 재정렬됩니다.
-
바람의취객
2013.12.13 10:27
결국 안정성을 위해 order by를 사용하는게 좋다는 말씀이시군요 감사합니다.
sql은 너무 어려운거같습니다. 앞으로도 많이 가르쳐 주시면 감사하겠습니다.
-
향지
2013.12.13 12:01
GROUP BY 한다고 해서 정렬이 보장되는 것은 아닙니다.
예전에는 모르겠지만
SQL SERVER가 GROUPING 하는 방법은 제가 아는건 두가지 입니다.
정렬된 값으로 비교해서 GROUP 하는 방법과
HASH로 같은 값을 찾아서 GROUP 하는 방법
첫번째 방법은 정렬이 보장되지만
두번째 방법은 정렬을 하지 않고 같은 값만 찾아서 GROUP을 하기 때문에 결과가 정렬되지 않을수 있습니다.
옵티마이저가 계산하기에 정렬을 꼭 해야된다는 구문이 없고 HASH가 더 빠르다고 계산되면 HASH로 GROUP을 할 수 있습니다.
정렬이 필요하시면 꼭 ORDER BY를 넣으셔야됩니다.
그외에도 정렬이 보장 안되는 경우가 조금씩 있긴 합니다....
실행계획에 GROUP하는 연산이 HASH인지 SORT인지 확인 해보시면 좋을것 같습니다.
-
향지
2013.12.13 12:19
추가
ORDER BY 안썼지만
정렬해서 GROUP하는 방법도
병렬처리 하면 정렬이 안될수 있습니다.
테스트한 2008 기준이고 민석님 말씀대로 나중에는 또 어떨지 모릅니다.
-
104동 401호
2013.12.13 15:04
클러스터 인덱스는 정렬을 보장 할수 없다
SCAN 방식에는 2가지 종류가 있다.
- Allocation Ordered Scan
이 스캔 방식은 IAM(index Allocation Map) 페이지를 통해서 혼합 익스텐트(Mixed Extent)를 읽고 난 후에
균일 익스텐트(Uniform Extent)를 순서대로 읽는 방식이다.
- Index Ordered Scan
인덱스 스캔 방식으로 인덱스의 Double Linked List 구조 기반으로 next page 포인터로 연결된 다음 페이지들을 읽는 방식이다.
이런 스캔 방식때문에 클러스터 인덱스가 있음에도 불구하고 정렬되지 않은 데이터를 가져올수 있습니다
정렬이 보장 되지 않는 경우는 3가지 조건이 있다.
- READUNCOMMITTED 이거나 읽기 전용 환경인 경우
- SQL SERVER 2005 이상에서 테이블 페이지가 64 이상인 경우
- 실행계획에 Ordered 연산자가 false (order by 가 없는 경우)
결론은 정렬이 필요한 경우 ORDER BY를 꼭 사용해야 한다!!.
클러스터인덱스 순서대로 쌓일텐데요..