Now Loading ...
-
SQL: 데이터 분석 고급 SQL
연재중
현재 해당 과정은 하나씩 여러가지의 답변을 기준으로 작성해나가는 중임.
섹션1
DML(Data Manipulation Language)
데이터 조작하는 언어로
데이터에 변형을 가하는 종류다. (삽입, 수정, 삭제)
= SELECT, INSERT, UPDATE, DELETE
DDL와 다른점은 문구이 다르고,
DDL은 영구변화 DML은 임시 변경 후 뒤로 가기 가능 차이
1-1)INSERT
INSERT INTO (table name) VALUES (Value_list);
컬럼의 순서대로 입력한다.
Table: Salary
| ID | NAME | SALARY | DATE |
| —- | —- | —— | —- |
예시인데,
INSERT INTO Salary Values(‘1’,’A’,’250’,2020-03-31’)
하면 순서대로 값에 들어가는 것이다.
대신 지정해서 넣을수도 있다.
INSERT INRO Salary(Id, Salary) VALUES (‘2’,’550’)
이러면 2, Null, 550, Null로 들어간다. (디폴트 = Null)
1-2)UPDATE
UPDATE (table name) SET colunme = values
이는 내용을 수정하는 것이다.
UPDATE Salary SET Salary = Salry + 100;
을 하면 기존의 Salary 테이블에 Salary값이 +100이 되어 수정된다.
즉 SET에서 =는 비교가 아닌 대입연산자다.
만약 특정 행만 넣고 싶다면?
...Salary + 100 Where ID =2;
WHERE 조건문을 통해서 ID 가 2인 행만 적용시킨다.
그러면 ID가 2인 행만 Salary값이 늘어난다.
1-3) DELECT
DELECT (DELETE FROM (table name) + WHERE (requirement)
컬럼은 삭제하되 구조는 남은 상태라고 생각하면 된다.
기본적 형식은 UPDATE와 같음,
해당 관련하여
LeetCode 627. Swap Salary ,
LeetCode 196. Delete Duplicate Emails
를 풀것이다.
LeetCode 627. Swap Salary
문제 : Salary Table에서 아래 Example과 같은 Table에서 M=Male F=Female 이다. m와 f값을 스왑해달라.
Temp를 쓰지말고 UPDATE statement를 써서 바꿔라.
NOT SELECT .
즉 그러면 UPDATE _ SET_ 문을 써야되니까.
간단하게 입력을 해보자.
UPDATE "table"
SET "column"
예시로 CASE _ WHEN _ THEN를 써서 하나씩 여러개 바꿀 수 있다.
UPDATE _
SET _ = case
WHEN (조건) THEN (변할 예정 값) --조건이 True면 THEN 변경
..
ELSE '_' --위 조건이 모두 아닌 경우의 출력
END ; //끝내기
-- 답안A
UPDATE salary
SET sex = CASE WHEN sex='f' THEN 'm'
ELSE 'f'
END;
설명
UPDATE 테이블 SET 성이 F면 m으로 바꾸면 아니면 f로 변경
--답안B
UPDATE salary
SET sex = IF(sex = 'f', 'm', 'f');
설명
만약 성이 F면 M으로 아니면 F로 변경한다. 위랑 비슷한데
여러가지 측면에서 A가 낫다고 생각이 든다.
(간단한 경우는 이거고 다중 조건이면 위가 더?)
LEET CODE 196. Delete Duplicate Emails
문제: 중복이 되는 이메일을 삭제하고 싶다. (Person Table)
하지만 최소한 한 개는 남겨야한다.(ID가 smallest인것만)
즉, John 중복을 날리는데 3번은 날려야하는 것
필요개념) 서브쿼리 = 쿼리 안에 쿼리를 통해서 조건부여
-- Email로 묶고 ID가 작은 경우를 출력하는 거다.
SELECT Email, MIN(Id)
FROM Person
GROUP BY Email
즉 그러면 우리가 했던 값들이 보관해야되는 것이 되는 것이다 = 서브쿼리
SELECT sub.min_id
FROM (
SELECT Email, Min(Id) AS min_id
FROM Person
Group By Email)
sub
실행하면 오류가 걸림
why? 동작하지 못하게 세팅이 되어있어서 짤라야된다고 함
-- 정답
DELETE
FROM Person
WHERE ID Not in (
SELECT sub.min_id
FROM(
SELECT Email, Main(Id) AS min_id
FROM Person
Group By Email) sub)
ID컬럼을 기준으로
서브쿼리 제외하고 나머지를 DELECT하는 구문을 생성
NOT IN을 통해서 (…)안에 있는 것을 제외한 나머지를 삭제 시키는 것이다.
-- 여러가지 연습 Row_number
DELETE FROM Person
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as id_rank
FROM Person
) ranked_emails
WHERE id_rank > 1
);
-- Row_number
내부쿼리의 동작
Row_number로 사용했다.
윈도우 함수 Row_number는 순위시스템이다.
ROW_NUMBER () OVER (PARTITON BY ‘그룹화’ ORDER BY ‘순위기준점’)
(PARTITION BY email ORDER BY id) 이니까
이메일로 그룹을 짜주고 ID를 순위로 매긴다. 그러면 ID가 작으면 1순위.
외부쿼리의 동작
SELECT id
FROM ( ... ) ranked_emails -- 내부 서브쿼리
WHERE id_rank > 1
id_rank가 1순위가 아닌 것을 뽑는다. (즉 1제외 중복된 것 거름망)
DELECT동작
DELECT
WHERE id IN ( 외부 서브 쿼리)
로 외부 서브 쿼리에 해당하는 것들을 제거해준다.
-- 여러가지 문제연습 CTE
-- Row_number는 CTE로 바꿀 수 있다.
-- CTE?: Common Table Expressions = 임시 데이터 세트
-- 위에서 쿼리를 단계별로 정리했으니
/*
=> CTE변경
WITH CTE AS (
내부서브쿼리
)
DELECT
WHERE id IN ( 외부 서브 쿼리)
*/
WITH CTE_practice AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as id_rank
-- 서브쿼리
FROM Person
)
DELETE FROM Person -- DELECT 문
WHERE id IN (SELECT id FROM CTE_practice WHERE id_rank > 1); -- 외부쿼리
로 나타냈는데 실무적으로 무엇이 좋은 코드(?)인가에 대해서 질문을 남겼으니 추후 확인예정이다.
-
SQL: 데이터 분석 중급 SQL
수료
중급 수강을 하였는데 대부분 학교에서 들었던 내용을 실습하고 개념을 복습하는 기분이라 좋았다.
근데 마지막 문제는 조금 어려웠을지도?
간단내용
SQL중급반은 기존의 기초반과 같이 SQL이론을 설명 후 코딩 테스트를 통한 실습을 진행한다.
중급반은 추가적으로 리트코드 사이트를 추가적으로 진행할 것이고
해커랭크에서 부족한 부분을 리트코드 사이트로 보완한다.
목표는 프로그래머스 고득점 kit에 있는 SQL 문제들을 쉽게 풀 수 있도록 한다.
플러스 문제 풀이 팁이나 SQL작성 요령들을 공유해주시는 듯하다.
일단 섹션을 통해서
COUNT - 요약통계
GROUP BY - 그룹화
HAVING - 조건추가
이후 문제풀이
그리고 SQL조건문인 case .. if의 사용이다.
일단 현업에서 테이블을 한개 만 쓰는 경우는 거의 없기 떄문에
이번에는 두 가지 이상의 테이블을 쓸 것이고
JOIN을 통해서 테이블과 테이블을 묶어줄 것이다.
이 부분에서 리트코드를 사용한다. 해커랭크는 JOIN문제가 적다.
추가적으로 UNION을 통한 데이터 붙히기와 고급반 안내다.
일단 강의를 리마인드 하기 좋게 Cheat Sheet를 이용하자.
Pandas 계열이 Cheet Sheet가 잘 나온다고 한다.
집계함수
집계함수는 통계량을 뽑는것으로 요약통계를 보여로서
데이터를 요약하는 숫자들이다.
이러한 SQL에도 집계함수가 있는데
COUNT 개수체크
SELECT COUNT(*)
FROM Table
-- 레코드 개수 (excel과 같음)
여기서 Count(Price)를 했을 떄 값이 77개가 나왔다.
그 이유는 내가 실험했던 사이트에서 NULL값이 없었기 떄문에 같았다.
Null값이 존재하면 제외하고 카운터를 해주고 (*)은 널 포함을 의미한다.
중복 제거는 DISTINCT column을 써준다.
SUM 합계
Excel와 비슷하고 SUM은 간단하다.
COUNT자리에 SUM이 들어가고 그 안에 컬럼이 들어가는 것이다.
SELECT SUM(column, column)
FROM Table
-- 레코드 개수 (excel과 같음)
이렇게 말이다.
AVG 평균값
AVG도 그냥 엑셀과 똑같은데 NULL값에 주의해야된다는 점이 있다.
(*)값이 있고 (column)일때 NULL에 따라서 값이 다르기 때문이다
예를 들어보면
SELECT AVG(Visits) FROM Sample:이 있다고 해보면
샘플테이블에 있는 자료중 방문자의 평균을 알고있다는 것인데
방문자가 1+2+3+5/4 라고 했을때
위와같은 경우는 NULL값을 제외하는 (column)형 이기 떄문에
Vistis컬럼이 1,2,3,5,Null의 5가지 값이 있다고 한다면
1+2+3+5+NULL / 5가 되는 것이 아닌 NULL값을 제외한
1+2+3+5/4 = 11/4 = 2.75가 된다는 것이다.
만약 NULL까지 카운트 하고 싶다면?
SUM(column)/Count(*)이다.
IF)만약 위에 예시를 모두 출력한다면?
SELECT COUNT(A), SUM(B), AVG(C), ...
,MIN(A), MAX(B) ..
이렇게 SELECT절에 넣어주면 된다.
그룹과 조건
기존에 우리는 테이블 전체를 요약했는데
SELECT SUM(Price)
FROM Products
이런 구문이 있다고 하지만 현업에서는 이렇게 쓰지 않는다고함.
총 얼마를 팔았는가?에 대한 문제보다는 이 매출이 어떠한 기준으로 근거를 삼아서 보기떄문이다.
ex) 지역, 나이, 성별, …
SELECT column, column2
FROM table
GROUP BY column
이 예문을 보면 GROUP BY에 있는 column은 SELECT절에 세트로 들어가야있어야함.
왜냐면 묶어놨는데 출력을 안할리는 없으니까.
만약 가독성을 높히고 싶다면?
SELECT column
,column2
,column3
FROM table
GROUP BY column
이렇게 사용해서 컬럼의 내용을 위 아래로만 훑어서 볼 수 있게 하기.
ms-sql은 GROUP BY 1,2도 가능하다고 함.
이것은 SELECT절의 첫번째와 두번쨰를 해라라는 의미.
하지만 가독성이 구려지기 때문에 지양함.
추가,
they’re lazy = 숫자는 게으르다.
it’s not self-documenting = GROUP BY를 보고도 어떤 것을 기준으로 했는지 알아야하는데 불가능
it’s brittle = 서비스나 비즈니스에서 활용할 때 마다 끊임없이 수정을 해줘야함.
만약 column값이 계속 바뀌면? 숫자는 애매해지기 때문에 숫자가 아닌
‘명확하게’제시 해주자.
오름차순과 내림찬순은 GROUP BY뒤 ORDER BY를 사용한다.
기본형은 ASC인 오름차순이고 내림차순은 DESC로 한다.
IF)만약 AVG가격이 100불 이상인 경우를 보고싶다고 하면?
WHERE AVG(price) >= 100 --?
GROUP BY column
=이렇게 하면 불가능하다.
왜냐면 SQL은 C처럼 절차지형언어 느낌이기 떄문에
가져온 다음에 GROUP을 하는 것으로 컴퓨터는 느낀다.
--WHERE AVG(price) >= 100 --?
GROUP BY column
HAVING AVG(price) >= 100
이렇게 하면 가능하게 된다. 왜냐면 그룹화를 진행 후 조건을 걸었기 때문이다.
여기서 만약 출력할때 컬럼명을 바꾸고 싶다면 별명인 AS를 쓰자.
SELECT AVG(price) AS avg_price
...
HAVING avg_price >= 100
이러면 출력될떄 avg_price밑에 어쩌구가 뜬다.
약간 별명를 붙혀서 포인터 느낌으로 써주는 느낌..?
집계함수 문제풀이
-- 문제: Revising Aggregatuons - Averages
SELECT AVG(population)
FROM city --table
WHERE district = "California"
SELECT에 집계함수 출력할 것.
WHERE은 조건절 / FROM에는 그냥 테이블
을 이용해서 그대로 넣어줌.
-- Revising Aggregatuons - The sum Function
SELECT SUM(population)
FROM city
WHERE district = "California"
이거는 위의 문제랑 비슷한데 그냥 집계함수만
변경하면되는 문제다.
-- Average Population
SELECT FLOOR(AVG(population))
FROM city
이거는 SELECT AVG부분에 내림을 하는 것으로
FLOOR을 해준다.
소수점이 몇이든 무조건 적으로 내림인듯? Excel이랑 사용이 비슷.
-- Population Density Difference
SELECT MAX(population)-MIN(population)
FROM city
이거는 문제를 보면 그냥 MAX에서 MIX을 빼달라는 내용이다.
-- Weather Observation station 4
SELECT COUNT(city) - COUNT(DISTINCT city)
FROM station
중복을 제거한 City를 전체 City에서 뺴줘.
즉 중복된 City이름을 알려달라는거다.
COUNT(DISTINCT *)이다.
GROUP BY
-- Top Earners
조건을 살펴보자.
(1)총수입을 월수입으로 계산한다. 그것은 salary x months
이고 총수입은 Employee table에 있음.
총수입 + 최대수입직원을 구하고싶어.
조건은 공백으로 구분 되어 진 2개의 정수.
ㅁ ㅁ 이렇게 인듯.
SELECT salary * months AS earnings
FROM Employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1
이렇게 문제를 풀었는데 여기서 옆의 직원의 수를 어떻게 뽑아서 쓸지 감이 잘 안왔음.
SELECT salary * months AS earnings
, count(*)
FROM Employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1
추가적으로 count(*)을 통해서 low출력시킨 듯.
그리고 별명을 지정한다. 이름 다 쓰기 귀찮으니까.
그룹화시킨 후 차순정리를 위해서 OREDER BY..
그리고 최고 출력이니까 리밋
조건문
Excel의 if처럼 if then의 중첩이다.
case when 조건 AND then 참반환 이다.
SELECT case
when categoryid = 1 then '음료'
-- 조건의 True 1
when categoryid = 2 then '조미료'
-- 조건의 True 2
ELSE '기타'
END
FROM Products
이렇게 조건 + ELSE의 불만족시 걸어주고 END를 통해서 컴퓨터에게 조건문이 끝남을 암시하는 듯하다.
추가로 별명을 하려면 AS하고 나머지 출력은 ELSE에 ,*을 한다.
SELECT case
when categoryid = 1 AND when supplierid = 1 then "음료"
when categoryid = 2 then "조미료"
ELSE "기타"
END as "name" -- ,*
FROM Products
여기서 *을 쓰면 오류가 나는거 봐서는 아마
데이터의 불일치 가능성 때문에 출력이 안되나..?봄.
추가적으로 위에서 그룹화를 진행한다면?
SELECT CASE
WHEN categoryid = 1 THEN '음료'
WHEN categoryid = 2 THEN '소스'
ELSE '이외'
END AS new_category, avg(price)
FROM Products
GROUP BY new_category
SELECT에 GROUP BY절을 넣어주지만 여기서는 AS를 통해서 별명을 지정했으니
별명을 넣어준다.
이를 통해서 가시성과 편리성을 업그레이드 하는 듯하다.
CASE문 해커랭크 문제풀이
-- Type of Triangle
-조건 체크
(1) 삼각형의 표가 있는데 3개의 변의 길이를 사용해서 레코드 유형을 식별하는 쿼리를 작성하기.
(2) 등삼각형, 정사각형, 삼각형이 아님, 다 다른 삼각형?
(3)4가지 케이스설명
-1) 이등변삼각형 : 20 20 23 A=B
-2) 등변 : 20 20 20 A=B=C
-3)스칼렌 : 20,21,22 A=!B=!=!C
-4)삼각x: 13,14,30 A+B<C
Sample Input에서 찾아보자!
일단 코드 조회해서 내부 쿼리를 확인해봤음.
조건문 삽입이니까 CASE 수식의 사용
WHEN 조건 THEN '출력물'
SELECT CASE
WHEN A=B OR B=C OR C=A THEN 'Isosceles'
WHEN A=B AND B=C THEN 'Equilateral'
WHEN A+B <= C OR A+C <= B OR B+C <= A THEN 'Not A Triangle'
ELSE 'Scalene'
END
FROM Triangles
이렇게 하니까 오류가 난다. why?
결과 값을 확인하니까 상단에 있는 것이 우선조건이 되는 형식인듯 하다.
조건문을 할때는 큰개념부터 시작해서 작은개념으로 좁혀가는 것이 중요할듯 하다.
SELECT CASE
WHEN A=B AND B=C THEN 'Equilateral'
WHEN A+B <= C OR A+C <= B OR B+C <= A THEN 'Not A Triangle'
WHEN A=B OR B=C OR C=A THEN 'Isosceles'
ELSE 'Scalene'
END
FROM Triangles
조건이 가장 좁은거 부터 큰거로 뻗어가는 형식으로 바꿔본다.
검증이 잘 안될거 같으니 Excel을 통해서 한번 검증을 해보자.
내용물과 출력된 값에 대한 분류를 적어둔 다음에
=IF(E6="정","Equilateral",IF(E6="이등변","Isosceles",IF(E6="노삼각","Not A Triangle","Scalenc")))
을 통해서 치환시켜준다. 값을 어떤 삼각형인지.
그리고 Not a Triangle의 확인용으로
=IF(B6+C6<=D6,"노삼각",IF(C6+D6<=B6,"노삼각",IF(D6+B6<=C6,"노삼각","패스")))
을 걸어주고 2차 체크를 하면 이상없음을 알 수 있다.
Case를 이용한 테이블 피봇 (난이도 상)
기존에는 ID따로 avg(price)의 따로의 형식으로 진행되었으나
각 카테고리별로 avg값을 출력하는 방식으로 바꿔볼 것이다.
ㄴ Excel의 피벗테이블과 유사한 기능인것 같다.
SELECT CASE
WHEN categoryid = 1 THEN price
ELSE NULL
END as category1_price
FROM Products
이렇게 했을 때 평균값으로 변경하고 싶다면?
SELECT AVG(CASE WHEN categoryid = 1 THEN price ELSE NULL END) as category1_price
FROM Products
AVG를 END까지 감아주면된다.
하나의 조건문에 대한 AVG를 감아주는 거라고 생각하면 된다.
만약 추가적으로 카테고리별로 한다면 SELECT 부분에 id=2.. id=3..를 추가해서 준다.
,AVG(CASE WHEN categoryid = 2 THEN price ELSE NULL END) as category2_price
이렇게 세로로 늘려서 가독성을 늘리고 편하게 보게 해준다.
여기서 ELSE NULL 이 있는데 이러면 NULL값이 결과 값에 들어가게 된다.
NULL은 공백의 값이다 0이 아님..
CASE를 활용한 테이블 피봇 리트코드 문제풀이
여기서 강의에서 이야기가 있는데 좋은것 같다.
Q: 어려운 것은 어떻게 공부해야되는가?
A: 어려운 것은 어렵게 공부하는 수밖에 없다.
요즘 말하면 MBTI 너 T야? 이런거 같지만 어렵게 배워야하는건 어렵게 배워야하는게 맞다..!
즉, 일단 풀어보고 해보라는 것이다.
마치 공부할 때처럼 답안지를 보고 푸는 것이 아니라, 직접 풀어보고 답안지를 보는 것은
하늘과 땅 차이 인것 처럼 말이다.
-- Reformat Department Table
문제체크
매월 부서ID 수익 컬럼을 출력하도록 한다.,
결과 테이블은 순서대로 반환하되, 결과형식은 예제를 따른다.
단, apr4월 Dec12월까지의 수익은 무효가 되고
결과표에는 13개 열이 있게 된다.
(부서 ID 1개 + 12개) = 13개
이를 통해서 체크되는건.
1. Department의 모든 정보 탐색 id,revenue,month순서
2. 이 Revenue가 월별 수익에 해당하는 Colum인듯?
3. 결과 값을 보면 각 월 별로 출력하되 빈값은 null로 한다.
4. id는 쌩으로 나머지는 CASE문으로
SELECT
id,
CASE
WHEN month='Jan' THEN Revenue ELSE NULL END as 'Jan_Revenue',
WHEN month='Feb' THEN Revenue ELSE NULL END as 'Feb_Revenue',
WHEN month='Mar' THEN Revenue ELSE NULL END as 'Mar_Revenue',
WHEN month='Apr' THEN Revenue ELSE NULL END as 'Apr_Revenue',
WHEN month='May' THEN Revenue ELSE NULL END as 'May_Revenue',
WHEN month='Jun' THEN Revenue ELSE NULL END as 'Jun_Revenue',
WHEN month='Jul' THEN Revenue ELSE NULL END as 'Jul_Revenue',
WHEN month='Aug' THEN Revenue ELSE NULL END as 'Aug_Revenue',
WHEN month='Sep' THEN Revenue ELSE NULL END as 'Sep_Revenue',
WHEN month='Oct' THEN Revenue ELSE NULL END as 'Oct_Revenue',
WHEN month='Nov' THEN Revenue ELSE NULL END as 'Nov_Revenue',
WHEN month='Dec' THEN Revenue ELSE NULL END as 'Dec_Revenue',
FROM Department
맞는 것 같은데 구동이 안된다.
앞에는 ID로 묶어주고 뒤에는 이름을 변경한 상태에서 각 월의 수익을 보고 싶은것 아닌가?
[문제풀이후]
Table를 가로로 펼쳐줘라
기존에 우리가 Categoryid then Price문과 비슷하게 사용
여기서 부서별로 나눠야되니까 GROUP BY를 넣어줘야했다.
예산을 보여줘야되니까 집계함수인 SUM을 사용해야했다.
Month를 기준으로 옆으로 쭈욱 펼쳐주는 과정으로서
SELECT id뿐 아닌 id를 그룹화 시켜야된다.
하지만 피벗팅의 경우 ms-sql보다는 orcle이 더 좋다고 한다.
나의 문제점)
1. 맨 처음에 "jan_Revenue"하면 나왔으나, 맨 앞에 id를 1 2 3 을 넣어줘야되거 부서별로 해줘야되니까
그룹화 ID별로 해줘야되기 때문에 GROUP BY + SELECT id추가
2. 부서별 예산이기 때문에 그냥 출력이 아닌 SUM출력
- 문제 해석 이해 부족, 그룹화 미스
SELECT id
,SUM( CASE WHEN month='Jan' THEN Revenue ELSE NULL END )as 'Jan_Revenue'
,SUM( CASE WHEN month='Feb' THEN Revenue ELSE NULL END )as 'Feb_Revenue'
,SUM( CASE WHEN month='Mar' THEN Revenue ELSE NULL END )as 'Mar_Revenue'
,SUM( CASE WHEN month='Apr' THEN Revenue ELSE NULL END )as 'Apr_Revenue'
,SUM( CASE WHEN month='May' THEN Revenue ELSE NULL END )as 'May_Revenue'
,SUM( CASE WHEN month='Jun' THEN Revenue ELSE NULL END )as 'Jun_Revenue'
,SUM( CASE WHEN month='Jul' THEN Revenue ELSE NULL END )as 'Jul_Revenue'
,SUM( CASE WHEN month='Aug' THEN Revenue ELSE NULL END )as 'Aug_Revenue'
,SUM( CASE WHEN month='Sep' THEN Revenue ELSE NULL END )as 'Sep_Revenue'
,SUM( CASE WHEN month='Oct' THEN Revenue ELSE NULL END )as 'Oct_Revenue'
,SUM( CASE WHEN month='Nov' THEN Revenue ELSE NULL END )as 'Nov_Revenue'
,SUM( CASE WHEN month='Dec' THEN Revenue ELSE NULL END )as 'Dec_Revenue'
FROM Department
GROUP BY id
거의다 맞았는데 SUM으로 덮어주는 과정과 그룹화 과정이 빠졌다.
부서별 (그룹) + 수익최총(SUM) 기억하자.
여기서 입사를 하게 된다면 팀바팀이지만, 팀마다 스타일이 다르다.
주석이 없어도 알 수 있게끔 코드를 짜는 것이 좋다.
제작자 말고는 의도를 정확하게 파악할 수 없으니 주석을 쓰는 것이 좋다.
팀에 맞춰서 잘 적응하자.
추가로 한다면
SUM을 사용하는 이유:
department테이블의 모든 행을 하나씩 확인하여
해당 행의 month값이 무엇인지에 따라 revenue 값 또는 NULL을 차례대로 반환한다
Department의 첫 번째 행 month가 Feb가 아니면,
output의 첫 번째 행은 NULL이 되는 것이다.
그런데 피벗테이블의 여러 개의 행(Null또는 다른 값)을
필요로 하는 게 아니라 id 당 하나의 행만 갖도록 만들고 싶기에
여러 값을 하나로 요약하려면 집계함수를 써야되니까 그 중 하나 SUM을 사용하는 것이다.
피봇테이블 전체가 아니라 집계된 자료를 봐야하기 때문에
집계함수를 넣는다.
여기서 집계된 자료는 예산의 총합을 나타낸다.
JOIN 테이블의 연결
JOIN은 A테이블과 B테이블의 연결을 하는 것으로
보통 테이블을 하나만 가지고 하는 경우는 적을 것이다.
즉, 두 개 이상의 테이블을 PPAP 하는 것이다.
RDB = 관련성 있는 테이블을 나눠서 저장하는 시스템이다.
관계성에 초점을 맞춘 Relational Data Base..?
Data의 중복은 효율적이기 않기 때문에 제거하기 위한 방법으로서
쪼개서 관리하고 이 둘을 연결시켜서 중복을 제거하는 것이다.
즉 이렇게 하려면 연결과정이 필요하기 때문에 JOIN을 쓴다.
INNER JOIN
[고대의 유물 기법]
User에 Data가 있고 Orders에 Data가 있다고 하자.
FROM절에 Table에 복수를 적으면 모든 경우의 수를 출력하게 된다.
SELECT
FROM Users, Orders
WHERE User.Id = Orders.usersId
예시를 보면 WHERE 절을 통해서 연결시킬 것을 걸어준다.
FROM절에서 모든 경우의 수를 걸어주고 WHERE절에서 거름망을 해주는 형식이다.
[신식군대- 임오군란 st]
SELECT * FROM Users
INNER JOIN Orders ON Users.Id = Orders.userId
INNER JOIN A ON B 이다.
“Orders에 붙힐 건데 기준은 ON뒤에 있는 id들이 같을 때”
SQL Joins Visualizer 에 들어가면 볼 수 있음. (시각화)
INNER JOIN은 마치 교집합을 구하는 것과 같다.
그러기 때문에 A와 B 두 개가 존재해야지
교집합이 생성되기 때문에 양쪽의 정보가 필요하다.
[예문]
주문ID가 있다고 하면 그 주문 ID에 관련된 것을 보고싶다면?
WHERE ID = Number하면 거기 있는 사람의 정보를 알 수 있음.
하지만 이렇게 하면 한명 한명 찾아보는데 비효율적이기에 조인을 쓰게 된다.
SELECT *
FROM Orders
INNER JOIN Customers ON
Orders.CustomerID = Customers.CustomerID
이렇게 하면 좌측은 Orders 테이블이고
뒤에는 Customers 테이블이 붙는 것을 확인가능하다.
좌측은 FROM에 우측은 INNER JOIN 뒤에다.
즉, FROM과 JOIN뒤는 Table
ON 뒤에는 Colum이 들어간다.
그러면 두번도 가능할까?
SELECT *
FROM Orders
INNER JOIN Customers ON
Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers ON
Orders.ShipperID = Shippers.ShipperID
가능하다.
Orders - Customers - Shippers 순서로 오른쪽으로 하나씩 붙힌다.
주의)
문제는 조인을 쓸 수 없는 Colum인지 알 수가 없는 상황 발생
만약) A는 A.Apple인데 B는 Apple라는 것으로 하면 조인하는 컬럼에 대해서 이름이 다르면…?
=> ERD라는 것을 통해서 테이블 간의 관계를 파악한다.
SQL의 도식화. 예전에 해본거..
옛날에 대학교에서 ERD관련해서 직접 설계해본 경험이 있는데
아마 이거로 연결되는 키와 관계를 통해서 아는 것 같다.
OUTER JOIN
기존 INNER JOIN은 A∩B
OUTER JOIN은 약간 INNER JOIN 제외는 OUTER라고 생각하면 된다.
LEFT JOIN (왼쪽을 기존으로 데이터 연결)
왼쪽 table 데이터있고 오른쪽이 없는 경우
기존 INNER JOIN은 교집합이라서
두개 다 부합해야만 나오는데 왼쪽의 데이터는 다 출력하고 교집합 붙히려면 LEFT.
왼쪽에 두고 싶은건 FROM절에 두면된다.
그리고 LEFT JOIN 오른쪽 ON 조건 을 한다.
만약 USER에 ID 1,2,3 ORDERS에 ID 1,2만 있다고하면
INNER면 1,2만 나오고
LEFT를 USER에 두고 연결하면 1,2,3출력이라는 것.
만약 추가적으로 조건을 달고싶으면
WHERE절에 조건을 추가적으로 한다.
만약 한번도 주문하지 않는 사람을 출력하고 싶다면?
WHERE coulum IS NULL로 하면
기준컬럼이 널로 출력되니까.
주문하지 않은 사람만됨.
RIGHT JOIN
은 그냥 오른쪽이 기준이 되는 것으로..
보통 LEFT JOIN을 씀
why? FROM절과 LEFT JOIN colum 위치를 바꾸면
결국 LEFT RIGHT가 되기 때문이다.
즉 조인을 왼쪽 오른쪽 섞어서 쓰면 헷갈리기 떄문에
대부분 LEFT JOIN을 사용한다.
아까 INNER 제외는 다 OUTER기 때문에
LEFT JOIN = LEFT OUTER JOIN과 같음.
JOIN 문제풀이
-- African Cities
문제: 도시와 국가 테이블의 대륙이 아프리카인 것들의 출력한다.
+ City.Country와 Country.code가 주요열과 일치
즉 이 두개는 기준이 되는 컬럼이다.
-- 첫번쨰는 출력을 통해서 city와 country를 확인 후 조인실행했음. 쭈욱 나오고 조건절을 추가해야될듯?
SELECT *
FROM city
INNER JOIN country ON city.countrycode = country.code
SELECT *
FROM city
INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Afirca'
왜 안나오지..? 응답이 없다고 함. *이라고 너무 큰가
아 스펠링 틀림 ‘Africa’ ㅋㅋ 항상 단어 오타에 주의하자..
SELECT city.name
FROM city
INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Africa'
마지막으로 city.name을 잡아준다.
연결 후 조건으로 Africa 출력은 City 이름이다.
-- Aisan Population
-문제: 기존문제와 같은듯? populations을 더하고 조건은 continent에서 asia조건을 달아줘야됨.
그리고 결합은 city.country 와 country.code임.
SELECT sum(city.population) --출력이름
FROM city
INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Asia'
이거는 비슷한데 조금 다른거 정도인것 같다.
-- Average Population of Each Continent
-문제: 모든 대륙의 이름 출력인듯?(country.continent)
그것에 대해서 average city populations이고 내림 해줘라.
그리고 키는 city.countrycode 와 country.code다.
SELECT country.continent
,FLOOR(AVG(city.population))
FROM city
INNER JOIN country ON city.countrycode = country.code
GROUP BY country.continent
거의 다 완성하긴 했는데 여기는
GROUP BY를 생각하지 못했고 WHERE로 해결하려고 했다.
위에 이름 출력이라고 걸어줬으니 그룹인것 같다.
LEFT JOIN 리트코드 문제풀이
-- Customers Who Never Order
문제 : id는 PK이다. 각 row행은 ID와 이름
customer id는 외부키. 똑같이 row행 동일
즉 id끼리 연결됨.
아무것도 주문하지 않은 고객을 찾아야함.
SELECT Customers.name
FROM Customers
LEFT JOIN Orders ON Customers.id = Orders.customerid
WHERE Orders.id IS NULL
1차 시도를 했는데 정답은 출력됐는데 답이 틀렸다고 한다..
문제를 자세히 보니 출력을 Customers로 이름하고싶다고하니 as로 별명으로 바꾸면될듯..?
SELECT Customers.name as Customers
FROM Customers
LEFT JOIN Orders ON Customers.id = Orders.customerid
WHERE Orders.id IS NULL
문제 없이 된다.
[문제풀이 강의후]
SELECT Customers.name AS Customers
FROM Customers AS c
LEFT JOIN Orders AS o ON C.id = O.customerid
WHERE O.id IS NULL
AS를 통한 별명지정으로 앞으로 영어단어를 적게 쓰는 것으로 효율 좋은듯.
근데 AS많이쓰면 메모리 영역에 문제는 없나? <- 질문으로 남겨봤다.
Self Join 리트코드 문제풀이
SELF JOIN은 내 테이블을 나한테 조인하는 상태
재귀같은 느낌인듯? 나 x 나 느낌?
-- Employees Earning More Than Their Managers
문제: Emplyee Table에는 모든 직원을 포함된다.
각 직원은 ID를 지니고 있으며 추가로 직원별로 담당 매니저는 Managerid가 부여되고 있음.
여기서 찾아야하는데. 매니저보다 Salary를 더 많이 버는 경우를 찾아야 됨.
아마 자기테이블 x 자기테이블 이니까
조인이 되는 기준은.. 매니저 ID로 구분해야되니까
매니저 ID로 합친다음에 조건 월급을 넣어줘야되는듯?
SELECT Employee.Name AS Employee
FROM employee
INNER JOIN employee as Manager ON Employee.managerid = Manager.id
WHERE Employee.Salary > Manager.Salary
잘 모르겠어서 강의를 봤음.
재귀조인같은 느낌인데 ON 부분을 어떻게 할지 감이 안왔다.
예상한게 맞는 듯 하다 ON을 ID끼리 묶어주고 조건절에 월급을 걸어주는 거다.
FROM employee
INNER JOIN employee as Manager ON Employee.managerid = Manager.id
여기서 왼쪽 테이블을 지정 후 오른쪽 테이블은 AS를 통해서 이름을 바꿔 헷갈리지 않도록 함.
WHERE Employee.Salary > Manager.Salary
이 구분을 통해서 매니저보다 월급 높은것을 소거시킬 수 있었음.
SELF JOIN 리트코드 2 +MS SQL 날짜데이터
-- Rising Temperature
-문제: 날씨테이블이 있고. 전날보다 온도가 올라간 날 출력한다.
그러면 재귀 조인이 될 것이고.
아마 전날이 되는 것과 오늘이 되는 날을 AS로 구분을 해줌.
그리고 어제를 기준으로 오늘을 연결하고
조인하는 키는 RecordDate를 기준으로 맞추는 것도?
RecordDate +1 은 = 오늘이 되니까?
근데 이거 +1해도되나..?
찾아보니까 DATE_ADD( 기준, INTERVAL 숫자 초/분/,,)
이라고 하니까 DAY로 하면될듯?
DATE_ADD(어제컬럼, INTERVAL 1 DAY?)
근데 빼는건 DATE_SUB군..
잘 모르겠으면 SELECT 구문에 AS를 부여해서 잘 보이게
그리고 조건절을 추가해본다.
어제보다 오늘이 Temperature가 더 큰 경우?
SELECT yesterday.id
FROM Weather AS yesterday
INNER JOIN Weather AS today ON DATE_SUB(today.recorddate, INTERVAL 1 DAY) = yesterday.recorddate
WHERE yesterday.temperature > today.temperature
답이 자꾸 1,3이 나오거나 2만 나오게 된다. 왜지?
어제의 온도가 오늘보다 높은 경우 어제 id값을 반환하는게 아닌
오늘의 id값을 반환해야되지 않을까?
SELECT today.id
FROM Weather AS yesterday
INNER JOIN Weather AS today ON DATE_SUB(today.recorddate, INTERVAL 1 DAY) = yesterday.recorddate
WHERE today.temperature > yesterday.temperature;
정답이다.
[문제풀이후]
SELECT today.id
FROM Weather AS today
INNER JOIN Weather AS yesterday ON DATE_ADD(yesterday.recorddate, INTERVAL 1 DAY) = today.recorddate
WHERE today.temperature > yesterday.temperature
이거인데 결과적으로 같은데 접근법이 다른 듯하다.
문제풀이를 통해서 알게 되었는데 이 문제에서
날씨 테이블의 id로 접근하면 테스트 파일 데이터는 id별로 record가 올라가는 것이 아닌
내려가는 것으로 되어있어서 id를 기준으로 잡고 하는건 방지가 되어있다고 한다.
UNION, UNION ALL
UNION 집합연산
-우리가 기존에는 JOIN을 통해서 옆으로 테이블을 붙히는 것 이었다. 기준을 잡고 추가로 왼쪽이나 오른쪽으로 붙힘.
이번에는 위로 아래로 데이터를 붙히는 작업이다.
옛날의 테이블까지 붙혀서 년도별로 보는 경우는
세로로 붙혀야 되기 때문이다.
ㄴ 이거는 중복값을 제거 한 경우를 보여줌
UNION ALL은 중복값이 있어도 출력된다.
ALL은 중복값의 유무다.
SELECT를 보면 우리는 중복을 제거 할때
DISTINCT를 쓰면 중복가 제거가 되고 아니면 다 보여주는데
SELECT는 ALL이 디폴트값이고
UNION은 DISTINCT가 디폴트값이라는거다.
MS-SQL이 아닌 경우의 설명
-- MSSQL은 아니지만 UNION 쓰는법
-- 문제 : product 테이블에서 Price가 10이하 200이상인 상품들 출력
SELECT *
FROM Products
WHERE price <= 10
OR price >= 200
-- SQL
SELECT *
FROM Products
WHERE price <= 10
UNION
SELECT *
FROM Products
WHERE Price>=200
예약어를 2개 해주고 샌드위치 식으로 UNION을 껴줌.
뺴고 싶다면 EXCEPT
오라클이면 MINUS로 됨
위 아래에 교집합은 INTERSECT
FULL JOIN
A와 B의 원이 있을 때 가운데 INNER
왼쪽은 LEFT 오른쪽은 RIGHT
이지만 모두 다 보고 있으면 FULL OUTER JOIN
하지만 MS SQL은 안됨.
그래서 LEFT와 RIGHT 구현 후 UNION 묶기.
SELECT *
FROM Customers
lEFT JOIN orders ON Customers.CustomerID = Orders.CustomerID
-- Customers 기준으로 조인을 함.
-- Orders는 데이터가 있으나 Customer가 없을 수 있음. [비회원같음] 주문은 있으나 고객은 없음
-- 이런 경우는 LEFT와 RIGHT를 섞어서 씀
UNION
SELECT *
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders
-- 주문은 있으나 고객은 없을 때
LEFT + RIGHT를 PPAP 해주는거다.
UNION 문제풀이 난이도 상
-- Symmetric Pairs
모든 지식을 다 써야 한다고 하셨음.
HAVING ORDER BY GROUP BY UNION INNERJOIN
Difficulty : Medium
일부여도 좋으니 경험을 쌓자.
문제:
표가 주어지는데 이는 대칭쌍의 형태를 한다.
X1 = Y2 , X2 = Y1으로 쌍이라고 한다.
쿼리를 통해 대칭 쌍을 X값만큼 오름차순으로 출력한다.
이는 X1 <= Y1이다.
테이블은 Functions고 컬럼은 X,Y
SELECT FA.X, FA.Y
FROM Functions as FA
INNER JOIN Functions as FB ON FA.X = FB.Y AND FB.X = FA.Y
WHERE FA.X < FA.Y
UNION
SELECT FA.X, FA.Y
FROM Functions as FA
WHERE FA.X = FA.Y
GROUP BY X,Y
HAVING count(*) > 1
ORDER BY X
정답이다. 조금 어렵지만 밑에 셀렉은 굳이 FA 지정을 안했어도 될 것 같다.
일단 풀이를 이야기하자면
위에는 INNER JOIN을 통해서 셀프조인을 시켜서 자기자신과 비교하게 함.
이 구문은 대칭쌍(X1=Y2 X2=Y1)에서 시작되는데
FA.X에서 (20)가져온 후 그 20 값을 만족하는 Y값을 FB에서 가져온다. 이는 Y2인 (20)이다.
그리고 그 반대의 경우도 가져온다. 그러면 쌍 조건을 만족하게 된다. (X1=Y2, Y1= X2)
그리고 WHERE조건절로 X가 큰 상황을 잘라낸다
대충 이런 느낌으로 보면 될 것같다.
잘 몰라서 Excel로 하나 대입해서 봤다.
그러면 남은건 X=Y가 같은 경우인데.
출력은 같고 WHERE 절을 통해서 X와 Y가 같은 값을 찾는다.
그 것을 X,Y로 그룹화를 시켜서
(X,Y)의 조건이 동일 행이 1개 초과만 출력하는 것이다.
즉 (20,20) (20,20)이 여러개 존재할 수 있으니 Cut같은 개념이다.
(20,20) (21,21)은 WHERE에 부합한다.
GROUP BY를 해서 (20,20)쌍은 2번 나타나게 되고 (21,21)쌍은 1번이다.
HAVING Count(*)>1을 하면 (20,20)은 포함된다 2개이상(1개초과)니까.
하지만 (21,21)은 1개니까 제거된다.
즉 무결성의 보장이다
[문제풀이 후]
SELECT F1.X, F1.Y
FROM Functions as F1
INNER JOIN Functions as F2 ON F1.X = F2.Y AND F1.Y = F2.X
WHERE f1.X < f2.Y
ORDER BY X
UNION
SELECT X, Y
FROM Functions
WHERE X = Y
GROUP BY X,Y
HAVING count(*) = 2
문제풀이는 이렇게 하고 ORDERBY가 달랐고
HAVING 부분이 조금 달랐다.
이 부분에 대해서는 다른 사람의 풀이 방법을 봐야할 것 같다.
아까보니까 UNION을 사용하지 않고 하는 방법도 있는 것 같다.
마지막으로
추가적으로 했으면 좋은 것은
프로그래머스(해커랭크랑 비슷함)
SQL 고득점 Kit를 풀어주기 중급반까지 들었다고 하면 어렵지 않게 풀 수 있을 것라고 하신다.
일단 SQLD를 준비하면서 짬짬히 다 풀어보자
자유자재로 풀수있다 정도까지 올라가보자!
추가적으로 하면 좋은 것은 Cheat Sheer이다.
종이 치트키 (약간 요약본)
SQL고급강의의 컬리큘럼은
SELECT를 기준으로 배웠고
INSERT, UPDATE, DELETE같은
DML이라고 해서 우리가 데이터를 넣고 업데이트하고 삭제하는 것까지.
약간 엔지니어 과정까지 해보는 느낌?
데이터분석가가 직접 쌓을일이 거의 없긴하지만
약간 엔지니어까지 겸해야되는 경우면
알고 하는거랑 모르고 하는거랑은 차이가 있기 떄문에.
Data Manipulation Lauage라고 배워야한다
그리고 ERD를 잘 읽어야한다!
이것이 몇백개가 펼쳐져 있고
그 안에서 필요한 데이터를 골라내려면 ERD를 잘 뽑아내야한다.
어떻게 하면 그려볼 수 있는지 체크한다.
(옜날에 학교에서 배운 ERD인듯)?
그리고 쿼리안에 쿼리를 한번 더 써서
서브쿼리를 하는것
추가로 쿼리로 복잡한 것을 추가로 하기 위한
윈도우함수라는 것을 다뤄보자.
추가적으로 해커랭크 리트코드의 고난이도의 문제들까지
챌린지 형식으로 시도 해보기!
후기
정말 좋은 강의였고 아마 SQLD를 시험을 보고 난 후
프로그래머스 SQL kit를 풀고 고급반을 들을 것 같다.
아니면 마저 C언어 조금 더 파볼 수도..?
-
SQL: 데이터 분석 기초 SQL
수료
기초 수강을 하였는데 생각보다 재미있었다!
이 글을 정리를 위해서 시작하고자 한다.
인프런에서 [백문이불여일타] 데이터 분석을 위한 기초 SQL 추천한다.
내용 정리
DQL (Data Query Language - 질의어)
select
데이터 조회시 사용
DML (Data Manipulation Language - 조작어)
Insert, Update, Delete..
데이터 조작할 때 사용
해당강의는 데이터를 가져오는 것 위주의 강의이다.
데이터를 가져와야지 분석을 하기 때문에 끌어올 수 있어야한다.
데이터를 수정하고 지우는 것은 엔지니어의 영역
이후 강의는
이론을 바탕으로 문제풀이
해커랭크 코딩테스트를 병행한다.
= 이 강의는 데이터를 불러오는 것과 이론 설명 후 코딩테스트의 형식을 통해 이론과 실습을 진행함.
초급시작
데이터는 테이블형태로 지정된다
종류
이름
가격
과일
배
1200
채소
당근
800
우리가 잘 아는 엑셀과 같다고 생각하자.
가로에서 옆으로 뻗어져나가는 세로데이터
=> 종류, 이름, 가격은 열-컬럼(colmn)
=> 과일,배,1200는 열이다.
세로에서 아래로 뻗어 나가는 데이터
=> 종류,과일,채소는 행-로우(row)다.
+)
[1,2,3]
[4,5,6]
[7,8,9]
가 있다고 치면
행은 행1 행 2 행3 이니까 [123] [456] [789]
열은 열1 열2 열3 이니까 [147] [258] [360]
그러면 여기 최상단에 제목이 추가되면 행(Row)인가 열(column)인가? 퀴즈
SELECT *
FROM Customers
이 구절을 보면 되는데
SELECT *을 통해 전체지정을 해준 후 FROM으로 내가 보고싶은 테이블 적기이다.
두개를 보고싶으면 FROM apple, banana 하면된다.
주의할점
전체지정을 하여 찾기를 하면 시간이 오래 걸린다. 그래서 상한을 걸어준다
LIMIT n이다.
문제풀이
인터프리터를 MSSQL로 놓고 시작한다.
Select All 문제:
Query all columns (attributes) for every row in the CITY table
CITY테이블에 모든 컬럼과 모든 로우를 추출
SELECT * -- 컬럼명이 붙는다 (제일 큰 분류)
FROM CITY -- 테이블명이 붙는다. (시트의 개념)
Weather Observation Station 1 문제:
Query a list of CITY and STATE from the STATION table.
City과 State를 가져와야된다. 어디서? STATION 테이블에서
SELECT CITY, STATE -- 컬럼인 city state
FROM STATION -- 테이블인 STATION
연산과 조건절
실제로 모든 데이터를 볼일은 자주없고 특정성이 들어가 있는 작업들을 많이 보게 될 것이다.
이것이 Where(조건)이다.
WHERE Country = 'Germany'
WHERE Customer ID <50 .. Customer <>20..
AND Country='Germany'
대입연산과 비교연산자를 통해서 조건을 넣어줄 수 있다.
그리고 조건결합인 논리연산자(AND OR)처럼 추가 도킹이 가능하다.
LIKE, IN, BETWEEN, IS NULL
LIKE = 문자열에서 패턴을 찾는다.
LIKE '%r' --앞에 무엇이 있든 상관없는 r
LIKE 'r%' --뒤에 무엇이 있든 상관없는 r
IN = 추가조건을 묶어준다
WHERE Country IN ('Germany', 'France')
= WHERE Country = 'Germany' OR Country='France'
두 개는 같으나 가독성과 구문의 차이만 봐도 위가 Goat임을 알 수 있다.
IN은 마치 SUM(A, B, C..)다
BETWEEN = 조건 A와 B사이 출력한다. (부분조건)
BETWEEN 3 AND 5
= Where A >= 3 AND A <=5
3과 5를 포함하고 그 사이 값을 출력하라
즉 BETWEEN은 이상과 이하의 개념
IS NULL = NULL값 탐색
-- IS NULL (NULL값 찾기)
WHERE A IUNULL;
NULL은 배운 것처럼 하나의 공백이다.
NULL = Nam = Not a Number이다.
LIKE의 심화과정
위 LIKE에서 %r이 있었다, 여기서 %를 ‘와일드카드’ 라고 부른다.
어떤 것이 와도 상관없다 = Wild Card
WHERE Country LIKE 'Brazil'
= WHERE Country = 'Brazil'
둘은 같으나 단순하게 하나의 완전한 값을 찾는 위 상황이라면
LIKE보다는 = 가 더 처리가 빠르다.
언더바도 추가시킬 수 있다.
LIKE 'b%' -- B로 시작하는 단어 찾기
LIKE 'b_____' -- _5개로 b이후 5개만 찾기
%는 모두찾기 _는 갯수 지정 찾기다.
WHERE discount LIKE '50%' -- 이러면 %가 문제가 생기는데
WHERE discount LIKE '50\%' --이렇게 해주면 된다. \를 통해서 풀어준다고 보면 됨.
회사마다 사용하는 SQL이 다른데
PostgreSQL, MSSQL, RedShift SQL등 다양한 DB가 있다.
구글에 검색해서 찾는데 그때는 보통 Redshift like % escape으로 타이핑하면 좋다
WHERE절 요약
BETWEEN은 범위검색형이고 AND와 친구다.
WHERE BETWEEN A AND B;
WHERE A > = B AND <= C;
IN = 값 목록의 지정 (SUM) + OR로 풀어쓰기 가능
WHERE [ ] IN (A,B, C...)
IS NULL / IS NOT NULL
WHERE [ ] IS (NOT) NULL
문제풀이
1. Revising the Select Query II
문제: Query the NAME field for all American cities in the CITY table with populations larger than 120000.
The CountryCode for America is USA.
조건
1. 12만보다 큰 Code
2. Country Code가 USA
3. 큰 테이블은 CITY 컬럼은 NAME
SELECT name
FROM city
WHERE population > 120000
AND countrycode = 'USA'
2. Select By ID
문제 : Query all columns for a city in CITY with the ID 1661.
조건
1.모든 컬럼
2.ID는 1661
SELECT *
FROM citry
WHERE ID = 1661 --숫자니까 ''안들어감
3. Weather Observation Station 6
문제: Query a list of CITY names from STATION for cities that have an even ID number.
Print the results in any order, but exclude duplicates from the answer.
조건
1. 테이블 City name 영어모음 시작 City 고르기
2. 중복제거 DISTINCT 넣기
3. 추가 답변으로 IN써보기
[1번 정답]
SELECT DISTINCT city
FROM station
WHERE city LIKE 'a%'
OR city LIKE 'e%'
OR city LIKE 'o%'
OR city LIKE 'i%'
OR city LIKE 'u%'
[2번 정답]
SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) IN ('A', 'E', 'I', 'O', 'U')
나중을 생각하면 2번을 통해서 하면 가독성이 좋을 것 같아서 2번이 좋을듯 하다..!
4. Weather Observation Station 12
문제: Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.
조건
1. 모음 aeiou 시작은 x 끝나지도 x
2. 중복을 제거 (DISTINCT)
[1번정답]
SELECT DISTINCT city
FROM station
WHERE city NOT LIKE 'a%'
AND city NOT LIKE 'e%'
AND city NOT LIKE 'i%'
AND city NOT LIKE 'o%'
AND city NOT LIKE 'u%' --동시니까 AND
AND city NOT LIKE '%a'
AND city NOT LIKE '%e'
AND city NOT LIKE '%i'
AND city NOT LIKE '%o'
AND city NOT LIKE '%u'
[2번정답]
SELECT DISTINCT CITY
FROM station
WHERE lEFT(city,1) NOT IN ('a', 'i', 'e', 'o', 'u')
AND RIGHT(city,1) NOT IN ('a', 'i', 'e', 'o', 'u')
이것도 가독성을 생각해서 후자가 더 편리할 것 같다.
이런 의문이 든다. IN과 LIKE는 서로 어떨 때 쓰는게 유용한가?
성능, 가독성부분은 LIKE보다 IN이 좋다고 한다.
즉. 단순히 앞글자 뒷글자 확인후 IN의 체크가 더 좋고 가독성도 IN이 높힐 수 있다. (리스트형식)
구체적인 요구사항은 LIKE가 더좋다고한다.
LIKE는 문자열 패턴 매칭에 강점이 있기에 복잡한 매칭일떄 쓴다.
LIKE %pattere%같이 특정패턴의 포함일때!
ORDER BY 정리
정렬하기 = ORDER BY
SELECT *
FROM Customers
WHERE -
ORDER BY customerid DESC --(내림차순)
ORDER BY Customerid ASC --(오름차순) - Default
--응용하면?
ORDER BY Price DESC --특정 테이블 가격의 내림차순
--조건으로 상단에 추가
WHERE price >=20
ORDER BY
문제풀기
1.Employee Names
문제: Write a query that prints a list of employee names (i.e.: the name attribute)
from the Employee table in alphabetical order.
조건
1. list of employee names
2. ASC (aplhabetical order)
3. Talbe : employee
SELECT name
FROM employee
ORDER BY name ASC
2.Employee Salaries
문제: Write a query that prints a list of employee names (i.e.: the name attribute) for employees in Employee having a salary greater than per month who have been employees for less than months.
Sort your result by ascending employee_id.
조건
1.월 2000$ 이상
2.근무 10달 미만
3.ID정렬
SELECT name
FROM employee
WHERE salary > 2000
AND months < 10
ORDER BY employee_id
간단함수 설명 및 문제풀이
LEFT = 왼 , RIGHT = 오
SUBSTRING = 시작위치~ n까지
= SUBSTR (약자)
ORDER BY RIGHT (name,3), id
조건절의 두번째는 뒤로
여기서 생각한게 ORDER BY에 _ 표현이 들어갈 수 있을까?
=> NO였다. ORDER BY는 컬럼이나 표현식 정렬이기에 와일드카드는 불가능 (NOT 문자열)
1. Weather Observation Station 15
문제: Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345. Round your answer to 4 decimal places.
조건
1.쿼리 LONG_W 부르기
2.LAT_N보다 큰것
3. STATION 테이블
4. ROUND함수 사용
5. Less than 137.2345보다 작은
6. 소수점 n자리 SELECT
7. 최대한개만 출력
SELECT ROUND (LONG_W,4)
FROM station
WHERE LAT_N < 137.2345
ORDER BY LAN_T_N DESC
끝.
Touch background to close
인디너굴탱
