쿼리 실행 순서
FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT
Subquery
- where
카카오페이로 결제한 주문건 유저
SELECT * FROM users u
where user_id in (
SELECT user_id from orders
where payment_method = 'kakaopay'
//실행 순서 1
)
//실행 순서 2- select
SELECT c.checkin_id,
c.user_id,
c.likes,
//외부 쿼리문을 실행할 때마다 안의 쿼리문 실행
(
SELECT avg(likes) FROM checkins
where user_id = c.user_id
//ex. c.user_id =4b8a10e6
) as avg_likes_user
//유저별 좋아요 평균
FROM checkins c
- from
/*SELECT user_id, round(avg(likes),1) as avg_likes FROM checkins c
group by user_id
두 쿼리를 연결하는 방법
SELECT user_id, point FROM point_users pu
*/
SELECT pu. user_id, pu.point, a.avg_likes point FROM point_users pu
inner join(
SELECT user_id, round(avg(likes),1) as avg_likes FROM checkins c
group by user_id
)a on pu.user_id = a.user_id
//유저별 포인트와 라이크 평균의 상관관계 구하기
전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
SELECT * FROM point_users pu
WHERE point > (
SELECT round(avg(point),-1)
FROM point_users pu
)이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
SELECT * FROM point_users pu
WHERE point > (
SELECT avg(pu.point) from point_users pu
inner join users u on pu.user_id = u.user_id
where u.name = '이**'
)SELECT * FROM point_users pu
WHERE point > (
SELECT avg(point) from point_users pu
where user_id in (
SELECT user_id FROM users where name = '이**'
)
)checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
/*SELECT checkin_id,
course_id,
user_id,
likes,
()
FROM checkins c
SELECT avg(likes) FROM checkins c
WHERE course_id = '5f0ae408765dae0006002817'*/
SELECT checkin_id,
course_id,
user_id,
likes,
(
SELECT avg(likes) FROM checkins
WHERE course_id = c.course_id
) as avg
FROM checkins c
checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
SELECT c.checkin_id,
c.course_id,
c.user_id,
c.likes,
(
SELECT round(avg(likes),1) FROM checkins
WHERE course_id = c.course_id
) as avg,
c2.* //c2리스트가 모두 잘 붙었는지 보기
FROM checkins c
inner join courses c2 on c.course_id = c2.course_id
SELECT c.checkin_id,
c2.title,
c.user_id,
c.likes,
(
SELECT round(avg(likes),1) FROM checkins
WHERE course_id = c.course_id
) as avg,
FROM checkins c
inner join courses c2 on c.course_id = c2.course_id course_id별 like 개수에 전체 인원을 붙이기
//준비1
SELECT course_id, count(DISTINCT(user_id)) as cnt_checkins FROM checkins c
group by course_id
//준비2
SELECT course_id, count(*) as cnt_total FROM orders o
group by course_id
//결과
select a.course_id, a.cnt_checkins, b.cnt_total from
(
SELECT c.course_id, count(DISTINCT(c.user_id)) as cnt_checkins FROM checkins c
group by course_id
)a
inner join
(
SELECT o.course_id, count(*) as cnt_total FROM orders o
group by o.course_id
)b
on a.course_id = b.course_id
//퍼센트
select a.course_id, a.cnt_checkins, b.cnt_total, (a.cnt_checkins/b.cnt_total) from
(
SELECT c.course_id, count(DISTINCT(c.user_id)) as cnt_checkins FROM checkins c
group by course_id
)a
inner join
(
SELECT o.course_id, count(*) as cnt_total FROM orders o
group by o.course_id
)b
on a.course_id = b.course_id
//강의이름
select c2.title, a.cnt_checkins, b.cnt_total, (a.cnt_checkins/b.cnt_total) from
(
SELECT c.course_id, count(DISTINCT(c.user_id)) as cnt_checkins FROM checkins c
group by course_id
)a
inner join
(
SELECT o.course_id, count(*) as cnt_total FROM orders o
group by o.course_id
)b
on a.course_id = b.course_id
inner join courses c2 on b.course_id = c2.course_id with
with table1 as (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
), table2 as(
select course_id, count(*) as cnt_total from orders
group by course_id
)
select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id- 문자열
substring (쪼개기)
SELECT user_id,email, substring_index(email,'@',1) FROM users u
//앞부분
SELECT user_id,email, substring_index(email,'@',-1) FROM users u
//뒷부분문자열 일부만 출력하기
SELECT order_no, created_at, SUBSTRING(created_at,1,10) FROM orders o
//시작 포인트, 글자수
SELECT SUBSTRING(created_at,1,10) as date, count(*) FROM orders o
group by date
//기간별 주문량case
SELECT pu.user_id, point,
(case when pu.point>10000 then '잘하고 있어요!'
else '조금만 더 파이팅!'end) as msg
FROM point_users pu
//구간 설정
select a.lv, count(*) from (
SELECT pu.user_id, point,
(case when pu.point>10000 then '1만이상'
when pu.point>5000 then '5천 이상'
else '5천 미만'end) as lv
FROM point_users pu
)a
group by a.lv
//구간별 데이터 통계평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!'
SELECT pu.point_user_id,
pu.point,
(case when point > (select avg(point) FROM point_users pu) then '잘하고있어요!'
else '열심히 합시다!' end)as msg
from point_users pu이메일 도메인별 유저수 세기
select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력
SELECT a.enrolled_id, a.total_count, b.done_count from
(SELECT enrolled_id, count(*) as total_count from enrolleds_detail
group by enrolled_id )a
inner join
(SELECT enrolled_id, count(*) as done_count FROM enrolleds_detail
where done = '1'
group by enrolled_id
)b
on a.enrolled_id = b.enrolled_id
//subquery
with table1 as (
SELECT enrolled_id, count(*) as total_count from enrolleds_detail
group by enrolled_id
),
table2 as(
SELECT enrolled_id, count(*) as done_count FROM enrolleds_detail
where done = '1'
group by enrolled_id
)
SELECT a.enrolled_id,
a.total_count,
b.done_count,
round(b.done_count/a.total_count,2) as ratio
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id
//with, ratio
select enrolled_id,
sum(done) as cnt_done,
count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id
//count대신 sum 사용