본문 바로가기

Dev. Database

 (58)
LAG & LEAD 함수 - 이전, 이후 값 참조 -- 입사 형태 중 정직원으로 입사한 사원의 정보와 이전, 이후에 입사한 사원을 같이 표기해라 SELECT SABUN ,ENG_NAME ,JOIN_DAY ,JOIN_GBN_CODE ,LEAD(ENG_NAME,1) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN) AS NEXT_MEM ,LAG(ENG_NAME,1) OVER(PARTITION BY JOIN_GBN_CODE ORDER BY SABUN) AS PREV_MEM FROM INSA WHERE JOIN_GBN_CODE = 'RGL' 이전 값, 이후 값을 조회하는 함수
REGEXP_LIKE( 다중 LIKE 검색 ) http://blog.naver.com/PostView.nhn?blogId=mk1126sj&logNo=220985887670&parentCategoryNo=&categoryNo=12&viewDate=&isShowPopularPosts=true&from=search
Oracle data dictionary를 이용한 테이블의 컬럼 정의 SQL SELECT a.column_name, c.comments AS column_name_ko, CASE WHEN data_type = 'VARCHAR2' THEN 'VC2' WHEN data_type = 'NUMBER' THEN 'NUM' ELSE data_type END || '(' || data_length || ')' AS TYPE, b.key, nullable, data_default AS defalut, c.comments FROM cols a LEFT OUTER JOIN ( SELECT table_name, COLUMN_NAME, 'PK' AS KEY FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM ALL_C..
pivot table - 행렬변환 SELECT COUNTRY , SUM(CASE WHEN activity='Adventure park' THEN num_of_places ELSE 0 END) as adventure_park , SUM(CASE WHEN activity='Golf' THEN num_of_places ELSE 0 END) as golf , SUM(CASE WHEN activity='River cruise' THEN num_of_places ELSE 0 END) as River_cruise , SUM(CASE WHEN activity='Kart racing' THEN num_of_places ELSE 0 END) as Kart_racing FROM ( WITH pivot_table AS ( SELECT 1 AS id, 'Fra..
증감률(성장률) 계산하기 - LAG OVER 참고 ) https://yahwang.github.io/posts/74 SELECT A.MONTH , A.TOTAL_SALES , CASE WHEN A.GROWTH_RATE 0 THEN ROUND(A.GROWTH_RATE, 2) || '%' ELSE '-' END AS GR_RATE FROM ( WITH MONTH_SUMMARY AS ( SELECT EXTRACT(MONTH FROM DT) AS MONTH, SUM(SALES) AS TOTAL_SALES FROM SAMPLE GROUP BY EXTRACT(MONTH FROM DT) ) SELECT MONTH, TOTAL_SALES, 100 * (TOTAL_SALES - LAG(TOTAL_SALES,1) OVER (ORDER BY MONTH)) / LAG(..
구분자로 나누어 행,열 바꾸기 [출처] 구루비 [퀴즈] 구분자로 나누어 행,열 바꾸기 이번 퀴즈로 배워보는 SQL 시간에는 구분자로 데이터를 분할하고, 행과 열을 바꾸어 출력하는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리.. www.gurubee.net -- 구분자로 나누어 행, 열 바꾸기 WITH TEST AS ( SELECT 1 NO, '1:10|2:11|3:12|4:15' V FROM DUAL UNION ALL SELECT 2, '1:17|3:15|4:25' FROM DUAL UNION ALL SELECT 3, '2:11|4:15' FROM DUAL UNION ALL SELECT 4, '1:10|2:21|4:19' FROM DUAL ) SELECT * FROM TEST ; WITH TEST AS ( SELECT 1 ..
[mysql] 연령대별 집계(통계) 구하기 SELECT TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) DIV 10 * 10 BIRTH , COUNT(*) CNT FROM EMPLOYEES E , SALARIES S WHERE S.EMP_NO=E.EMP_NO AND TO_DATE='9999-01-01' GROUP BY TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) DIV 10 ;
행열 변환2 (가로 -> 세로) SELECT MM , SUM(CASE WHEN YR=1998 THEN CNT ELSE 0 END) AS BEFOREYEAR , SUM(CASE WHEN YR=1999 THEN CNT ELSE 0 END) AS THISYEAR FROM (SELECT YEAR(HIRE_DATE) YR , MONTH(HIRE_DATE) AS MM , COUNT(*) AS CNT FROM EMPLOYEES WHERE YEAR(HIRE_DATE) IN ('1998', '1999') GROUP BY YEAR(HIRE_DATE), MONTH(HIRE_DATE) ) DS GROUP BY MM

티스토리툴바