[SQL] 데이터 캠프 수강 (7) - Exploratory Data Analysis in SQL

SQL Fundermentals 과정을 완료하고 SQL for Business Analysts 과정을 시작했다. 😄

SQL for Business Analysts의 과정은 5개의 과정으로 이루어져 있다.

• Exploratory Data Analysis in SQL
• Data-Driven Decision Making in SQL
• Applying SQL to Real-World Problems
• Anaylizing Business Data in SQL
• Reporting in SQL

이번 글에서는 EDA in SQL을 들었던 것을 정리할 예정이다.

PostgreSQL을 사용하여 해당 과정을 진행한다.

---

컬럼의 제약 조건

• Foreign key : Unique + Null 도 포함 가능
• Primary key : Not Null & Unique
• Unique
• Not null
• Check constrains : 값에 조건을 준다.
  • ex) column1 > 0

 

coalesce() : 순서대로 인수를 평가하고 NULL 이 아닌 첫 번째 식의 현재 값을 반환한다.

• ex) coalesce(NULL, 1, 2) -> 1

Casting 할 때 주의점 : GROUP BY 에서 해당 column의 type을 바꾼 경우, SELECT 절에서도 해당 column의 type을 바꾸어 주어야 Error가 나지 않는다.

 

SELECT col1::integer, COUNT(*)
FROM tableA
GROUP BY col1::integer -- GROUP BY와 SELECT 절에서 type을 동일시 할 것 !

---

Summarizing and aggregating numeric data

[Numeric Data Types and Summary Functions]

값을 나누는 숫자의 유형에 따라 결과도 다르게 나온다.

SELECT 10/4; -- Result : 2
SELECT 10/4.; -- Reseul : 2.5

Variance

• var_pop : 모분산을 계산
• var_samp : 표본분산을 계산
  • variance = var_samp

SELECT var_pop(col1), var_samp(col2), variance(col2) -- 모분산/표본 분산/표본 분산
FROM tableA;

Standard deviation

• stddev_pop : 모 표준편차를 계산
• stddev_samp : 표본 표준편차를 계산
  • stddev = stddev_samp

SELECT stddev_pop(col1), stddev_samp(col2), stddev(col2) -- 모 표준편차/표본 표준편차/표본 표준편차
FROM tableA;

• Trunc(num, loc) : num을 소수점 뒤의 loc 까지만 나타내고 나머지는 버린다. (loc > 0 인 경우)
• Trunc(num, loc) : num을 소수점 왼쪽의 loc 까지만 나타내고 나머지는 버린다. (loc < 0 인 경우)

SELECT TRUNC(42.1956, 2) -- Result : 42.19
SELECT TRUNC(12345, -3) -- Result : 12000

• generate_series(start, end, step) : start ~ end 를 step 만큼 나누어 값을 나타낸다. (Python의 range와 같음)

SELECT generate_series(1, 10, 2)
-- Result : 1 3 5 7 9

• corr(var1, var2) : 두 변수 사이의 상관관계를 나타내준다.

 

• Percentile functions : 백분위 수 값을 구할때 사용하는 함수이다.
  • percentile_disc : Returns a value from column
  • percentile_cont : Interpolates between values

SELECT percentile_disc(percentile) WITHIN GROUP (ORDER BY column_name)
FROM table;
-- percentile between 0 and 1

---

[Creating Temporary Tables]

• Create Temp Table Syntax

CREATE TEMP TABLE new_tablename AS
SELECT col1, col2
FROM tableA;

• Select Into Syntax

SELECT col1, col2
 INTO TEMP TABLE new_tablename
 FROM table;

• Insert into Table (행 삽입)

INSERT INTO new_table
SELECT col1, col2
	FROM tableB
WHERE col3 BETWEEN val1 AND val2 

• DROP Table (테이블 삭제)

DROP TABLE IF EXISTS tableA; -- tableA가 존재하면 테이블을 삭제하겠다라는 의미

---

[Character data types and common issues]

• trim(col, 제거할 문자) : col이 문자열 값을 가질때, 제거할 문자들을 제거해서 결과를 반환한다.
  • ex)trim(col, '0123456789') : 숫자 값을 모두 지운 값을 반환
• substring(string FROM start FOR length) : 문자열의 문자 추출 함수이다.
  
  • ex) substring('abcde' FROM 2 FOR 3); -> bcd
• substr('abcde', 2, 3) -> bcd
• split_part(string, delimiter, part) : string을 delimiter를 기준으로 구분하여 part에 위치한 문자를 반환한다.
  
  • ex) split_part('a,bc,d', ',', 2) -> bc

---

[Data/Time  types and formats]

date Type에서는 정수를 더해서 결과를 도출할 수 있지만, Timestamp Type에서는 interval로 Type을 변환하여 계산을 수행해야 한다.

SELECT '2020-01-01'::date + 1;
-- Result : 2020-01-02

SELECT '2020-12-20'::date + '1 year'::interval;
--Result : 2021-12-20 00:00:00

SELECT '2018-12-20'::date + '1 year 2 days 3minute'::interval;
--Result : 2019-12-12 00:03:00

Common date/time fields

Fields

• century : 2019-01-01 = century 21
• decate : 2019-01-01 = decade 201
• year, month, day
• hour, minute, second
• week
• dow : day of week (0 : 일, 6 : 토요일)

date_trunc('field', col)

• to_char(SYSDATE, 'DAY')
  • ex) to_char(now(), 'DAY') : 금요일 
  • *오늘은 2021/4/2 금요일이다.

 

Aggregating with date/time series

• generate_series(from, to, interval) : 시작 ~ 끝 날짜를 interval 만큼 띄어 결과를 반환해준다.

SELECT generate_series('2018-01-01', '2018-01-15', '2 days'::interval);

<generate_series 실행 결과>