쿼리쫌 부탁 합니다 ^^

쿼리쫌 ^^ 2016.11.23 08:29 Views : 2568

안녕 하세요

많은 도움을 받고 있는 메아리(쿼리쫌) 입니다.

감사 드리고요

한번더 부탁좀 드립니다.

대략 짜집기로 해서 select 문은 만들어 냈지만,

날짜와 시간에서 문제가 생깁니다

=============================================================

1 => 000 번의 id 에 대해서, 하루 하나의 레코드만 추출

2 => 255 번의 id 에 대해서, 하루 하나의 레코드만 추출

3 => 위의 1번의 결과와, 2 번의 결과를 합친다

이때 아래 2번 그림에서 보듯이 아랫쪽에는 날짜만 나오고 시간이 안나옵니다.

=============================================================

집계함수 때문인것 같은데 아시는 분께 부탁좀 드립니다.

글로써 설명하기가 너무 복잡할거 같아서, 캡쳐화면을 첨부하여 드립니다.

부탁좀 드립니다.

감사 합니다.

WITH surveydb ([site_code], [timestamp], [sensor_id], [sensor_type], sensor_value, calc_value, sensor_value2, calc_value2, etc, alert_flag, site_addr)

(

SELECT '001007003001', '2015-08-29 11:51:41', '000', '3', 21403, 0.02, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-08-30 11:51:42', '000', '3', 21403, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-08-31 11:51:43', '000', '3', 21403, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-01 11:51:44', '000', '3', 21403, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-02 11:51:45', '000', '3', 21467, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-03 11:51:46', '000', '3', 21467, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-04 11:51:47', '000', '3', 21467, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-05 11:51:58', '000', '3', 21467, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-05 11:11:48', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-08-29 11:21:41', '255', '3', 12345, 0.02, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-08-30 11:31:42', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-08-31 11:41:43', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-01 11:51:44', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-02 11:61:45', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-03 11:52:46', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-04 11:53:47', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-08-29 11:54:51', '255', '3', 12345, 0.02, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-08-30 11:55:52', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-08-31 11:56:53', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-01 11:57:54', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-02 11:58:55', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-03 11:59:56', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-04 11:31:57', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL UNION ALL

SELECT '001007003001', '2015-09-05 11:32:58', '255', '3', 23456, 0.03, NULL, NULL, 53, 0, NULL

masterdb ([site_code], [sensor_id], [begin_date], [end_date], value, [offset_eq] )

(

SELECT '001007003001', '000', '2015-08-29 00:39:00', '2015-08-29 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '255', '2015-08-30 11:51:41', '2015-08-30 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-08-31 11:51:41', '2015-08-31 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '255', '2015-09-01 11:51:41', '2015-09-01 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-09-02 11:51:41', '2015-09-02 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-09-03 11:51:41', '2015-09-03 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-09-04 00:14:00', '2015-09-04 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '255', '2015-09-04 11:51:41', '2015-09-04 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-09-05 11:51:41', '2015-09-05 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-09-29 11:51:41', '2015-08-29 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-08-29 11:51:41', '2015-08-30 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-08-30 11:51:41', '2015-08-31 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-08-31 11:51:41', '2015-09-01 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-09-01 11:51:41', '2015-09-02 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-09-02 11:51:41', '2015-09-03 11:51:41', 393.74, '({v} / 100.0) - {o}' UNION ALL

SELECT '001007003001', '000', '2015-09-05 00:15:00', '2015-09-05 11:51:41', 393.74, '({v} / 100.0) - {o}'

)

SELECT a.*

FROM

(

SELECT CONVERT(NVARCHAR, s.timestamp, 120) as timestamp, s.sensor_id as sensor, s.sensor_value as raw_value,

s.calc_value as calc_value, o.value as offset, o.offset_eq as equation, s.etc as etc

FROM surveydb s

inner join

(

SELECT site_code, MIN(timestamp) timestamp , sensor_id

FROM surveydb

WHERE site_code = '001007003001'

AND CONVERT(VARCHAR, timestamp, 23) between '2015-08-29' AND '2015-09-05'

AND sensor_id IN ('000')

GROUP BY site_code, CONVERT(VARCHAR, timestamp, 23) , sensor_id

) ss

on s.site_code = ss.site_code

and s.timestamp = ss.timestamp

and s.sensor_id = ss.sensor_id

LEFT OUTER JOIN masterdb o

ON s.site_code = o.site_code

AND s.sensor_id = o.sensor_id

AND o.begin_date <= s.timestamp AND o.end_date >= s.timestamp

) a

union all

select b.*

FROM

(

SELECT CONVERT(VARCHAR, ss.timestamp, 23) as timestamp, ss.sensor_id , sum(s.sensor_value) raw_value ,

sum( s.calc_value) as calc_value, sum(o.value) as offset ,max(o.offset_eq) as equation, max(s.etc) as etc

FROM surveydb s

inner join surveydb ss

on s.site_code = ss.site_code and s.timestamp = ss.timestamp

and s.sensor_id = ss.sensor_id

LEFT OUTER JOIN masterdb o

ON s.site_code = o.site_code

AND s.sensor_id = o.sensor_id

AND o.begin_date <= s.timestamp

AND o.end_date >= s.timestamp

WHERE ss.site_code = '001007003001'

AND CONVERT(VARCHAR, ss.timestamp, 23) between '2015-08-29' AND '2015-09-05'

AND ss.sensor_id IN ('255')

GROUP BY CONVERT(VARCHAR, ss.timestamp, 23), ss.sensor_id

) b

=====================================================================

1. 번 화면

=====================================================================

=====================================================================

2. 번 화면

=====================================================================

=====================================================================

3. 번 화면

=====================================================================

=====================================================================

4. 번 화면

=====================================================================

=====================================================================

5. 번 화면

=====================================================================

=====================================================================

6. 번 화면. 이화면은 동일한 가상테이블을 만들어서, 현상을 재현해서, 계시판에 질문 할려고 했는데

현장 재현도 안되고 있습니다. 몇날 며칠을 삽질해도 안되네요 ㅠ

=====================================================================

=====================================================================

마지막 7. 번 화면

=====================================================================

I want to

cmd_document_vote_user Update Log List

Comment 6

이리

2016.11.23 09:36

쿼리 보면 위 SELECT 에는 120을 쓰시고 밑에는 23을 쓰시는게 이유가 있는지요?

SELECT CONVERT(VARCHAR, GETDATE(), 23)

SELECT CONVERT(VARCHAR, GETDATE(), 120)

결과 비교해보세요..

cmd_comment_vote_user Upvote0 Downvote0
쿼리쫌 ^^

2016.11.23 10:11

이리님 안녕 하십니까 감사 드리고요
23 => 120 으로 하게 되면은 1일 하나의 레코드만 추출하는 기능이 안 먹힙니다.ㅋ
cmd_comment_vote_user Upvote0 Downvote0
이리

2016.11.23 10:48

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY SUBSTRING(a.timestamp, 1, 10) ORDER BY a.timestamp) AS ROW_NUM
, a.*
FROM
(
SELECT s.timestamp, s.sensor_id as sensor, s.sensor_value as raw_value,
s.calc_value as calc_value, o.value as offset, o.offset_eq as equation, s.etc as etc
FROM surveydb s
LEFT OUTER JOIN masterdb o
ON s.site_code = o.site_code
AND s.sensor_id = o.sensor_id
AND s.timestamp >= CONVERT(CHAR(10), o.begin_date, 120) + ' 00:00:00'
AND s.timestamp <= CONVERT(CHAR(10), o.begin_date, 120) + ' 23:59:59'
WHERE s.site_code = '001007003001'
AND s.timestamp between '2015-08-29 00:00:00' AND '2015-09-05 23:59:59'
AND s.sensor_id IN ('000')
) a
union all
select ROW_NUMBER() OVER(PARTITION BY SUBSTRING(b.timestamp, 1, 10) ORDER BY b.timestamp) AS ROW_NUM
, b.*
FROM
(
SELECT s.timestamp, s.sensor_id , sum(s.sensor_value) raw_value ,
sum(s.calc_value) as calc_value, sum(o.value) as offset ,max(o.offset_eq) as equation, max(s.etc) as etc
FROM surveydb s
LEFT OUTER JOIN masterdb o
ON s.site_code = o.site_code
AND s.sensor_id = o.sensor_id
AND s.timestamp >= CONVERT(CHAR(10), o.begin_date, 120) + ' 00:00:00'
AND s.timestamp <= CONVERT(CHAR(10), o.begin_date, 120) + ' 23:59:59'
WHERE s.site_code = '001007003001'
AND s.timestamp between '2015-08-29 00:00:00' AND '2015-09-05 23:59:59'
AND s.sensor_id IN ('255')
GROUP BY s.timestamp, s.sensor_id
) b
) AS a
WHERE a.ROW_NUM = 1

이런식의 결과면 되는것인지요?

그리고 예제로 주신 data에 61분인거 있네요..

cmd_comment_vote_user Upvote0 Downvote0
쿼리쫌 ^^

2016.11.23 12:13

이리님 감사 합니다.
하나만 더 부탁 드리겠습니다.

raw_value 하고 calc_value 가 하루의 합계가 나오는 것만 부탁좀 드리겠습니다.

그래서 sum 하려고 convert (nvarchar, timestamp, 23) 식으로 23 을 했던 것입니다.

올려주신 쿼리는 합계가 나오지 않고, 0 이 나옵니다

필요한 부분은 아래와 같습니다.

한번만 더 부탁 드리겠습니다.

정말 감사 합니다 꾸~~벅~~~~~~~~~
cmd_comment_vote_user Upvote0 Downvote0
이리

2016.11.23 12:57

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY SUBSTRING(a.timestamp, 1, 10) ORDER BY a.timestamp) AS ROW_NUM
, a.*
FROM
(
SELECT s.timestamp, s.sensor_id as sensor, s.sensor_value as raw_value,
s.calc_value as calc_value, o.value as offset, o.offset_eq as equation, s.etc as etc
FROM surveydb s
LEFT OUTER JOIN masterdb o
ON s.site_code = o.site_code
AND s.sensor_id = o.sensor_id
AND s.timestamp >= CONVERT(CHAR(10), o.begin_date, 120) + ' 00:00:00'
AND s.timestamp <= CONVERT(CHAR(10), o.begin_date, 120) + ' 23:59:59'
WHERE s.site_code = '001007003001'
AND s.timestamp between '2015-08-29 00:00:00' AND '2015-09-05 23:59:59'
AND s.sensor_id IN ('000')
) a
union all
select ROW_NUMBER() OVER(PARTITION BY SUBSTRING(b.timestamp, 1, 10) ORDER BY b.timestamp) AS ROW_NUM
, b.*
FROM
(
SELECT s.timestamp, s.sensor_id as sensor, ss.raw_value as raw_value,
ss.calc_value as calc_value, ss.offset as offset, ss.equation as equation, s.etc as etc
FROM surveydb s
INNER JOIN
(
SELECT s.site_code
, s.sensor_id
, CONVERT(CHAR(10), s.timestamp, 120) AS dt
, sum(s.sensor_value) raw_value
, sum(s.calc_value) as calc_value
, sum(o.value) as offset
, max(o.offset_eq) as equation
, max(s.etc) as etc
FROM surveydb s
LEFT OUTER JOIN masterdb o
ON s.site_code = o.site_code
AND s.sensor_id = o.sensor_id
AND s.timestamp >= CONVERT(CHAR(10), o.begin_date, 120) + ' 00:00:00'
AND s.timestamp <= CONVERT(CHAR(10), o.begin_date, 120) + ' 23:59:59'
WHERE s.site_code = '001007003001'
AND s.timestamp between '2015-08-29 00:00:00' AND '2015-09-05 23:59:59'
AND s.sensor_id IN ('255')
GROUP BY s.site_code, s.sensor_id, CONVERT(CHAR(10), s.timestamp, 120)
) ss ON s.site_code = ss.site_code AND s.sensor_id = ss.sensor_id

) b
) AS a
WHERE a.ROW_NUM = 1

이렇게인가요?

cmd_comment_vote_user Upvote0 Downvote0
쿼리쫌 ^^

2016.11.23 13:54

마지막에 조건하나 추가 했습니다.
AND convert (nvarchar, s.timestamp, 23) = convert(nvarchar, ss.dt, 23)
감사합니다 ^^
대단히 감사 합니다~~~~~~~~~~~~~~~~~
꾸~~~~~~~~~~~~~~~벅~~~~~~~~~~~~~~~~~
며칠간 삽질 했는데. 감사 감사 ^^
이리님은 어디 사십니까 ?? 저는 지방입니다만,,,,

cmd_comment_vote_user Upvote0 Downvote0

No.	Subject	Author	Date	Views
Notice	SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트)	코난(김대우)	2023.08.18	36276
Notice	Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트)	코난(김대우)	2021.01.01	18882
9623	"해당 버전이 706이므로 열 수 없습니다...." 하는 에러와 함께 mdf 파일 연결이 되지 않습니다. 도와주세요...ㅠㅠㅠ [1]	난키군	2016.11.24	61296
9622	프로시저을 만들고 싶은데..어떻게 해야하는지요? [1]	슈토파이터	2016.11.24	2505
9621	ms server 2000이 ms server 2008 r2 보다 휠씬 빠른데 어떻게 된 건가요? [5]	신생	2016.11.24	2902
9620	개체탐색기 로그인 계정제거 [2]	라꾸라꾸	2016.11.24	2146
9619	인덱스 다시구성 다시작성 일정 [3]	냥냥	2016.11.23	3956
9618	안녕하세요 sql입문자인데요 [2]	라라리룰	2016.11.23	2122
9617	mysql 질문 드립니다.. [3]	꼽냐™	2016.11.23	1942
»	쿼리쫌 부탁 합니다 ^^ [6]	쿼리쫌 ^^	2016.11.23	2568
9615	2012로 업데이트 후 프로시져 실행 에러가 발생했습니다. [1]	약도사	2016.11.22	2150
9614	DB 파일 자동 증가 관련 문의	뛰어	2016.11.22	2088
9613	쿼리 관련 문의 입니다!! DB 사용량 관련 [1]	뛰어	2016.11.22	2085
9612	특정 데이터 찾기 쿼리가 궁급합니다. [1]	진후킴	2016.11.21	1973
9611	데이터 실시간 연동 관련 문의 입니다. [4]	호짱	2016.11.21	4607
9610	DB 복제에 대해 질문 드립니다.	조형근	2016.11.21	3832
9609	TABLE 특정컬럼 결과값 확인 [2]	슬이슬이	2016.11.21	1829
9608	쿼리좀 풀어 주세요 [4]	메아리	2016.11.18	2840
9607	100만건정도의 데이터를 한페이지에 모두 뿌려주는 게 가능할까요? [2]	냥냥	2016.11.18	4181
9606	문자 치환에 대하여 질의드립니다. [1]	방혁	2016.11.17	1559
9605	쿼리 관련 문의 [2]	냥냥	2016.11.17	3124
9604	쿼리좀 구합니다 [2]	메아리	2016.11.17	2440

Write Tag

First Page 63 64 65 66 67 68 69 70 71 72 Last Page

쿼리쫌 부탁 합니다 ^^

Comment 6

이리

쿼리쫌 ^^

이리

쿼리쫌 ^^

이리

쿼리쫌 ^^

커뮤니티

데이터베이스 개발

클라우드 개발

머신러닝/AI개발

파이썬&오픈소스 개발