1 / 23

SQL 조회 도움말

SQL 조회 도움말. 2011.11.02. 목차. SQL 조회 사용안내 SQL 설명 SQL 응용. SQL 이란 ?. 정의 : Structured Query Language - Data : 존재 목적이 뚜렷하고 유용성 있는 운영 자료  - DDL(Data Definition Language) : create, alter, drop, grant … - DML(Data Manipulation Language) : select , delete, insert …

orsen
Download Presentation

SQL 조회 도움말

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL 조회 도움말 2011.11.02

  2. 목차 • SQL조회 사용안내 • SQL 설명 • SQL 응용

  3. SQL 이란? 정의 : Structured Query Language - Data : 존재 목적이 뚜렷하고 유용성 있는 운영 자료  - DDL(Data Definition Language) : create, alter, drop, grant … - DML(Data Manipulation Language) : select, delete, insert … - DCL(Data Control Language) : commit, rollback … select 구문 형식 - select field_1[, field_2 …] from table명 [where 조건절] [group by 절] [order by 절] * [] 부분은 생략 가능 select 예시 select stn_id, tma, avg_ta from DB_SFC_TA_DD where stn_id in (90) group by stn_id order by stn_id

  4. SQL 변수 • number : 숫자형 : 정수, 실수 • - 정수 : 지점번호 : stn_id number(10) • - 실수 : 위도, 경도, 기온, 습도, 강수 … • 기온 : avg_ta number(7,2) • char : 문자형 : 고정길이 • - avg_ta_qcflg char(1) • varchar2 : 문자형 : 가변길이 • - stn_nm varchar2(30) • date : 날짜형 • 시각: tma date • 종료시간: clse_tma date

  5. SQL 함수 to_char : 날짜, 숫자 등 : 특정 형식으로 변환 to_char(.5, '90.9‘), to_char(avg(ta),’9990.9’) ta to_char(tma,'yyyy-mm-dd') yyyymmdd, to_char(avg_ta,'9990.9') ta round : 숫자 : 반올림 처리 round(45.8), round(3.45,1) round(2.356,2), round(hm) hm trim : 공백제거 trim(‘ ab’), trim(‘ 3.4 ‘) nvl : 결과값이 NULL 일 경우 처리 정의 nvl(rn_day,0) : rn_day : 값이 있으면[rn_day], 값이 NULL[0] 으로 처리 주석 처리 -- 라인 끝까지 주석 /* */ : 여러 라인 또는 임의 부분 설정 주석

  6. SQL 함수 avg, sum, min, max, count : 그룹함수 round(case when avg(ta)<0 then avg(ta)+0.01 else avg(ta) end,1) ta, max(ta_max) ta_max, min(ta_min) ta_min, count(*) cnt_1 : 레코드단위, count(ta) cnt_2 : 필드단위 decode : 단일함수 decode(soon,0,avg_ta) avg_ta0 : soon 이 0이면 avg_ta 그렇지 않으면 NULL case 문 case when 조건절 then 결과1 [when 조건절 then 결과2] [else 결과] end * [] 부분은 생략 가능 casewhento_char(tm,'dd') <= 10 then 1 when to_char(tm,'dd') <= 20 then 2 else 3 end sun : 날짜를 기준으로 순(상순, 중순, 하순) 결정  sun 변수에 적재

  7. SQL 함수 분석함수(해석함수) : 오라클에만 존재 row_number()over(partition by stn_id, yyyy order bystn_id, yyyy) rk : 지점, 년도별로 자료를 나누어서 지점,연도별로 rank를 설정 : rank 값은 1부터 1씩 증가함 이전/이후 레코드 참조 lag(tm) , lag(tm,0), lag(ta,3), lag(tm,1) : 이전 레코드 lead(tm), lead(tm,0), lead(ta_min,5), lead(tm,1) : 다음 레코드 lag(tm) over(partition by stn_id order by stn_id, tm) tm_b, lag(ta_min) over(partition by stn_id order by stn_id, tm) ta_min_b select stn_id, tm, ta, lag(ta), lead(ta) from sfc_day_ta where stn_id in (90,108) and tm >= to_date(20000101,'yyyymmdd') and tm <= to_date(20001231,'yyyymmdd') sum(rn_day) over(partition by stn_id ORDER BY tm) rn_day select stn_id, tm, rn_day, sum(rn_day) over(partition by stn_id ORDER BY tm) rn_day from sfc_day_rn where stn_id in (90,108) and tm >= to_date(20000101,'yyyymmdd') and tm <= to_date(20001231,'yyyymmdd‘)

  8. SELECT alias field, table, 결과 set 에 대한 alias with aaa as -- 결과 setalias ( select stn_id, mm, sun, round(avg(ps),1) ps from ( select stn_id, yyyy, mm, sun, round(avg(ps),1) ps -- 필드 alias from ( select stn_id, to_char(tm,'yyyy') yyyy, to_char(tm,'mm') mm, to_char(tm,'dd') dd, ps, case when to_char(tm,'dd') <= 10 then 1 when to_char(tm,'dd') <= 20 then 2 else 3 end SUN from sfc_day_ps where stn_id in (90) and tm >= to_date(20000101,'yyyymmdd') and tm <= to_date(20001231,'yyyymmdd') ) group by stn_id, yyyy, mm, sun ) group by stn_id, mm, sun ) select A.stn_id, A.mm, A.sun, to_char(max(B.ps)*10) ps from ( select stn_id, yyyy, mm, sun, round(avg(ps),1) ps from ( select stn_id, to_char(tm,'yyyy') yyyy, to_char(tm,'mm') mm, to_char(tm,'dd') dd, ps, case when to_char(tm,'dd') <= 10 then 1 when to_char(tm,'dd') <= 20 then 2 else 3 end sun from sfc_day_ps where stn_id in (90) and tm >= to_date(20000101,'yyyymmdd') and tm <= to_date(20001231,'yyyymmdd') ) group by stn_id, yyyy, mm, sun ) A, aaa B-- 결과 set 및 테이블 alias where A.stn_id = B.stn_id and A.mm = B.mm and A.sun = B.sun group by A.stn_id, A.mm, A.sun order by A.stn_id, A.mm, A.sun

  9. SELECT 기본 지점정보 추출 1. select * from DB_COMN_INTG_OBS_STN_DTL 2. select stn_id, stn_nmfrom DB_COMN_INTG_OBS_STN_DTL where OBS_SPCLT_CD = 'DB001001' /*지상지점만 조회*/ and CLSE_TMA > SYSDATE and stn_id in (90, 108) /*특정지점만 조회*/ order by stn_id /*지점번호로 정렬*/ 기온추출 select stn_id, round(case when avg(ta) < 0 then avg(ta)+0.01 else avg(ta) end,1) ta from sfc_day_ta where stn_id in (90,108,133) and tm >= to_date('2010.10.01','yyyy.mm.dd') and tm <= add_months(to_date('2010.10.01','yyyy.mm.dd'),1) group by stn_id

  10. SELECT 기본 • 날짜 및 인덱스 생성 방법 • - 00 ~ 23 : 24시간 생성 • select to_char(tm,'hh24') tm • from ( • select to_date(20100101,'yyyymmdd') + level/24 - 1/24 tm • from dual • connect by level < to_date(20100101,'yyyymmdd')+24 - to_date(20100101,'yyyymmdd') + 1 • ) • - 20100101 ~ 20100131 : 2010년 1월 날짜 생성 • select to_char(tm,'yyyymmdd') tm • from ( • select to_date(20100101,'yyyymmdd') + level-1 tm • from dual • connect by level < to_date(20100201,'yyyymmdd') - to_date(20100101,'yyyymmdd') + 1 • ) • - 1 ~ 100 : 인덱스 생성 • select rownum from sfc_day_ta where rownum < 101

  11. SQL 조인 union : 합집합 : 중복 제거 select 1 a from dual union select 2 a from dual union select 1 a from dual union all : 중복 포함 select stn_id, to_char(tm,'yyyymmdd') tm, max(ta) ta, max(hm) hm from ( select stn_id, tm ,ta,null hm from SFC_DAY_TA where tm >= to_date('2010.11.03','yyyy.mm.dd') and tm <= to_date('2010.11.03','yyyy.mm.dd') and stn_id in (90) union all select stn_id, tm ,null ta,hm from SFC_DAY_HM where tm >= to_date('2010.11.03','yyyy.mm.dd') and tm <= to_date('2010.11.03','yyyy.mm.dd') and stn_id in (90) ) group by stn_id, to_char(tm,'yyyymmdd') order by stn_id, tm EQI 조인 : [=] select A.stn_id, A.stn_ko, B. ta from ( select stn_id, stn_ko from stn_sfc where stn_id in (90,108) and tm_ed > sysdate ) A, ( select stn_id, ta from sfc_day_ta where stn_id in (90,108) and tm > sysdate - 2 ) B where A.stn_id = B.stn_id

  12. SQL 조인 outer join : [+] : 기온과 강수 또는 시간과 강수(무강수 포함) with dd as ( select tm from ( select to_date('2010.11.03','yyyy.mm.dd') + level - 1 tm from dual connect by level <= to_date('2010.11.03','yyyy.mm.dd') - to_date('2010.11.03', 'yyyy.mm.dd') + 1 ) ), tm as ( select B.stn_id, A.tm from dd A, ( select stn_id from stn_sfc where stn_id in (90) and tm_ed > sysdate ) B ) select A.stn_id, to_char(A.tm,'yyyymmdd') tm, B.* from tm A, ( select stn_id, to_char(tm,'yyyymmdd') tm, max(rn_day) rn_day from ( select stn_id, tm ,rn_day from SFC_DAY_RN where tm >= to_date('2010.11.03','yyyy.mm.dd') and tm <= to_date('2010.11.03','yyyy.mm.dd') and stn_id in (90) ) group by stn_id, to_char(tm,'yyyymmdd') ) B where A.stn_id = B.stn_id(+) and A.tm = B.tm(+) order by A.stn_id, A.tm

  13. SQL 조인 • 카테시안곱 : 데이터복제 : where 조건절이 없음 • select A.tm+B.hh/24 • from ( select to_date(20100101,'yyyymmdd')+rownum-1 tm • from sfc_day_ta where rownum < 100 ) A, • ( select rownum-1 hh from stn_sfc where rownum < 25 ) B • select A.tm, B.stn_id • from ( select to_date(20100101,'yyyymmdd')+rownum-1 tm • from sfc_day_ta where rownum < 32 ) A, • ( select stn_id from stn_sfc where stn_id in (90,108) and tm_ed > sysdate ) B • 기온, 강수, 습도 등 테이블 조인 방안 • 1안 : 기온 union all 강수 union all 습도 • 2안 : 기온, 강수, 습도를 기온을 기준으로 outer join • 일자, 기온, 강수, 습도 등 테이블 조인 방안 • 1안 : 일자(지점, 날짜)와 (기온 union all 강수 union all 습도)의 outer join • 2안 : 일자와 (기온, 강수, 습도를 기온을 기준으로 outer join)의 outer join

  14. SQL 그룹핑 group by select yyyy, round(case when avg(ta) < 0 then avg(ta)+0.01 else avg(ta) end,1) ta from ( select stn_id, yyyy, mm, sun, round(case when avg(ta) < 0 then avg(ta)+0.01 else avg(ta) end,1) ta from ( select stn_id, to_char(tm,'yyyy') yyyy, to_char(tm,'mm') mm, case when to_char(tm,'dd') <= 10 then 1 when to_char(tm,'dd') <= 20 then 2 else 3 end sun, ta from sfc_day_ta where tm >= to_date(20000101,'yyyymmdd') and tm <= to_date(20101231,'yyyymmdd') and to_number(to_char(tm,'mm')) = 1 and stn_id in (90,108) ) where sun = 1 group by stn_id, yyyy, mm, sun ) group by yyyy order by yyyy

  15. SQL 응용 1. 기본 자료 추출 select stn_id, tm ,ta from SFC_TM_OBS where tm >= to_date('2010.11.01','yyyy.mm.dd')+1/24 and tm <= to_date('2010.11.03','yyyy.mm.dd')+1 and stn_id in (90,108) 2. 세로  가로로 배열 select stn_id, to_char(tm,'yyyymmdd') tm, decode(hh,'00',ta) ta_01_시, decode(hh,'01',ta) ta_02_시, decode(hh,'02',ta) ta_03_시 from ( select stn_id, tm-1/24 tm, to_char(tm-1/24,'hh24') hh, max(ta) ta from ( select stn_id, tm ,ta from SFC_TM_OBS where tm >= to_date('2010.11.01','yyyy.mm.dd')+1/24 and tm <= to_date('2010.11.03','yyyy.mm.dd')+1 and stn_id in (90,108) ) group by stn_id, tm-1/24 )

  16. SQL 응용 3. 일자별 시간자료  가로로 배열 : sum(decode) select stn_id, to_char(tm,'yyyymmdd') tm, sum(decode(hh,'00',ta)) ta_01_시, sum(decode(hh,'01',ta)) ta_02_시, sum(decode(hh,'02',ta)) ta_03_시, sum(decode(hh,'03',ta)) ta_04_시, sum(decode(hh,'04',ta)) ta_05_시 -- decode 결과값이 숫자가 아닐 경우  min 또는 max를 사용할것 -- max(decode(hh,'00',ta)) ta_01_시, -- max(decode(hh,'01',ta)) ta_02_시, -- max(decode(hh,'02',ta)) ta_03_시, -- max(decode(hh,'03',ta)) ta_04_시, -- max(decode(hh,'04',ta)) ta_05_시 from ( select stn_id, tm-1/24 tm, to_char(tm-1/24,'hh24') hh, max(ta) ta from ( select stn_id, tm ,ta from SFC_TM_OBS where tm >= to_date('2010.11.01','yyyy.mm.dd')+1/24 and tm <= to_date('2010.11.03','yyyy.mm.dd')+1 and stn_id in (90,108) ) group by stn_id, tm-1/24 ) group by stn_id, to_char(tm,'yyyymmdd') order by stn_id, tm

  17. SQL 응용 max(decode) 사용 예시 : 1/6 with dat_buoy as ( select stn_id, to_date(to_char(tm-1/24,'yyyymmdd'),'yyyymmdd') tm, -- 날짜 : [년월일] to_char(tm-1/24,'mm') mm, -- 월 pa, -- 기압 hm, -- 습도 ta, -- 기온 tw, -- 수온 tm tm3, -- 날짜 : [년월일 01:00 ~ 00:00] tm-1/24 tm2, -- 날짜 : [년월일 00:00 ~ 23:00] floor((wo+11.25)/22.5) wd_, -- 풍향 : 16방위 : (0~17) : [N,NNE ... NNW,N] mod(floor((wo+11.25)/22.5),16) wd__, -- 풍향 : 16방위 : (0~15) : [N,NNE ... NNW] mod((mod(floor((wo+11.25)/22.5),16)+15),16)+1 wd2, -- 풍향 : 16방위 : (1~16) : [NNE,NE ... NNW,N] floor((wo+11.25)/22.5) wo_, -- 파향 : 16방위 : (0~17) : [N,NNE ... NNW,N] mod(floor((wo+11.25)/22.5),16) wo__, -- 파향 : 16방위 : (0~15) : [N,NNE ... NNW] mod((mod(floor((wo+11.25)/22.5),16)+15),16)+1 wo2, -- 파향 : 16방위 : (1~16) : [NNE,NE ... NNW,N] to_char(tm-1/24,'dd') dd -- 일자 : from sea_buoy where tm > to_date(20090101,'yyyymmdd') and tm < to_date(20091231,'yyyymmdd')+1+1/24 and stn_id in (22101,22102,22103,22104,22105,22106,22107,22108) ),

  18. SQL 응용 max(decode) 사용 예시 : 2/6 aaa_buoy as ( select stn_id, round(avg(pa),1) pa, round(avg(hm)) hm, round(case when avg(ta) < 0 then avg(ta)+0.01 else avg(ta) end,1) ta, round(case when avg(tw) < 0 then avg(tw)+0.01 else avg(tw) end,1) tw from ( select stn_id, mm, round(avg(pa),1) pa, round(avg(hm)) hm, round(case when avg(ta) < 0 then avg(ta)+0.01 else avg(ta) end,1) ta, round(case when avg(tw) < 0 then avg(tw)+0.01 else avg(tw) end,1) tw from ( select stn_id, mm, tm, round(avg(pa),1) pa, -- 기압 round(avg(hm)) hm, -- 습도 round(case when avg(ta) < 0 then avg(ta)+0.01 else avg(ta) end,1) ta, -- 기온 round(case when avg(tw) < 0 then avg(tw)+0.01 else avg(tw) end,1) tw -- 수온 from dat_buoy where to_char(tm3,'hh24') in ('03','06','09','12','15','18','21','00') group by stn_id, mm, tm ) group by stn_id, mm ) group by stn_id ),

  19. SQL 응용 max(decode) 사용 예시 : 3/6 -- 극값/발생일자 bbb_buoy as ( select stn_id, mm, pa, hm, ta, tw, tm, row_number() over(partition by stn_id order by (case when ta is null then -999 else ta end) desc) rk_ta_2, -- 기온 : 최고 row_number() over(partition by stn_id order by (case when tw is null then -999 else tw end) desc) rk_tw_2, -- 수온 : 최고 row_number() over(partition by stn_id order by ta, tm desc) rk_ta_4, -- 기온 : 최저 row_number() over(partition by stn_id order by tw, tm desc) rk_tw_4 -- 수온 : 최저 from dat_buoy ),

  20. SQL 응용 max(decode) 사용 예시 : 4/6 -- 통합 kkk_buoy as ( -- 기압 : 평균 select stn_id, 1 inx, to_char(pa*10) val from aaa_buoy union all -- 습도 : 평균 select stn_id, 1+1 inx, to_char(hm) val from aaa_buoy union all -- 기온 : 평균, 최고, 나타난날, 최저, 나타난날 select stn_id, 1+2 inx, to_char(ta*10) val from aaa_buoy union all select stn_id, 2+2 inx, to_char(ta*10) val from bbb_buoy where rk_ta_2 = 1 union all select stn_id, 3+2 inx, to_char(tm,'mm/dd') val from bbb_buoy where rk_ta_2 = 1 union all select stn_id, 4+2 inx, to_char(ta*10) val from bbb_buoy where rk_ta_4 = 1 union all select stn_id, 5+2 inx, to_char(tm,'mm/dd') val from bbb_buoy where rk_ta_4 = 1 union all -- 수온 : 평균, 최고, 나타난날, 최저, 나타난날 select stn_id, 1+7 inx, to_char(tw*10) val from aaa_buoy union all select stn_id, 2+7 inx, to_char(tw*10) val from bbb_buoy where rk_tw_2 = 1 union all select stn_id, 3+7 inx, to_char(tm,'mm/dd') val from bbb_buoy where rk_tw_2 = 1 union all select stn_id, 4+7 inx, to_char(tw*10) val from bbb_buoy where rk_tw_4 = 1 union all select stn_id, 5+7 inx, to_char(tm,'mm/dd') val from bbb_buoy where rk_tw_4 = 1 ),

  21. SQL 응용 max(decode) 사용 예시 : 5/6 inx_buoy as ( select A.stn_id, A.stn_ko, B.inx from ( select stn_id, stn_ko from stn_buoy where stn_id in (22101,22102,22103,22104,22105,22106,22107,22108) ) A, ( select rownum inx from sea_buoy where rownum < 13 ) B ), lll_buoy as ( select A.stn_id, A.stn_ko, A.inx, B.val from inx_buoy A, kkk_buoy B where A.stn_id = B.stn_id(+) and A.inx = B.inx(+) )

  22. SQL 응용 max(decode) 사용 예시 : 6/6 select stn_id, max(stn_ko) stn_ko, max(decode(inx, 1,val)) inx_01, max(decode(inx, 2,val)) inx_02, max(decode(inx, 3,val)) inx_03, max(decode(inx, 4,val)) inx_04, max(decode(inx, 5,val)) inx_05, max(decode(inx, 6,val)) inx_06, max(decode(inx, 7,val)) inx_07, max(decode(inx, 8,val)) inx_08, max(decode(inx, 9,val)) inx_09, max(decode(inx,10,val)) inx_10, max(decode(inx,11,val)) inx_11, max(decode(inx,12,val)) inx_12 from lll_buoy group by stn_id order by stn_id

  23. 감사합니다

More Related