演算子と関数(2)
5394 ワード
n.関数
関数は、単行関数と複数行関数、およびウィンドウ関数に分けられ、特定のルールに基づいて新しい結果値が返されます.
1行
単行関数は、各ローに1つの関数を返します.
ネスト可能な関数
単行関数は、数値、文字、日付、フォーマットのコピー、および一般的な関数に分けられます.
複数行は結果値を返します
主にグループBy節と組み合わせて使用
集約とグループ関数に分けます.
行と行の関係を定義して結果値を返します.
ORDER BYで行と行を並べ替え、PARTIONBYにグループ化
ランキングは、集約(累積)関数に分けられます.
関数は、単行関数と複数行関数、およびウィンドウ関数に分けられ、特定のルールに基づいて新しい結果値が返されます.
1行
単行関数は、各ローに1つの関数を返します.
ネスト可能な関数
単行関数は、数値、文字、日付、フォーマットのコピー、および一般的な関数に分けられます.
/***************숫자형 함수***************/
/* ABS(숫자) : 절대값 반환 */
SELECT ABS(- 200);
/* ROUND(숫자, N) : N 기준으로 반올림 값 반환 */
SELECT ROUND(2.18, 1);
/* SQRT(숫자) : 제곱근 값 반환 */
SELECT SQRT(9);
/***************문자형 함수***************/
/* LOWER(문자) / UPPER(문자) : 소문자 / 대문자 반환 */
SELECT LOWER('AB');
SELECT UPPER('ab');
/* LEFT(문자, N) / RIGHT(문자, N) : 왼쪽 / 오른쪽부터 N만큼 반환 */
SELECT LEFT('AB', 1);
SELECT RIGHT('AB', 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');
/***************형변환 함수***************/
/* DATE_FORMAT(날짜, 형식) : 날짜형식으로 변환 */
SELECT DATE_FORMAT('2022-12-31', '%m-%d-%y');
SELECT DATE_FORMAT('2022-12-31', '%M-%D-%Y');
/* CAST(형식a, 형식b) : 형식a를 형식b로 변환 */
SELECT CAST('2022-12-31 12:00:00' AS DATE);
/***************일반 함수***************/
/* IFNULL(A, B) : A가 NULL이면 B를 반환, 아니면 A 반환 */
SELECT IFNULL(NULL, 0);
SELECT IFNULL('NULL이 아님', 0);
/*
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;
複数行複数行は結果値を返します
主にグループ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 열들을 오른쪽에서 왼쪽순으로 그룹 (소계, 합계)*/
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で行と行を並べ替え、PARTIONBYにグループ化
ランキングは、集約(累積)関数に分けられます.
/***************순위 함수***************/
/* 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 동일한_순위_반환
,DENSE_RANK() OVER (ORDER BY ORDER_DATE ASC) AS 동일한_순위_반환_하나의등수
FROM SALES;
/* 순위 함수+ 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 ORDER_DATE
,SALES_QTY
,'-' AS 구분
,COUNT(ORDER_NO) OVER (ORDER BY ORDER_DATE ASC) AS 누적_구매횟수
,SUM(SALES_QTY) OVER (ORDER BY ORDER_DATE ASC) AS 누적_구매수량
,AVG(SALES_QTY) OVER (ORDER BY ORDER_DATE ASC) AS 누적_평균구매수량
,MAX(SALES_QTY) OVER (ORDER BY ORDER_DATE ASC) AS 누적_가장높은구매수량
,MIN(SALES_QTY) OVER (ORDER BY ORDER_DATE ASC) AS 누적_가장낮은구매수량
FROM SALES;
/* 집계 함수(누적)+ PARTITION BY: 그룹별 집계 함수(누적) */
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;
Reference
この問題について(演算子と関数(2)), 我々は、より多くの情報をここで見つけました https://velog.io/@qsdcfd/연산자-및-함수-2テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol