국가 통계 데이터 분석하기: 주택 매매 데이터로 분석하기

주택 매매가 지수란 전국 시/군/구의 주택을 모집단으로 해당 지역 아파트 평균가를 기준값인 100으로 잡고, 이후 상승 또는 하락 정도를 쉽게 알 수 있도록 측정한 값이다. 

다운로드 경로

kossi.kr > 주거 > 전국주택가격동향조사 > 전국주택가격동향(기준원:2021.06) > 규모별 매매가격지수

 

01. 주택 매매 데이터 분석을 시작하기 전에 파일 살피기

SELECT * FROM house_city_code  -- 지역 메타 데이터

지역별           code_value
수도권          서울, 경기, 인천
지방              수도권을 제외한 전 지역
6대광역시   부산, 대구, 광주, 대전, 울산, 인천
5대광역시   부산, 대구, 광주, 대전, 울산
9개도           경기, 강원, 충북, 충남, 전북, 전남, 경북, 경남, 제주
8개도           강원, 충북, 충남, 전북, 전남, 경북, 경남, 제주

SELECT * FROM house_scale_code  -- 규모 메타 데이터

주택유형별    주택규모별   code_value
아파트           규모1           40㎡이하
아파트           규모2           40㎡초과~60㎡이하
아파트           규모3           60㎡초과~85㎡이하
아파트           규모4           85㎡초과~102㎡이하
아파트           규모5           102㎡초과~135㎡이하
아파트           규모6           135㎡초과
연립다세대  규모1           40㎡이하
연립다세대  규모2           40㎡초과~60㎡이하
연립다세대  규모3           60㎡초과~85㎡이하
연립다세대  규모4           85㎡초과
단독주택      규모1           전용면적99㎡이하 & 대지면적231㎡이하
단독주택  규모2 전용면적99㎡초과 331이하&대지면적662㎡이하 또는 전용면적99㎡이하&대지면적231㎡초과 662㎡이하
단독주택     규모3          전용면적331㎡초과 또는 대지면적662㎡초과

 

02. 전국 아파트 매매 지수의 증감 데이터 검색하기

WITH cte_house AS (
SELECT
  ROW_NUMBER() OVER (ORDER BY [거래월] ASC) AS num,
  [주택유형별],
  [지역별],
  [주택규모별],
  [항목],
  [거래월],
  [거래지수]
FROM house_transaction
WHERE  [주택유형별] = N'아파트' AND [지역별] = N'전국' AND [주택규모별]= N'규모3'
)
SELECT
    a.[주택유형별],
    a.[주택규모별],
    c.code_value,
    a.[거래월] AS a_date, CONVERT(DECIMAL(18,2), a.[거래지수]) AS a_transaction,
    b.[거래월] AS b_date, CONVERT(DECIMAL(18,2), b.[거래지수]) AS b_transaction,
    CONVERT(DECIMAL(18,2), a.[거래지수] - b.[거래지수]) AS diff_transaction
FROM cte_house AS a
    LEFT OUTER JOIN cte_house AS b on a.num = b.num +1
    LEFT OUTER JOIN house_scale_code AS c on a.[주택유형별] = c.[주택유형별]
AND a.[주택규모별] = c.[주택규모별]
ORDER BY a.num ASC

결과

주택유형별 주택규모별 code_value                 a_date a_transaction b_date    b_transaction   diff_transaction
아파트          규모3          60㎡초과~85㎡이하 2012-01 79.92              NULL        NULL              NULL
아파트          규모3          60㎡초과~85㎡이하 2012-02 79.92             2012-01    79.92              0.00
아파트          규모3          60㎡초과~85㎡이하 2012-03 79.90             2012-02    79.92             -0.02
아파트         규모3           60㎡초과~85㎡이하 2012-04 79.82             2012-03    79.90             -0.08

주택 유형과 규모를 미리 정의하고 WHERE  문 조건을 사용해 기초 데이터셋을 만들었다.

거래월 데이터가 문자열로 저장되어 있어 거래월로 데이터를 정렬하기 위해 순위 ( ROW_NUMBER )를 부여했다.

SELECT 문에서 SELF JOIN을 사용해 기준행과 그 전 행( a.num = b.num +1)을 계산해 그 차이를 구했다.  

규모 정보가 저장된 메타 데이터 테이블(house_scale_code )과 조인해 해당 규모의 설명을 검색 결과에 함께 나타냈다.

결과를 보면 전월 매매 지수(diff_transaction)의 증감 결과를 확인할 수 있다. 2021년 6월 매매지수가 100인 것으로 보아 매매 지수 기준 날짜가 2021년6월로 예상된다. 기준일과 비교할 때 매매가 지수 변동 사항을 확인해 가격의 증감 추세를 분석할 수 있다. 

 

03. 동월 대비 매매가 지수 증감 분석하기

WITH cte_house AS (
SELECT
  ROW_NUMBER() OVER (ORDER BY [거래월] ASC) AS num,
  [주택유형별],
  [지역별],
  [주택규모별],
  [항목],
  [거래월],
  [거래지수]
FROM house_transaction
WHERE  [주택유형별] = N'아파트' AND [지역별] = N'전국' AND [주택규모별]= N'규모3'

AND SUBSTRING([거래월], 6, 2) = '06'
)
SELECT
    a.[주택유형별],
    a.[주택규모별],
    c.code_value,
    a.[거래월] AS a_date, CONVERT(DECIMAL(18,2), a.[거래지수]) AS a_transaction,
    b.[거래월] AS b_date, CONVERT(DECIMAL(18,2), b.[거래지수]) AS b_transaction,
    CONVERT(DECIMAL(18,2), a.[거래지수] - b.[거래지수]) AS diff_transaction
FROM cte_house AS a
    LEFT OUTER JOIN cte_house AS b on a.num = b.num +1
    LEFT OUTER JOIN house_scale_code AS c on a.[주택유형별] = c.[주택유형별]
AND a.[주택규모별] = c.[주택규모별]
ORDER BY a.num ASC

결과

주택유형별 주택규모별 code_value                 a_date         a_transaction  b_date             b_transaction
아파트          규모3        ㎡초과~85㎡이하  2012-06         79.42                 NULL             NULL
아파트         규모3         60㎡초과~85㎡이하  2013-06         77.75                 2012-06         79.42
아파트         규모3         60㎡초과~85㎡이하  2014-06         79.50                 2013-06         77.75
아파트         규모3         60㎡초과~85㎡이하  2015-06         82.64                 2014-06         79.50
아파트         규모3         60㎡초과~85㎡이하  2016-06         84.52                 2015-06         82.64
아파트         규모3         60㎡초과~85㎡이하  2017-06         85.44                 2016-06         84.52
아파트         규모3         60㎡초과~85㎡이하  2018-06         86.25                 2017-06         85.44
아파트         규모3         60㎡초과~85㎡이하  2019-06         84.85                 2018-06         86.25
아파트         규모3         60㎡초과~85㎡이하  2020-06         88.30                 2019-06         84.85
아파트         규모3         60㎡초과~85㎡이하  2021-06         100.00               2020-06         88.30

CTE 테이블에서 기초 데이터셋을 만들 때 매년 06월의 데이터만 검색하려고, WHERE 문 조건에 SUBSTRING([거래월], 6, 2) = '06'을 넣었다. 

※ 날짜 데이터는 날짜형으로 바꿔 저장하고 날짜 관련 메타 데이터를 참조하는 편이 더 좋다.

각 연도의 거래월은 문자열이므로 앞뒤 행을 비교하고자 거래월 기준으로 오름찬순 정렬하고, ROW_NUMBER 함수를 사용해 순위를 부여했다. 결과를 보면 2019년과 2020년 사이에 매매 지수가 큰 폭으로 증가했다.

캘린더 테이블을 활용하면 훨씬 효율적이다.  SELEC * FROM dim_date WHERE date >= '2020-01-01'

 

04. 특정 기준일 대비 매매가 지수 증감 분석하기

WITH cte_house AS (
SELECT
  ROW_NUMBER() OVER (ORDER BY [거래월] ASC) AS num,
  [주택유형별],
  [지역별],
  [주택규모별],
  [항목],
  [거래월],
  [거래지수]
FROM house_transaction
WHERE  [주택유형별] = N'아파트' AND [지역별] = N'전국' AND [주택규모별]= N'규모3'
)
SELECT
    a.[주택유형별],
    a.[주택규모별],
    c.code_value,
    a.[거래월] AS a_date, CONVERT(DECIMAL(18,2), a.[거래지수]) AS a_transaction,
    b.[거래월] AS b_date, CONVERT(DECIMAL(18,2), b.[거래지수]) AS b_transaction,
    CONVERT(DECIMAL(18,2), a.[거래지수] - b.[거래지수]) AS diff_transaction
FROM cte_house AS a
    CROSS JOIN (SELECT [거래월], [거래지수] FROM cte_house WHERE [거래월]= '2012-01')
AS b
    LEFT OUTER JOIN house_scale_code AS c on a.[주택유형별] = c.[주택유형별]
AND a.[주택규모별] = c.[주택규모별]
ORDER BY a.num ASC

결과

주택유형별 주택규모별 code_value               a_date    a_transaction  b_date         b_transaction  diff_transaction
아파트        규모3           60㎡초과~85㎡이하 2012-01 79.92                2012-01       79.92               0.00
아파트        규모3          60㎡초과~85㎡이하 2012-02 79.92                 2012-01       79.92               0.00
아파트       규모3           60㎡초과~85㎡이하 2012-03 79.90                 2012-01       79.92              -0.02
아파트       규모3           60㎡초과~85㎡이하 2012-04 79.82                 2012-01       79.92              -0.10

CTE 문 바깥의 SELECT 문을 살펴보면  CROSS JOIN 을 했다. CROSS JOIN 을 사용한 이유는 cte_house 테이블 데이터의 2012년 1월 날짜를 모두 찾은 다음 차이를 구하기 때문이다. LEFT OUTER JOIN을 보면 규모에 관한 메타 데이터 테이블을 조인한 것을 확인할 수 있다. 조인 조건에서 cte_house AS a 열과 house_scale_code AS c 열을 조인한 것을 볼 수 있다. 조인할 때는 쿼리에 작성한 순서대로 조인하는 것이 아니므로 연산에 필요한 조인 조건에 맞는 테이블과 열을 잘 선정하는 것이 중요하다. 결과를 보면 왼쪽 테이블인 cte_house AS a 열을 기준으로 2012년 1월  데이터가 모두 매치되어 매매 지수 증감을 보여준다. 2014년 8월까지는 지수가 하락했다가 그 이후로 2020년 10월까지 계속 증가했다.