국가 통계 데이터 분석하기: 행정 구역별 남녀 비율 구하기

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 census_2015_2020 
         WHERE [시점] = 2020 AND [C행정구역별(읍면동)] LIKE '_____'
     ) AS x
) AS y
ORDER  BY diff_ratio DESC

결과

c_city     city       population  m_population    f_population   m_ratio            f_ratio           diff_ratio
23320  옹진군 19292         11073                 8219                 19234.60       19249.40      14.80
33370  음성군 103725       57583                46142               103669.48      103680.52  11.04
37430  울릉군 8444            4683                  3761                 8388.54          8399.46       10.92

WHERE 문에서 2020년 및 5자리의 행정 구역 코드로 패턴 검색을 하고자 LIKE '_____' 5글자의 문자 중 어느 것이나 검색될 수 있게 했다.남녀 비율의 크기에 따른 정렬 조건을 위해 ABS 함수를 사용했고 성별 비율이 높은 순에서 낮은 순으로 정렬했다. 데이터를 살펴보면 시/군/구 이름으로만 표시되다 보니 어느 광역시 또는 도의 구,군인지 확인하기 어렵다. 

 

02. 행정 구역 코드 기준으로 상위 레벨의 행정 구역명 검색하기

SUBSTRING 함수를 사용해 행정 구역 코드의 앞 2자리를 분리해 새로운 열 cc_city를 만들고, SELECT 문에 두 자릿수 행정 구역 코드의 데이터셋과 조인해 상위 레벨의 행정 구역을 표시할 수 있도록 했다.

SELECT
    y.cc_city,
    y.c_city,
    z.city,
    y.city,
    y.population,
    y.m_population,
    y.f_population,
    y.m_ratio,
    y.f_ratio,
    y.diff_ratio
FROM (
     SELECT
      *,
    ABS(m_ratio - f_ratio) AS diff_ratio
      FROM(
          SELECT    
               SUBSTRING([C행정구역별(읍면동)], 1,2) AS cc_city,  -- C행정구역별 열에서 1첫번째부터 2개의 문자를 반환
               [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 census_2015_2020 
          WHERE [시점] = 2020 AND [C행정구역별(읍면동)] LIKE '_____'
      ) AS x
) AS y
     INNER JOIN (SELECT 
                              [C행정구역별(읍면동)] AS cc_city,  
                              [행정구역별(읍면동)] AS city
                           FROM census_2015_2020 
                           WHERE [C행정구역별(읍면동)] LIKE '[^0]_'
                           GROUP BY  [C행정구역별(읍면동)] , [행정구역별(읍면동)]
                         ) AS z ON y.cc_city = z.cc_city
ORDER  BY diff_ratio DESC

결과

cc_city   c_city    city                        city         population  m_population  f_population m_ratio         f_ratio        diff_ratio
23          23320    인천광역시         옹진군  19292          11073            8219                19234.60    19249.40    14.80
33          33370    충청북도             음성군  103725        57583            46142             103669.48  103680.52  11.04
37          37430    경상북도             울릉군   8444           4683               3761               8388.54       8399.46      10.92
29          29005    세종특별자치시 면부       52383        29029             23354             52327.58      52338.42   10.84

최상위 행정 구역의 데이터와 조인하려고 조인 조건에 해당하는 데이터를 생성해 사용했다.

 

03. 지역 코드의 메타 데이터를 생성하고 테이블에 저장하기

SELF JOIN 문을 사용해 지역 코드의 메티 데이터를 생성하고 census_city_code라는 이름으로 테이블을 생성해 결과를 저장한다.

SELECT
   c.city1,
   c.city1_name,
   b.city2,
   b.city2_name,
   a.city3,
   a.city3_name
   INTO cencus_city_code
FROM (
   SELECT 
   SUBSTRING([C행정구역별(읍면동)], 1,2) AS city1,
   SUBSTRING([C행정구역별(읍면동)], 1,5) AS city2,
   [C행정구역별(읍면동)] AS city3,
   [행정구역별(읍면동)] AS city3_name
   FROM census_2015_2020
   WHERE LEN([C행정구역별(읍면동)])=7
   GROUP BY SUBSTRING([C행정구역별(읍면동)], 1,2), SUBSTRING([C행정구역별(읍면동)],1,5), [C행정구역별(읍면동)], [행정구역별(읍면동)]
   ) AS a
   LEFT OUTER JOIN (
   SELECT 
   [C행정구역별(읍면동)] AS city2,
   [행정구역별(읍면동)] AS city2_name
   FROM census_2015_2020
   WHERE LEN([C행정구역별(읍면동)])=5
   GROUP BY [C행정구역별(읍면동)], [행정구역별(읍면동)]
   ) AS b ON a.city2 = b.city2 
   LEFT OUTER JOIN (
   SELECT 
   [C행정구역별(읍면동)] AS city1,
   [행정구역별(읍면동)] AS city1_name
   FROM census_2015_2020
   WHERE LEN([C행정구역별(읍면동)])=2
   GROUP BY [C행정구역별(읍면동)], [행정구역별(읍면동)]
   ) AS c ON a.city1 = c.city1 
 ORDER BY city3

 SELECT * FROM cencus_city_code

 

04. 메타 데이터를 활용해 데이터 분석하기

생성한 메타 데이터는 JOIN문을 사용해 데이터를 분석할 때 활용할 수 있다.

다음은 census_2015_2020 테이블과 cencus_city_code 테이블을 조인해 2020년의 상세 행정 구역별 인구 분포를 확인한 것이다. 

SELECT
   b.city1,
   b.city2,
   b.city3,
   b.city1_name,
   b.city2_name,
   b.city3_name,
   a.[시점],
   a.[총인구 (명)],
   a.[남자 (명)],
   a.[여자 (명)],
   a.[내국인-계 (명)],
   a.[내국인-남자 (명)],
   a.[내국인-여자 (명)]
FROM census_2015_2020 AS a
    INNER JOIN cencus_city_code AS b on a.[C행정구역별(읍면동)] = b.city3
WHERE a.[시점] = 2020

결과

city1 city2 city3 city1_name city2_name city3_name 시점   총인구 (명) 남자 (명) 여자 (명)  내국인-계 (명)
11 11010 1101053 서울특별시  종로구   사직동        2020 8705             3959       4746        8344
11 11010 1101054 서울특별시  종로구   삼청동        2020 2433            1184        1249        2337
11 11010 1101055 서울특별시  종로구   부암동        2020 9666            4622        5044        9245
11 11010 1101056 서울특별시  종로구   평창동        2020 17379          8109        9270       17029

위는 census_2015_2020를 기준으로 cencus_city_code 를 내부 조인한 결과다. 원래는census_2015_2020 열이 먼저 나와야 하나, cencus_city_code 테이블의 열이 먼저 나온 것은 SELECT 문에서 cencus_city_code 열을 먼저 작성했기 때문이다.