해보자 후회하지말고

1) GROUP BY는 문제가 되질 않는데, 저렇게 값이 여러개 들어간 경우는 어떻게 조회해야 하나 싶었다.

2) IN (조건들) 으로 처음엔풀었는데, 지금 다시 생각해보니 IN에만 꽂혀서 LIKE를 써서 앞뒤로 문자가 더 들어갈 수 있다

는 고려를 안한게 문제였다.

3) 정규식을 사용하면 된다는 팁을 얻어서 REGEXP를 쓴다 해도 다 한글이고 '시트'가 들어가는게 공통점인데 어떻게 정규식으로 만들지..? 했더니 '|' 를 사용하면 OR조건으로 그냥 문자열 자체를 검색 가능했다.

SELECT CAR_TYPE
    , COUNT(CAR_TYPE) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS REGEXP '통풍시트|열선시트|가죽시트'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE
;


-- VER 2.
-- 위의 정규식은 아래 LIKE '%문자열%' OR LIKE '%문자열%'과도 같다
SELECT CAR_TYPE
    , COUNT(CAR_TYPE) AS CARS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE
;

#️⃣정규식 참고한 블로그

프로젝트로 ORACLE ▶ MYSQL 마이그레이션을 하면서 오라클 함수들을 MYSQL버전으로 갈아엎어야 했다.

그래도 대부분 비슷하겠지 했는데, 생각보다 다른 것들이 많아서 구글링을 엄청나게 해야했음.

작업 끝나자마자 내가 헷갈렸던 것, 검색했던것 모두 총망라해서 노션에 정리를 해두었는데, 그 일부를 올려보고자 한다.

딴소리지만 화면만드는것보다 이 작업이 훨씬 재밌었는데, 일단 프레임워크나 다른걸 확인할 필요 없이

• 자유롭게 코드를 짤 수 있음. 단순한데 복잡하고 복잡한데 단순함.
• 기존 코드를 분석해서 어떤 형태의 값을 인풋으로 넣고 어떤 답을 원하는지 로직을 이해하는 재미..!
• 다른 버전으로 짜면서 느껴지는 다이나믹함
• PL/SQL 자체가 새로운 언어를 배우는 느낌

등등 복합적이었음.

처음엔 재귀함수도 어려워했고 안해봤던 PL/SQL 틀 자체를 이해하느라 고심했는데 어느순간부턴 기존코드를 슥 보고도 다이렉트로 MYSQL버전을 쭉쭉 치면서 금방금방 만들어냈다. 그래서 더 뿌듯하고 재미있었다. 이 페이지에 있는건 작업하면서 다 써봤음!

SQL을 확장한 절차적 프로그래밍

• ORACLE : PL/SQL (Procedural Language/SQL)
• MYSQL : SQL/PSM (SQL/Persistent Stored Modules)

이 언어들은 SQL의 모든 기능을 내포하며, 변수, 조건문, 반복문 등 프로그래밍 언어의 기능을 제공하여 데이터베이스 서버에서 복잡하고 효율적인 로직을 작성할 수 있게 해준다.

MYSQL에서 FUNCTION 생성시 오류가 나타난다면?

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

show global variables like log_bin_trust_function_creators; --세팅 조회

SET GLOBAL log_bin_trust_function_creators = 1; -- ON
#SET GLOBAL log_bin_trust_function_creators = 0; -- OFF

• MYSQL 버전이 올라가면서 GLOBAL변수 LOG_BIN_TRUST_FUNCTION_CREATORS OFF→ON 으로 변경해야 메서드 생성이 가능해짐.

일반함수

정규식함수

FUNCTION 생성

FUNCTION에서 := 와 :의 차이 (ORACLE, MYSQL 공통)

• 콜론등호(:=) 는 SELECT문의 결과/ 혹은 값을 변수에 할당하는데 사용
• 등호(=)도 혼용 가능하나 비교연산자로 사용될때와 구분하기위해 변수에 값을 할당할 때에는 := 연산자를 쓰는것이 일반적.

-- 예시 (MYSQL)

CREATE FUNCTION IS_ZERO( -- 0이 입력되었을때 "확인"을 리턴하는 함수
	IN_DATA INT -- INT형 매개변수
)RETURNS VARCHAR(10) -- 문자열은 무조건 크기를 명시해주어야 한다
BEGIN
	DECLARE RESULT VARCHAR(10); -- 변수 선언
    
    IF IN_DATA = 0 THEN -- 비교연산이므로 = 사용
    	SET RESULT := "확인"; -- 값을 대입하므로 := 사용
    ELSE
    	SET RESULT := "0이 아닙니다";
    END IF;

	RETURN RESULT;
END;

-- 참고로 사용할때는 SELECT문에 FUNCTION을 넣어주면 됨!
SELECT IS_ZERO(10); -- "0이 아닙니다"를 반환함

행을 여러개 생성해야 할 때, 오라클은 CONNECT BY 라는 깔끔한 문장이 있다.

이렇게 말이지.

SELECT 
	A.*
FROM
   (
   SELECT 
   	LEVEL 
   FROM 
   	DUAL
   CONNECT BY LEVEL <= 24 -- LEVEL 24까지 생성
   ) A
;

하지만 MARIA DB, MYSQL에서는 재귀함수를 통해서 아래 행들을 부르고 부르고 또 불러야 한다. 언제까지? 내가 설정한 개수까지. WITH RECURSIVE로 시작하는 이 문장은, 처음에는 너무 어색했는데 자꾸 보다 보니까 조금 감이 생겼다.

WITH RECURSIVE A AS(
	SELECT 1 AS LEVEL -- 기본 레벨로 삼는다
	UNION ALL -- UNION ALL로 아래 생기는 것들을 모두 붙일것임
	SELECT 1 + LEVEL  -- 기본 1 + 레벨을 붙이므로 여기는 2부터 시작한다
	FROM A
	WHERE LEVEL < 10 -- 레벨이 9가 될때까지 이므로 총 1~10레벨이 생긴다.
) -- WITH RECURSIVE안에서 A라는것을 정의하는 것과 같음
SELECT
	*
FROM A;
-- 이렇게 하면 위에 있는 오라클 구문과 레벨만 다른 동일 결과가 나오므로, 다른 식으로 더 확장시켜 보겠음.


WITH RECURSIVE A AS(
	SELECT 1 AS LEVEL 
	UNION ALL 
	SELECT 1 + LEVEL
	FROM A
	WHERE LEVEL < 10 
)
SELECT
	R.FIRST
	, R.SECOND
	, R.RESULT
	, FLOOR(R.RESULT * 0.5) DIVIDED -- RESULT로 간단하게 가져다 쓰려고 INNER TABLE을 하나 더 올렸다
FROM 
	(
	SELECT 
		T.N1 FIRST
		, T.N2 SECOND
		, T.N1 + T.N2 RESULT -- 여기서는 바로 RESULT값을 쓰지 못하고 (T.N1+T.N2)*0.5로 해줘야 함. SELECT문은 가로로 붙기 때문
	FROM 
		(
		SELECT
			ROUND((RAND() *2 +1) * 100000) N1
			, ROUND((RAND() *3 +1) * 1000000) N2
		FROM
			A -- WITH RECURSIVE로 만든 행에다가 SELECT 를 붙여서 출력한다.
		) T
	) R
;

번외로,  ROW_NUMBER()를 가지고 다른 테이블에서 행 개수만 따오는 방법도 있다.

이 방법은 ORACLE, MARIADB계열 양쪽 모두에 가능하다.

SELECT
   ADDDATE('2023-01-25', INTERVAL (T.seq - 1) MONTH) AS SALARY_DATE
   , M.ID
FROM
   (
   SELECT
       R.*
   FROM
       (
       SELECT
           ROW_NUMBER() OVER (ORDER BY TEAM_ID) AS seq
       FROM TEAM
       LIMIT 24 -- TEAM 테이블에서 ROW NUMBER만 24까지 가져온다 (동시에 TEAM_ID로 정렬도 함)
       ) R
   ) T
   INNER JOIN MEMBER M ON 1=1 -- MEMBER 테이블과 카티션 조인을 함. 24개 * 멤버테이블 행개수만큼 곱한 결과가 나온다
ORDER BY M.ID, SALARY_DATE
;

* 이때 ROW_NUMBER는 행 개수 (정확히는 각 행의 순번)만 가져다 쓰는거고, TEAM 테이블 데이터를 가져오는게 아니다.

: (상위부속질의의) 튜플을 이용하여 하위 부속질의를 계산하는 경우.

: 두개의 테이블은 서로 관련되어있다

Q. 출판사별로 출판사의 평균도서가격보다 비싼 도서를 구하시오

이때 구해야 하는 값은

① 출판사별 평균도서가격

② ~ 보다 비싼 도서의 이름

먼저 각각의 sql문을 작성하면 이렇게 된다.

-- 1. 출판사별 평균도서가격
SELECT 
	B1.PUBLISHER 
	, FLOOR(AVG(B1.PRICE))
FROM 
	BOOK B1
GROUP BY
	B1.PUBLISHER 


-- 2. 보다 비싼 도서의 이름
SELECT 	
	B2.NAME
FROM
	BOOK B2
WHERE
	B2.PRICE > (평균가격)
;

(*같은 테이블을 사용하므로 구분을 위해 B1, B2로 나누어 주었다)

이때 상관부속질의는 B2의 튜플 (행) 을 가져다가 B1의 조건에서 조회하게 되므로, 우리는 두 테이블을 엮어줘야 하는 조건을 만들게 되는데, 이때 일치 조건은 일반적으로 생각하던 BOOK_ID가 아니라 B.PUBLISHER에 해당한다. '같은 출판사'라는 조건이 붙었기 때문.

WHERE B1.PUBLISHER = B2.PUBLISHER

따라서 GROUP BY는 사라지고 아래와 같은 루트를 타게 된다.

-- 상관부속질의는 상관부속질의의 튜플을 이용하여 하위 부속질의를 계산한다.(서로 관련됨)
SELECT 
	B2.BOOK_NAME -- 4) 이름을 출력한다
FROM 
	BOOK B2 -- 1) 튜플에 해당하는 출판사를 하위 테이블로 가져간다 
WHERE 
	B2.PRICE >( -- 3) 튜플의 가격을 비교한다
				SELECT -- 2) 일치하는 출판사에서 평균값을 얻는다
					FLOOR(AVG(B1.PRICE))
				FROM 
					BOOK B1
				WHERE
					B1.PUBLISHER = B2.PUBLISHER 
				)
;

이때, 하위 서브쿼리에서 실수했던 부분은 출판사명을 그대로 남겨놨던 부분인데, 서브쿼리의 SELECT절에 '여러개의 열(COLUMN)'을 사용하는 경우,서브쿼리에서 반환되는 값들은 하나의 단일값을 가져야 한다. 하지만 여러 행이 출력되지!

(* SQL Error [1241] [21000]: Operand should contain 1 column(s))

따라서 내가 눈으로 PUBLISHER별 평균가격을 확인하고 싶은 마음과는 다르게 열 정리를 해주어야 한다.

SELECT 
	B2.BOOK_NAME
FROM 
	BOOK B2다 
WHERE 
	B2.PRICE >(
				SELECT 
					B2.PUBLISHER 
					, FLOOR(AVG(B1.PRICE))
				FROM 
					BOOK B1
				WHERE
					B1.PUBLISHER = B2.PUBLISHER 
				)
;

#️⃣MYSQL

SELECT 
    MCDP_CD AS '진료과 코드'
    , COUNT(MCDP_CD) AS '5월예약건수'
FROM 
    APPOINTMENT
WHERE 
    APNT_YMD BETWEEN '2022-05-01' AND '2022-05-31' -- 개인적으론 날짜 구간을 이렇게 구하는건 별로라 생각한다
GROUP BY 
    MCDP_CD
ORDER BY 
    5월예약건수 ASC, MCDP_CD ASC
;

#️⃣ORACLE

SELECT MCDP_CD AS "진료과코드", 
       COUNT(*) AS "5월예약건수"
FROM 
	APPOINTMENT
WHERE 
	TO_CHAR(APNT_YMD, 'YYYY-MM') = '2022-05' -- oracle 포멧함수를 사용해봄!
GROUP 
	BY MCDP_CD
ORDER 
	BY COUNT(*), MCDP_CD ASC
;

출근전에 하나씩 풀고가기!