행 비교 분석 함수

SELECT
       name
     , dept
     , sal
     , FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY sal DESC) AS top_name
  FROM emp
 ORDER BY dept, sal DESC;

NAME   | DEPT | SAL  | TOP_NAME
-------+------+------+---------
이부장 | 개발 | 800  | 이부장
박과장 | 개발 | 700  | 이부장
최대리 | 개발 | 600  | 이부장
김대표 | 경영 | 1000 | 김대표
정사원 | 영업 | 500  | 정사원
강사원 | 영업 | 480  | 정사원
(6행)

SELECT
       name
     , dept
     , sal
     , LAST_VALUE(name) OVER (PARTITION BY dept ORDER BY sal DESC) AS last_name
  FROM emp
 ORDER BY dept, sal DESC;

NAME   | DEPT | SAL  | LAST_NAME
-------+------+------+----------
이부장 | 개발 | 800  | 이부장
박과장 | 개발 | 700  | 박과장
최대리 | 개발 | 600  | 최대리
김대표 | 경영 | 1000 | 김대표
정사원 | 영업 | 500  | 정사원
강사원 | 영업 | 480  | 강사원
(6행)

SELECT
       name
     , dept
     , sal
     , LAST_VALUE(name) OVER (PARTITION BY dept ORDER BY sal DESC
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS low_name
  FROM emp
 ORDER BY dept, sal DESC;

NAME   | DEPT | SAL  | LOW_NAME
-------+------+------+---------
이부장 | 개발 | 800  | 최대리
박과장 | 개발 | 700  | 최대리
최대리 | 개발 | 600  | 최대리
김대표 | 경영 | 1000 | 김대표
정사원 | 영업 | 500  | 강사원
강사원 | 영업 | 480  | 강사원
(6행)

SELECT
       name
     , dept
     , sal
     , NTH_VALUE(name, 2) OVER (PARTITION BY dept ORDER BY sal DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_name
  FROM emp
 ORDER BY dept, sal DESC;

NAME   | DEPT | SAL  | SECOND_NAME
-------+------+------+------------
이부장 | 개발 | 800  | 박과장
박과장 | 개발 | 700  | 박과장
최대리 | 개발 | 600  | 박과장
김대표 | 경영 | 1000 | NULL
정사원 | 영업 | 500  | 강사원
강사원 | 영업 | 480  | 강사원
(6행)

-- Oracle · Tibero: 날짜끼리 빼면 일수
SELECT ord_date - LAG(ord_date) OVER (PARTITION BY emp_id ORDER BY ord_date) AS gap_days FROM orders;

-- MySQL: DATEDIFF(뒤, 앞) 이 뒤 - 앞 일수
SELECT DATEDIFF(ord_date, LAG(ord_date) OVER (PARTITION BY emp_id ORDER BY ord_date)) AS gap_days FROM orders;

-- MSSQL: DATEDIFF(단위, 앞, 뒤) 는 경계 횟수
SELECT DATEDIFF(DAY, LAG(ord_date) OVER (PARTITION BY emp_id ORDER BY ord_date), ord_date) AS gap_days FROM orders;

-- Oracle · Tibero, MySQL 8.0, MSSQL 2012 이상 모두 같은 문장
-- monthly CTE 동일
SELECT
       mon
     , sales
     , sales - LAG(sales) OVER (ORDER BY mon) AS diff
  FROM monthly
 ORDER BY mon;

MON | SALES | DIFF
----+-------+-----
1   | 1200  | NULL
2   | 700   | -500
3   | 800   | 100
4   | 600   | -200
(4행)

SELECT
       day_no
     , status
     , prev_status
  FROM (
        SELECT
               day_no
             , status
             , LAG(status) OVER (ORDER BY day_no) AS prev_status
          FROM svc_log
       ) t
 WHERE prev_status IS NULL
    OR prev_status <> status
 ORDER BY day_no;

DAY_NO | STATUS | PREV_STATUS
-------+--------+------------
1      | 정상   | NULL
3      | 장애   | 정상
6      | 정상   | 장애
7      | 장애   | 정상
8      | 정상   | 장애
(5행)

SELECT
       status
     , MIN(day_no) AS from_day
     , MAX(day_no) AS to_day
     , COUNT(*)    AS days
  FROM (
        SELECT
               day_no
             , status
             , day_no - ROW_NUMBER() OVER (PARTITION BY status ORDER BY day_no) AS grp
          FROM svc_log
       ) t
 GROUP BY status, grp
 ORDER BY from_day;

STATUS | FROM_DAY | TO_DAY | DAYS
-------+----------+--------+-----
정상   | 1        | 2      | 2
장애   | 3        | 5      | 3
정상   | 6        | 6      | 1
장애   | 7        | 7      | 1
정상   | 8        | 8      | 1
(5행)