본문 바로가기

IT Story/SQL Story

 (26)
국가 통계 데이터 분석하기: 주택 매매 데이터로 분석하기 주택 매매가 지수란 전국 시/군/구의 주택을 모집단으로 해당 지역 아파트 평균가를 기준값인 100으로 잡고, 이후 상승 또는 하락 정도를 쉽게 알 수 있도록 측정한 값이다. 다운로드 경로 kossi.kr > 주거 > 전국주택가격동향조사 > 전국주택가격동향(기준원:2021.06) > 규모별 매매가격지수 01. 주택 매매 데이터 분석을 시작하기 전에 파일 살피기 SELECT * FROM house_city_code -- 지역 메타 데이터 지역별 code_value 수도권 서울, 경기, 인천 지방 수도권을 제외한 전 지역 6대광역시 부산, 대구, 광주, 대전, 울산, 인천 5대광역시 부산, 대구, 광주, 대전, 울산 9개도 경기, 강원, 충북, 충남, 전북, 전남, 경북, 경남, 제주 8개도 강원, 충북, 충..
국가 통계 데이터 분석하기: 행정 구역별 남녀 비율 구하기 01. 2020년 시/군/구 단위의 남녀 인구수와 성비가 큰 도시순으로 정렬하기 SELECT * FROM( SELECT *, ABS(m_ratio - f_ratio) AS diff_ratio FROM( SELECT [C행정구역별(읍면동)] AS c_city, [행정구역별(읍면동)] AS city, [총인구 (명)] AS population, [남자 (명)] AS m_population, [여자 (명)] AS f_population, CONVERT(DECIMAL(18,2), [총인구 (명)] - [남자 (명)]/[총인구 (명)] * 100) As m_ratio, CONVERT(DECIMAL(18,2), [총인구 (명)] - [여자 (명)]/[총인구 (명)] * 100) As f_ratio FROM cens..
국가 통계 데이터 분석하기: 연도별 인구 증감 분석하기 01. 행정 구역 코드로 전체 인구의 연도별 증감, 증감률 검색하기 SELECT a.[시점] AS a_year, a.[총인구 (명)] AS a_population, b.[시점] AS b_year, b.[총인구 (명)] AS b_population, b.[총인구 (명)] - a.[총인구 (명)] AS diff_population, CONVERT(DECIMAL(18,2), (b.[총인구 (명)] - a.[총인구 (명)] )/a.[총인구 (명)] * 100) AS diff_ratio FROM census_2015_2020 AS a LEFT OUTER JOIN census_2015_2020 AS b ON a.[C행정구역별(읍면동)] = b.[C행정구역별(읍면동)] AND a.[시점] = b.[시점] -1 WHE..
국가 통계 데이터 분석하기: 행정 구역별 인구 분석하기 kosis.kr에 접속 01. 행정 구역별 인구 데이터 내려받기 -csv 형태로 저장된 데이터 파일 확인하기 02. SQL Server로 데이터 가져오기 전에 사전 작업하기 - 데이터를 가져오는 작업을 ETL이라 한다. 다운로드한 파일을 열고 다른 이름으로 저장한다[Excel97-2003 통합문서(*.xls)] 03. SSMS에서 엑셀 파일을 읽어 데이터베이스로 가져오기 04. TOP N문으로 상위 10개 데이터 확인하기 SELECT TOP 10 * FROM census_2015_2020 05. 행정구역 정의 확인하기 SELECT [C행정구역별(읍면동)], --한글이므로 대괄호 사용 [행정구역별(읍면동)] FROM census_2015_2020 GROUP BY [C행정구역별(읍면동)], [행정구역별(읍면..
5일, 20일 가격 이동 평균 구하기 이동 평균선은 일정 기간에 주가를 산술 평균한 값을 모두 연결해 선으로 표현한 것이다. 보통 5일, 20일, 60일, 120일 범위로 이동 평균선을 보여준다. 01. 현재 날짜로부터 5일, 20일 이동 평균값 구하기 stock 테이블에서 symbol = 'MSFT"인 일별 주가 데이터에서 현재 날짜로부터 5일 이동 평균값과 20일 이동 평균값을 구한다. a 테이블 기준으로 5일 범위의 b 테이블과 20일 범위의 c 테이블을 LEFT OUTER JOIN한다. 구체적인 조건은 a 테이블을 기준으로 b 테이블에서는 5일 이전의 a 테이블의 현재 날짜까지의 평균값을 계산하고, c테이블에서는 20일 날짜 이전부터 a 테이블의 현재까지의 평균값을 계산한다. SELECT 문에는 기준이 되는 a 테이블에서 date, ..
보유 주식 수익 증감액 구하기 01. 내가 보유한 주식 테이블 생성하기 주식 매입 날짜, 수량, 구매가 데이터를 임시 테이블에 저장하기 CREATE TABLE #mystock ( date datetime, --날짜 자료형 symbol nvarchar(255), --문자 자료형 qty int, -- 정수형 price decimal(18,2) --실수형 ) INSERT INTO #mystock VALUES('2019-01-02', 'TSLA', 10, 61.00) INSERT INTO #mystock VALUES('2019-05-23', 'TSLA', 20, 39.00) INSERT INTO #mystock VALUES('2019-07-14', 'TSLA', 5, 300.00) INSERT INTO #mystock VALUES('2019..
특정 기간 주가 하락했다가 회복한 종목의 증감률 구하기 2020년 3월 코로나로 인해 전 세계 주식 시장이 매우 큰 폭으로 하락했다. 코로나가 발생하기 전 1주일 평균 시장 가격과 코로나 도중 시장 가격, 현재 시장 가격을 비교해 보면 얼마나 떨어졌다가 얼마나 회복되었을까? 01. 기준 날짜 구간의 데이터 저장하기 기준 날짜 구간의 데이터를 #temp1에 저장한다. symbol 별로 구간별 평균가(AVG)를 계산해 저장하며 GROUP BY에 symbol 을 사용한다. SELECT symbol, AVG([close]) AS [close] INTO #temp1 FROM stock WHERE date >= '2021-05-31' AND date = '2021-06-28' AND date < '2021-07-02' GROUP BY symbol SELECT * FRO..
주가가 연속 상승한 종목 분석하기 일정 날짜 동안 주가가 N% 이상 오른 종목 중 특히 연속으로 오른 종목을 검색할 것이다. stock 테이블에서 2021년2월17일부터 2021년2월24일까지의 일별 주식 데이터를 비교해 주가가 10% 이상 오른 종목 중 해당 기간 동안 한번도 주가가 떨어지지 않은 종목만 검색한다. 01. 증감 주가와 증감 주기율 저장하기 첫 날짜와 마지막 날짜의 종가를 검색해 증감 주기와 증감 주기율의 데이터셋을 만든다. 같은 symbol 데이터를 비교하기 위해 FROM 문의 서브 쿼리를 사용해 2021년2월17일의 데이터셋을 만들고, 2021년2월24일의 데이터셋을 만든 다음 조인 조건으로 symbol 열을 비교한다. 검색 결과는 임시 테이블 #temp에 저장한다. SELECT -- 최종적으로 보고 싶은 컬럼을 선택..
전일 대비 종목 분석하기 전일 대비 종목을 분석하려면 오늘 날짜의 행과 어제 날짜의 행을 일치시켜 오늘 종목 가격에서 어제 종목 가격을 빼는 계산을 수행해야 한다. 같은 테이블에서 데이터를 비교해야 하므로 SELF JOIN 또는 LAG/LEAD 함수를 사용해야 한다 01. SELF JOIN 함수로 전일 대비 증감과 증감률 구하기 SELECT a.symbol, a.date AS a_date, CONVERT(DECIMAL(18,2), a.[close]) AS a_close, '' As '---', b.date AS b_date, CONVERT(DECIMAL(18,2), b.[close]) AS b_close, '' As '---', CONVERT(DECIMAL(18,2), b.[close] - a.[close]) AS diff_pr..
하루 동안 상승/하락 종목 분석하기 01. 상승/하락 종목 관련 다양한 정보 검색하기 stock 테이블에서 하루 시작가와 하루 종가를 비교해 상승한 금액, 비율과 하루 거래 중 최저 거래가와 최대 거래가의 차이를 구하는 쿼리이다. SELECT date, symbol, CONVERT(DECIMAL(18,2), [open]) AS [open], CONVERT(DECIMAL(18,2), [close]) AS [close], CONVERT(DECIMAL(18,2), [open]-[close]) AS diff_price, CONVERT(DECIMAL(18,2), ([close]-[open])/[open] * 100) AS diff_ratio, '' AS ' ---', --공란 CONVERT(DECIMAL(18,2), [low]) AS [low], ..

티스토리툴바