SQL Tips - Heeyoung-Ahn/MariaDB-with-Excel-VBA GitHub Wiki

INSERT ON DUPLICATE KEY UPDATE

• INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE.

INSERT INTO ins_duplicate VALUES (4,'Gorilla') ON DUPLICATE KEY UPDATE animal='Gorilla';

CTE Recursive

-- 특정일(2019-01-31)부터 전월말까지 조회월을 목록으로 구성
WITH RECURSIVE query_month AS (
	SELECT '2019-01-31' AS q_month
	UNION ALL
	SELECT LAST_DAY(q_month + INTERVAL 1 MONTH)
		FROM query_month
		WHERE q_month < LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH)
)
SELECT q_month FROM query_month;

DB가 느려질면

• DB서버에서 실행중인 프로세스 확인: SHOW FULL processlist
• Command Prompt에서 netstat -an | findstr Host에 찍힌 문제가 되는 연결의 문자열
  • 예 netstat -an | findstr 51955
• 해당 Session Terminate: KILL id_no;
• 그래도 안되면 서버 재시작
  • 시작 > services.msc 입력 후 엔터
  • 설치 시 설정한 윈도우 서비스명 (기본 MySQL) 을 찾아 '다시시작'

Next Record, Previous Record

SELECT * 
    FROM co_account.corporations 
    WHERE corporation_id = (SELECT MIN(corporation_id) 
                            FROM co_account.corporations 
                            WHERE corporation_id > 4 AND suspended = 0)
SELECT * 
    FROM co_account.corporations 
    WHERE corporation_id = (SELECT MAX(corporation_id)
                            FROM co_account.corporations 
                            WHERE corporation_id < 4 AND suspended = 0)

EVENT SCHEDULER

• SET GLOBAL event_scheduler = ON;
• SHOW VARIABLES LIKE 'event%'; -- 이벤트 변수 확인
• SELECT * FROM information_schema.EVENTS; -- 이벤트 목록 확인
• my.ini의 [mysqld]에도 event_scheduler = ON 추가해야 함
  • 옵션을 쿼리로 변경해도 서버가 재시작되면 기본 설정인 off로 변경되기 때문

CREATE EVENT IF NOT EXISTS set_today_to_refer_date
	ON SCHEDULE
	    EVERY 1 DAY STARTS '2019-11-28 00:00:01'
	ON COMPLETION PRESERVE
	ENABLE
	COMMENT '전표조회기준일의 마지막날을 오늘로 업데이트'
	DO BEGIN
        -- 법인의 월별 총계정 원장 데이터 구성
        -- 법인의 연별 총계정 원장 데이터 구성
        -- 총계정원장의 기간은 2019년 1월 부터 현재까지
        CALL account_report.s_dw_general_ledger_corporation_monthly(3);
        CALL account_report.s_dw_general_ledger_corporation_yearly;
END

CREATE OR REPLACE EVENT make_refer_month
	ON SCHEDULE
		EVERY 1 MONTH
		STARTS '2020-01-01 00:10:00'
	ON COMPLETION PRESERVE
	ENABLE
	COMMENT '조회월 자료 생성'
	DO
		CALL account_report.s_update_refer_month;

Date_Format

• 문법: DATE_FORMAT(date, format[, locale])
• 옵션 참조: https://mariadb.com/kb/en/date_format/

SELECT DATE_FORMAT(CURDATE(), '%Y'); -- 문자형으로 변경됨: 2020
SELECT DATE_FORMAT(CURDATE(), '%Y') - 1; -- 숫자로 표시됨: 2,019
SELECT CAST(DATE_FORMAT(CURDATE(), '%Y') AS INT); -- 숫자로 변경됨: 2,020

STR_TO_DATE

• DATE_FORMAT의 반대
• 문법: STR_TO_DATE(str,format)
• 옵션참조: https://mariadb.com/kb/en/str_to_date/

SELECT STR_TO_DATE('20200111', '%Y%m%d'); -- 2020-01-11
SELECT STR_TO_DATE('20200111', '%Y%m%d %T'); -- 2020-01-11 00:00:00

ROW(), DENSE_ROW(), ROW_NUMBER()

• ROW() 문법

RANK() OVER (
  [ PARTITION BY partition_expression ]
  [ ORDER BY order_list ]
) 

• DENSE_ROW() 문법

DENSE_RANK() OVER (
  [ PARTITION BY partition_expression ]
  [ ORDER BY order_list ]
) 

• ROW_NUMBER() 문법

ROW_NUMBER() OVER (
  [ PARTITION BY partition_expression ]
  [ ORDER BY order_list ]
) 

• 예

CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10));
INSERT INTO student VALUES 
  ('Maths', 60, 'Thulile'),
  ('Maths', 60, 'Pritha'),
  ('Maths', 70, 'Voitto'),
  ('Maths', 55, 'Chun'),
  ('Biology', 60, 'Bilal'),
  ('Biology', 70, 'Roger');
SELECT 
  RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS rank, 
  DENSE_RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS dense_rank, 
  ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark DESC) AS row_num, 
  course, mark, name 
FROM student ORDER BY course, mark DESC;

• 참조: https://mariadb.com/kb/en/rank/

LAG, LEAD

• LAG 문법

LAG (expr[, offset]) OVER ( 
  [ PARTITION BY partition_expression ] 
  < ORDER BY order_list >
)

• LEAD 문법

LEAD (expr[, offset]) OVER ( 
  [ PARTITION BY partition_expression ] 
  [ ORDER BY order_list ]
)

• 참조: https://mariadb.com/kb/en/lag/

Moving Average

• ROWS BETWEEN N PRECEDING(FOLLOWING) AND N PRECEDING(FOLLOWING)

WITH t AS (
    SELECT '201201' yyyymm, 100 amt FROM dual
    UNION ALL SELECT '201202', 200 FROM dual
    UNION ALL SELECT '201203', 300 FROM dual
    UNION ALL SELECT '201204', 400 FROM dual
    UNION ALL SELECT '201205', 500 FROM dual
    UNION ALL SELECT '201206', 600 FROM dual
    UNION ALL SELECT '201207', 700 FROM dual
    UNION ALL SELECT '201208', 800 FROM dual
    UNION ALL SELECT '201209', 900 FROM dual
    UNION ALL SELECT '201210', 100 FROM dual
    UNION ALL SELECT '201211', 200 FROM dual
    UNION ALL SELECT '201212', 300 FROM DUAL
    ORDER BY yyyymm
    )
SELECT 	yyyymm,
	amt,
        (SUM(amt) OVER(ORDER BY yyyymm ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)) AS amt_pre3,
        (SUM(amt) OVER(ORDER BY yyyymm ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING)) AS amt_fol3
  FROM t
;

• 참조
  • https://mariadb.com/kb/en/window-frames/
  • https://mariadb.com/kb/en/window-functions/
  • https://mariadb.com/kb/en/window-functions-overview/

WITH cte_test AS (
    SELECT 'Chun' name, 'SQL' test, 75 score FROM DUAL
    UNION ALL SELECT 'Chun', 'Tuning', 73 FROM DUAL
    UNION ALL SELECT 'Esben', 'SQL', 43 FROM DUAL
    UNION ALL SELECT 'Esben', 'Tuning', 31 FROM DUAL
    UNION ALL SELECT 'Kaolin', 'SQL', 56 FROM DUAL
    UNION ALL SELECT 'Kaolin', 'Tuning', 88 FROM DUAL
    UNION ALL SELECT 'Tatiana', 'SQL', 87
    ORDER BY name
    )
SELECT  NAME, 
        test, 
        score, 
        SUM(a.score) OVER (ORDER BY a.score RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_score 
  FROM cte_test a;

• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
• ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 최근 3개월

DATETIME 형식 자료 조회

• 로그 데이터 검색 등

SET @start_dt = '2020-01-17'; -- 조회 시작일
SET @end_dt = '2020-01-17'; -- 조회 종료일

SELECT * FROM v_logs a 
    WHERE a.time_stamp >= CONVERT(@start_dt, DATETIME) AND
          a.time_stamp < CONVERT(DATE_ADD(@end_dt, INTERVAL 1 DAY), DATETIME);
          
SELECT CONVERT(@start_dt, DATETIME); -- 조회 시작일
SELECT CONVERT(DATE_ADD(@end_dt, INTERVAL 1 DAY), DATETIME); -- 조회 종료일

TIMESTAMP 검색

SELECT * FROM financial_db.slips a
	WHERE a.time_stamp >= CONVERT('2019-11-17', DATETIME) AND
	      a.time_stamp < CONVERT('2019-11-18', DATETIME);