이건 sql 마지막 레쓴~

population_trends_2015_2024.csv

0.00MB

민원마스터.csv

0.00MB

민원통계.csv

2.11MB

새로운 데이터의 등장이라....

pivot : long format->wide format

unpivot : wide format->long format

transpose : 가로와 세로를 바꾸기

 

년도   지역          인구수

2015 서울특별시 9801991  -> 이런식으로 바꿔야됨 

 

그렇게 되려면 년도, 지역 두개 묶여야한단다...

 

-- PopulationTrend 원본 테이블 확인
SELECT * from PopulationTrend;

-- wide → long 변환 예시 (서울, 부산만)
SELECT 년도, '서울특별시' as region, 서울특별시 FROM PopulationTrend
UNION 
SELECT 년도, '부산광역시', 부산광역시 FROM PopulationTrend;

-- 전체 지역을 long 형식으로 바꾸는 VIEW 생성
CREATE VIEW VvPopulationTrend AS
SELECT 년도, '서울특별시' AS region, 서울특별시 AS population FROM PopulationTrend
UNION ALL
SELECT 년도, '부산광역시', 부산광역시 FROM PopulationTrend
UNION ALL
SELECT 년도, '대구광역시', 대구광역시 FROM PopulationTrend
UNION ALL
SELECT 년도, '인천광역시', 인천광역시 FROM PopulationTrend
UNION ALL
SELECT 년도, '광주광역시', 광주광역시 FROM PopulationTrend
UNION ALL
SELECT 년도, '대전광역시', 대전광역시 FROM PopulationTrend
UNION ALL
SELECT 년도, '울산광역시', 울산광역시 FROM PopulationTrend
UNION ALL
SELECT 년도, '세종특별자치시', 세종특별자치시 FROM PopulationTrend
UNION ALL
SELECT 년도, '경기도', 경기도 FROM PopulationTrend
UNION ALL
SELECT 년도, '강원특별자치도', 강원특별자치도 FROM PopulationTrend
UNION ALL
SELECT 년도, '충청북도', 충청북도 FROM PopulationTrend
UNION ALL
SELECT 년도, '충청남도', 충청남도 FROM PopulationTrend
UNION ALL
SELECT 년도, '전북특별자치도', 전북특별자치도 FROM PopulationTrend
UNION ALL
SELECT 년도, '전라남도', 전라남도 FROM PopulationTrend
UNION ALL
SELECT 년도, '경상북도', 경상북도 FROM PopulationTrend
UNION ALL
SELECT 년도, '경상남도', 경상남도 FROM PopulationTrend
UNION ALL
SELECT 년도, '제주특별자치도', 제주특별자치도 FROM PopulationTrend
ORDER BY region, 년도;

-- VIEW 확인
SELECT * from VvPopulationTrend;

-- MinTong(민원데이터) 확인
SELECT * from MinTong;

-- MinMaster(민원분야코드 마스터) 확인
SELECT * from MinMaster;

-- 민원데이터 + 분야명 + 인구수를 INNER JOIN (일치하는 데이터만 조회)
SELECT
    mt.년도,
    mt.기관,
    mt.성별,
    mt.연령,
    mm.분야 AS 분야명,      -- 민원마스터에서 가져온 분야 이름
    mt.건수 AS 민원건수,
    vpt.population AS 인구수 -- VvPopulationTrend에서 가져온 인구 수
FROM MinTong AS mt
INNER JOIN MinMaster AS mm ON mt.분야 = mm.민원분야코드
INNER JOIN VvPopulationTrend AS vpt ON mt.기관 = vpt.region AND mt.년도 = vpt.년도;

-- 민원데이터 + 분야명 + 인구수를 LEFT JOIN (일치하지 않아도 MinTong 기준으로 모두 조회)
SELECT
    mt.년도,
    mt.기관,
    mt.성별,
    mt.연령,
    mm.분야 AS 분야명,       -- 일치하는 데이터가 없으면 NULL
    mt.건수 AS 민원건수,
    vpt.population AS 인구수 -- 일치하는 데이터가 없으면 NULL
FROM MinTong AS mt
LEFT JOIN MinMaster AS mm ON mt.분야 = mm.민원분야코드
LEFT JOIN VvPopulationTrend AS vpt ON mt.기관 = vpt.region AND mt.년도 = vpt.년도;

-- VvPopulationTrend 안에 있는 지역 목록만 확인
SELECT DISTINCT region from VvPopulationTrend;

-- long → wide 변환 (지역별 인구를 다시 컬럼으로 펼치기)
SELECT 년도,
    SUM(CASE WHEN region = '서울특별시' THEN population END) AS 서울특별시,
    SUM(CASE WHEN region = '부산광역시' THEN population END) AS 부산광역시,
    SUM(CASE WHEN region = '대구광역시' THEN population END) AS 대구광역시,
    SUM(CASE WHEN region = '인천광역시' THEN population END) AS 인천광역시,
    SUM(CASE WHEN region = '광주광역시' THEN population END) AS 광주광역시,
    SUM(CASE WHEN region = '대전광역시' THEN population END) AS 대전광역시,
    SUM(CASE WHEN region = '울산광역시' THEN population END) AS 울산광역시,
    SUM(CASE WHEN region = '세종특별자치시' THEN population END) AS 세종특별자치시,
    SUM(CASE WHEN region = '경기도' THEN population END) AS 경기도,
    SUM(CASE WHEN region = '강원특별자치도' THEN population END) AS 강원특별자치도,
    SUM(CASE WHEN region = '충청북도' THEN population END) AS 충청북도,
    SUM(CASE WHEN region = '충청남도' THEN population END) AS 충청남도,
    SUM(CASE WHEN region = '전북특별자치도' THEN population END) AS 전북특별자치도,
    SUM(CASE WHEN region = '전라남도' THEN population END) AS 전라남도,
    SUM(CASE WHEN region = '경상북도' THEN population END) AS 경상북도,
    SUM(CASE WHEN region = '경상남도' THEN population END) AS 경상남도,
    SUM(CASE WHEN region = '제주특별자치도' THEN population END) AS 제주특별자치도
FROM VvPopulationTrend
GROUP BY 년도
ORDER BY 년도;

-- 특정 지역만 CASE WHEN 으로 추출하는 연습
SELECT 년도, 
       CASE WHEN region='강원특별자치도' THEN population END AS 강원특별자치도
FROM VvPopulationTrend;

데이터수집링크.pptx

0.10MB

 

이것저것 참고해서 어떤 데이터 분석해보고싶은지 생각하기.. 

그래서 내가 분석해보고싶은게 무엇이냐 하면...

 

공공데이터포털에서 이런 데이터를 발견했음

 

 

경찰청_범죄 발생 시간대 및 요일_20191231.csv

0.00MB

경찰청_범죄 발생 지역별 통계_20231231.csv

0.03MB

경찰청_범죄 발생 장소별 통계_20241231.csv

0.01MB

 

갑자기 이 세 데이터의 연관성을 파악하고싶어지다... 

   폭행 범죄 데이터 분석 보고서

1. 사용된 데이터

      본 분석에는 경찰청 공개 범죄 데이터를 활용하였다.

• crime_time_day.csv : 범죄 발생 시간대 및 요일별 통계
  
  
• crime_location.csv : 범죄 발생 장소별 통계
  
  
• crime_region.csv : 범죄 발생 지역별 통계
  
  

 이 3개 파일을 SQLite DB에 적재 후, 전처리 및 가공 과정을 거쳐 범죄유형별·지역별·시간대별·장소별로 통합 분석할 수 있도록 구성하였다.

2. 사용한 코드 

(1) 분석용 뷰 생성

(2) 폭행 범죄 집중도 계산

3. 가설

• 가설: “폭행 범죄는 심야(21시~03시), 주말(금·토)에 유흥가(강남구 유흥주점)에서 가장 많이 발생할 것이다.”
  
  
• 일반적인 사회적 인식에 기반한 가설 설정임.
  
  

4. 분석 결과

쿼리 실행 결과, 예상 비중이 높은 상위 4개 조합은 다음과 같음:

1. 노원구-아파트 : 2,176,653 (최고)
   
   
2. 강남구-일반도로 : 2,168,013
   
   
3. 노원구-일반도로 : 1,129,676
   
   
4. 강남구-유흥주점 : 873,534
   
   

5. 결론

1. 폭행은 주거지역(아파트) 에서 가장 많이 발생하는 것으로 추정됨.
   → 층간소음, 가정 내 갈등, 이웃 간 분쟁 가능성이 높음.
   
   
2. 일반도로에서도 높은 비중을 차지.
   → 보행자 간 시비, 교통 관련 다툼 등 생활 속 갈등으로 인한 폭행 발생.
   
   
3. 유흥주점(강남구) 은 여전히 위험지역이지만, 주거·도로 공간에 비해 비중이 낮음.
   → 기존 인식과 다른 결과.
   
   

6. 나의 의견

• 정책적 제언:
  ① “유흥가 단속” 중심의 기존 정책은 한계가 있음.
  ② 실제로는 주거지역과 도로에서 더 많은 폭행이 발생하므로, 생활밀착형 치안 대책이 필요.
  ③ 아파트 관리사무소·주민센터와 협력해 갈등 조정 프로그램을 강화하고, 층간소음 중재 제도 홍보 필요.
  ④ 도로 및 번화가에는 심야 순찰·CCTV 보강이 효과적일 것.
  
  
• 데이터 활용적 시사점:
  단순 집계보다, 시간·요일·장소·지역별로 조건 조합을 곱셈형 모델로 계산하는 접근이 의미 있었음.
  향후에는 더 많은 지역(강북·서초·송파 등)을 포함해 비교 분석을 확대하면 정책 수립에 더욱 도움이 될 것.
  
  

요약:
가설(유흥가 중심)과 달리, 실제 분석 결과 폭행은 주거지역(노원구 아파트) 와 일반도로(강남·노원) 에서 더 많이 발생하는 것으로 나타났음.
따라서 폭행 예방 정책은 생활공간 중심으로 전환해야 한다는 인사이트를 도출할 수 있었다.

지피티에게 부족한 점을 물어봤어요 

 

 

코드는 지피티가 짜주긴 했는데 주제는 내가 고름 .... 근데 sqlite로 분석하는거 좀 안맞는것같기도하고.... 전처리를 왜 여기서 해야하는지도 의문임 엑셀 내에서 전처리 하면 안되나...  여튼 그래도 열심히는 했는데 ... 지난 5일동안의 배움을 최대한 활용했다...정도임 근데 아직도 지피티를 진짜 많이 써야됨....그리고 결과 자체도 원하는대로 나오진 않았음...... 뭔가 복습을 해야할듯 ,,,,,...