|
안녕하세요
즐거운 추석 연휴 보내시고 있으신가요?
A테이블의 코드 값에 맞는 정보를 다른 3개의 테이블에서 각각 최신 한건씩 가져와야하는데요
----------------------------
CREATE TABLE TB_A
(
CD VARCHAR(10) PRIMARY KEY
,CD_NM VARCHAR(20) NOT NULL
,YN VARCHAR(1)
,LAT VARCHAR(10) -- 위도
,LON VARCHAR(10) -- 경도
);
CREATE TABLE TB_B
(
CD VARCHAR(10) NOT NULL
,B_YMDH VARCHAR(10)
,B_VALUE VARCHAR(7)
,RG_DT DATE
);
인덱스 (CD + B_YMDH) 유니크아님
CREATE TABLE TB_C
(
CD VARCHAR(10) NOT NULL
,C_YMDH1 VARCHAR(10)
,C_VALUE1 VARCHAR(7)
,C_YMDH2 VARCHAR(10)
,C_VALUE2 VARCHAR(7)
,RG_DT DATE
);
인덱스 (CD + C_YMDH1) 유니크아님
CREATE TABLE TB_D
(
CD VARCHAR(10) NOT NULL
,D_YMDH1 VARCHAR(10)
,D_VALUE1 VARCHAR(7)
,D_YMDH2 VARCHAR(10)
,D_VALUE2 VARCHAR(7)
,RG_DT DATE
);
인덱스 (CD + D_YMDH1) 유니크아님
-------------------------------
1. 위도 경도로 A테이블에서 가장 가까운 코드로 B,C,D테이블의 가장 최근/최신 데이터 한 건을 가져와야 합니다.
2. 최근 데이터 여부는 YMDH(YYYYMMDDHH24)가 가장 큰값이면서 RG_DT가 가장 최근이여야합니다.
3. 최종으로 필요한 값은 A테이블의 코드명과 B,C,D테이블의 VALUE들 입니다.
질문1. A테이블의 건수는 3500여건 고정인데요. 가장 가까운 좌표의 코드를 가져오는데 가끔 살짝 느릴때가 있습니다. 안느리게 하려면 어떻게 수정을 해야 할까요.?
WITH TB_A AS
(
SELECT '1' CD, '제기동 CD_NM, 'Y' YN, '37.58308' LAT, '127.03778 ' LON FROM dual
UNION ALL SELECT '2', '정릉동, 'Y', '37.59864', '127.01324 ' FROM dual
UNION ALL SELECT '3', '쌍문동, 'Y', '37.64525 ', '127.02818 ' FROM dual
)
SELECT CD, CD_NM, LAT, LON
FROM ( SELECT CD, CD_NM, LAT, LON
, SQRT(POWER(69.1 * (LAT - 37.585398), 2) +
POWER(69.1 * (127.040405 - LON) * COS(LAT / 57.3), 2)
) AS distance
FROM TB_A
WHERE YN = 'Y'
ORDER BY distance
)
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 12 | 00:00:01 |
| * 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 3774 | 366078 | 12 | 00:00:01 |
| * 3 | SORT ORDER BY STOPKEY | | 3774 | 196248 | 12 | 00:00:01 |
| 4 | TABLE ACCESS FULL | TB_A | 3774 | 196248 | 11 | 00:00:01 |
-----------------------------------------------------------------------------------
질문2. 필요한 값을 한번에 다 가져오고 싶었는데 잘안되서 A를 한번 조회한 결과로 B,C,D를 조회해야하나요? 혹시 한번에 전부 조회할 수 있는 방법이 있나요?
WITH TB_B AS
(
SELECT '1' CD, '2016091501' B_YMDH, 'B10' B_VALUE, TO_DATE('20160915010000','YYYYMMDDHH24MISS') RG_DT FROM dual
UNION ALL SELECT '2', '2016091501', 'B20', TO_DATE('20160915010000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '3', '2016091501', 'B30', TO_DATE('20160915010000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '1', '2016091502', 'B40', TO_DATE('20160915020000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '2', '2016091502', 'B50', TO_DATE('20160915020000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '3', '2016091502', 'B60', TO_DATE('20160915020000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '1', '2016091502', 'B41', TO_DATE('20160915021000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '2', '2016091502', 'B51', TO_DATE('20160915021000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '3', '2016091502', 'B61', TO_DATE('20160915021000','YYYYMMDDHH24MISS') FROM dual
)
, TB_C AS
(
SELECT '1' CD, '2016091501' C_YMDH1, 'C10' C_VALUE1, '2016091502' C_YMDH2, 'C40' C_VALUE2, TO_DATE('20160915010000','YYYYMMDDHH24MISS') RG_DT FROM dual
UNION ALL SELECT '2', '2016091501', 'C20', '2016091502', 'C50', TO_DATE('20160915010000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '3', '2016091501', 'C30', '2016091502', 'C60', TO_DATE('20160915010000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '1', '2016091502', 'C40', '2016091503', 'C70', TO_DATE('20160915020000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '2', '2016091502', 'C50', '2016091503', 'C80', TO_DATE('20160915020000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '3', '2016091502', 'C60', '2016091503', 'C90', TO_DATE('20160915020000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '1', '2016091502', 'C71', '2016091503', 'C71', TO_DATE('20160915021000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '2', '2016091502', 'C81', '2016091503', 'C81', TO_DATE('20160915021000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '3', '2016091502', 'C91', '2016091503', 'C91', TO_DATE('20160915021000','YYYYMMDDHH24MISS') FROM dual
)
, TB_D AS
(
SELECT '1' CD, '2016091501' D_YMDH1, 'D10' D_VALUE1, '2016091502' D_YMDH2, 'D40' D_VALUE2, TO_DATE('20160915010000','YYYYMMDDHH24MISS') RG_DT FROM dual
UNION ALL SELECT '2', '2016091501', 'D20', '2016091502', 'D50', TO_DATE('20160915010000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '3', '2016091501', 'D30', '2016091502', 'D60', TO_DATE('20160915010000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '1', '2016091502', 'D40', '2016091503', 'D70', TO_DATE('20160915020000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '2', '2016091502', 'D50', '2016091503', 'D80', TO_DATE('20160915020000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '3', '2016091502', 'D60', '2016091503', 'D90', TO_DATE('20160915020000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '1', '2016091502', 'D71', '2016091503', 'D71', TO_DATE('20160915021000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '2', '2016091502', 'D81', '2016091503', 'D81', TO_DATE('20160915021000','YYYYMMDDHH24MISS') FROM dual
UNION ALL SELECT '3', '2016091502', 'D91', '2016091503', 'D91', TO_DATE('20160915021000','YYYYMMDDHH24MISS') FROM dual
)
SELECT B.*, C.*, D.*
FROM ( SELECT B_YMDH, B_VALUE
FROM ( SELECT B_YMDH, B_VALUE
FROM TB_B
WHERE CD = '1'
AND B_YMDH = (SELECT MAX(B_YMDH) FROM TB_B WHERE CD = '1')
ORDER BY RG_DT DESC )
WHERE ROWNUM = 1
) B
,( SELECT C_YMDH1, C_VALUE1, C_YMDH2, C_VALUE2
FROM ( SELECT C_YMDH1, C_VALUE1, C_YMDH2, C_VALUE2
FROM TB_C
WHERE CD = '1'
AND C_YMDH1 = (SELECT MAX(C_YMDH1) FROM TB_C WHERE CD = '1')
ORDER BY RG_DT DESC )
WHERE ROWNUM = 1
) C
,( SELECT D_YMDH1, D_VALUE1, D_YMDH2, D_VALUE2
FROM ( SELECT D_YMDH1, D_VALUE1, D_YMDH2, D_VALUE2
FROM TB_D
WHERE CD = '1'
AND D_YMDH1 = (SELECT MAX(D_YMDH1) FROM TB_D WHERE CD = '1')
ORDER BY RG_DT DESC )
WHERE ROWNUM = 1
|
