데이터 탐색
SQL로 리포트 만들기
Tyler Pernes
Learning & Development Consultant
콘솔로 탐색하기
콘솔로 탐색하기
- 부정확한 그림을 보일 수 있음
preview of table: summer_games
+-------------+-----------------------------------------+--------+
| sport | event | bronze |
|-------------|-----------------------------------------|--------|
| Gymnastics | Gymnastics Men's Individual All-Around | null |
| Gymnastics | Gymnastics Men's Floor Exercise | null |
| Gymnastics | Gymnastics Men's parallel Bars | null |
+-------------+-----------------------------------------+--------+
콘솔로 탐색하기
- 분포를 파악하기 어려움
preview of table: clients
+--------------------+
| country_of_client |
|--------------------|
| United States |
| United States |
| Mexico |
| United States |
| Canada |
| Canada |
+--------------------+
쿼리로 탐색하기
SELECT DISTINCT region
FROM countries;
+-------------------------+
| region |
|-------------------------|
| WESTERN EUROPE |
| null |
| C.W. IF IND. STATES |
| OCEANIA |
| NEAR EAST |
| SUB-SAHARAN AFRICA |
+-------------------------+
쿼리로 탐색하기
SELECT region
FROM countries
GROUP BY region;
+-------------------------+
| region |
|-------------------------|
| WESTERN EUROPE |
| null |
| C.W. IF IND. STATES |
| OCEANIA |
| NEAR EAST |
| SUB-SAHARAN AFRICA |
+-------------------------+
필드 수준 집계
SELECT region, COUNT(*) AS row_num
FROM countries
GROUP BY region
ORDER BY row_num DESC;
+-------------------------+---------+
| region | row_num |
|-------------------------|---------|
| SUB-SAHARAN AFRICA | 49 |
| LATIN AMER. & CARIB | 38 |
| ASIA (EX. NEAR EAST) | 26 |
| WESTERN EUROPE | 23 |
| OCEANIA | 15 |
| EASTERN EUROPE | 15 |
+-------------------------+---------+
필드 수준 집계
SELECT revenue_source, SUM(revenue) AS revenue
FROM orders
GROUP BY revenue_source
ORDER BY revenue DESC;
+----------------+----------+
| revenue_source | revenue |
|----------------|----------|
| Olympics | 122000 |
| NFL | 80500 |
| MLB | 300 |
| NBA | 220 |
| NCAAF | 120 |
| NCAAB | 90 |
+----------------+----------+
테이블 수준 집계
SELECT COUNT(*)
FROM country_stats;
+---------+
| count |
|---------|
| 3451 |
+---------+
쿼리 검증
쿼리 검증
QUERY:
SELECT SUM(rev) AS revenue
FROM
(SELECT country, SUM(rev) AS rev
FROM orders AS o
JOIN countries AS c
ON o.country_id = c.id
GROUP BY country);
+------------+
| revenue |
|------------|
| 50.00 |
+------------+
ORIGINAL TABLE:
SELECT SUM(rev) AS revenue
FROM orders;
+------------+
| revenue |
|------------|
| 500.00 |
+------------+
- 우리 쿼리(왼쪽) = $50
- 원본 테이블(오른쪽) = $500
JOIN으로 매출 90% 손실
쿼리 검증
QUERY:
SELECT SUM(rev) AS revenue
FROM
(SELECT country, SUM(rev) AS rev
FROM orders AS o
JOIN country_stats AS cs
ON o.country_id = cs.country_id
GROUP BY country);
+------------+
| revenue |
|------------|
| 5,000.00 |
+------------+
ORIGINAL TABLE:
SELECT SUM(rev) AS revenue
FROM orders;
+------------+
| revenue |
|------------|
| 500.00 |
+------------+
- 우리 쿼리(왼쪽) = $5,000
- 원본 테이블(오른쪽) = $500
JOIN으로 10배 중복
탐색해 봅시다!
SQL로 리포트 만들기
