[MySQL]SQL 템플릿

SQL 템플릿(MySQL)

너무 기초적인 문법은 생략

SQL 논리적 실행 순서 : from -> where -> group by -> having -> select -> order by

MySQL 의 경우 select의 별칭을 group by, having 에서 사용할 수 있게 지원 중! (Oracle, SQL Server는 사용 불가)

  • having 절은 그룹을 나타내는 결과 집합의 행에 조건이 적용, where 절은 그룹화 전을 필터링

    • group by 절의 결과가 b.user_id, b.nickname, total_sales 이고 b.city 컬럼이 b테이블에 존재한다고 가정했을때 having 절에 b.city를 사용하면 “Unknown column ‘b.city’ in ‘having clause” 에러가 발생한다.
    • 특히 그룹화 전에 여러행의 데이터를 가지고 있었다면, 그룹화 후에는 하나의 행 데이터를 가지기 때문에 having 절에서는 하나의 행 데이터를 비교해야지 여러행의 데이터를 비교할 목적이면 원하는 결과를 얻을 수 없다.
      • where 절에서 여러행의 데이터를 비교하고 서브쿼리로 그룹화 하여 하나의 행 데이터를 비교하는 형태로 해결하도록 하자.
      • having 절이나 select 절에서 집계함수(max, sum 등)는 당연히 전체 데이터를 조회해서 구하고, 이 결과가 그룹마다 하나의 행 데이터로 나타나는 것이다.

  • 인라인뷰(from절 서브쿼리)에 미정의 된 컬럼은 메인쿼리에서 사용 불가

  • with절에서 SELECT *를 사용할 경우에는 중복된 컬럼명을 명시적으로 처리하지 않으므로 직접 명시를 해줘야 join으로 인한 중복 컬럼 에러를 마주치지 않을 수 있다.

  • 최근일 = max(날짜) 또는 order by 후 limit 로 상위 n개 가져오기

    • 최상위값 구하라 하면 max, limit 둘다 생각해보자 + 윈도우함수(rank())도 있다!

    • (select a.member_id from member_profile a join rest_review b on (a.member_id=b.member_id) 
group by a.member_id order by count(*) desc limit 1) 
    
WITH FAVRK AS(
SELECT FOODTYPE,RESTID,RESTNAME,FAVORITES,
RANK() OVER(PARTITION BY FOODTYPE ORDER BY FAVORITES DESC) AS rk
FROM RESTINFO)

  • 중복검사(재구매) having절 count(*)>1 활용, 값의 유무를 확인할 때는 어차피 group by에서 null은 무시하기 때문에 count(*)>0 이럴 필요는 없다.
    추가로 count(distinct 컬럼) 이런식으로 select절에서 바로 원하는 컬럼 중복 제거 가능

  • 별칭 사용에 주의할 예시 : concat(round(sum(d_between_dist),1),'km') as a 라고 해서 a 기준으로 정렬을 하면 문자(‘km’)가 섞여 있어서 원하는 결과를 얻을 수 없고 round() 부분인 숫자 부분만 정렬 해줘야 한다.

  • ORDER BY 를 사용하는 경우에 MYSQL에서는 따옴표로 감싸면 하나의 컬럼으로 처리하기에 따옴표를 지워서 열의 별칭이나 실제 이름을 작성해야 한다.
    즉, SELECT mcdp_cd as '진료과코드'... 일때 order by '진료과코드' 가 아닌 order by 진료과코드 로 작성해야 한다.

  • 참고)

    • 동일한 car_id가 여러개라서 join 하면 중복으로 여러개 생겨서, 대여 가능 여부(중복 때문에 end_date도 여러개) 판단이 불가능할 때 해결은?

      • 이를 해결하는 핵심코드 : group by car_id having max(end_date)<'2022-11-01'
      • 그룹핑으로 car_id 중복을 해결하고, max함수로 end_date 중복을 해결했다.

    • max(case when START_DATE<=DATE(20221016) && END_DATE >= Date(20221016) then "대여중" else "대여 가능" end) 처럼 case when 에도 집계함수 사용해 볼 수 있다.

      • “대여중, 대여 가능” 중에서 “대여중”이 더 큰 값으로 출력 해준다.

        • 필터 결과가 여러개면 “대여중”, “대여가능” 둘다 성립할 수 있는데 이를 해결해준다.

      • 또 다른 방법으로는 in 쿼리문 을 활용해도 된다.

      • (case 
 when car_id in (select a.car_id from car_rental_company_rental_history a where a.start_date <= '2022-10-16' and a.end_date >= '2022-10-16') then '대여중' 
 else '대여 가능' 
 end)

    • distinct 사용 자세히

      • count(distinct 컬럼) 식으로 집계함수 안에 컬럼에 사용 가능 -> 중복제거+null무시

        • distinct 는 중복 만 제거임! count(컬럼명)은 null 제외 해주고!

      • select distinct 컬럼1, 컬럼2, 컬럼3 처럼 사용이 기본 사용방식
        그렇다면 select * distinct 와 위 쿼리는 동일하게 동작하는가??

        • 정답은 NO

        • 테이블이 컬럼1~4 까지 있다면 두번째 쿼리는 컬럼1~4 까지 전부 확인해서 중복 행이 있을때 삭제하고,

        • 첫번째 쿼리는 컬럼1~3 까지만 확인해서 중복 행이 있을때 삭제한다.

        • -- 1:N join 이다 보니 중복 존재 -> distinct
-- in 쿼리문 사용 방식
select distinct b.user_id, b.nickname
from used_goods_board a join used_goods_user b on (a.writer_id=b.user_id) 
where b.user_id in (select writer_id from used_goods_board group by writer_id having count(*)>=3) 
order by b.user_id desc;

      • group by 로 join 중복 해결도 가능 -> group by는 null무시+중복제거 특징

        • -- group by 로 join 중복 무시 및 having 에 count 써서 해결한 방식
select b.user_id, b.nickname
from used_goods_board a join used_goods_user b on (a.writer_id=b.user_id) 
group by b.user_id having count(*)>=3 
order by b.user_id desc;


서브쿼리 3가지 종류 : 스칼라 서브쿼리, 인라인뷰, 중첩된 서브쿼리 (select, from, where)

  • 특히, 스칼라는 1행 1열 값을 취급하다 보니 집계함수와 콤비가 좋다.

  • 인라인뷰는 또다른 하나의 테이블을 만들어 쓴다고 생각해도 좋다.


서브쿼리방식을 구분하자면 2가지 : 동작 방식, 반환 방식

  • 동작 방식 서브쿼리 : 비연관(값) vs 연관(조건)
    • 값 예시 : (select max(num) from test)
    • 조건 예시(a는 메인쿼리 from절 테이블 별칭 가정) : (select id from test where a.id=id)
  • 반환 방식 서브쿼리 : 단일행 vs 다중행 vs 다중컬럼

    • 단일행은 진짜 하나의 튜플 결과가 나오는 것 -> 단일행 내장함수 사용 (젤 간단한거? =)

    • 다중행은 여러 튜플 결과가 나오는 것 -> 다중행 내장함수 사용 (in, any, exists 등)

      • in(1, 2, 3) 같은건 직관적이라 예상하기 쉽다. 그러나 in(서브쿼리) 는 어렵다.

      • 따라서 in쿼리로 나온 결과 리스트를 꼭 예상해서 대입해봐야 직관적이라 이해하기 쉽다.

      • in, exists 차이

        • where exists (select animal_id from animal_outs)
        • where animal_id in (select animal_id from animal_outs)

      • not in, not exists 란: 모든 값들과 일치하지 않는지를 체크

      • any를 exists 로 변환은? 둘이 성격 비슷함

      • when 0 < any (select skill_code & code from skillcodes where category = 'front end') then 'C'
when exists (select 1 from skillcodes where skill_code & code and category = 'front end') then 'C'
      • any의 select 절의 내용이 exists의 where절로 이동함.

    • 다중컬럼은 여러 컬럼으로 결과가 나오는 것

      • 예시 : ...where (a.id, a.type) in (select id, type from test ...)
      • 다중 컬럼을 연관 관계로 풀이하는 생각도 해보자. 아래 예시 참고 (food_type이 차이)
        • where (food_type, favorites) in (select food_type, max(favorites) as favorites from rest_info group by food_type)
        • where favorites = (select max(favorites) from rest_info where a.food_type=food_type)

기억할 내장함수 : 단일행 vs 다중행

  • 오라클의 to_number, to_char -> MySQL의 convert, cast 함수
    • convert 와 cast 는 오라클의 to_number, to_char 처럼 타입 변환을 지원한다.
      • 정수는 SIGNED(USIGNED), 문자는 CHAR, 날짜는 DATE 를 기입한다.
      • 사용법이 차이: convert(컬럼, char) <-> cast(컬럼 as char)

  • 단일행 : 문자형, 숫자형, 날짜형, 변환형, null

    • 문자 비교 : 컬럼 like '김%'

      • 문자열 붙이기 : concat(컬럼, 'cm')
        • 기존 + 연산으로는 문자+숫자 인 경우 에러가 발생! concat을 추천
      • 부분 문자열 추출: SUBSTRING(string, position, length)

    • 첫번째 자리에서 반올림 : round(컬럼, 0)

    • null 비교+함수 : is null or is not null -> ‘=’ 사용 불가

      • null 연산 결과는 null -> ex: 10000-null = null
      • 가장 기본인 ifnull 함수: ifnull(length,10) 은 null이면 10, null아니면 length 반환

    • bit 연산 : 9 & 8 => 1001 & 1000 의 결과는 1000(=8) 이다. (&는 and 비트 연산자)

      • select count(*) as count from ecoli_data
where genotype & 2 != 2 and genotype & 5
        • POINT: 이처럼 컬럼 타입이 integer(숫자)면 “숫자로 bit연산” 해야 함!
          2진수 타입(binary)면 “2진수로 bit연산” 하고!
        • POINT: 컬럼명 & 0101 = 0001; 과 컬럼명 & 0101 은 차이가 존재함.
          • 첫 번째는 0001(=2) 를 포함하기만 하면 됨. ex: 1111, 1101 … 등
          • 두 번째는 0100(=4) or 0001(=1) or 0101(=5) 를(3개 중 하나라도!) 포함하기만 하면 됨. ex: 1111, 1101, 0110 … 등

      • 보통 하나만 맞으면 되는 조건들이 많아서 비교할 기준이 되는 비트에는 sum(code) 이런식으로 sum 함수로 비트 값을 전부 더해서 비교에 사용하는 아이디어도 있다.

      • 서브쿼리를 활용해도 좋다. 0 < any (select skill_code & a.code from skillcodes a where category = 'front end') 참고로 category가 front end 인 튜플이 여러개라 가정

    • 날짜형 비교 : year, month, day, date, date_format, datediff

      • date 함수는 타입을 date 로 바꾼다 보면 됨.
        date 함수 없이 ‘2022-10-16’ 이런 형태로 바로 사용해도 잘 동작함.
        date 타입이면?? date 함수 없어도 date처럼 바로 사용하면 되는것 (바꿀 필요 없음)

        • timestamp 타입은 date 타입이랑 다르므로 date로 바꿔서 비교 필수

      • AND TIME >= date("2021-01-01") AND TIME <= date("2021-12-31")

        • between 으로 주로 사용 -> 아래는 created_date 컬럼이 date 타입이라 가정
        • created_date between '2022-10-01' and '2022-10-31'
        • 주의 : 단 9월은 30일 까지 있는데 31일로 표기 할 시 제대로 동작 안함.

      • date_format 사용 방식? -> 소문자 대문자 차이

        • date_format(created_date, '%Y-%m-%d') -> 2022-02-01
        • date_format(created_date, '%Y-%M-%D') -> 2022-October-2nd
        • date_format 를 원래 ‘Y-%m-%d’ 로 보통 쓰지만 아래도 기억해두자.
          • %c : 월 을 한자리 수로 -> 단, 문자 타입
          • %e : 일 수를 한자리 수로
          • %y : 년 도를 두자리 수로 -> ex : 2024 라면 24

        datediff(end_date,start_date) ?? -> 날짜 차이 계산 함수

        • (case when datediff(end_date,start_date)+1 >= 30 then '장기 대여' else '단기 대여' end) as rent_type
        • 중요 : 날짜 차이에 +1 까지 해줘야 정확한 대여일이 구해 진다.
          • 2022-01-01 ~ 2022-01-02 차이값을 1로 나타내기 때문이다.
          • 보통 우리가 보는 기준으로는 차이값이 2가 옳다.

    • 조건 함수 : case when ... then ... when ... then 반복... else ... end

    • 반올림, 버림, 올림, 자름 함수 : round, floor, ceil, truncate
      round, floor, ceil 정도만 알아두자.

      • 반올림: ROUND(value, decimals)
      • 버림: FLOOR(value)
      • 올림: CEIL(value) 또는 CEILING(value)
      • 자르기: TRUNCATE(value, decimals)

  • 다중행 : 집계 함수, 그룹 함수, 윈도우 함수 -> “데이터 분석 함수” 라고도 함

    • 집계함수 : count, sum, max, avg -> 집계함수는 null 자동 제외
      • 단, count(*) 는 null 값을 포함
      • count(column) 은 null 값 자동 제외

    • 그룹함수 : rollup, cube, grouping sets

    • 윈도우 함수 : select window_fuction(Arguments) over ([partition by order by windoing])

      • select 절 순서 특성상 where절 필터링 이후 데이터 결과에 적용한다.

      • partition by 는 group by 역할!

      • order by를 하면 해당 컬럼만 순서가 바뀌는게 아니라 당연히 동일 튜플 컬럼들도 다 영향을 받는다.

      • 예시1 : select rank() over (partition by job order by sal desc) from emp

        • 다양한 함수들이 많은데 rank() 함수는 자주 사용 된다.

        • # 랭킹 1등 출력
select a.name
from (
    select name, rank() over(order by datetime) as rn
    from animal_ins
) a
where a.rn = 1

      • 예시2 : select percent_rank() over (order by size_of_colony desc)

        • -- 사이즈 내림차순 했을 때의 랭크(%)를 분류하라.
with perTable as(
select id, percent_rank() over (order by size_of_colony desc) as per 
from ecoli_data
)
        
select a.id, 
(case when per <= 0.25 then 'CRITICAL' 
when per <= 0.5 then 'HIGH' 
when per <= 0.75 then 'MEDIUM' 
else 'LOW' end) as colony_name 
from ecoli_data a join perTable b on(a.id=b.id) 
order by a.id;


일반 집합 연산자 : union, intersect, except, cross join -> 합집합, 교집합, 차집합, product

  • 합집합(union, union all) 정도만 기억 -> union all 은 중복도 포함
    • ex) ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성하라.

순수 관계 연산자 : where, select, 다양한 join

  • 다양한 join -> (inner) join on 과 (outer) join on 정도만 기억

    • inner join 예시 (공통 키인 튜플만! 즉, true 행만)
      • 컬럼1 a join 컬럼2 b join on (a.key=b.key)

    • outer join 예시 (매칭 안되는 튜플도 다 포함! 즉, true 아닌 행도 포함)

      • 컬럼1 a join 컬럼2 b left join on (a.key=b.key) : 왼쪽 테이블 기준

      • 컬럼1 a join 컬럼2 b right join on (a.key=b.key) : 오른쪽 테이블 기준

      • 컬럼1 a join 컬럼2 b full join on (a.key=b.key)

      • outer join 활용 예시

        -- parent_id 에 null 이 있다고 가정했을때 inner join은 null을 무시하므로 a.id를 전부 살릴 수 없다.
-- 반면 outer join은 null을 무시하지 않기에 a.id를 전부 살릴 수 있다.
-- 단, count(*)는 null을 무시하지 않아서 null도 카운트 하고,
-- count(b.parent_id)같이 컬럼있으면 해당 컬럼의 null은 무시해서 카운트 한다.
select a.id, count(b.parent_id) as child_count from ecoli_data a left join ecoli_data b on (a.id = b.parent_id) 
group by a.id 
order by a.id;

중요한 점은 join 이 되었을 때 어떤 식으로 테이블 구조를 이루는지 머리 속에 그려봐야 한다.

  • 그 전에 join관계 관련해서 언급하자면, DB관점은 1:N, N:1 이든 데이터 중복은 발생한다. 1쪽이 N만큼 생성되는건 조인이라면 당연하기 때문이다. 예로 아래 코드를 봐라. 똑같은 결과를 나타낸다. 그저 관점을 양방향으로 봤을 뿐이다.

    • # 게시물:파일 = 1:N 관계
# N:1 로 조인 -> 예상 결과는 당연히 b쪽이 중복 데이터 생성
select concat('/home/grep/src/',a.board_id,'/',a.file_id,a.file_name,a.file_ext) as file_path
from used_goods_file a join used_goods_board b on (a.board_id=b.board_id)
where b.views = (select max(views) as views from used_goods_board)
order by a.file_id desc
    
# 1:N 로 조인 -> 예상 결과는 당연히 a쪽이 중복 데이터 생성
select concat('/home/grep/src/',b.board_id,'/',b.file_id,b.file_name,b.file_ext) as file_path
from used_goods_board a join used_goods_file b on (a.board_id=b.board_id)
where a.views = (select max(views) as views from used_goods_board)
order by b.file_id desc
    • 이렇게 예상 테이블 구조를 꼭 잘 판단하길 ! 어느쪽이 중복(N)인지를 알아야 연산자도 올바르게 쓰고(‘=’,’in’ 등), 중복을 제거한다던지(group by, distinct등)를 판단한다.

  • 추가) 그럼 N:N이 아니면 항상 튜플은 중복이 아니니까 걱정안해도 되자나? 왜 distinct를 쓸 때가 있었을까?

    • 만약 1:N에서 order쪽 컬럼만 사용하면?? 중복 데이터니까 distinct나 group by로 해결한 것!!

    • 1:N, N:1 조인 테이블 모습을 아예 참고해보자.

    • 1:N (Order->OrderItem)

      • ORDER_ID ORDER_DATE STATUS DELIVERY_ID MEMBER_ID ORDER_ITEM_ID COUNT ORDER_PRICE ITEM_ID
        4 2022-06-01 16:57:23 ORDER 5 1 6 1 10000 2
        4 2022-06-01 16:57:23 ORDER 5 1 7 2 20000 3
        11 2022-06-01 16:57:23 ORDER 12 8 13 3 20000 9
        11 2022-06-01 16:57:23 ORDER 12 8 14 4 40000 10

    • N:1 관계 (OrderItem->Order)

      • ORDER_ITEM_ID COUNT ORDER_PRICE ITEM_ID ORDER_ID ORDER_DATE STATUS DELIVERY_ID MEMBER_ID
        6 1 10000 2 4 2022-06-01 16:57:23 ORDER 5 1
        7 2 20000 3 4 2022-06-01 16:57:23 ORDER 5 1
        13 3 20000 9 11 2022-06-01 16:57:23 ORDER 12 8
        14 4 40000 10 11 2022-06-01 16:57:23 ORDER 12 8
  • animal_id 를 기준으로 inner join한 모습이며, a테이블과 b테이블이 합쳐서 컬럼이 추가된 모습을 알 수 있다. (자연조인이나 using 같은 걸 사용하면 animal_id 컬럼 두개있는 중복을 한개로 바꾼다던지 이런 차이가 있긴 함)

  • image

  • 위 처럼 1:1 로 정확히 매핑 되는것이 아닌 1:N 구조로 매핑되는 join 구조를 보기위해 animal_type 을 기준으로 inner join 해본다. (굉장히 많은 매핑으로 원하던 결과 1 이 아닌 N으로 출력된다.)
    임의로 만든 test 테이블은 Cat, Dog 으로 2개의 튜플을 가졌었다. 그러나 join 의 결과는 2개의 튜플이 아닌 N개의 튜플이 되었다.

  • image

  • null 신경 안 써도 되면 (outer) left join 으로 왼쪽 테이블(주테이블)을 기준으로 출력해서 좀 더 깔끔한 출력을 볼 수도 있다.
  • image


계층형 질의 : self join 인데, CTE의 재귀 성질을 활용하기도 한다.

  • CTE(Common Table Expression) 형태

    • WITH [RECURSIVE] TABLE명 AS (
    SELECT - # 비반복문. 무조건 필수
    [UNION ALL] # RECURSIVE 사용 시 필수. 다음에 이어붙어야 할 때 사용
    SELECT - 
    [WHERE -] # RECURSIVE 사용 시 필수. 정지 조건 필요할 때 사용
)
-- ex : 0~23 추가
with recursive testCTE as (
    select 0 as hour 
    union all
    select hour+1 from testCTE 
    where hour < 23
)
    
-- right join 으로 해당 0~23 값을 활용할 수도 있다.
select b.hour, count(a.animal_id) as count 
from animal_outs a right join testCTE b on (hour(a.datetime)=b.hour) 
group by hour order by hour;

    • with : CTE(common table expression)을 생성하는 문법

    • union 은 중복제거 제공, union all 은 중복제거X

  • 실제 사용 심화 예시: self join

    • -- self join -> recursive 풀이
WITH RECURSIVE CTE
AS
(
    SELECT ID, PARENT_ID, 1 AS DEPTH
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT CHILD.ID, CHILD.PARENT_ID, CTE.DEPTH + 1
    FROM ECOLI_DATA CHILD
    INNER JOIN CTE
        ON CHILD.PARENT_ID = CTE.ID
)
SELECT ID FROM CTE WHERE DEPTH = 3;
    • depth 만큼 self join -> 3세대(depth=3) id 구하는 모습


참고

  • between으로 0 and 0.5 이런식으로 하면 0.51 범위는 포함하지 않기에 <=0.5 를 활용하자.
    • ’<=’ 의 경우 정밀도 문제로 인해 약간 큰 값을 포함할 수 있어서 가능한 것.
    • 다만, 확실히 하려면 반올림을 해서 비교하는게 제일 안전하다. 그럼 between도 충분히 가능하다.

개인적인. 문제풀 때. 생각 흐름.

  • 테이블 관계 먼저 생각. 1:N인지? 1:1인지?
    • 조인이든 뭐든 데이터 합치게 되면 “중복 문제” 고려는 필수라서
      단, 1:N, N:1 조인은 1쪽이 중복 데이터 생성되는건 자명하단걸 잘 이해하고 시작.
      • 중복인지 검증하기 애매하면 해당 컬럼에 distinct 적용해서 출력해보기
      • group by, distinct 둘다 중복 제거 효과적 (group by는 집계함수 처럼 null 무시)
  • 조인 했을 때 예상 테이블을 꼭 생각하기.
    • 인라인뷰, 중첩된 서브쿼리(where), 스칼라 서브쿼리, with절 고려 항상!
    • 특히 where절 서브쿼리는 in, =, exists, not in 등 연산자 뭐 쓸지 잘 구분
      • in절은 꼭 in 데이터 리스트를 대입해서 생각. (직관적. 서브쿼리만 보면 헷갈림)

댓글남기기

참고