퀘스트 SQL 걷기반 6)
21. 레이팅 순위 계산 쿼리
나는 order by 절로만 순서를 정리했는데 rank () over(order by column desc)를 쓰면 순위도 매기는 것이 가능. sql문도 함수 검색을 잘 해봐야겠다.
22. 가장 늦게 게임을 시작한 유저의 이름
max 함수로 해보려 했으나 실패. order by limit 1
23. 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열
group by 로 region 을 묶고 order by rating 로 정렬하려 했으나 실패. order by 로 region, rating 두 컬럼을 정렬해서 나열. 근데 왜 안되지?
24. 지역별로 평균 레이팅을 계산
group by로 지역을 정렬하고 select 에서 avg()계산
걷기반 7)
25.만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리
select * FROM lol_feedbacks order by satisfaction_score desc;
26.각 유저별로 최신 피드백을 찾는 쿼리
질문을 잘 못 해석함.
select * from lol_feedbacks order by user_name, feedback_date desc;
->SELECT user_name, MAX(feedback_date) FROM lol_feedbacks GROUP BY user_name;
27.만족도 점수가 5점인 피드백의 수를 계산
select count(*) as "피드백 수" from lol_feedbacks where satisfaction_score=5
28.가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리
select from (select user_name, count() as rank_user from lol_feedbacks group by user_name)f order by rank_user desc limit 3;
->SELECT user_name, COUNT(*) AS feedback_count FROM lol_feedbacks GROUP BY user_name ORDER BY feedback_count DESC LIMIT 3;
중첩문으로 묶어줬는데, 굳이 묶어줄 필요 없었다.
29.평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리
select * from (select avg(satisfaction_score) as avg_score ,feedback_date as feed from lol_feedbacks group by feedback_date)f order by avg_score desc limit 1;
->SELECT feedback_date FROM lol_feedbacks GROUP BY feedback_date ORDER BY AVG(satisfaction_score) DESC LIMIT 1;
역시나 묶어줄 필요 없음.
<복습> group by로 묶을 때는 그 묶은 컬럼명만 검색하거나 계산할 수 있음
select
카테고리컬럼(원하는컬럼 아무거나),
sum(계산 컬럼),
from
group by
카테고리컬럼(원하는컬럼 아무거나)
걷기반 8)
30.전공(major)가 성형외과인 의사의 이름을 알아내는 쿼리
select name from doctors where major='성형외과'
31.각 전공 별 의사 수를 계산하는 쿼리
select major,count(distinct name) '의사수' from doctors group by major;
32.현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리
select count(distinct name) '5년 이상 일한 의사 수' from doctors where timestampdiff(year,hire_date,now())>=5;
->SELECT COUNT(*) AS num_of_doctors FROM doctors WHERE hire_date <= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);
:나는 timestampdiff()로 햇수를 계산했는데, date_sub함수로도 계산 가능
33.각 의사의 근무 기간을 계산하는 쿼리
select name '이름' ,timestampdiff(day,hire_date,now()) '의사 근무 기간' from doctors;
->SELECT name, DATEDIFF(CURDATE(), hire_date) AS working_days FROM doctors;
:datediff(종료일,생성일) 생성일-종료일 계산
걷기반 9)
34.각 성별(gender)에 따른 환자 수를 계산
select count(*) '환자 수' ,gender '성별' from patients group by gender;
35.현재 나이가 40세 이상인 환자들의 수를 계산
select count(*) '40세 이상 환자수' from patients where timestampdiff(year,birth_date,now())>=40;
->WHERE birth_date <= DATE_SUB(CURDATE(), INTERVAL 40 YEAR);
:date_sub()함수도 가능
36.마지막 방문 날짜(last_visit_date)가 1년 이상 된 환자들을 선택하는 쿼리
select * from patients where timestampdiff(year,last_visit_date,now())>=1;
->SELECT id, name, birth_date, gender, last_visit_date FROM patients WHERE last_visit_date <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
37.생년월일이 1980년대인 환자들의 수를 계산
select count(*) '80년대생 환자 수' from patients where year(birth_date) between 1980 and 1989;
->WHERE birth_date BETWEEN '1980-01-01' AND '1989-12-31’;
:year(): 날짜데이터에서 연도 추출
between and 문으로도 가능..
- *ERROR 1366: 1366: Incorrect string value: ' for column 'name' at row 1
워크벤치에서 갑자기 테이블 생성이 안돼서 검색을 해보니 문자형-한글은 UTF8 따로 설정을 해줘야한다 한다. 저장공간도 생각해야 한다.
char-고정크기 문자형/varchar-가변크기 문자형
(이름같이 짧은 문자는 char로 적게 잡는 것이 효율적일 것 같다.)
문제가 생기면 에러 코드를 자세히 읽어봐야 빨리 해결된다.
