데이터베이스 질문과 답변
안녕하세요. 평균값을 내는 쿼리에 대하여 여쭙고자 하는 것이 있어서 질문을 합니다! 도움주시면 감사하겠습니다 !
현재 쿼리 입니다!
SELECT *
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY measurement_date DESC,measurement_time DESC, device_plot ASC ) AS rownum
, (CASE
WHEN device_plot = '11' THEN 'Plot1'
WHEN device_plot = '22' THEN 'Plot2'
WHEN device_plot = '33' THEN 'Plot3'
WHEN device_plot = '44' THEN 'Plot4'
WHEN device_plot = '55' THEN 'Plot5'
WHEN device_plot = '66' THEN 'Plot6'
ELSE '' END)as deviceplot
, CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate
, STUFF(STUFF(LEFT(measurement_time,4), 3, 0, ':'), 4, 0, '') measurementtime
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value END) AS VARCHAR), '-') temperaturevalueT
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value END) AS VARCHAR), '-') temperaturevalueM
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value END) AS VARCHAR), '-') temperaturevalueB
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value END) AS VARCHAR), '-') humidityvalueT
, ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value END) AS VARCHAR), '-') humidityvalueM
FROM smarcle_data_log
WHERE 1=1
AND device_plot_tmb IN ('1','2','3')
AND(SUBSTRING(measurement_time,3,2) % 10)=0
device_plot = #searchCondition#
measurement_date BETWEEN REPLACE(#schFrom#, '-', '') AND REPLACE(#schTo#, '-', '')
GROUP BY device_plot, measurement_date, measurement_time)a
WHERE rownum BETWEEN #firstIndex# AND #lastIndex#
ORDER BY measurementdate desc, measurementtime desc, deviceplot asc
sql은 2005버전 쓰고있습니다!
데이터는 분단위로 들어오고 있지만
SUBSTRING 을 사용하여서 쿼리는 10분단위로 들어오는 데이터를 받게 하고있습니다!
하지만 정확한 시간 분,초에 맞추어 들어오는 데이터가 아니다 보니까 제대로 들어올 경우는 들어오게되어 아래처럼 데이터가 들어오지만
10분 단위로 데이터를 받을 때 나머지 값이 맞지않아 들어오지않으면 아래 사진 처럼 13:10~13:40분의 데이터가 출력이 안되고 있습니다..
그래서 이것을 차라리 1~10분까지의 데이터 받은 값을 평균을 내어서 10 20 30 40 50 60 분에 넣으면 데이터가 출력이 잘 되지 않겠냐고 하셨습니다.. 하지만
저의 미숙한 실력으로는 도저히 쿼리를 어떤식으로 짜야할지 머리가 돌아가지 않아서 선배님들의 조언을 얻고싶습니다! 도움을 주시면 감사하겠습니다!
감사합니다..
AND(SUBSTRING(measurement_time,3,2) % 10)=0
이 조건으로 인해 10분단위 만 걸러지고 있는데.
이 조건을 제거한다면? 어떻게 자료가 들어 올까요?
이미지의 누락된 부분이 실제로 누락이 된 것인지?
아니면 실제 자료는 있는데 10분단위가 아닌 9분이나 11분에 들어 온 자료가 있는 것인지?
자료 입력 주기가 10분인지? 아니면 더 짧은 간격으로 들어오는지?
조건을 제거한다면 쿼리를 돌려볼 경우
335 Plot1 2021/06/15 14:50 25.1 23.5 22.6 91.1 99.9
336 Plot1 2021/06/15 14:48 25.2 23.5 22.6 90.5 99.9
337 Plot1 2021/06/15 14:46 25.4 23.4 22.6 89.2 99.9
338 Plot1 2021/06/15 14:45 25.7 23.5 22.6 89.5 99.9
339 Plot1 2021/06/15 14:43 25.5 23.5 22.5 90.7 99.9
340 Plot1 2021/06/15 14:41 25.4 23.4 22.5 91.3 99.9
341 Plot1 2021/06/15 14:40 25.1 23.4 22.5 93.5 99.9
342 Plot1 2021/06/15 14:38 24.9 23.5 22.5 93.3 99.9
343 Plot1 2021/06/15 14:37 25.1 23.5 22.6 92.7 99.9
344 Plot1 2021/06/15 14:35 25.4 23.5 22.6 91.3 99.9
345 Plot1 2021/06/15 14:33 25.6 23.5 22.6 90.5 99.9
346 Plot1 2021/06/15 14:32 25.5 23.5 22.5 92.6 99.9
347 Plot1 2021/06/15 14:30 25.1 23.4 22.5 92.0 99.9
348 Plot1 2021/06/15 14:28 25.2 23.4 22.5 92.6 99.9
349 Plot1 2021/06/15 14:27 25.1 23.2 22.5 94.1 99.9
350 Plot1 2021/06/15 14:25 24.9 23.3 22.5 93.1 99.9
351 Plot1 2021/06/15 14:23 25.1 23.2 22.5 93.7 99.9
352 Plot1 2021/06/15 14:22 25.0 23.1 22.5 94.3 99.9
353 Plot1 2021/06/15 14:20 24.7 23.2 22.5 95.1 99.9
354 Plot1 2021/06/15 14:18 24.5 23.2 22.5 94.3 99.9
355 Plot1 2021/06/15 14:17 24.6 23.1 22.5 94.7 99.9
356 Plot1 2021/06/15 14:15 24.6 23.2 22.5 96.8 99.9
357 Plot1 2021/06/15 14:13 24.4 23.3 22.5 95.0 99.9
358 Plot1 2021/06/15 14:12 24.3 23.3 22.5 93.4 99.9
359 Plot1 2021/06/15 14:10 25.0 23.3 22.5 93.4 99.9
360 Plot1 2021/06/15 14:08 25.2 23.2 22.5 94.1 99.9
361 Plot1 2021/06/15 14:07 25.0 23.3 22.5 94.5 99.9
362 Plot1 2021/06/15 14:05 25.0 23.2 22.5 94.1 99.9
363 Plot1 2021/06/15 14:04 25.0 23.1 22.5 94.4 99.9
364 Plot1 2021/06/15 14:02 25.2 23.1 22.5 94.5 99.9
365 Plot1 2021/06/15 14:00 25.2 23.0 22.5 95.2 99.9
366 Plot1 2021/06/15 13:59 25.1 23.0 30.4 95.2 99.9
367 Plot1 2021/06/15 13:57 25.1 23.0 22.4 95.2 99.9
368 Plot1 2021/06/15 13:55 25.0 23.0 22.4 96.8 99.9
369 Plot1 2021/06/15 13:54 24.7 22.9 22.3 97.1 99.9
370 Plot1 2021/06/15 13:52 24.6 22.9 22.4 96.1 99.9
371 Plot1 2021/06/15 13:50 24.9 22.9 22.3 96.3 99.9
372 Plot1 2021/06/15 13:49 24.9 22.9 22.4 97.5 99.9
373 Plot1 2021/06/15 13:47 24.3 22.9 22.4 96.9 99.9
374 Plot1 2021/06/15 13:45 24.4 22.8 22.5 95.9 99.9
375 Plot1 2021/06/15 13:44 24.9 22.9 22.5 96.2 99.9
376 Plot1 2021/06/15 13:42 24.9 22.9 22.5 96.1 99.9
377 Plot1 2021/06/15 13:40 24.6 22.9 22.5 96.5 99.9
378 Plot1 2021/06/15 13:39 24.4 23.0 22.5 95.8 99.9
379 Plot1 2021/06/15 13:37 24.6 23.0 22.5 95.6 99.9
380 Plot1 2021/06/15 13:36 24.6 23.0 22.5 95.5 99.9
381 Plot1 2021/06/15 13:34 24.7 23.1 22.5 96.4 99.9
382 Plot1 2021/06/15 13:32 24.9 23.1 22.5 94.4 99.9
383 Plot1 2021/06/15 13:31 25.0 23.1 22.5 95.0 99.9
384 Plot1 2021/06/15 13:29 25.1 23.2 22.5 96.9 99.9
385 Plot1 2021/06/15 13:27 24.6 23.2 22.5 98.4 99.9
386 Plot1 2021/06/15 13:26 24.3 23.2 22.5 99.4 99.9
387 Plot1 2021/06/15 13:24 24.1 23.1 22.5 99.5 99.9
388 Plot1 2021/06/15 13:22 23.8 23.1 22.5 99.1 99.9
389 Plot1 2021/06/15 13:21 24.2 23.2 22.5 96.9 99.9
390 Plot1 2021/06/15 13:19 24.6 23.2 22.5 96.3 99.9
391 Plot1 2021/06/15 13:17 24.5 23.2 22.5 97.4 99.9
392 Plot1 2021/06/15 13:16 24.4 23.1 22.5 99.1 99.9
393 Plot1 2021/06/15 13:14 24.0 23.1 22.5 99.9 99.9
394 Plot1 2021/06/15 13:12 23.6 23.1 22.5 99.8 99.9
395 Plot1 2021/06/15 13:11 23.7 23.1 22.5 99.8 99.9
396 Plot1 2021/06/15 13:09 23.6 23.1 22.5 98.8 99.9
397 Plot1 2021/06/15 13:08 24.2 23.1 22.5 96.7 99.9
398 Plot1 2021/06/15 13:06 24.7 23.2 22.5 97.1 99.9
399 Plot1 2021/06/15 13:04 24.7 23.2 22.5 97.8 99.9
400 Plot1 2021/06/15 13:03 24.6 23.2 22.5 97.8 99.9
401 Plot1 2021/06/15 13:01 24.7 23.2 22.5 98.3 99.9
402 Plot1 2021/06/15 12:59 24.1 23.3 22.5 97.1 99.9
403 Plot1 2021/06/15 12:58 24.2 23.4 22.5 92.6 99.8
404 Plot1 2021/06/15 12:56 25.1 23.5 22.6 92.6 99.8
405 Plot1 2021/06/15 12:54 25.5 23.5 22.6 92.7 99.7
406 Plot1 2021/06/15 12:53 25.8 23.5 22.6 93.2 99.7
407 Plot1 2021/06/15 12:51 25.6 23.5 22.5 95.8 99.8
408 Plot1 2021/06/15 12:49 25.1 23.5 22.5 96.6 99.8
409 Plot1 2021/06/15 12:48 24.8 23.5 22.5 98.0 99.8
410 Plot1 2021/06/15 12:46 24.5 23.5 22.5 96.8 99.8
411 Plot1 2021/06/15 12:45 24.6 23.5 22.5 96.3 99.9
412 Plot1 2021/06/15 12:43 25.0 23.5 22.5 96.4 99.9
413 Plot1 2021/06/15 12:41 24.7 23.5 22.5 97.0 99.9
414 Plot1 2021/06/15 12:40 24.8 23.5 22.5 97.7 99.9
415 Plot1 2021/06/15 12:38 24.4 23.5 22.5 97.2 99.9
416 Plot1 2021/06/15 12:36 24.3 23.5 22.5 95.4 99.9
417 Plot1 2021/06/15 12:35 24.9 23.5 22.5 97.1 99.9
418 Plot1 2021/06/15 12:33 24.4 23.5 22.5 97.3 99.9
419 Plot1 2021/06/15 12:31 24.3 23.5 22.5 98.6 99.9
420 Plot1 2021/06/15 12:30 24.2 23.5 22.5 98.4 99.9
현재 데이터가 이렇게 들어오고 있습니다..!
이미지의 누락 된 부분은 실제로는 데이터가 들어오고 있습니다!
위의 값을 보게되면 11분 21분 31분 41분 51분 으로 들어오게 되어서 데이터는 가지고 있지만 출력을 하지 못하는 것 같습니다..
자료입력주기는 10분마다 출력을 하게 하라고 말씀하셨습니다... 프로젝트가 지금 현재 틀이 잡혀있지않아 그때그때 대표님 생각대로 바꾸고 싶으신 부분을 바꾸기 때문에 일단은 1~10분사이의 데이터를 갯수/ 총합 으로 나누어서 평균을 내어서 10분마다 자료 입력해서 출력하라고 하셨습니다.
SELECT ROW_NUMBER() OVER(ORDER BY measurement_date DESC, SUBSTRING(measurement_time, 1, 3) DESC, device_plot) rn
, CASE device_plot
WHEN '11' THEN 'Plot1'
WHEN '22' THEN 'Plot2'
WHEN '33' THEN 'Plot3'
WHEN '44' THEN 'Plot4'
WHEN '55' THEN 'Plot5'
WHEN '66' THEN 'Plot6'
ELSE '' END deviceplot
, CONVERT(CHAR(10), CONVERT(DATE, measurement_date), 111) measurementdate
, STUFF(SUBSTRING(measurement_time, 1, 3) + '0', 3, 0, ':') measurementtime
, AVG(CASE device_plot_tmb WHEN '1' THEN temperature_value END) temperaturevalueT
, AVG(CASE device_plot_tmb WHEN '2' THEN temperature_value END) temperaturevalueM
, AVG(CASE device_plot_tmb WHEN '3' THEN temperature_value END) temperaturevalueB
, AVG(CASE device_plot_tmb WHEN '1' THEN humidity_value END) humidityvalueT
, AVG(CASE device_plot_tmb WHEN '2' THEN humidity_value END) humidityvalueM
FROM smarcle_data_log
WHERE 1=1
AND device_plot_tmb IN ('1', '2', '3')
AND measurement_date BETWEEN REPLACE(#schFrom#, '-', '') AND REPLACE(#schTo#, '-', '')
AND device_plot = #searchCondition#
GROUP BY device_plot, measurement_date
, SUBSTRING(measurement_time, 1, 3)
;
-- 시간 4자리중 3자리까지 기준으로 집계 --
00:00 ~ 00:09 -> 00:00
00:10 ~ 00:19 -> 00:10
00:20 ~ 00:29 -> 00:20
00:30 ~ 00:39 -> 00:30
00:40 ~ 00:49 -> 00:40
00:50 ~ 00:59 -> 00:50
01:00 ~ 01:09 -> 01:00
마농님 덕분에 많이 알아가는 것 같아서 감사합니다!
혹시
GROUP BY device_plot, measurement_date
, SUBSTR(measurement_time, 1, 3)
현재 이부분이 지원되지않는 함수라고 나와서
GROUP BY device_plot, measurement_date,SUBSTRING (measurement_time, 1, 5))
서브스트링으로 받으려고 했는데
'smarcle_data_log.measurement_time'이(가) 집계 함수나 GROUP BY 절에 없으므로 SELECT 목록에서 사용할 수 없습니다. 이렇게 오류가 나는데 이유가 뭔지 알 수 있을까요?
SUBSTR 을 SUBSTRING 로 바꾸면 되는데.
숫자는 왜 바꾸셨나요? (1, 3)인데 (1, 5)로 바꿨네요?
) 버튼을 클릭하여 작성 하시면 됩니다.