꿈꾸는 개발자, DBA 커뮤니티 구루비

[오라클] 그룹별 최대값 구하는 쿼리 질문

by 쿨에어 [SQL Query] 오라클 11g group by over max SQL [2018.07.27 10:38:10]

TB_MSRR.png (5,404Bytes)

TB_MESURE.png (7,658Bytes)

SQL1.png (12,295Bytes)

SQL_RESULT1.png (4,656Bytes)

안녕하세요. SQL 초보입니다.

우선 제가 하려는건 각 '시설물'에 설치된 '계측기'의 상태를 조회하는 것입니다.

TB_MSRR(계측기) 에 계측기 종류(기울기, 온도, 등...), 경고수치, 위험수치가 있습니다.

TB_MESURE(계측정보) 에 각 계측기에서 매 시간마다 측정된 값이 저장됩니다.

측정값이 위험수치보다 높으면 위험, 경고수치보다 높으면 경고, 아니면 정상으로 상태를 표시하려고 합니다.

그리고 시설물(FACIL_NO) 에 계측종류(MESURE_KND_CD)의 상태를 표시하려고 합니다.

(시설물에 계측기가 여러개가 설치되고요, 계측종류가 '01'인 것 중에 상태가 (위험, 경고)이면 이 계측기의 계측종류 '01'의 상태는 위험입니다. (경고, 정상)이면 상태는 경고이구요.

그래서 계측상태를 숫자로 (위험:2, 경고:1, 정상:0) 만들고 여기서 시설물로 GROUP BY 하여 각 종류별로 MAX값을 찾아내면 되겠구나 해서

아래 쿼리로 상태를 숫자로 표현하는것까지는 했는데요. 그 다음 진행이 잘 안되네요 ㅜㅜ

-- SQL1 쿼리
SELECT M.FACIL_NO, M.MSRR_NO, M.MESURE_KND_CD, B.MESURE_VALUE, M.ATENT_NCL, M.RISK_NCL, 
        CASE WHEN M.RISK_NCL <= B.MESURE_VALUE THEN 2
            WHEN M.ATENT_NCL <= B.MESURE_VALUE THEN 1
            ELSE 0 END AS STATE,
            TO_CHAR(B.MESURE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS MESURE_DATETIME
FROM TB_MSRR M
LEFT JOIN (
SELECT *
    FROM TB_MESURE
    WHERE (MSRR_NO, MESURE_DATE) 
        IN (SELECT MSRR_NO ,
                (SELECT MAX(MESURE_DATE) FROM TB_MESURE WHERE MSRR_NO = A.MSRR_NO) MESURE_DATE 
            FROM TB_MSRR A
            )
) B ON B.MSRR_NO = M.MSRR_NO;

도움 부탁드립니다. ^^; 감사합니다.

by 우리집아찌 [2018.07.27 13:27:13]

TB_MSRR 테이블의 ATENT_NCL , RISK_NCL 사이에 있으면 MESURE_KND_CD 을 반환하는것 아닌가요?

나머지는 어떻게 처리하죠? MESURE_KND_CD 값이 더 많아야할것 같은데요.

아니면 STATE 처리를 임의로 한건가요?

by 쿨에어 [2018.07.27 14:04:04]

FACIL_NO : 시설물PK

MSRR_NO : 계측기PK

MESURE_KND_CD : 계측종류 (01: 기울기, 02: 온도, 03: 균열)

각 시설물의 기울기, 온도, 균열의 상태값을 구하는게 목표입니다. ㅜㅜ

by 우리집아찌 [2018.07.27 15:10:01]

TB_MESURE 에서 계측기 종류에 대해 알수가 없는데 어떻게 값을 찾아오나요?

by 쿨에어 [2018.07.27 15:24:03]

TB_MESURE 에 MSRR_NO가 있고

TB_MSRR 에 계측기종류가 있습니다.

by 우리집아찌 [2018.07.27 16:00:38]

TB_MESURE 만 보면 계측기 종류(MESURE_KND_CD ) 를 알수 없습니다.

MESURE_VALUE 값이 어느 계측기 값인지 알수없어요

컬럼이 더 필요해보이는데 빠진게 혹시 있는지요?

by 쿨에어 [2018.07.27 16:13:40]

TB_MESURE에 MSRR_NO(계측기번호)를 TB_MESURE 의 PK라고 헷갈리신건가요?

TB_MESURE에 MESURE_NO(계측값번호) 추가하여 샘플데이터 다시 만들었습니다...

  WITH TB_MSRR (MSRR_NO , FACIL_NO , MESURE_KND_CD , ATENT_NCL , RISK_NCL ) AS (
 SELECT 1 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 2 ,  11037 , '02' ,  40 , 50 FROM DUAL UNION ALL
 SELECT 3 ,  11037 , '03' , 1.5 ,  3 FROM DUAL UNION ALL
 SELECT 4 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 5 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 6 , 520533 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 7 , 520533 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 8 , 545512 , '01' , 2.5 ,  5 FROM DUAL  
 ) , TB_MESURE  (MESURE_NO , MSRR_NO , MESURE_VALUE , MESURE_DATE) AS (   
SELECT 48 , 8 , 2.1 , TO_DATE( '2018-07-27 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 47 , 8 , 2.7 , TO_DATE( '2018-07-28 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 46 , 8 , 2.8 , TO_DATE( '2018-07-29 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 45 , 8 , 1.1 , TO_DATE( '2018-07-30 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 44 , 1 , 5.6 , TO_DATE( '2018-07-31 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 43 , 2 , 43.4 , TO_DATE( '2018-08-01 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 42 , 2 , 58.1 , TO_DATE( '2018-08-02 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 41 , 2 , 38.4 , TO_DATE( '2018-08-03 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 40 , 2 , 55.8 , TO_DATE( '2018-08-04 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 39 , 2 , 37 , TO_DATE( '2018-08-05 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 38 , 2 , 39.1 , TO_DATE( '2018-08-06 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 37 , 2 , 43.8 , TO_DATE( '2018-08-07 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 36 , 2 , 56.1 , TO_DATE( '2018-08-08 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 35 , 2 , 21.4 , TO_DATE( '2018-08-09 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 34 , 2 , 50.3 , TO_DATE( '2018-08-10 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 33 , 2 , 54.1 , TO_DATE( '2018-08-11 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 32 , 2 , 41.5 , TO_DATE( '2018-08-12 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 31 , 3 , 1.3 , TO_DATE( '2018-08-13 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 30 , 2 , 30.5 , TO_DATE( '2018-08-14 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 29 , 1 , 1.2 , TO_DATE( '2018-08-15 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 28 , 3 , 3.1 , TO_DATE( '2018-08-16 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 27 , 1 , 5.6 , TO_DATE( '2018-08-17 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 26 , 2 , 40.15 , TO_DATE( '2018-08-18 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 25 , 2 , 25.15 , TO_DATE( '2018-08-19 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 24 , 1 , 2.52 , TO_DATE( '2018-08-20 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 23 , 1 , 2.51 , TO_DATE( '2018-08-21 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 22 , 7 , 1.4 , TO_DATE( '2018-08-22 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 21 , 7 , 4.4 , TO_DATE( '2018-08-23 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 20 , 6 , 4.8 , TO_DATE( '2018-08-24 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 19 , 7 , 5 , TO_DATE( '2018-08-25 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 18 , 4 , 3.9 , TO_DATE( '2018-08-26 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 17 , 8 , 3.3 , TO_DATE( '2018-08-27 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 16 , 6 , 7 , TO_DATE( '2018-08-28 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 15 , 7 , 3.7 , TO_DATE( '2018-08-29 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 14 , 4 , 4.3 , TO_DATE( '2018-08-30 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 13 , 8 , 1.1 , TO_DATE( '2018-08-31 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 12 , 6 , 2.9 , TO_DATE( '2018-09-01 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 11 , 4 , 6 , TO_DATE( '2018-09-02 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 10 , 8 , 5.4 , TO_DATE( '2018-09-03 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 9 , 6 , 6.5 , TO_DATE( '2018-09-04 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 4 , 6.6 , TO_DATE( '2018-09-05 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 7 , 8 , 4.5 , TO_DATE( '2018-09-06 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 6 , 8 , 4.3 , TO_DATE( '2018-09-07 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 5 , 8 , 3.8 , TO_DATE( '2018-09-08 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 4 , 8 , 7 , TO_DATE( '2018-09-09 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 3 , 8 , 2.7 , TO_DATE( '2018-09-10 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 6 , 5.3 , TO_DATE( '2018-09-11 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 1 , 4 , 4.7 , TO_DATE( '2018-09-12 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
 )

by 우리집아찌 [2018.07.27 13:48:53]

-- 위의 샘플데이터랑 출력데이터가 안맞는듯해서 임의로 만들었습니다.. 

 WITH TB_MSRR (MSRR_NO , FACIL_NO , MESURE_KND_CD , ATENT_NCL , RISK_NCL ) AS (
 SELECT 1 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 2 ,  11037 , '01' ,  40 , 50 FROM DUAL UNION ALL
 SELECT 3 ,  11037 , '01' , 1.5 ,  3 FROM DUAL UNION ALL
 SELECT 4 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 5 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 6 , 520533 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 7 , 520533 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 8 , 545512 , '01' , 2.5 ,  5 FROM DUAL  
 ) , TB_MESURE  (MSRR_NO , MESURE_VALUE , MESURE_DATE) AS (   
 SELECT 8 , 2.1 , TO_DATE( '2018-07-27 09:31:52' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 8 , 2.7 , TO_DATE( '2018-07-27 09:31:51' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 8 , 2.8 , TO_DATE( '2018-07-27 09:31:51' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 8 , 1.1 , TO_DATE( '2018-07-27 09:31:50' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 6 , 5.6 , TO_DATE( '2018-07-27 17:53:02' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,43.4 , TO_DATE( '2018-07-27 15:33:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,58.1 , TO_DATE( '2018-07-27 15:32:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,38.4 , TO_DATE( '2018-07-27 15:31:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,55.8 , TO_DATE( '2018-07-27 15:30:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,  37 , TO_DATE( '2018-07-27 15:29:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,39.1 , TO_DATE( '2018-07-27 15:28:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,43.8 , TO_DATE( '2018-07-27 15:27:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,56.1 , TO_DATE( '2018-07-27 15:26:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,21.4 , TO_DATE( '2018-07-27 15:25:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,50.3 , TO_DATE( '2018-07-27 15:24:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,54.1 , TO_DATE( '2018-07-27 15:23:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
 SELECT 2 ,41.1 , TO_DATE( '2018-07-27 15:22:21' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL  
 )


SELECT FACIL_NO
     , MAX(CASE WHEN RN = 1 THEN STATE_NM ELSE '-' END ) AS "MESURE_KND_CD = ''01''"
     , MAX(CASE WHEN RN = 2 THEN STATE_NM ELSE '-' END ) AS "MESURE_KND_CD = ''02''" 
     , MAX(CASE WHEN RN = 3 THEN STATE_NM ELSE '-' END ) AS "MESURE_KND_CD = ''03''" 
  FROM (SELECT FACIL_NO , STATE , STATE_NM , ROW_NUMBER() OVER(PARTITION BY FACIL_NO ORDER BY STATE ) RN
          FROM (SELECT B.FACIL_NO
                     , CASE WHEN A.MESURE_VALUE >  B.RISK_NCL  THEN 1
                            WHEN A.MESURE_VALUE >  B.ATENT_NCL THEN 2
                            ELSE 3 END STATE
                     , CASE WHEN A.MESURE_VALUE >  B.RISK_NCL  THEN '위험'
                            WHEN A.MESURE_VALUE >  B.ATENT_NCL THEN '경고'
                            ELSE '정상' END STATE_NM
                  FROM ( SELECT AA.*
                           FROM TB_MESURE AA
                        ) A
                     ,  TB_MSRR B   
                 WHERE A.MSRR_NO = B.MSRR_NO 
                )     
          GROUP BY FACIL_NO , STATE , STATE_NM
        ) 
  GROUP BY FACIL_NO
  ORDER BY FACIL_NO

by 쿨에어 [2018.07.27 14:15:01]

샘플데이터 올립니다. ㅠㅠ 늦게 올려 죄송합니다.

   WITH TB_MSRR (MSRR_NO , FACIL_NO , MESURE_KND_CD , ATENT_NCL , RISK_NCL ) AS (
 SELECT 1 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 2 ,  11037 , '02' ,  40 , 50 FROM DUAL UNION ALL
 SELECT 3 ,  11037 , '03' , 1.5 ,  3 FROM DUAL UNION ALL
 SELECT 4 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 5 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 6 , 520533 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 7 , 520533 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 8 , 545512 , '01' , 2.5 ,  5 FROM DUAL  
 ) , TB_MESURE  (MSRR_NO , MESURE_VALUE , MESURE_DATE) AS (   
SELECT 8 , 2.1 , TO_DATE( '2018-07-27 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 2.7 , TO_DATE( '2018-07-28 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 2.8 , TO_DATE( '2018-07-29 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 1.1 , TO_DATE( '2018-07-30 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 1 , 5.6 , TO_DATE( '2018-07-31 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 43.4 , TO_DATE( '2018-08-01 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 58.1 , TO_DATE( '2018-08-02 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 38.4 , TO_DATE( '2018-08-03 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 55.8 , TO_DATE( '2018-08-04 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 37 , TO_DATE( '2018-08-05 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 39.1 , TO_DATE( '2018-08-06 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 43.8 , TO_DATE( '2018-08-07 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 56.1 , TO_DATE( '2018-08-08 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 21.4 , TO_DATE( '2018-08-09 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 50.3 , TO_DATE( '2018-08-10 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 54.1 , TO_DATE( '2018-08-11 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 41.5 , TO_DATE( '2018-08-12 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 3 , 1.3 , TO_DATE( '2018-08-13 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 30.5 , TO_DATE( '2018-08-14 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 1 , 1.2 , TO_DATE( '2018-08-15 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 3 , 3.1 , TO_DATE( '2018-08-16 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 1 , 5.6 , TO_DATE( '2018-08-17 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 40.15 , TO_DATE( '2018-08-18 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 25.15 , TO_DATE( '2018-08-19 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 1 , 2.52 , TO_DATE( '2018-08-20 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 1 , 2.51 , TO_DATE( '2018-08-21 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 7 , 1.4 , TO_DATE( '2018-08-22 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 7 , 4.4 , TO_DATE( '2018-08-23 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 6 , 4.8 , TO_DATE( '2018-08-24 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 7 , 5 , TO_DATE( '2018-08-25 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 4 , 3.9 , TO_DATE( '2018-08-26 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 3.3 , TO_DATE( '2018-08-27 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 6 , 7 , TO_DATE( '2018-08-28 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 7 , 3.7 , TO_DATE( '2018-08-29 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 4 , 4.3 , TO_DATE( '2018-08-30 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 1.1 , TO_DATE( '2018-08-31 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 6 , 2.9 , TO_DATE( '2018-09-01 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 4 , 6 , TO_DATE( '2018-09-02 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 5.4 , TO_DATE( '2018-09-03 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 6 , 6.5 , TO_DATE( '2018-09-04 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 4 , 6.6 , TO_DATE( '2018-09-05 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 4.5 , TO_DATE( '2018-09-06 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 4.3 , TO_DATE( '2018-09-07 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 3.8 , TO_DATE( '2018-09-08 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 7 , TO_DATE( '2018-09-09 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 2.7 , TO_DATE( '2018-09-10 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 6 , 5.3 , TO_DATE( '2018-09-11 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 4 , 4.7 , TO_DATE( '2018-09-12 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
 )

by 우리집아찌 [2018.07.27 16:52:53]

  WITH TB_MSRR (MSRR_NO , FACIL_NO , MESURE_KND_CD , ATENT_NCL , RISK_NCL ) AS (
 SELECT 1 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 2 ,  11037 , '02' ,  40 , 50 FROM DUAL UNION ALL
 SELECT 3 ,  11037 , '03' , 1.5 ,  3 FROM DUAL UNION ALL
 SELECT 4 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 5 ,  11037 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 6 , 520533 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 7 , 520533 , '01' , 2.5 ,  5 FROM DUAL UNION ALL
 SELECT 8 , 545512 , '01' , 2.5 ,  5 FROM DUAL  
 ) , TB_MESURE  (MESURE_NO , MSRR_NO , MESURE_VALUE , MESURE_DATE) AS (   
SELECT 48 , 8 , 2.1 , TO_DATE( '2018-07-27 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 47 , 8 , 2.7 , TO_DATE( '2018-07-28 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 46 , 8 , 2.8 , TO_DATE( '2018-07-29 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 45 , 8 , 1.1 , TO_DATE( '2018-07-30 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 44 , 1 , 5.6 , TO_DATE( '2018-07-31 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 43 , 2 , 43.4 , TO_DATE( '2018-08-01 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 42 , 2 , 58.1 , TO_DATE( '2018-08-02 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 41 , 2 , 38.4 , TO_DATE( '2018-08-03 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 40 , 2 , 55.8 , TO_DATE( '2018-08-04 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 39 , 2 , 37 , TO_DATE( '2018-08-05 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 38 , 2 , 39.1 , TO_DATE( '2018-08-06 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 37 , 2 , 43.8 , TO_DATE( '2018-08-07 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 36 , 2 , 56.1 , TO_DATE( '2018-08-08 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 35 , 2 , 21.4 , TO_DATE( '2018-08-09 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 34 , 2 , 50.3 , TO_DATE( '2018-08-10 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 33 , 2 , 54.1 , TO_DATE( '2018-08-11 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 32 , 2 , 41.5 , TO_DATE( '2018-08-12 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 31 , 3 , 1.3 , TO_DATE( '2018-08-13 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 30 , 2 , 30.5 , TO_DATE( '2018-08-14 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 29 , 1 , 1.2 , TO_DATE( '2018-08-15 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 28 , 3 , 3.1 , TO_DATE( '2018-08-16 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 27 , 1 , 5.6 , TO_DATE( '2018-08-17 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 26 , 2 , 40.15 , TO_DATE( '2018-08-18 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 25 , 2 , 25.15 , TO_DATE( '2018-08-19 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 24 , 1 , 2.52 , TO_DATE( '2018-08-20 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 23 , 1 , 2.51 , TO_DATE( '2018-08-21 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 22 , 7 , 1.4 , TO_DATE( '2018-08-22 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 21 , 7 , 4.4 , TO_DATE( '2018-08-23 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 20 , 6 , 4.8 , TO_DATE( '2018-08-24 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 19 , 7 , 5 , TO_DATE( '2018-08-25 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 18 , 4 , 3.9 , TO_DATE( '2018-08-26 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 17 , 8 , 3.3 , TO_DATE( '2018-08-27 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 16 , 6 , 7 , TO_DATE( '2018-08-28 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 15 , 7 , 3.7 , TO_DATE( '2018-08-29 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 14 , 4 , 4.3 , TO_DATE( '2018-08-30 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 13 , 8 , 1.1 , TO_DATE( '2018-08-31 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 12 , 6 , 2.9 , TO_DATE( '2018-09-01 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 11 , 4 , 6 , TO_DATE( '2018-09-02 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 10 , 8 , 5.4 , TO_DATE( '2018-09-03 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 9 , 6 , 6.5 , TO_DATE( '2018-09-04 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8 , 4 , 6.6 , TO_DATE( '2018-09-05 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 7 , 8 , 4.5 , TO_DATE( '2018-09-06 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 6 , 8 , 4.3 , TO_DATE( '2018-09-07 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 5 , 8 , 3.8 , TO_DATE( '2018-09-08 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 4 , 8 , 7 , TO_DATE( '2018-09-09 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 3 , 8 , 2.7 , TO_DATE( '2018-09-10 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2 , 6 , 5.3 , TO_DATE( '2018-09-11 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL UNION ALL
SELECT 1 , 4 , 4.7 , TO_DATE( '2018-09-12 09:31:52' , 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
 )

SELECT FACIL_NO
     , MAX(CASE WHEN MESURE_KND_CD = '01' THEN STATE_NM END ) AS MESURE_KND_CD_01
     , MAX(CASE WHEN MESURE_KND_CD = '02' THEN STATE_NM END ) AS MESURE_KND_CD_02
     , MAX(CASE WHEN MESURE_KND_CD = '03' THEN STATE_NM END ) AS MESURE_KND_CD_03
  FROM (SELECT B.FACIL_NO
             , A.MESURE_NO 
             , A.MSRR_NO 
             , A.MESURE_VALUE 
             , A.MESURE_DATE
             , B.MESURE_KND_CD
             , CASE WHEN A.MESURE_VALUE > B.RISK_NCL  THEN 2 
                    WHEN A.MESURE_VALUE > B.ATENT_NCL THEN 1 
                    ELSE 3
                END STATE
              , CASE WHEN A.MESURE_VALUE > B.RISK_NCL THEN '위험' 
                    WHEN A.MESURE_VALUE  > B.ATENT_NCL THEN '경고' 
                    ELSE '정상'
                END STATE_NM
          FROM ( SELECT AA.*
                     , ROW_NUMBER() OVER(PARTITION BY MSRR_NO ORDER BY AA.MESURE_DATE DESC )RN 
                  FROM TB_MESURE AA
                ) A
              , TB_MSRR B
         WHERE A.MSRR_NO = B.MSRR_NO
           AND A.RN      = 1       
        ) 
 GROUP BY FACIL_NO

by 쿨에어 [2018.07.27 18:00:42]

감사합니다~!!

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변