[패스트캠퍼스: SQL 데이터 분석 첫걸음] Week 2

[패스트캠퍼스: SQL 데이터 분석 첫걸음] Week 2 - SQL을 활용해 데이터 다루기

2023. 5. 25. 03:54ㆍLearn

728x90

본 게시글은 내일배움카드로 신청한 패스트캠퍼스의 국비지원교육 강의: SQL 데이터 분석 첫걸음입니다.
본 게시글은 패스트캠퍼스의 열공 챌린지 형식을 준수하며, 개인적인 정리 목적으로 학습 내용을 정리한 글입니다.
- 제목, 본문, 해쉬태그 키워드 포함
- 사진 1장 이상, 글자수 500자 이상 (공백 포함, 코드 제외)

강의 자료: https://drive.google.com/drive/folders/1Etbo7qDoP7rGUHOfSU3SZP1yFuMJ6vWP

DAY 5

ORDER BY ( [column_name | number] [(default: ASC) | DESC], ... )

ROW_NUMBER | (DENSE)RANK() OVER (ORDER BY [] () ) AS []

문자형 데이터 함수

index+1 (0 if null) LOCATE(find_ch, str)
str[idx_from+1:] SUBSTRING(src_str, idx_from+1)
[-n:] [:n] RIGHT / LEFT (str, n)
UPPER / LOWER
len(str) LENGTH(str)
strA+strB CONCAT(strA, strB)
REPLACE(str_src, str_from, str_to)

숫자형 데이터 함수

abs(n)
CEILING / FLOOR (n)
ROUND / TRUNCATE (n, digit)
POW(ER)(base, pow)
a%b MOD(a, b)

날짜형 데이터 함수

NOW, CURRENT_DATE/TIME()
YEAR, MONTH(NAME)(-)
DAYNAME(-), DAYOFMONTH | WEEK(-), WEEK(-)
HOUR / MINUTE / SECOND
DATE_FORMAT(date/time, format)
DATE | TIMEDIFF(date1, date2)

/*
 * START MISSION
 */
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
	 number INT,
       name	VARCHAR(20),
       type	VARCHAR(10),
       attack INT,
       defense INT,
       capture_date DATE
);
INSERT INTO mypokemon
		(number, name, type, attack, defense, capture_date)
VALUES
		(10, 'caterpie', 'bug', 30, 35, '2019-10-14'),
        (25, 'pikachu', 'electric', 55, 40, '2018-11-04'),
        (26, 'raichu', 'electric', 90, 55, '2019-05-28'),
        (125, 'electabuzz', 'electric', 83, 57, '2020-12-29'),
        (133, 'eevee', 'normal', 55, 50, '2021-10-03'),
        (137, 'porygon', 'normal', 60, 70, '2021-01-16'),
        (152, 'chikoirita', 'grass', 49, 65, '2020-03-05'),
        (153, 'bayleef', 'grass', 62, 80, '2022-01-01');

/*
 * MISSION 1~3
 */
SELECT name, LENGTH(name) FROM mypokemon ORDER BY LENGTH(name);
SELECT name, RANK() OVER(ORDER BY defense DESC) AS defense_rank FROM mypokemon;
SET @v_date_from = "2022-02-14";
SELECT name, datediff(@v_date_from, capture_date) AS days FROM mypokemon;

/*
 * MISSION 1~15
 */
SELECT right(name, 3) AS last_char FROM mypokemon;
SELECT left(name, 2) AS left2 FROM mypokemon;
SELECT replace(name, 'o', 'O') AS bigO FROM mypokemon WHERE name LIKE "%o%";
SELECT name, upper(concat(left(type,1), right(type,1))) AS type_code FROM mypokemon;
SELECT * FROM mypokemon WHERE length(name) > 8;
SELECT round(avg(attack),0) AS avg_of_attack FROM mypokemon;
SELECT truncate(avg(defense),0) AS avg_of_defense FROM mypokemon;
SELECT name, pow(attack, 2) AS attack2 FROM mypokemon WHERE length(name) < 8;
SELECT name, attack%2 AS div2 FROM mypokemon;
SELECT name, abs(attack-defense) AS diff FROM mypokemon WHERE attack <= 50;
SELECT date(now()) AS now_date, time(now()) AS now_time;
SELECT month(capture_date) AS month_num, monthname(capture_date) AS month_eng FROM mypokemon;
SELECT weekday(capture_date) AS day_num, date_format(capture_date, "%W") AS day_eng FROM mypokemon;
SELECT year(capture_date) AS year, month(capture_date) AS month, day(capture_date) AS day FROM mypokemon;

** 이전 강의에서 거듭제곱을 ^ (caret)으로 수행 가능하다고 설명했는데, 확인 결과 사실이 아닌 것으로 판명됐다.

MySQL :: MySQL 8.0 Reference Manual :: 12.12 Bit Functions and Operators

본 강의에서는 정상적으로 pow 함수를 사용하여 오류가 없었다.

DAY 6

GROUP BY (col, group_function) HAVING (cond: group_function)

/*
 * START MISSION
 */
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number int,
name varchar(20),
type varchar(10),
height float,
weight float
);
INSERT INTO mypokemon
(number, name, type, height, weight)
VALUES
(10, 'caterpie', 'bug', 0.3, 2.9),
(25, 'pikachu', 'electric', 0.4, 6),
(26, 'raichu', 'electric', 0.8, 30),
(125, 'electabuzz', 'electric', 1.1, 30),
(133, 'eevee', 'normal', 0.3, 6.5),
(137, 'porygon', 'normal', 0.8, 36.5),
(152, 'chikoirita', 'grass', 0.9, 6.4),
(153, 'bayleef', 'grass', 1.2, 15.8),
(172, 'pichu', 'electric', 0.3, 2),
(470, 'leafeon', 'grass', 1, 25.5);

/*
 * MISSION 1~2
 */
 SELECT type, AVG(weight)
 FROM mypokemon
 WHERE length(name) > 5
 GROUP BY type
 HAVING AVG(weight) >= 20
 ORDER BY AVG(weight) DESC;
 
 SELECT type, MIN(height), MAX(height)
 FROM mypokemon
 WHERE number < 200
 GROUP BY type
 HAVING MAX(weight) >= 10 AND MIN(weight) >= 2
 ORDER BY MIN(height) DESC, MAX(height) DESC;

DAY 7

IF (cond, if_true, if_false)

IS NULL

IFNULL

CASE

WHEN cond_a THEN result_a

~~== OR ==~~

CASE [column]

WHEN value_a THEN result_a

(ELSE result_else)

DEFAULT NULL

END

DELIMITER //

CREATE FUNCTION func_name( col1_name col1_type, ... )

RETURNS result_type

BEGIN

DECLARE var1_name var1_type;

DECLARE var2_name var2_type;

SET var1 = col1_name;

SELECT var1 * var1 INTO var2;

RETURN var2;

END//

DELIMITER ;

MySQL :: MySQL 8.0 Reference Manual :: 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements

** 프로시저 또는 함수의 정의 과정 중 DELIMITER를 // (이중 슬래쉬)로 재정의하는 이유:
함수 내부에 사용된 기존 delimiter(;)를 쿼리로 취급하지 않고 서버로 전달하기 위해

때문에 위 사진에 정의된 함수 hello처럼 내부에 ; 가 필요없는 함수는 재정의 없이 바로 정의가 가능하다.

/*
 * START MISSION
 */
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number int,
name varchar(20),
type varchar(10),
attack int,
defense int
);
INSERT INTO mypokemon (number, name, type, attack, defense)
VALUES (10, 'caterpie', 'bug', 30, 35),
(25, 'pikachu', 'electric', 55, 40),
(26, 'raichu', 'electric', 90, 55),
(125, 'electabuzz', 'electric', 83, 57),
(133, 'eevee', 'normal', 55, 50),
(137, 'porygon', 'normal', 60, 70),
(152, 'chikoirita', 'grass', 49, 65),
(153, 'bayleef', 'grass', 62, 80),
(172, 'pichu', 'electric', 40, 15),
(470, 'leafeon', 'grass', 110, 130);

/*
 * MISSION 1
 */
 
DELIMITER //
CREATE FUNCTION isStrong( par_atk INT, par_def INT )
    RETURNS VARCHAR(20)
BEGIN
-- 	DECLARE plus INT DEFAULT par_atk + par_def;
-- 	DECLARE result VARCHAR(20);
-- 	SELECT CASE
-- 		WHEN plus > 120 THEN "very strong"
-- 		WHEN plus >  90 THEN "strong"
--         ELSE "not strong"
-- 	END INTO result;

	DECLARE plus INT DEFAULT par_atk + par_def;
	DECLARE result VARCHAR(20) DEFAULT "not strong";
	CASE
		WHEN plus > 120 THEN SET result = "very strong";
		WHEN plus >  90 THEN SET result = "strong";
        ELSE BEGIN END;
	END CASE;
	RETURN result;
END
//
DELIMITER ;
    
SELECT name, isStrong(attack, defense) AS isStrong FROM mypokemon;

# TYPE 1

DECLARE plus INT DEFAULT par_atk + par_def;
DECLARE result VARCHAR(20);
SELECT CASE
        WHEN plus > 120 THEN "very strong"
        WHEN plus >  90 THEN "strong"
        ELSE "not strong"
END INTO result;

RETURN result;

# TYPE 2

DECLARE plus INT DEFAULT par_atk + par_def;
DECLARE result VARCHAR(20) DEFAULT "not strong";
CASE
        WHEN plus > 120 THEN SET result = "very strong";
        WHEN plus >  90 THEN SET result = "strong";
        ELSE BEGIN END;
END CASE;
RETURN result;

** BEGIN, END는 프로그래밍 언어 코딩에서의 중괄호 { }와 비슷하게 context를 만들어주는 역할을 하는 것 같다.

그래서 단일 (반환; RETURN) 쿼리로 구성된 lambda형 함수는 BEGIN / END가 필요없고, 다중 쿼리로 구성되면 필요하다.

** CASE는 다중 조건에 대한 서브 루틴을 정의하기 위해 void형 또는 return이 존재하는 함수 형태로 활용할 수 있다.

DAY 8

드디어 나왔다 JOIN... 서로 다른 테이블 병합

SELECT [ table ].[col]
X JOIN [ ]

ON [ ].[ ] = [ ].[ ]

INNER JOIN: 교집합
LEFT/RIGHT JOIN: 기준 설정. 설정한 기준 값에 대해서만 연결.
~~OUTER JOIN: 합집합~~
OUTER 키워드가 없으므로, LEFT와 RIGHT JOIN의 UNION으로 정의.
- UNION; 집합 연산자: 두 쿼리의 결과에 대해 중복 제외 병합
CROSS JOIN: 기준 불필요. 각 테이블의 각 record가 모두 '조합'됨.
~~SELF JOIN: 동일 테이블 INNER JOIN.~~
두 개의 동일 테이블은 각각 별명으로 구분되어야 함.

/*
 * READY MISSION
 */
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;

/*
 * TABLE_A: mypokemon
 */
CREATE TABLE mypokemon(
	number INT,
	name VARCHAR(20),
	type VARCHAR(10)
);
INSERT INTO mypokemon
(number, name, type)
VALUES
(10, 'caterpie', 'bug'),
(25, 'pikachu', 'electric'),
(26, 'raichu', 'electric'),
(133, 'eevee', 'normal'),
(152, 'chikoirita', 'grass');

CREATE TABLE ability (
	number INT,
	height FLOAT,
	weight FLOAT,
	attack INT,
	defense INT,
	speed int
);

/*
 * TABLE_B: ability
 */
INSERT INTO ability
(number, height, weight, attack, defense, speed)
VALUES
(10, 0.3, 2.9, 30, 35, 45),
(25, 0.4, 6, 55, 40, 90),
(125, 1.1, 30, 83, 57, 105),
(133, 0.3, 6.5, 55, 50, 55),
(137, 0.8, 36.5, 60, 70, 40),
(152, 0.9, 6.4, 49, 65, 45),
(153, 1.2, 15.8, 62, 80, 60),
(172, 0.3, 2, 40, 15, 60),
(470, 1, 25.5, 110, 130, 95);

/*
 * START MISSION
 */
SELECT
	mypokemon.name,
	ability.attack, ability.defense
FROM mypokemon
LEFT JOIN ability ON mypokemon.number = ability.number;

SELECT
	ability.number,
	mypokemon.name	
FROM ability
LEFT JOIN mypokemon ON mypokemon.number = ability.number;

-- SELECT
-- 	ability.number,
-- 	mypokemon.name
-- FROM mypokemon
-- RIGHT JOIN ability ON mypokemon.number = ability.number;

** 서로 다른 두 개의 테이블을 나열한 상태에서 기준이 좌측이냐, 우측이냐 결정하는 것은 쿼리의 재사용성 관점에서는 더 낫겠지만, 가독성이나 이해의 관점에서는 기준이 될 테이블을 항상 좌측으로 고정시켜둔 상태로 LEFT JOIN하는 것이 더 간편할 것 같다. (아직까지는...)

728x90

저작자표시 비영리 동일조건

'Learn' 카테고리의 다른 글

[패스트캠퍼스: SQL 데이터 분석 첫걸음] Week 4 - SQL 실무 감각 익히기 (0)	2023.06.05
[패스트캠퍼스: SQL 데이터 분석 첫걸음] Week 3 - SQL 고급 기능 익히기 (0)	2023.05.30
[패스트캠퍼스: SQL 데이터 분석 첫걸음] Week 1 - SQL과 친해지기 (0)	2023.05.24
[코딩 테스트 준비] 그래프 (0)	2023.05.13
[패스트캠퍼스: Java&Spring 웹 개발] Week 1 - Java Basic (0)	2023.05.09

데이터 과학자의 꿈