🤔 SQL과 날아올라

• 오늘은 SQL의 여러 가지 관련 함수들을 배우고, 항공 여객 데이터를 분석하고 시각화하며 개인 프로젝트를 진행하게 되었습니다.
• 국내 항공 데이터를 분석한 과정은 다음 글에 작성되어 있습니다!

[Data Analysis] 국내 항공기 운항 데이터

 

 

---

🤷🏻 여러 가지 함수들

• SQL에서 함수는 크게 문자열, 숫자, 날짜, 집계, 수학 관련 함수로 나눌 수 있는데, 저희는 수학 관련 함수는 따로 다루진 않았습니다!

 

❌ 문자열 함수

💡 문자열을 연결할 때

SELECT CONCAT('I', 'Love ', 'You') AS col;

• CONCAT은 LIKE와 함께 많이 사용되는 함수로, 문자열과 문자열을 연결해주는 역할을 합니다.
• 파이썬에서 쿼리문을 던질 때, 주로 사용되는 함수입니다.

SELECT CONCAT_WS(', ', first_name, last_name, email) AS customer_info 
FROM customer;

• CONCAT_WS 함수는 여러 열을 합칠 때 모든 열 이름 사이에 직접 일일이 구분자를 입력하기 번거롭기 때문에 구분자를 미리 정의해서 자동으로 적용할 수 있습니다.
• 구분자를 NULL로 설정할 경우, 모든 데이터가 NULL로 변환되고!
• 비어있는 열은 그대로 적용하고, 구분 인수 뒤에 오는 NULL은 무시하고 건너 뛰게 됩니다.

 

💡 데이터 형 변환

• 문자열을 다른 유형으로 변환시켜주는 함수들로,
  • BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, JSON, NCHAR, SIGNED[INTEGER], UNSIGNED[INTEGER]와 같은 형으로 변환이 가능합니다.
• SQL은 '2' 같은 경우에도 계산을 하고자 하면 정수형인 2로 암시적 형 변환되어 계산되기 때문에 최대한 이 방식은 피해서 아래 나와있는 함수들로 명시적 형 변환을 시켜주어야 합니다.

SELECT CAST(20230819 AS DATE);

• CAST 함수를 사용해서 NOW 함수로 가져온 값을 정수형으로 변환하는 등, 함수도 입력을 할 수가 있습니다.
• 데이터의 길이(CHAR(5)와 같이)도 변경해줄 수 있습니다.

SELECT CONVERT(열, 데이터 유형) FROM 테이블;

• CAST나 CONVERT 함수로 Overflow도 방지할 수가 있는데, BIGINT 형으로도 담아낼 수 없는 값을 UNSIGNED 형으로 변환해서 Overflow를 방지할 수 있게 됩니다.

 

💡 NULL을 대체

IFNULL(열, 대체값)

• IFNULL 함수에 인자 값으로 열 이름과 대체할 값을 전달하게 되면 해당 열에 있는 NULL을 대체할 값으로 변경해주게 됩니다.

COALESCE(열 1, 열 2, ...)

• COALESCE 함수는 여러 열의 이름을 인자로 전달해줘야 합니다.
• NULL이 아닌 값이 나올 때까지 후보군의 여러 열을 입력할 수 있고,
• 첫 번째 인자로 전달한 열에 NULL이 있을 때 그 다음 인자로 작성한 열의 데이터로 대체하는 식입니다!
• 마지막 인자까지도 NULL이 저장되어 있다면 결국에는 NULL을 반환하게 됩니다.

 

💡 소문자, 대문자 변경

• 파이썬과 같이 LOWER, UPPER 함수를 사용하면 소문자 혹은 대문자로 입력 문자열을 일관되게 변경해줍니다!

 

💡 공백 제거

• LTRIM, RTRIM 함수는 왼쪽 그리고 오른쪽의 공백을 제거해줍니다.

SELECT TRIM(BOTH '#' FROM '# I Love You #');

• TRIM 함수는 공백이 아닌 단어 앞뒤에 있는 특정 문자를 제거하는 기능도 포함되어 있습니다!

 

💡 문자열 크기 및 개수 반환

• LENGTH 함수는 특이하게도 문자열의 바이트 크기를 알아낼 수 있어서, 문자열의 실제 길이를 알아내기는 어렵습니다.
  • 여기서 영어 및 공백은 1바이트, 한글과 한자, 특수문자는 3바이트를 차지합니다.
• 그래서! CHAR_LENGTH를 이용하면 문자열의 실제 길이를 확인할 수 있습니다.

 

💡 특정 문자까지의 문자열 길이 반환

• POSITION 함수는 지정한 특정 문자까지의 문자열 길이를 반환해주는 친구입니다.
• 지정한 문자가 탐색 대상이 되는 문자열에 존재하지 않으면 0을 반환해줍니다.

 

💡 지정한 길이나 범위만큼 문자열 반환

• LEFT는 문자열의 왼쪽부터, RIGHT는 오른쪽부터 정의한 위치만큼의 문자열을 반환해줍니다.

SELECT SUBSTRING('abc@email.com', 1, POSITION('@' IN 'abc@email.com') - 1); 
-- abc

• 이들과 유사하지만, SUBSTRING은 지정한 범위의 문자열을 반환해주는 역할을 합니다.
• 위 코드는 전처리할 때 정말 자주 사용되는 코드이니 반드시 알아두기! 사용자의 이메일에서 아이디만 추출할 때 빈번하게 사용됩니다.

 

💡 특정 문자를 다른 문자로 대체

SELECT first_name, REPLACE(first_name, 'A', 'C') 
FROM customer 
WHERE first_name LIKE 'A%';

• REPLACE는 지정한 문자를 다른 문자로 대체해주는... 파이썬과 비슷합니다.

 

💡 문자 반복

SELECT first_name, REPLACE(first_name, 'A', REPEAT('C', 10)) 
FROM customer WHERE first_name LIKE '%A%';

• REPEAT는 지정한 문자를 반복할 때 사용됩니다. 파이썬에서는 * 연산자만 있으면 간단히 가능했었죠.

 

💡 그 외 기타 함수들

• SPACE는 지정한 인자만큼 공백을 생성해주고,
• REVERSE는 문자열을 거꾸로 정렬해줍니다.
• 마지막으로 STRCMP는 두 문자열을 비교해서 같으면 0, 다르면 -1 반환해주는데, 이 친구가 아이디나 비밀번호를 조회할 때 유용하게 사용된다고 하네요! 이것도 꼭 기억해두기!

 

🗓️ 날짜 함수

💡 서버의 현재 날짜나 시간 반환

• DB 서버의 현재 날짜를 확인하려면 CURRENT_DATE, 시간을 알고 싶으면 CURRENT_TIME를 사용합니다.
• 그리고 날짜와 시간을 합쳐 확인하고 싶으면 CURRENT_TIMESTAMP 또는 NOW 함수를 사용합니다.
• 이에 더해서, 정밀한 시간을 확인하려면 밀리초나 마이크로초를 함수에 입력해주면 됩니다.
• 여기서 주의할 건 지금까지 알려드린 함수들은 UTC가 아닌, SQL DB 서버의 날짜와 시간이라는 것입니다!
• 그래서 실제 UTC 날짜를 확인하려면 UTC_DATE, 시간은 UTC_TIME, 날짜 + 시간은 UTC_TIMESTAMP 사용해서 추출해낼 수가 있겠습니다.

 

💡 날짜 계산

• DATE_ADD 함수나 DATE_SUB을 이용해서 날짜를 더하거나 빼는 것이 간편하게 가능합니다.

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

• 두 번째 인자로 INTERVAL과 함께 더하거나 빼고자 하는 숫자, 연, 월, 일 등의 단위를 입력해주면 동작합니다.
• DATE_SUB는 양수를 입력하면 날짜를 빼고, 음수를 입력하면 날짜를 더하는 청개구리 방식입니다.

 

💡 날짜 간 차이 계산

• 시계열 데이터 분석 시에 자주 사용되는 함수들로 구성되어 있습니다.
• 먼저 DATEDIFF 함수는 날짜 간의 시간 차이를 계산해줍니다. 시작 날짜, 종료 날짜를 인자로 받고, 그 차이만큼 일수를 반환해주는 친구입니다.
• 근데 일수가 아닌 연 또는 시간 등 다양한 단위로 확인하고 싶으면 TIMESTAMPDIFF 함수를 이용하시면 됩니다!
• 여기서는 MONTH, YEAR, QUARTER 등의 단위를 사용할 수가 있습니다.

 

💡 요일, 연, 월, 주, 일 반환

• DAYNAME은 요일, YEAR은 연도, MONTH은 월, WEEK은 주, DAY은 일을 반환해주는 함수들입니다.

 

💡 날짜 형식 변환

SELECT DATE_FORMAT('2023-08-20 20:23:01', '%m/%d/%Y');

• DATE_FORMAT은 날짜를 다양한 형식으로 표현해야 할 때 사용해주는 함수입니다.

SELECT GET_FORMAT(DATE, 'USA') AS USA;

• 그리고 개인적으로 보면서 신기해했던 함수 중 하나인데, 이 GET_FORMAT 함수를 이용해서 국가나 지역별 날짜 형식이 어떠한지 간단히 알 수가 있게 됩니다.

 

🦞 집계 함수

💡 COUNT

• COUNT 함수는 익숙하죠! 조건에 맞는 데이터의 개수를 셀 때 사용하는 함수입니다.
• COUNT에서는 NULL 값이 발생하면 제외하고 세기 때문에, 전체 데이터 개수와 COUNT 함수로 얻은 데이터 개수가 다를 수 있습니다.
• 또한, DISTINCT를 사용하면 중복값을 제거한 후, 행의 개수를 반환하게 됩니다.

 

💡 통계값 반환

• 합을 구해주는 SUM, 평균을 구해주는 AVG, 최솟값 또는 최댓값을 구해주는 MIN과 MAX 함수가 있습니다.
• 모든 값에 대한 표준편차를 구해주는 STDDEV, 표본에 대한 표준편차를 구해주는 STDDEV_SAMP 함수도 있네요!

 

💡 부분합과 총합을 계산

• GROUP BY와 ROLLUP 조합을 이용하면 입력한 열을 기준으로 오른쪽에서 왼쪽으로 이동하며 부분합과 총합을 계산해줍니다.

SELECT customer_id, staff_id, SUM(amount)
FROM payment
GROUP BY customer_id, staff_id WITH ROLLUP;

• ROLLUP을 사용할 때는 GROUP BY 뒤의 순서에 따라서 계층화되는 부분도 달라지기 때문에 열의 순서를 잘 정해줘야 합니다.
• 이 ROLLUP 함수와 뒤 순위 함수들이 특히 SQLD에서 많이 나오는 함수!

 

🥇 순위 함수

💡 ROW_NUMBER

ROW_NUMBER() OVER([PARTITION BY 열] ORDER BY 열)

• 이 함수는 모든 행에 유일한 값으로 순위를 부여해줍니다.
• 순위를 부여하기 위한 정렬 조건으로 OVER(ORDER BY 열)을 사용해서 해당 열을 정리한 뒤에 순위를 부여해주게 됩니다.
• 데이터 그룹별 순위를 부여하고 싶다면 PARTITION BY 옵션을 사용하면 되고,
• 같은 순위 데이터의 경우, 어떤 데이터에 어떤 순위를 결정할지는 데이터 정렬 순서에 따라 달라지게 됩니다.
• 만약 동점에 대한 순위를 MySQL이 임의로 부여하지 않게 하려면 ORDER BY 절에 정렬 조건을 세세하게 추가하는 수 밖에 없네요!

 

💡 RANK

RANK() OVER([PARTITION BY 열] ORDER BY 열)

• ROW_NUMBER와 비슷한 역할을 하지만, 같은 순위를 처리하는 방법이 다릅니다.
• RANK는 공동 순위가 있으면 그 다음 순위는 같은 순위에 있는 데이터 개수만큼 건너뛴 순위가 부여됩니다.

 

💡 DENSE_RANK

• RANK 함수와 동일하게 동작하지만, 얘는 RANK 함수와 달리 같은 순위에 있는 데이터 개수를 무시하고 순위를 매깁니다.
• 예를 들면, 1위가 3개여도 그 다음 데이터는 4위가 아닌 2위로 매겨지게 됩니다.

 

💡 NTILE

NTILE(정수) OVER([PARTITION BY 열] ORDER BY 열)

• 인자로 지정한 개수만큼 데이터 행을 그룹화한 뒤 각 그룹에 순위를 부여하는 함수입니다.
• 이때 순위는 각 행의 순위가 아닌 행이 속한 그룹의 순위입니다.
• 이 함수는 전체 행을 균등하게 나누어서 1순위 그룹, 2순위 그룹에 차등으로 혜택을 지급할 때 활용되는 함수입니다.

 

---

🤔 23일차 회고

• 오늘은 오전에 SQL에 내장되어 있는 여러 함수들을 배웠습니다. 문자열부터 순위 함수까지 쭉 달려봤는데, 생각보다 외울 함수들은 많이 보이지 않아 수월했습니다.
• 오후에는 오늘도 어김없이 프로젝트가 진행되었습니다.
• 오늘은 항공 데이터를 분석했는데, 또다른 분야의 데이터라 아주 새로웠고, 계속해서 데이터를 접해보며 데이터 공모전이나 데이콘, 캐글 대회에도 자주 출전해야겠다는 생각을 하게 된 오늘입니다!
• 이번 주를 마지막으로 SQL도 끝나고 다음 주부터는 머신러닝을 공부하게 될 것 같습니다. 심화적인 개념들을 배우는만큼 할 수 있는 것도 많이 풍성해지는 것 같아 이 열심을 놓지 않고 계속해서 배우고 싶습니다. 🔥