SQL 4. SQL 활용_연산자 및 함수(2)

함수

단일 및 복수 행 그리고 윈도우 함수로 나누어지며, 특정 규칙에 의해 새로운 결과값으로 반환하는 명령어이다.

 

 

단일 행 함수 : 모든 행에 대하여 각각 함수가 적용되어 반환한다.

 

/********************************단일 행 함수***********************************/
/***************숫자형 함수***************/
/* ABS(숫자) : 절대값 반환 */
select abs(-205);

/* ROUND(숫자, N) : N 기준으로 반올림 값 반환 */
select round(2.18, 1);

/* SQRT(숫자) : 제곱근 값 반환 */
SELECT SQRT(9); -- 3

/***************문자형 함수***************/
/* LOWER(문자) / UPPER(문자) : 소문자 / 대문자 반환 */
SELECT LOWER('AB');
SELECT UPPER('ab');

/* LEFT(문자, N) / RIGHT(문자, N) : 왼쪽 / 오른쪽부터 N만큼 반환 */
SELECT LEFT('AB', 1); -- 왼쪽에서 1번째 숫자만 출력
SELECT RIGHT('AB', 1); -- 오른쪽에서 1번째 숫자만 출력

/* LENGTH(문자) : 문자수 반환 */
SELECT LENGTH('AB');

/***************날짜형 함수***************/
/* YEAR / MONTH / DAY  (날짜) : 연 / 월 / 일 반환 */
SELECT YEAR('2022-12-31');
SELECT MONTH('2022-12-31');
SELECT DAY('2022-12-31');

/* DATE_ADD(날짜, INTERVAL) : INTERVAL만큼 더한 값 반환 */
SELECT DATE_ADD('2022-12-31', INTERVAL -1 MONTH);

/* DATEDIFF(날짜a, 날짜b) : 날짜a – 날짜b 일수 반환 */
SELECT DATEDIFF('2022-12-31', '2022-12-1'); -- 31-1=30

/***************형변환 함수***************/
/* DATE_FORMAT(날짜, 형식) : 날짜형식으로 변환 */
SELECT DATE_FORMAT('2022-12-31', '%m-%d-%y');/* 12-31-22 */
SELECT DATE_FORMAT('2022-12-31', '%M-%D-%Y');/* December-31st-2022 */

/* CAST(형식a, 형식b) : 형식a를 형식b로 변환 */
SELECT CAST('2022-12-31 12:00:00' AS DATE); -- 2022-12-31

/***************일반 함수***************/
/* IFNULL(A, B) : A가 NULL이면 B를 반환, 아니면 A 반환 */
SELECT IFNULL(NULL, 0); -- null이면 뒤를 출력, 아니면 앞을 출력
SELECT IFNULL('NULL이 아님', 0); -- null이면 뒤를 출력, 아니면 앞을 출력 > 아니기 때문에 앞을 출력

/* 
CASE WHEN [조건1] THEN [반환1]
   WHEN [조건2] THEN [반환2]
     ELSE [나머지] END
: 여러 조건별로 반환값 지정
*/

SELECT  *, CASE WHEN GENDER = 'MAN'
THEN '남성'
ELSE '여성'
            END
  FROM  CUSTOMER;

 

단일 행 함수의 특징 : 각각 함수가 적용되어 반환, 여러 함수들이 중첩되어 사용이 가능

/***************함수 중첩 사용***************/
select *, year(join_date) as 가입년도, length(year(join_date)) as '가입년도 문자수'
from customer;

 

 

<복수 행 함수>

복수 행 함수는 여러 행들이 하나의 결과값으로 반환한다.

주로 group by 절과 함께 사용된다.

집계함수 & 그룹함수로 구분된다.

/********************************복수 행 함수***********************************/
/***************집계 함수***************/
SELECT  COUNT(ORDER_NO) AS 구매횟수 /* 행수 */
,COUNT(DISTINCT MEM_NO) AS 구매자수 /* 중복제거된 행수  */
        ,SUM(SALES_QTY) AS 구매수량 /* 합계 */
        ,AVG(SALES_QTY) AS 평균구매수량 /* 평균 */
        ,MAX(ORDER_DATE) AS 최근구매일자 /* 최대 */
        ,MIN(ORDER_DATE) AS 최초구매일자 /* 최소 */
FROM  SALES;
/* DISTINCT: 중복제거 */   

/***************그룹 함수***************/
/* WITH ROLLUP : GROUP BY 열들을 오른쪽에서 왼쪽순으로 그룹 (소계, 합계)*/
/* group by절 아래에 사용된다. */
SELECT  YEAR(JOIN_DATE) AS 가입연도, ADDR, COUNT(MEM_NO) AS 회원수
FROM  CUSTOMER
GROUP BY  YEAR(JOIN_DATE), ADDR
WITH ROLLUP; -- 소계 및 합계를 (중간에서) 조회할 수 있다.

 

 

/***************집계 함수 + GROUP BY***************/

SELECT  MEM_NO, SUM(SALES_QTY) AS 구매수량
FROM  SALES
GROUP BY  MEM_NO;
    
/* 확인 */
SELECT  *
FROM  SALES
WHERE  MEM_NO = '1000970';

 

 

/********************************윈도우 함수************************************/

윈도우 함수는 행과 행간의 관계를 정의하여 결과 값을 반환한다.

순위함수 & 집계합수(누적) 으로 나누어 진다.

 

윈도우 함수는 order by로 행과 행간의 순서를 정하며, partition by 로 그룹화가 가능하다.

 

 

/***************순위 함수***************/ 
/* ROW_NUMBER: 동일한 값이라도 고유한 순위 반환 (1,2,3,4,5...) */
/* RANK: 동일한 값이면 동일한 순위 반환 (1,2,3,3,5...) */
/* DENSE_RANK: 동일한 값이면 동일한 순위 반환(+ 하나의 등수로 취급) (1,2,3,3,4...) */

SELECT  ORDER_DATE
,ROW_NUMBER() OVER (ORDER BY ORDER_DATE ASC) AS 고유한_순위_반환 -- 그냥 어떻게든 순서대로
        ,RANK()    OVER (ORDER BY ORDER_DATE ASC) AS 동일한_순위_반환 -- 2등 2명이면 3등 없음
        ,DENSE_RANK() OVER (ORDER BY ORDER_DATE ASC) AS 동일한_순위_반환_하나의등수 -- 공동 수상 가능
  FROM  SALES;


/* 순위 함수+ PARTITION BY: 그룹별 순위 */ -- partition by 한 애들끼리 경쟁
SELECT  MEM_NO,

                ORDER_DATE,
                ROW_NUMBER() OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 고유한_순위_반환,
                RANK()    OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 동일한_순위_반환,
                DENSE_RANK() OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 동일한_순위_반환_                   하나의등수
  FROM  SALES;

 

/***************집계 함수(누적)***************/ 

 

SELECT  MEM_NO
,ORDER_DATE
,SALES_QTY
        ,'-' AS 구분
        ,COUNT(ORDER_NO) OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_구매횟수        
,SUM(SALES_QTY)  OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_구매수량
        ,AVG(SALES_QTY)  OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_평균구매수량
        ,MAX(SALES_QTY)  OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_가장높은구매수량
,MIN(SALES_QTY)  OVER (PARTITION BY MEM_NO ORDER BY ORDER_DATE ASC) AS 누적_가장낮은구매수량       
  FROM  SALES;

 

연산자는 비교,논리, 특수, 산술, 집합 연산자로 나누어진다.