Dev. Oratorium : For Mobile, By Mobile

javaexpert.tistory.com/503

★ OVER()함수란?

OVER함수는 ORDER BY, GROUP BY 서브쿼리를 개선하기 위해 나온 함수라고 할 수 있습니다.

★ 전통 SQL 사용

★ OVER 함수 이용

★ COUNT(*)OVER() 사용

실제 데이터와 함께 해당 테이블의 전체 로우 컬럼을 쉽고 편리하게 추출할 수 있다.

위의 쿼리를 실행하면 다음과 같은 오류 메시지가 나온다.
ORA-00937: not a single-group group function

다음 쿼리로 쉽게 전체 카운트를 추출할 수 있다.

★ OVER() 함수

COUNT(*)OVER() : 전체행 카운트
COUNT(*)OVER(PARTITION BY 컬럼) : 그룹단위로 나누어 카운트

MAX(컬럼)OVER() : 전체행 중에 최고값
MAX(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 최고값

MIN(컬럼)OVER() : 전체행 중에 최소값
MIN(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 최소값

SUM(컬럼)OVER() : 전체행 합
SUM(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 합

AVG(컬럼)OVER() : 전체행 평균
AVG(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 평균

STDDEV(컬럼)OVER() : 전체행 표준편차
STDDEV(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 표준편차

RATIO_TO_REPORT(컬럼)OVER() : 현재행값/SUM(전체행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.
RATIO_TO_REPORT(컬럼)OVER(PARTITION BY 컬럼) : 현재행값 / SUM(그룹행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.

======================================================================

COUNT(expr) OVER(analytic_clause)

- 같거나 작은 조건에 대한 갯수 반환

/* 부서번호가 50인 부서 지원에 대해 각 직원의 급여보다 같거나 적게 받는 사람에 대한 누적 합을 반환. */

SELECT employee_id, salary

, COUNT(*) over(ORDER BY salary) AS "Count"

FROM employees

WHERE department_id = '50';

SUM(expr) OVER(analytic_clause)

-- 같거나 작은 값들에 대한 누적

/* 특정 값을 누적하여 결과를 보여준다. */

SELECT employee_id, salary

, SUM(salary) over(ORDER BY employee_id)

FROM employees

WHERE department_id = '50';

/* 위 예제에 더해 부서별 누적 결과를 함께 보고자 한다. */

SELECT employee_id, department_id, salary

, SUM(salary) over(ORDER BY department_id, employee_id)

, SUM(salary) over(partition by department_id order by employee_id)

FROM employees;

RANK() OVER()

--순위

SELECT SAL_SNO, SAL_YYMM, SAL_TOTAL,

RANK() OVER(ORDER BY SAL_TOTAL) AS "CONT"

FROM TB_SALARY

WHERE SAL_YYMM = '201101';

DENSE_RANK 함수

- 값의 그룹에서 값의 순위를 계산합니다. RANK와는 달리 같은 순위가 둘 이상 있어도 다음 순위는 1만 증가하여 반환.

SELECT employee_id, department_id, salary

, DENSE_RANK() over(PARTITION BY department_id ORDER BY salary DESC)

FROM employees

WHERE department_id = '50'

★ ROW_NUMBER() OVER- 특정 컬럼 기준으로 순위정하기(행번호 부여하기)

결과

MAIN_CODE GOODS_SEQ OFFER_MASTER_SEQ MODEL_CD RANK

--------------------------------------------------------------

1 1 1 AAA 1

1 1 2 AAA 2

1 2 4 BBB 3

1 2 5 BBB 4

2 1 1 AAA 1

2 1 4 AAA 2

2 2 5 BBB 3

2 2 6 BBB 4

2 3 7 CCC 5

2 3 9 CCC 6

★ 특정 column의 값을 기준으로 레코드의 순서정하여 정렬하기.

TEAM_CD SCORE PALY_DATE

------------------------------

AAA 90 2010/08/01

AAA 50 2010/08/02

AAA 60 2010/08/03

AAA 50 2010/08/04

BBB 50 2010/08/01

BBB 90 2010/08/02

BBB 95 2010/08/03

BBB 100 2010/08/04

방법 1. RANK() OVER

결과

TEAM_CD SCORE RANK PALY_DATE

------------------------------

BBB 100 1 2010/08/04

BBB 95 2 2010/08/03

AAA 90 3 2010/08/01

BBB 90 3 2010/08/02

AAA 60 5 2010/08/03

AAA 50 6 2010/08/02

AAA 50 6 2010/08/04

BBB 50 6 2010/08/01

방법 2. ROW_NUMBER() OVER

결과

TEAM_CD SCORE RANK PALY_DATE

------------------------------

BBB 100 1 2010/08/04

BBB 95 2 2010/08/03

AAA 90 3 2010/08/01

BBB 90 4 2010/08/02

AAA 60 5 2010/08/03

AAA 50 6 2010/08/02

AAA 50 7 2010/08/04

BBB 50 8 2010/08/01

방법 3. DENSE_RANK() OVER

결과

TEAM_CD SCORE RANK PALY_DATE

------------------------------

BBB 100 1 2010/08/04

BBB 95 2 2010/08/03

AAA 90 3 2010/08/01

BBB 90 3 2010/08/02

AAA 60 4 2010/08/03

AAA 50 5 2010/08/02

AAA 50 5 2010/08/04

BBB 50 5 2010/08/01

★ 분석용 함수

RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용)

DENSE_RANK - 해당값에 대한 우선순위를 결정 (중복 우선순위 허용 안함)

ROW_NUMBER - 조건을 만족하는 모든 행의 번호를 제공

CUME_DIST - 분산값

PERCENT_RANK - 백분율

NTILE(n) - 전체 데이터 분포를 n-Buckets으로 나누어 표시

FIRST_VALUE - 정렬된 값중에서 첫번째 값을 반환.

LAST_VALUE - 정렬된 값중에서 마지막 값을 반환.

★ OVER() 에 사용되는 OPTION

1. PARTITION BY
2. ORDER BY DESC
3. NULLS FIRST : NULL 데이터를 먼저 출력.
4. NULLS LAST : NULL 데이터를 나중에 출력.

[출처] 오라클 OVER() 함수 |작성자 whitefre

오류내용

mysql> ERROR 1366 (HY000): Incorrect string value: '\xEB\xB2\x88 \xED\x94...

요즘 리눅스에서 MySQL을 설치하면 이 캐릭터셋 설정을

/etc/mysql/mysql.conf.d/ 아래에 각 파일별로 저장을 해놓아야함

client.cnf
[client]
default-character-set=utf8


mysqld.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
init_connect=SET collation_connection=utf8_general_ci
init_connect=SET NAMES utf8


mysqldump.cnf
[mysqldump]
default-character-set=utf8


mysql.cnf
[mysql]
default-character-set=utf8

euckr로 변경시

위의 파일들 utf-8 ==> euckr로 변경 및

[mysqld]
default-character-set=euckr
init_connect=SET collation_connection=euckr_korean_ci
init_connect=SET NAMES euckr
character-set-server=euckr
collation-server=euckr_korean_ci

+ 특정 데이터베이스 전체 언어셋 변경 쿼리문

mysql> alter database [DB명] default character set euckr collate euckr_korean_ci;

mysql> alter database [DB명] default character set utf-8 collate utf8_general_ci;

변경 상태 확인

mysql> status;

그뒤에는

sudo systemctl restart mysql;

Reference

https://avengersrhydon1121.tistory.com/162

https://zzznara2.tistory.com/765

https://tmdgus.tistory.com/152

http://dohyunworlds.blogspot.com/2016/08/mysql-utf-8-euckr.html

https://lovelydiary.tistory.com/60

출처: https://2dubbing.tistory.com/51 [비실이의 개발공간]

예시

테이블 명: TEST 

위 테이블에서 idx가 1인 row의 컬럼 name 값을 KIM으로, 

idx가 3인 row의 컬럼 age 값을 29 로 변경

이 경우 아래와 같이 쿼리를 구성하여 처리한다.

UPDATE  TEST
SET
  name = CASE idx WHEN 1 THEN 'KIM' ELSE name END,
  age = CASE idx WHEN 3 THEN 29 ELSE age END

WHERE  idx IN (1, 3);

4번째 라인을 보면 ELSE 구문이 있는데  이 쿼리를 해석해보면 아래와 같다.

`idx가 1인 row의 컬럼 name의 값을 'KIM' 으로 변경하고, idx가 1이 아닌 row의 컬럼 name은 이전 값을 유지`

만약 위 쿼리에서 ELSE 구문을 모두 지운 뒤에 쿼리를 수행하면 아래와 같이 바뀌게된다.

Batch

배치(batch)란 일괄적으로 작업을 처리한다는 의미다. 예제에서 토픽 저장을 할 때마다 해당 작업을 처리하는 것이 아니라 이메일을 발송해야 한다는 사실만 일단 기록해두고, 실제 이메일 전송 작업은 백그라운드로 일괄적 처리할 것이다. 

---------------------------------------------------------------------------------------------------

1. MySQL SQL 배치 작업 (in Window)

출처: https://skshpapa80.tistory.com/27

리눅스에서는 cron 이라는 좋은툴이 있으니 넘어가고 윈도우에서 MySQL 배치 작업을 하는 방법을 소개합니다.

MySQL윈도우 버전을 설치하면 MySQL Command Line Tool이 같이 설치됩니다.

이툴을 사용하여 배치작업을 할예정인데요.

배치작업을 위해 두개의 파일이 필요합니다.

bat확장자를 가진 배치파일과 실행될 SQL 이저장된 파일

​

1. 우선 bat 파일을 하나 만듭니다.

텍스트 에디터에서 신규 파일을 생성한 후

ex) MYSQL_BATCH.bat 이런식으로 파일을 생성합니다.

그리고 다음과 같이 코딩합니다.

C:\PROGRA~1\MySQL\MYSQLS~1.1\bin\mysql.exe -u아이디 -p패스워드 < runsql.sql

(MySQL 설치위치는 설치환경에 따라 다를수 있습니다!)

bat 파일을 저장합니다.

​

2.두개의 파일은 같은 폴더에 있어야 합니다.

SQL 파일을 하나 만듭니다. ex) runsql.sql

신규로 텍스트파일을 하나 생성한 후

runsql.sql 파일로 저장하고

실행할 내용의 SQL 들을 정리해서 입력합니다.

여러줄을 입력할경우 SQL 문 마지막에 ; 를 입력합니다.

​

3.미리 작성한 sql문을 테스트 하고

runsql.sql 파일을 저장합니다.

​

4.MYSQL_BATCH.bat 실행

MYSQL_BATCH.bat 파일을 더블 클릭하여 정상적으로 실행되는지 테스트 합니다.

아이디와 비밀번호, 경로를 잘 확인해야 합니다.

​

5.마지막으로 윈도우 제어판에가서 예약을 설정합니다.

이때 처음작성한 bat 파일을 원하는시간에 동작하도록 설정하면

사용자가 원하는 시간에 실행되는

MySQL 배치작업이 완성됩니다.

​

이방법을 사용해서 MySQL 배치작업 스크립트를 많이 돌려 봤는데 잘 운영됩니다. ^^

2. Linux - cron 사용

Cron

크론은 유닉스 계열 (리눅스 포함)에 기본적으로 포함된 스케줄러다. 스케줄러라는 것은 정해진 시간에 어떤 작업을 수행하도록 하는 소프트웨어를 말한다. 특정 시간에 어떤 스크립트가 실행되도록 크론에 정의해두면 그 시간에 그 스크립트를 실행해준다. 이에 대한 자세한 설명은 리눅스 수업 크론편을 참고하자.

CLI 수업에서 만든 예제는 바로 크론을 이용하기 위한 것이다. 큐에 쌓여있는 작업을 하나씩 수행하도록 할 때 크론을 이용할 것이다. 이렇게 해야 할일을 순차적으로 쌓아놨다가 일괄적으로 처리하는 것을 배치(batch) 작업이라고 부른다. 

https://opentutorials.org/course/697/4130

blog.naver.com/todangs/220779628651

https://dalgoo.tistory.com/17