SQL을 이용한 통계

SQL을 이용한 통계

 : 기준(group by)있어야 함.

 : 고객별 월별 년도별 .. ex) 2010년 월별 방문자 수 평균.

정렬: order by [컬럼명][정렬방법] 

LIMIT:개수 제한, LIMIT 5 -> 5개만 조회.

SELECT [컬럼들]

FROM[테이블명 별칭] 

JOIN[테이블명 별칭]  ON 연결키 = 연결키 

WHERE [조건1]

AND[조건2]

GROUP BY[기준컬럼] 

ORDER BY[컬럼명][정렬방법]

LIMIT [개수]

COUNT() 개수

SUM() 합계

AVG() 평균

MIN() MAX()

STD() 표준편차

SELECT SUBSTR('20180615' , 5, 6) AS month;

SELECT SUBSTR('20180615' , 1, 4) AS YEAR;

SELECT NOW(); -- DATETIME 을 보기좋게 변환

SELECT date_format(now(),'%Y'

* 오늘일시 YYYY/MM/DD 형식으로 표기하기

 ❖ %Y:2018,

     %y: 18,

     %M:1월 .. 

     %m:01,

     %D: 1st, 2nd..

     %d: 01, 02 ...

     %e: 1, 2 ...

    %H: 01 ~24..

     %h: 01~~ 12..

    %i : 00~59

    %s: 00~59

e.g)

SELECT date_format(now(),'%Y %M %')

SELECT *
FROM payment
WHERE date_format(payment_date, '%Y%m%d') = '20050616';

2005-0616일에 결제된 금액의 합.

SELECT sum(amount)
FROM payment
WHERE date_format(payment_date, '%Y%m%d')= '20050616';

결제일자 기준, 2005년 각 월별 결제 금액의 합. 

1. 2005년 데이터 추출

SELECT date_format(payment_date, '%m') AS month, SUM(amount) 
FROM payment
WHERE date_format(payment_date, '%Y')= '2005';

 2. 월별로 묶어준다. 그룹

GROUP BY date_format(payment_date, '%m');

 ❖ CONCAT() //문자열 붙이기. 

CONCAT('A','B') : AB

CONCAT('hi','there') : hithere

CONCAT('hi',' ','there') : hi there

e.g)

SELECT CONCAT(p.prvn_name,' ',d.distc_name,' ',att.attrc_name) AS attrc_name 의 결과

⇢ 

 ❖ 함수

SUBSTR(기준일, 시작, 몇개): 문자열 잘라내기

AVG()

SUM()

COUNT()

DATE_FORMAT(일시값, 형식): 일시 -> 문자열

CONCAT(문자열...):문자열 연결

ROUND()

QUARTER(일시값): 분기 값.(결과는 1,2,3,4 로 나옴.)

MAX()

MIN()

e.g)

2017년, 경기도에 속한 관광지중, 내국인 방문객 수가 가장 많은 관광지명과 관광지 속한 도시명, 방문객 조회 .

-조건 2017, 경기도

-기준: 2017 경기도에 속한 관광지.!!1 방문객수가장많은

-컬럼: 내국인 방문객수 가장많은 max 관광지명, 관광지 속한 도시명, 방문객. 

                                                 attrc_name,prvn_name,distc_name,native_cnt

⇢ 

SELECT a.attrc_name, p.prvn_name, d.distc_name,sum(f.native_cnt)
FROM figure f
JOIN attraction a ON f.attrc_cd = a.attrc_cd AND f.distc_cd = a.distc_cd AND f.prvn_cd = a.prvn_cd
JOIN district d ON d.distc_cd = a.distc_cd AND d.prvn_cd = a.prvn_cd
JOIN province p ON p.prvn_cd = d.prvn_cd
WHERE p.prvn_name = '경기도' AND SUBSTR(f.basis_date,1,4) = '2017' 
GROUP BY a.attrc_cd, p.prvn_cd ,d.distc_cd (기본키가 3개.)
ORDER BY sum(f.native_cnt) desc LIMIT 1;