SQL(3)(order by, aggregate functions, count, sum, min, max, avg)

-

ORDER BY

하나 이상의 열에 있는 데이터를 기반으로 결과를 재정렬 할 수 있다.

1
2
3
SELECT *
  FROM tutorial.billboard_top_100_year_end
 ORDER BY artist
cs

결과가 artist열의 내용에 따라 a부터 z까지 알파벳순으로 정렬되어 있음을 알 수 있다. 이를 오름차순이라고 한다. 기본값이다. 숫자는 작은숫자부터 큰숫자로 정렬된다.







1
2
3
4
SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 2013
 ORDER BY year_rank DESC
cs

결과를 반대(내림차순)으로 표시하려면 DESC연산자를 추가해야한다.







연습문제

Z에서 A까지 노래 제목순으로 2012년의 모든 행을 반환하는 쿼리

1
2
3
4
SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 2012
 ORDER BY song_name DESC
cs







여러 열로 데이터 정렬

order by절의 열은 쉼표로 구분해야한다.

DESC연산자는 앞에있는 열만 적용된다.

순서의 차이가 있다.

1
2
3
4
SELECT *
  FROM tutorial.billboard_top_100_year_end
  WHERE year_rank <= 3
 ORDER BY year DESC, year_rank
cs

최근연도가 먼저 나오지만 순위가 낮은 노래보다 상위 순위의 노래를 정렬한다.







1
2
3
4
SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year_rank <= 3
 ORDER BY year_rank, year DESC
cs







연습문제

아티스트가 각 노래에 대해 알파벳순으로 정렬 된 2010년의 모든 행을 순위 순으로 반환하는 쿼리

1
2
3
4
SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year = 2010
 ORDER BY year_rank, artist
cs







주석

1. --를 사용하여 주어진 줄에서 오른쪽에 있는 모든 것을 주석처리 할 수 있다.

2. /* 주석을 시작하고, */ 주석을 닫는 데 사용하여 여러 줄에 주석을 남길 수 있다.

연습문제

T-Pain이 그룹 구성원이었던 모든 행을 차트에서 순위에 따라 가장 낮은 순위에서 가장 높은 순위로 표시하는 쿼리

1
2
3
4
SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE "group" ILIKE '%t-pain%'
 ORDER BY year_rank DESC
cs







1993, 2003 또는 2013년에 10에서 20을 포함하는 순위의 노래를 반환하는 쿼리를 작성, 연도 및 순위별로 결과를 정렬하고 해당 줄의 기능을 나타내는 각 줄에 주석을 남긴다.

1
2
3
4
5
SELECT *
  FROM tutorial.billboard_top_100_year_end
 WHERE year IN (2013, 2003, 1993)  --연도 선택
   AND year_rank BETWEEN 10 AND 20  --순위 10~20
 ORDER BY year, year_rank
cs







AGGREGATE FUNCTIONS

- COUNT : 특정 열에있는 행 수를 계산

- SUM : 특정 열의 모든 값을 더한다.

- MIN, MAX : 각각의 특정 항목의 최소값 및 최대값을 반환

- AVG : 선택한 값 그룹의 평균을 계산

집계함수는 열로만 집계된다. 행에 대해 계산을 수행하려면 간단한 산술로 수행해야한다.

COUNT

특정 열의 행 수를 계산하는 집계함수이다.

모든 행 계산

1
2
SELECT COUNT(*)
  FROM tutorial.aapl_historical_stock_price
cs







개별 열 계산

1
2
SELECT COUNT(high)
  FROM tutorial.aapl_historical_stock_price
cs







전체와 개별이 결과가 다른 이유는 null값이 세어지지 않기 때문이다.

연습문제

low열에서 null이 아닌 행의 수를 계산하는 쿼리를 작성

1
2
SELECT COUNT(low) AS low
  FROM tutorial.aapl_historical_stock_price
cs







숫자가 아닌 열 계산

1
2
SELECT COUNT(date) AS count_of_date
  FROM tutorial.aapl_historical_stock_price
cs







1
2
SELECT COUNT(date) AS "Count Of Date"
  FROM tutorial.aapl_historical_stock_price
cs

열 이름을 지정할 때 위와 같이 공백이 있는 경우 ""를 사용하면 된다.







연습문제

모든 단일 열의 개수를 결정하는 쿼리를 작성. null값이 가장 많은 열은 무엇입니까?

1
2
3
4
5
6
7
8
SELECT COUNT(year) AS year,
       COUNT(month) AS month,
       COUNT(open) AS open,
       COUNT(high) AS high,
       COUNT(low) AS low,
       COUNT(close) AS close,
       COUNT(volume) AS volume
  FROM tutorial.aapl_historical_stock_price
cs





high

SUM

주어진 열의 값을 합산하는 집계함수이다. 숫자 값이 포함 된 열에만 사용이 가능하다.

null을 0으로 취급한다.

1
2
SELECT SUM(volume)
  FROM tutorial.aapl_historical_stock_price
cs







연습문제

평균 시가를 계산하는 쿼리를 작성(count, sum을 모두 사용)

1
2
SELECT sum(open)/count(open) as open_avg
  FROM tutorial.aapl_historical_stock_price
cs






MIN, MAX

특정 열에서 최소값 및 최대값을 반환하는 집계함수이다. 숫자가 아닌 열에 사용할 수 있다.

1
2
3
SELECT MIN(volume) AS min_volume,
       MAX(volume) AS max_volume
  FROM tutorial.aapl_historical_stock_price
cs






연습문제

Apple의 최저 주가는 얼마입니까?

1
2
SELECT MIN(low)
  FROM tutorial.aapl_historical_stock_price
cs






Apple의 주가가 하루에 가장 많이 증가한 것은 무엇입니까?

1
2
SELECT MAX(close - open)
  FROM tutorial.aapl_historical_stock_price
cs






AVG

선택한 그룹의 평균을 계산하는 집계함수이다. 숫자열에서만 사용이 가능하다. null이 무시된다. 

다음을 보면 null값이 무시된다는 것을 확인할 수 있다.

1
2
3
SELECT AVG(high)
  FROM tutorial.aapl_historical_stock_price
 WHERE high IS NOT NULL
cs






1
2
SELECT AVG(high)
  FROM tutorial.aapl_historical_stock_price
cs







연습문제

Apple주식의 일일 평균 거래량을 계산하는 쿼리

1
2
SELECT AVG(volume) AS avg_volume
  FROM tutorial.aapl_historical_stock_price
cs








댓글

댓글 쓰기

이 블로그의 인기 게시물

SQL(9)(performance tuning queries, pivoting data)

-
이미지
PERFORMANCE TUNING QUERIES 쿼리를 개선할 수 있는 개선방법을 식별하는 방법 먼저 수행해야하는 계산 수와 쿼리 런타임에 영향을 주는 몇가지 사항 - 테이블 크기 : 쿼리가 수백만개의 행이 있는 하나 이상의 테이블에 도달하면 성능에 영향을 미칠 수 있다. - 조인 : 쿼리가 결과 집합의 행 수를 크게 늘리는 방식으로 두 테이블을 조인하면 쿼리가 느려질 수 있다. - 집계 : 결과를 생성하기 위해 여러 행을 결합하려면 단순히 해당 행을 검색하는 것보다 더 많은 계산이 필요하다. - 쿼리를 실행하는 다른 사용자 : 데이터베이스에서 동시에 실행되는 쿼리가 많을수록 데이터베이스가 주어진 시간에 더 많은 처리해야하며 모든것이 느리게 실행된다. - 데이터베이스 소프트웨어 및 최적화 : 제어 할 수 없는 부분이지만 사용중인 시스템을 알고있다면 범위 내에서 작업하면 쿼리를 보다 효율적으로 만들 수 있다. 테이블 크기 줄이기 1 2 3 4 SELECT   *    FROM  benn.sample_event_table   WHERE  event_date  > =   '2014-03-01'    AND event_date  <    '2014-04-01' cs 1 2 3 SELECT  COUNT( * )    FROM  benn.sample_event_table   LIMIT   100 cs 1 2 3 4 5 6 SELECT  COUNT( * )    FROM  (      SELECT   *        FROM  benn.sample_event_table       LIMIT ...
자세한 내용 보기

데이터 크롤링(3주차)

-
이미지
 데이터를 시각화하고 분석을 진행함에 앞서 필요한 데이터들을 수집가능한지 어떤 문제점이 있는지 알아보기 위해 테스트를 진행해 보았다. 먼저 네이버 금융에 들어가 IT서비스 분야를 선택하면 다음과 같은 화면이 나타난다. 이 곳에서 필요한 데이터를 수집해 보기로 했다. 일단 종목명부터 수집해 보았다. import requests from pprint import pprint from bs4 import BeautifulSoup 먼저 웹 크롤링을 하기 위해 필요한 패키지들을 불러왔다. html = requests.get('https://finance.naver.com/sise/sise_group_detail.nhn?type=upjong&no=154') # 웹 페이지 요청 soup = BeautifulSoup(html.text, 'html.parser') # 다루기 좋게 파싱작업진행 data2 = soup.find_all('div', {'class':'name_area'}) # 회사 이름들을 추출한다. len(data2) #66 66개의 모든 종목명을 추출했다. name=[] for i in range(len(data2)):     name.append(soup.find_all('div', {'class':'name_area'})[i].get_text()) print(name) 리스트로 만들어 좀 더 보기 쉽게 만들었다. 결과는 다음과 같다. name1=[] for i in range(len(name)):     name1.append(name[i].replace('*', '').replace(' ','')) print(name1) 종목명에 특수문자와 공백을 제거했다. 결과는 다음과 같다. 이제 종목명 옆에 있는 다른 값들을 추출해 볼것이다. 이 곳에서 두가지의 문제점을 발견한다. 1. na...
자세한 내용 보기

SQL(8)(writing subqueries, window functions)

-
이미지
WRITING SUBQUERIES 하위쿼리는 쿼리내의 여러 위치에서 사용할 수 있지만 from문으로 시작하는 것이 가장 쉽다. 1 2 3 4 5 6 7 SELECT  sub. *    FROM  (          SELECT   *            FROM  tutorial.sf_crime_incidents_2014_01           WHERE  day_of_week  =   'Friday'        ) sub   WHERE  sub.resolution  =   'NONE' cs 1 2 3 SELECT   *    FROM  tutorial.sf_crime_incidents_2014_01   WHERE  day_of_week  =   'Friday' cs 연습문제 모든 Warrant Arrests를 선택하는 쿼리를 작성하고 해결되지 않은 사건만 표시하는 외부쿼리로 래핑 1 2 3 4 5 6 7 SELECT  sub. *    FROM  (          SELECT   *            FROM  tutorial.sf_crime_incidents_2014_01        ...
자세한 내용 보기