데이터분석을 위한 고급 SQL : 서브쿼리(subquery) 예제 실습

https://www.inflearn.com/course/%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9D-%EA%B3%A0%EA%B8%89-SQL

[백문이불여일타] 데이터 분석을 위한 고급 SQL - 인프런 | 강의

https://www.youtube.com/watch?v=Hj5edTNJe_Y&t=169s

해당 강의의 서브쿼리 챕터에서 제시된 crimes 테이블에 대한 실습을 진행하고자 합니다. 강의에서 따로 제공된 ddl은 없었지만 SQL test 사이트를 이용하여 ddl을 직접 설계해보고 dml query를 생성할 수 있는 script를 생성해 보았습니다.

1. ddl script

CREATE TABLE crimes ( incident_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, week INT UNSIGNED NOT NULL, date DATETIME, type VARCHAR(5) ); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3001, 2, '2020-01-05', 'A'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3002, 2, '2020-01-06', 'A'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3003, 2, '2020-01-06', 'B'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3004, 2, '2020-01-06', 'B'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3005, 2, '2020-01-07', 'B'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3006, 2, '2020-01-07', 'A'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3007, 2, '2020-01-08', 'C'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3008, 2, '2020-01-09', 'A'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3009, 2, '2020-01-09', 'D'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3010, 2, '2020-01-10', 'D'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3011, 2, '2020-01-11', 'A'); INSERT INTO `crimes` (`incident_id`, `week`, `date`, `type`) VALUES (3012, 2, '2020-01-11', 'C');

[테이블 전체 조회]

SELECT * FROM crimes;

보이는 것처럼 잘 만들어진 테이블 하나가 완성되었습니다. 이제 해당 테이블을 가지고 아래 예제에 대한 dml 쿼리를 짜보겠습니다.

[각 주차당 요일별로 발생한 범죄 사건 발생 횟수 조회하기]

SELECT week, date_format(date, '%Y-%m-%d') as date ,COUNT(incident_id) as 'incidents_daliy' FROM crimes GROUP BY week, date;

date 컬럼의 데이터 타입은 DATETIME으로 연도,일,월만 조회하고 싶은 경우, DATE_FORMAT() 함수를 사용해줘야 합니다. DATE_FORMAT(컬럼명, '%Y-%m-%d')와 같이 사용하면 데이터 형식을 위와 같이 바꿀 수 있습니다.

[각 주차당 요일별로 발생한 범죄 사건 발생 횟수에 대한 평균값 구하기]

SELECT daily_stats.week ,AVG(daily_stats.incidents_daliy) FROM ( SELECT week ,date_format(date, '%Y-%m-%d') as date ,COUNT(incident_id) as incidents_daliy FROM crimes GROUP BY week, date ) daily_stats GROUP BY daily_stats.week;

해당 예제는 위와 같이 from절 서브쿼리를 이용하여 풀이할 수 있습니다. 이 서브쿼리는 바로 위 예제에서 생성한 쿼리를 이용한 것입니다. 해당 서브쿼리를 통해 가상의 테이블을 생성하고 테이블에 이름을 지어줍니다. 이렇게 하면 주차당 요일별로 발생한 범죄사건 발생 횟수를 나타내는 컬럼명인 incidents_daily에 접근할 수 있습니다.
반드시 가상테이블명.컬럼명(daliy_stats.incidents_daily)이 아닌 컬럼명(incidents_daily)과 같은 방식으로 접근해도 문제없이 실행되지만 컬럼명이 중북될 가능성을 고려하여 전자와 같은 방식으로 명시해주는 것이 좋습니다.