코딩공부/데이터베이스, SQL

엑셀보다 쉬운 SQL 2주차

정해인3 2023. 4. 26. 21:54

데이터 분석의 목적: 쌓여있는 날것의 데이터 -> 의미를 갖는 정보로 변환

  • 데이터베이스 테이블에 저장된 데이터: 쌓여있는 날것의 데이터
  • 가장 많은 Like를 받은 사람의 이름, 전체 신청자수, 평균 연령: 의미있는 '정보' (통계: 최대/최소/평균/개수)
  • 더 나아가 범주(category) 각각의 정보가 필요할 수 있다(예: 과목별 신청자 평균 연령, 과목별 신청자 수, 성씨별 회원수 등)

기존 방법(1주차): 성씨별 회원 수를 구할 때 -> 54개의 성씨가 있으니 54 개의 쿼리를 작성

해결 방법

  • 동일한 범주의 데이터를 묶어주는 Group by
    • Group by: 동일한 범주를 갖는 데이터를 하나로 묶어 범주별 통계를 내줌 (54개의 회원 성씨 -> 1)같은 성씨의 데이터를 하나로 묶고 2) 각 성씨의 회원수를 구할 수 있음)
SELECT name, count(*) FROM users
group by name

 

  • 깔끔하게 데이터를 정렬해주는 Order by
select name, count(*) from users
group by name
order by;


Group by 학습하기

SELECT name, count(*) FROM users
group by name

SQL문 작성시: 위에서 부터 쓰기(x), 사용하는 순서대로 쓰기(o)

위의 쿼리가 실행되는 순서: from -> group by -> select

  1. from users: users 테이블 데이터 전체를 가져옵니다.
  2. group by name: users 테이블 데이터에서 같은 name을 갖는 데이터를 합쳐준다.
  3. select name, count(*): name에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어준다.
  4. 예) 이**, 이**, 김**, 김**, 박** 이렇게 데이터가 있었다면, 이는 2개, 김은 2개, 박**은 1개

Group by, Order by 함께 사용하기

 

동일한 범주의 갯수 구하기-count(*) 사용

select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세어주고 싶은 필드명;

동일한 범주의 최소값 구하기-min(알고 싶은 필드) 사용

SELECT week, min(likes) from checkins
group by week

동일한 범주의 최대값 구하기-max(알고 싶은 필드) 사용

SELECT week, max(likes) from checkins
group by week

평균값 구하기-avg(알고 싶은 필드) 사용

SELECT week, avg(likes) from checkins
group by week
  • 소수점 2번째 자리까지만 구하기-round(평균, n)
SELECT week, ROUND(avg(likes),2) from checkins
group by week

합계 구하기-sum(알고 싶은 필드) 사용

SELECT week, SUM(likes) from checkins
group by week

order by 사용하기

정렬 사용: 계산을 다 한 뒤 마지막, 기본적으로 오름차 순

SELECT name, count(*) FROM users 
group by name 
order by count(*)

내림차순: desc(descending)

SELECT name, count(*) FROM users 
group by name 
order by count(*) desc

오름차순: asc(ascending)

SELECT name, count(*) FROM users 
group by name 
order by count(*) asc

사용 규칙

select * from 테이블명
order by 정렬의 기준이 될 필드명;

order by는 group by랑 반드시 함께 사용하는 것이 아님

SELECT * FROM checkins
order by likes desc

실행 순서

from -> group by -> select -> order by

(;)은 안 붙여도 가능, 끝 표시용

 

WHERE와 함께 사용하기

where, group by, order by 함께 사용하기

SELECT payment_method,count(*) FROM orders o 
WHERE course_title = '웹개발 종합반'
GROUP by payment_method 
order by count(*)

실행 순서

from -> Where -> group by -> select -> order by

 

*자주내는 에러 

group by를 사용할 때: 무엇을 통계 내는지 지정해 주지 않는 것-> 어떤 통계치로 출력해달라는 명령어 추가

통계치는 나왔지만 어떤 범주에 대한 것인지 나와있지 않는 것 -> group by에 들어간 필드를 select문에 적어주기

 

에러 메세지 -> 당황하지 않고 읽기/ 원하는 결과가 나오지 않을 때 -> 이유를 생각해 보기

 

ORDER BY의 다양한 활용

문자열로 정렬하기

SELECT * FROM users u 
order by name

시간으로 정렬하기

SELECT * FROM users u 
order by created_at

 

퀴즈

1)

SELECT payment_method, COUNT(*) FROM orders
WHERE course_title = '앱개발 종합반'
GROUP BY payment_method

2)

SELECT name, COUNT(*) FROM users 
WHERE email LIKE '%gmail.com'
GROUP BY name

3)

SELECT course_id, ROUND(AVG(likes),2)  FROM checkins
GROUP BY course_id

팁)쿼리 작성할 때

1)show tables로 어떤 테이블이 있는지 살펴보기

2)제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 사용하기

3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기

4) 테이블을 찾으면 -> 범주를 나눠서 보고싶은 필드를 찾기

5) 범주별로 통계를 보고싶은 필드를 찾기

6) SQL 쿼리 작성하기

 

별칭 Alias(알리아스)

쿼리가 길어질 때 사용

예시 1)

SELECT * FROM orders o
WHERE o.course_title ='앱개발 종합반'

o는 orders의 별칭

구체적으로 쿼리 지시

예시 2)

SELECT payment_method, COUNT(*) as cnt  FROM orders o
WHERE o.course_title ='앱개발 종합반'
GROUP BY payment_method

cnt는 COUNT(*)의 별칭

 

2주차 숙제

SELECT payment_method, COUNT(*) FROM orders o 
WHERE email LIKE '%naver.com' AND course_title = '앱개발 종합반'
GROUP BY payment_method