[MySQL] - DateType / window function

Data Control / DateType / window function

Data Control

• -- 빈갑 제외하고 출력하는 방법1
  SELECT 
  	컬럼명1,
  	AVG(컬럼명2)
  	AVG(IF(컬럼명2 <> 'Not', 컬럼명2, null))
  FROM 테이블명
  GROUP BY 1;
  
  -- 빈값 제외하고 출력하는 방법2
  SELECT 1.컬럼명1,
         1.컬럼명2,
         1.컬럼명3,
         1.컬럼명4,
         2.컬럼명5,
         2.컬럼명6,
         2.컬럼명7
  FROM 테이블명1 1 LEFT JOIN 테이블명2 1 ON 1.컬럼명1=2.컬럼명1
  WHERE 2.컬럼명1 IS NOT NULL;
  
  -- COALESCE() 함수는 지정한 표현식들 중에 NULL이 아닌 첫 번째 값을 반환합니다.
  -- NULL 처리 상황
  -- COALESCE(컬럼명1, 컬럼명1이 NULL인 경우 대체할 값)
  -- 컬럼명1~3 중 NULL이 아닌 첫 번째 컬럼을 출력
  -- COALESCE(컬럼명1, 컬럼명2, 컬럼명3)
  SELECT 1.컬럼명1,
         1.컬럼명2,
         1.컬럼명3,
         1.컬럼명4,
         2.컬럼명5,
         2.컬럼명6,
         COALESCE(2.컬럼명6, 20),
         2.컬럼명7
  FROM 테이블명1 1 LEFT JOIN 테이블명2 2 ON 1.컬럼명1=2.컬럼명1
  WHERE 2.컬럼명6 is NULL;
  
  -- 조회한 데이터가 예상치 못한 데이터일때
  SELECT 컬럼명1,
  	   컬럼명2,
  	   CASE
  		   WHEN 컬럼명2 < 15 THEN 15
  		   WHEN 컬럼명2 >= 80 THEN 80
  		   ELSE 컬럼명2
  	   END CASE문 이름
  FROM 테이블명;​

---

DateType

• 날짜 형식의 SQL입니다.
•  

-- 날짜 형식의 SQL
-- yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기
select date(date컬럼) date_type,
       date
from 테이블명;

-- date type에 date_format 이용해보기
-- 년 : (y)2자리 (Y)4자리
-- 월 : M, m
-- 일 : d, e
-- 요일 : w
select 
	date(date컬럼) date_type,
	date_format(date(date컬럼), '%Y') "년",
	date_format(date(date컬럼), '%m') "월",
	date_format(date(date컬럼), '%d') "일",
	date_format(date(date컬럼), '%w') "요일"
from 테이블명;

SELECT 
	date_format(date(date컬럼), '%Y') y,
    date_format(date(date컬럼), '%m') m
FROM 테이블명1 1
INNER JOIN 테이블명2 2
ON 1.컬럼명1 = 2.컬럼명1;

SELECT date_format(date(date컬럼), '%Y') y,
       date_format(date(date컬럼), '%m') m,
       count(*) 작명
FROM 테이블명1 1 
INNER JOIN 테이블명2 2 
ON 1.컬럼명1 = 2.컬럼명1
GROUP BY 1, 2;

select date_format(date(date컬럼), '%Y') y,
       date_format(date(date컬럼), '%m') m,
       date_format(date(date컬럼), '%Y%m') ym,
       count(1) order_count
from 테이블명1 1 inner join 테이블명2 2 on 1.컬럼명1=2.컬럼명1
where date_format(date(date컬럼), '%m')='03'
group by 1, 2, 3
order by 1;

---

 

window function

• -- window function 기본 문법
  -- window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
  -- window function : 기능 명을 사용해줍니다. (sum,avg와 같이 기능명이 있습니다.)
  -- argument : 함수에 따라 작성하거나 생략합니다.
  -- partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사하다고 생각해주시면 됩니다.
  
  -- 특정 기준으로 순위를 매길 때 사용하는 window 함수 rank
  
  SELECT
  	컬럼명1, 
  	컬럼명2, 
  	COUNT(*) order_count
  FROM 테이블명
  GROUP BY 1, 2;
  
  SELECT 
  	컬럼명1,
  	컬럼명2,
  	컬럼명3,
  	RANK() OVER (PARTITION BY 컬럼명1 ORDER BY 컬럼명3 DESC) rn
  FROM
  (
  SELECT 
  	컬럼명1, 
  	컬럼명2, 
  	COUNT(1) 컬럼명3
  FROM 테이블명
  GROUP BY 1, 2
  ) 서브쿼리명;
  
  SELECT
  	컬럼명1,
  	컬럼명2,
  	컬럼명3,
      rn "순위"
  FROM
  (
  SELECT 
  	컬럼명1,
  	컬럼명2,
  	컬럼명3,
  	RANK() OVER (PARTITION BY 컬럼명1 ORDER BY 컬럼명3 DESC) rn
  FROM
  (
  SELECT 
  	컬럼명1, 
  	컬럼명2, 
  	COUNT(*) 컬럼명3
  FROM 테이블명
  GROUP BY 1, 2
  ) 서브쿼리명1
  ) 서브쿼리명2
  WHERE rn<=3
  ORDER BY 1, 4;
  
  -- window 함수 sum
  
  SELECT  
  	컬럼명1, 
  	컬럼명2, 
  	COUNT(1) 컬럼명3
  FROM 테이블명
  GROUP BY 1, 2;
  
  SELECT 컬럼명1,
         컬럼명2,
         컬럼명3,
         sum(컬럼명3) OVER (PARTITION BY 컬럼명1),
         sum(컬럼명3) OVER (PARTITION BY 컬럼명1 ORDER BY 컬럼명3, 컬럼명2) cumulative_sum
  FROM
  (
  SELECT 컬럼명1, 컬럼명2, COUNT(1) 컬럼명3
  FROM 테이블명
  GROUP BY 1, 2
  ) 서브쿼리명
  ORDER BY 컬럼명1, 컬럼명3;