2/28 데이터베이스(데이터분석)

mm11game·2021년 2월 28일
0

데이터 조회로 기본 다지기

SELECT / WHERE

WHERE의 조건(BW <> IN)

ㄴ WHERE age > 27;
ㄴ WHERE age BETWEEN 30 AND 39; (30세이상 39세이하)
ㄴ WHERE age NOT BETWEEN 30 AND 39;
ㄴ WHERE day > '2019-01-01' (날짜타입에도 부등호가 가능)
ㄴ SELECT * FROM member WHERE age IN (20, 30);

문자열 패턴 매칭 조건 (LIKE BINARY)

SELECT * FROM member WHERE address LIKE '서울%';
SELECT * FROM member WHERE address LIKE BINARY '서울%';
ㄴ 서울로 시작하는거 제외하고 조회

날짜 관련 함수

YEAR/MONTH/DAYOFMONTH
DATEDIFF(sign_day, '2010-01-01')
등등등 ... 더 찾아볼것

여러개 조건 걸기

SELECT * FROM member
WHERE gender = 'm' AND address LIKE '%서울' AND age > 25; (AND를 쓰기)
WHERE MONTH(day) BETWEEN 3 AND 5
OR MONTH(day) BETWEEN 9 AND 11;(OR를 쓰기)

데이터 정렬해서 보기(ORDER BY)

SELECT * FROM member
ORDER BY height DESC;
SELECT * FROM member
ORDER BY YEAR(day) DESC, email ASC;
ㄴ 먼저 연도별로 내림차순으로, 그다음에 email기준으로 오름차순

데이터 일부만 추려보기(LIMIT)

SELECT * FROM member
ORDER BY height DESC LIMIT 10;
SELECT * FROM member
ORDER BY height DESC LIMIT 8, 2;
ㄴ 8번째 인덱스부터 2개를 출력

데이터 분석 단계로 나아가기

COUNT / MAX / MIN / AVG

ㄴ 만약에 NULL값이 포함이 되어있으면, 그걸 제외하고 개수를 센다.
ㄴ 그냥 전체 수를 구하려면 COUNT(*)를 쓰면 된다.
ㄴ AVG를 구할때, NULL을 제외하고 평균을 구하므로 평균이 잘 나온다.

SUM / STD / CEIL / FLOOR / ROUND

NULL 다루기 (COALESCE)

SELECT * FROM member WHERE adress IS NULL;
SELECT * FROM member WHERE adress IS NOT NULL;
SELECT
COALESCE(height, "@@@"),
COALESCE(weight, "---")
FROM member;
ㄴ 만약에 NULL이면 @@@이나 ---으로 대체한다.

컬럼끼리 계산하기

SELECT email, weight/height FROM member;
ㄴ 몸무게나 키가 둘다 NULL이 있으면 계산값도 NULL

AS(alias) / CONCAT

ㄴ concat은 괄호 안의 값을 모두 더해서 보여준다

고유값만보기 (DISTINCT / SUBSTRING)

SELECT DISTINCT(gender) FROM member;
ㄴ m과 f만 출력이 된다. 즉 gender에 들어가는 고유한 값만 출력
ㄴ 만약 (address)를 하게 되면, 전부 고유값이 되므로 전부가 출력이 된다.
SELECT DISTINCT(SUBSTRING(address, 1, 2)) FROM member;
ㄴ 앞에서 2글자를 추출해서, 그걸 기준으로 고유값을 나눈다.
ㄴ 즉 서울, 경기, 부산, 전라 등으로 나누게 된다.
SELECT COUNT(DISTINCT(SUBSTRING(address, 1, 2))) FROM member;
ㄴ 카운트 사용도 가능하다

GROUB BY

SELECT gender FROM member GRUOP BY gender;
ㄴ DISTINCT와는 완전히 다르다
ㄴ 그룹안에 하나의 그룹의 rows가 전부 들어가 있다.
SELECT gender, COUNT() FROM member GRUOP BY gender;
ㄴ 이렇게하면?
m / 15
f / 10
ㄴ 남자 여자의 각각의 개수가 출력이 된다.
SELECT gender, COUNT(
), AVG(heigth) FROM member GRUOP BY gender;
ㄴ 이건? 각 그룹의 평균키가 출력이 된다.
SELECT SUBSTRING(address, 1, 2), gender, COUNT() FROM memeber GROUP BY SUBSTRING(address, 1, 2), gender;
ㄴ 이렇게되면 주소의 앞자리 2글자를 기준으로 그룹이 된다.
ㄴ 그 이후 gender로 또다시 그룹을 나눈것이다.
ㄴ 즉, 서울에사는 남자와 여자를 나누는것이다.
SELECT SUBSTRING(address, 1, 2), gender, COUNT(
) FROM memeber GROUP BY SUBSTRING(address, 1, 2), gender
HAVING SUBSTRING(address, 1, 2) = '부산'
;
ㄴ 이렇게하면? 부산에 사는 남자 그룹의 숫자, 여자의 숫자만 보여준다.
WHERE은 테이블에서 맨처음 rows를 조회할때 쓰고,
HAVING은 이미 조회된 곳에서 다시 필터링을 할 때 쓴다.

GROUB BY를 할 때 순서

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

테이블 조인(JOIN)을 통한 깊이 있는 분석

SELECT p.name, COALESCE(s.sales_volume, '판매량 정보 없음') AS '판매량'
FROM pizza_price_cost AS p
LEFT OUTER JOIN sales AS s
ON p.id = s.menu_id

UNION

SELECT * FROM item
UNION
SELECT * FROM item_new;
ㄴ 이렇게하면 두 테이블을 완전히 합친다. 같은 값은 1개만 출력이 된다.

의미있는 데이터 추출하기

여성회원들 중에서 가장 별점이 높은 값을 추출 하시오
SELECT item.id, item.name, AVG(star)
FROM ~~~~ (join~on~)
WHERE member.gender = 'f'
GROUP BY item.id, item.name
ORDER BY AVG(star) DESC;
/////////////////////////////////////////
SELECT item.id, item.name, AVG(star), COUNT(*)
FROM ~~~~ (join~on~)
WHERE member.gender = 'f'
GROUP BY item.id, item.name
HAVING COUNT(*) > 1
ORDER BY AVG(star) DESC;
ㄴ 카운트를 추가하면? 별점마다 몇명이 별점을 줬는지 알 수가 있다.
ㄴ 또한 1명 이상이 별점을 준 것만 추린다.
/////////////////////////////////////////
남녀 공용 상품의 등록 연도별 평균 별점을 추출 하시오
1.남녀 공용 상품 WHERE item.gender = 'u'
2.등록 연도별 GROUP BY YEAR(item.registration_date)
3.평균 별점 AVG(star)
SELECT
YEAR(i.registration_date) AS '등록 연도',
COUNT() AS '리뷰 개수',
AVG(r.star) AS '별점 평균값'
FROM item AS i
INNER JOIN review AS r ON i.id = r.item_id
LEFT OUTER JOIN member AS m ON r.mem_id = m.id
WHERE i.gender = 'u'
GROUP BY YEAR(i.registration_date)
HAVING COUNT(
) >= 10
ORDER BY YEAR(i.registration_date)

profile
code newby

관심 있을 만한 포스트

0개의 댓글