2024-01-16 공부흔적

# 지역별 면적
SELECT 구, 동, 면적
FROM seoul_area 
WHERE 구 IN ('중랑구', '도봉구', '강북구', '관악구', '은평구');

# 지역별 전체 은행 수
select 구, 동, count(stores)
from 은행통합2
WHERE 구 IN ('중랑구', '도봉구', '강북구', '관악구', '은평구')
group by 1, 2

2. 접근성
(2) 은행밀집도
   구         동     점포수    면적   점포수 / 면적
강북구 번동 6         1.61         0.27
강북구 미아동 10        0.93         0.09
강북구 수유동 8         2.96         0.37
관악구 은천동 1         0.78         0.78
관악구 신림동 17        0.54         0.03
관악구 봉천동 13        8.16         0.62
관악구 대학동 1         8.3           8.3
관악구 행운동 1         0.72         0.72
도봉구 도봉동 4         9.55         2.39
도봉구 쌍문동 3         2.26         0.75
도봉구 창동 9         4.25         0.47
도봉구 쌍문2동 1         0.54         0.54
도봉구 방학동 7         4.05         0.58
은평구 수색동 2         1.29         0.65
은평구 대조동 5         0.85         0.17
은평구 응암동 9         2.61         0.24
은평구 진관동 7         11.53       1.65
은평구 불광동 5         4.51         0.90
중랑구 묵동 4         1.87         0.47
중랑구 신내동 4         3.49         0.87
중랑구 망우동 5         3.95         0.79
중랑구 면목3.8동 1         1.63         1.63
중랑구 중화동 4         1.65         0.41
중랑구 상봉동 3         1.53         0.51
은평구 역촌동 4         1.16         0.29
은평구 갈현동 4         4.09         1.02
중랑구 면목동 5         4.38         0.88
은평구 녹번동 1         1.79         1.79
관악구 남현동 1         3.27         3.27

#전체 인구
USE senior;

SELECT
    동,
    SUM(50s1 + 50s2) AS 50s,
    SUM(60s1 + 60s2) AS 60s,
    SUM(70s1 + 70s2) AS 70s,
    SUM(50s1 + 50s2 + 60s1 + 60s2 + 70s1 + 70s2) AS total
FROM
    seoul_pop2
WHERE
    구 IN ('중랑구', '도봉구', '강북구', '관악구', '은평구')
GROUP BY
    동;

#점포

USE senior;

SELECT
    동,
    ((1100900 * 365) * (50s) / (51325329) * 0.158) AS 50s_store,
    ((1100900 * 365) * (60s) / (51325329) * 0.21) AS 60s_store,
    ((1100900 * 365) * (70s) / (51325329) * 0.224) AS 70s_store
FROM
(
SELECT
    동,
    SUM(50s1 + 50s2) AS 50s,
    SUM(60s1 + 60s2) AS 60s,
    SUM(70s1 + 70s2) AS 70s,
    SUM(50s1 + 50s2 + 60s1 + 60s2 + 70s1 + 70s2) AS total
FROM
    seoul_pop2
WHERE
    구 IN ('중랑구', '도봉구', '강북구', '관악구', '은평구')
GROUP BY
    동
) as subquery


#신용카드

USE senior;

SELECT
    동,
    (16936181 * ((50s / 51325329) * 0.94)) AS 50s_card,
    (16936181 * ((60s / 51325329) * 0.848)) AS 60s_card,
    (16936181 * ((70s / 51325329) * 0.573)) AS 70s_card
FROM
(
    SELECT
        동,
        SUM(50s1 + 50s2) AS 50s,
        SUM(60s1 + 60s2) AS 60s,
        SUM(70s1 + 70s2) AS 70s,
        SUM(50s1 + 50s2 + 60s1 + 60s2 + 70s1 + 70s2) AS total
    FROM
        seoul_pop2
    WHERE
        구 IN ('중랑구', '도봉구', '강북구', '관악구', '은평구')
    GROUP BY
        동
) AS subquery;


#생명보험

USE senior;

SELECT
    동,
    (702686168 * ((50s / 51325329) * 0.856)) AS 50s_insurance,
    (702686168 * ((60s / 51325329) * 0.777)) AS 60s_insurance,
    (702686168 * ((70s / 51325329) * 0.423)) AS 70s_insurance
FROM
(
SELECT
    동,
    SUM(50s1 + 50s2) AS 50s,
    SUM(60s1 + 60s2) AS 60s,
    SUM(70s1 + 70s2) AS 70s,
    SUM(50s1 + 50s2 + 60s1 + 60s2 + 70s1 + 70s2) AS total
FROM
    seoul_pop2
WHERE
    구 IN ('중랑구', '도봉구', '강북구', '관악구', '은평구')
GROUP BY
    동
) as subquery


#1번 합친 것
USE senior;

SELECT
    s.동,
    SUM((1100900 * 365 * (50s1 + 50s2)) / 51325329 * 0.158) AS 50s_store,
    SUM((1100900 * 365 * (60s1 + 60s2)) / 51325329 * 0.21) AS 60s_store,
    SUM((1100900 * 365 * (70s1 + 70s2)) / 51325329 * 0.224) AS 70s_store,
    SUM((1100900 * 365 * (50s1 + 50s2)) / 51325329 * 0.158 + (1100900 * 365 * (60s1 + 60s2)) / 51325329 * 0.21 + (1100900 * 365 * (70s1 + 70s2)) / 51325329 * 0.224) AS total_store,
    SUM((16936181 * (50s1 + 50s2)) / 51325329 * 0.94) AS 50s_card,
    SUM((16936181 * (60s1 + 60s2)) / 51325329 * 0.848) AS 60s_card,
    SUM((16936181 * (70s1 + 70s2)) / 51325329 * 0.573) AS 70s_card,
    SUM((16936181 * (50s1 + 50s2)) / 51325329 * 0.94 + (16936181 * (60s1 + 60s2)) / 51325329 * 0.848 + (16936181 * (70s1 + 70s2)) / 51325329 * 0.573) AS total_card,
    SUM((702686168 * (50s1 + 50s2)) / 51325329 * 0.856) AS 50s_insurance,
    SUM((702686168 * (60s1 + 60s2)) / 51325329 * 0.777) AS 60s_insurance,
    SUM((702686168 * (70s1 + 70s2)) / 51325329 * 0.423) AS 70s_insurance,
    SUM((702686168 * (50s1 + 50s2)) / 51325329 * 0.856 + (702686168 * (60s1 + 60s2)) / 51325329 * 0.777 + (702686168 * (70s1 + 70s2)) / 51325329 * 0.423) AS total_insurance
FROM
    seoul_pop2 s
WHERE
    구 IN ('중랑구', '도봉구', '강북구', '관악구', '은평구')
GROUP BY
    s.동;


#모바일 자금이체

USE senior;

SELECT
    동,
    (19710000 * 365) / total * 0.410 * 0.158 AS 50s_mobile,
    (19710000 * 365) / total * 0.410 * 0.084 AS 60s_mobile,
    (19710000 * 365) / total * 0.410 * 0.015 AS 70s_mobile
FROM
(
    SELECT
        동,
        SUM(50s1 + 50s2) AS 50s,
        SUM(60s1 + 60s2) AS 60s,
        SUM(70s1 + 70s2) AS 70s,
        SUM(50s1 + 50s2 + 60s1 + 60s2 + 70s1 + 70s2) AS total
    FROM
        seoul_pop2
    WHERE
        구 IN ('중랑구', '도봉구', '강북구', '관악구', '은평구')
    GROUP BY
        동
) AS subquery;