📊서브쿼리(subquery)

 

1. FROM

 

crimes 테이블

---

crimes table 데이터

---

[주 마다 얼마나 자주 범죄가 발생하는지 알아보기 ]

 

SELECT daily_stats.week , AVG(daily_stats.incidents_daily) FROM 
( SELECT week , date , COUNT(incident_id) AS incidents_daily FROM crimes GROUP BY week, date ) 
daily_stats GROUP BY daily_stats.week

---

가상의 새로운 테이블을 만들고, 해당 테이블 이름을 daily_stats라고 한다.

 

---

서브쿼리로 만들어진 가상의 daily_stats table 

( SELECT week , date , COUNT(incident_id) AS incidents_daily FROM crimes GROUP BY week, date ) 
daily_stats

week	date	incidents_daily
2	2020-01-05	2
2	2020-01-06	1
2	2020-01-07	3
.	.	.
.	.	.
.	.	.

그럼 새로 만들어진 가상의 테이블(daily_stats )에서 다시 쿼리문을 짜는데 이번에는 해당 테이블의 week을 기준으로Group By 하게 되면 최종적으로 우리가 원하는 각 주의 평균 범죄 발생률을 구할 수 있다.

---

🔻하지만_ AVG함수에는 주의점이 있다. 

 AVG(daily_stats.incidents_daily)

week	date	incidents_daily
2	2020-01-05	2
2	2020-01-07	1
2	2020-01-08	3
2	2020-01-09	2
2	2020-01-10	3
2	2020-01-11	2

만약, 이런 가상의 테이블이 생겼다고 가정해보자.

우리는 week로 groupby 했지만, 사실 avg 함수는 "week" 뜻을 모른다.

즉, 우리는 AVG(daily_stats.incidents_daily)라고 하면, 특정 요일에 ( 예를 들면 수요일) 에 범죄가 일어나지 않는다면,
해당 요일을 0으로 count해서 7로 나눠주겠지라고 기대할 수 있지만...

그건 우리의 기대일 뿐..

사실상 groupby week로 해버리면 row가 6개 밖에 없는 걸로 인식해서, AVG함수는 (일주일 범죄 횟수/6) 으로 계산해준다.

 

2. WHERE

(1) 정확하게 1개의 값이 동일

SELECT *
FROM crimes
WHERE date = (SELECT MIN(date) FROM crimes)

보통 min/max 같이 결과물이 하나가 나오는 함수를 사용할 떄 자주 사용

📏MIN(date) 가장 '오래된' 날짜
     MAX(date) 가장 '최신'의 날짜

 

(2) 해당 조건이 여러 개의 값

SELECT *
FROM crimes
WHERE date IN (SELECT distinct date FROM crimes ORDER BY date desc LIMIT 5);

---

HACKER_RANK - TOP_EARNERS 

EMPLOYEE LIST

Select salary*months as earnings,count(*) from employee
Where salary*months = (select max(salary*months) from employee)
Group By earnings;

 

earnings(별칭)의 경우 where에서는 못쓰지만, group by에서는 쓸 수 있다.

이유는 SQL 쿼리 실행 순서가 있기 때문!

FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

근데 그럼 왜 select가 group by보다 뒤에 있는데 group by에서는 쓸 수 있을까?
사실상 불가능하지만, MySQL에서만 예외적으로!!!! Group By부터 alias를 사용할 수 있게 해두었다.... 

 

👇참고하기 좋은 블로그 글이다. 

https://myjamong.tistory.com/172

 

👇MySQL 예외 조항 

https://dev.mysql.com/doc/refman/5.7/en/problems-with-alias.html

MySQL :: MySQL 5.7 Reference Manual :: B.3.4.4 Problems with Column Aliases

having도 마찬가지로 MySQL에서는 alias 사용 가능 

select salary*months as earnings,count(*) from employee
group by earnings
having earnings=(select max(salary*months) from employee);

 

---

HACKER_RANK - TOP_EARNERS : 부서 별 salary가 가장 많은 사람 뽑기

employee

 

department

기대 값

 

select d.name as department,e.name as employee,e.salary from employee as e 
  inner join (
    select departmentid,max(salary) max_salary 
    from employee 
    group by departmentid
  ) as ds on ds.departmentid=e.departmentid
         and ds.max_salary=e.salary
  inner join department as d on d.id=e.departmentid;