sql- 공공데이터포털 활용하기

https://www.data.go.kr/data/15083033/fileData.do

소상공인시장진흥공단_상가(상권)정보_20250630

여기서 두번째 파일을 다운로드 받고

 

새로운 데이터 베이스를 하나 만든다음 파일을 집어넣는다. 

 

 

-- 1. TShopInfo 테이블 행 개수 확인
SELECT count(*) FROM TShopInfo;  -- 총 레코드 수 확인

-- 2. TShopInfo 전체 데이터 확인
SELECT * FROM TShopInfo;  -- 테이블 구조 및 데이터 확인용

-- 3. 중복되지 않은 시도명 조회
SELECT DISTINCT 시도명 FROM TShopInfo;  -- 전국 시도 목록 확인

-- 4. 서울특별시의 시군구명 확인
SELECT DISTINCT 시군구명 
FROM TShopInfo
WHERE 시도명 = '서울특별시';  -- 서울 내 구/군 목록 조회

-- 5. 서울 공차 매장 정보 조회
SELECT 상호명, 시도명, 시군구명, 행정동명
FROM TShopInfo
WHERE 시도명 = '서울특별시' AND 상호명 LIKE '%공차%'
ORDER BY 시군구명, 행정동명;  -- 서울 내 공차 매장 정렬 조회

-- 6. TPopulation 일부 데이터 확인
SELECT * FROM TPopulation LIMIT 10;  -- 인구 데이터 샘플 확인

-- 7. 서울 종로구 총인구수 조회
SELECT 행정구역, [2025년07월_총인구수]
FROM TPopulation
WHERE 행정구역 LIKE '서울특별시 종로구%';  -- 특정 구 인구 확인

-- 8. 행정구역에서 시도/시군구 추출 후 조회
SELECT
    TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)) AS 전체지역,  -- 괄호 제거 전체 지역
    SUBSTR(
        TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)),
        1,
        INSTR(TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)), ' ') - 1
    ) AS 시도,  -- 시도명 추출
    TRIM(
        REPLACE(
            TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)),
            SUBSTR(
                TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)),
                1,
                INSTR(TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)), ' ') - 1
            ),
            ''
        )
    ) AS 시군구,  -- 구/군 추출
    "2025년07월_총인구수",
    "2025년07월_세대수",
    "2025년07월_세대당 인구",
    "2025년07월_남자 인구수",
    "2025년07월_여자 인구수",
    "2025년07월_남여 비율"
FROM TPopulation;  -- 인구 테이블 전체 조회 후 시도/시군구 분리

-- 9. 시도, 시군구 컬럼 추가 후 행정코드도 추출
ALTER TABLE TPopulation ADD COLUMN 시도 TEXT;  -- 시도명 컬럼 추가
ALTER TABLE TPopulation ADD COLUMN 시군구 TEXT;  -- 시군구명 컬럼 추가
ALTER TABLE TPopulation ADD COLUMN 행정코드 TEXT;  -- 행정코드 컬럼 추가

-- 10. 기존 행정구역 값으로 새 컬럼 채우기
UPDATE TPopulation
SET
    시도 = SUBSTR(
        TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)),
        1,
        INSTR(TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)), ' ') - 1
    ),
    시군구 = TRIM(
        REPLACE(
            TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)),
            SUBSTR(
                TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)),
                1,
                INSTR(TRIM(SUBSTR(행정구역, 1, INSTR(행정구역, '(') - 1)), ' ') - 1
            ),
            ''
        )
    ),
    행정코드 = TRIM(SUBSTR(
        행정구역,
        INSTR(행정구역, '(') + 1,
        INSTR(행정구역, ')') - INSTR(행정구역, '(') - 1
    ));  -- 행정코드 추출

-- 11. 경상남도 데이터 확인
SELECT * FROM TPopulation
WHERE 시도 = '경상남도';  -- 특정 시도 인구 데이터 확인

-- 12. 공차 매장 수와 인구수 결합
SELECT 
    매장.시도명,
    매장.시군구명,
    매장.매장수,
    인구.[2025년07월_총인구수] AS 인구수
FROM 
(
    SELECT 시도명, 시군구명, COUNT(*) AS 매장수
    FROM TShopInfo
    WHERE 시도명 IN ('서울특별시', '경기도', '인천광역시') 
      AND 상호명 LIKE '공차%'
    GROUP BY 시도명, 시군구명
) AS 매장
JOIN TPopulation AS 인구
ON 매장.시도명 = 인구.시도 AND 매장.시군구명 = 인구.시군구;  -- 매장수와 인구수 매칭

 

또 새로운 파일

bird_tracking_devices_01.csv

0.00MB

bird_tracking_01.csv

5.09MB

 

-- 전체 조류 추적 데이터 조회
select * from bird_tracking_01;

-- 장치 정보 테이블에서 특정 rowid(=1) 데이터 조회
select species_code, catch_location, device_info_serial, sex, scientific_name, ring_code, bird_name
from bird_tracking_devices_01
where rowid = 1;

-- 추적 데이터 중 장치번호 851번의 기록만 조회
select * from bird_tracking_01
where device_info_serial = 851;

-- 장치번호별 기록 건수 집계
select device_info_serial, count(*) as 건수
from bird_tracking_01
group by device_info_serial;

-- 장치정보 테이블에는 있는데 추적데이터에는 없는 장치 찾기
SELECT bt.device_info_serial, btd.device_info_serial
from bird_tracking_01 bt
right outer join bird_tracking_devices_01 btd
    on bt.device_info_serial = btd.device_info_serial
where bt.device_info_serial is null;

 

-- 장치번호 851번에 대해 요약 통계 조회
select 
    min(altitude),   -- 최소 고도
    max(altitude),   -- 최대 고도
    min(date_time),  -- 가장 이른(최초) 기록 시간
    max(date_time),  -- 가장 늦은(최신) 기록 시간
    max(speed_2d)    -- 가장 빠른 속도
from bird_tracking_01
where device_info_serial = 851;