본문 바로가기

Oracle/SQL

 (98)
SQL 데이터 분석함수 - 전행과, 다음행 출력 Lag, Lead(컬럼, 1 or 2..)over (partition by ~ order by ~ desc/asc ) LAG, LEAD ( 전행과, 다음행 출력하기) → 은행에서 통장 잔고 확일할때 LAG ,LEAD 의 원리로 SQL 쿼리를 입력한다. LAG : 전행 → LAG( 컬럼명, 1 OR 2 OR 3 ...) LEAD : 다음행 → LEAD (컬럼명 , 1 OR 2 OR 3...) → 1 은 바로 전행 2는 전전행 3은 전전전행 .... 예제 46 : 이름 , 입사일, 바로전에 입사한 사원과의간격일 출력 select ename, hiredate, hiredate - lag(hiredate,1) over (order by hiredate asc) as days from emp; 문제 : 이름, 생일 , 생일과의 간격일을 출력 select ename, birth, birth - lag(birth , 1) over ..
SQL 데이터 분석함수 - 데이터 가로출력 Listagg (컬럼명,',') within group(order by~ desc/asc ) LISTAGG(가로로 출력하기) → group by 절을 반드시 써야한다. 예제 : 부서번호를 출력, 해당부서번호별로 속한 사원들의 이름을 가로로 출력 select deptno, listagg(ename, ',') within group (order by ename asc) from emp group by deptno; 분석함수 ( ) 에 괄호에 무언가 채워넣어져 있다면 within group 을 사용해야한다. 이름과 이름사이는 ',' , 콤마로 구분하겠다. 어떻게 가로로 출력 하는가?? → within group ( order by 컬럼명 asc or desc)를 쓰지 않고 listagg 만 써도 데이터 결과는 나오지만, 출력되는 결과를 정렬해서 가로로 나오게 하고싶다면 within group ( or..
SQL 데이터 분석함수 - 상위비율구하기 Cume_dist () over ( partition by ~ order by ~ desc/asc) CUME_DIST(순위의 비율 출력하기) → 상위 몇 % ? 예제 : 이름, 월급, 월급순위, 월급순위의비율을 출력 select ename, sal, dense_rank() over (order by sal desc)as 순위 , cume_dist () over (order by sal desc)as 순위비율 from emp; → emp table 기준으로 1등은 1/14 로 계산된 비율 2등은 3/14 로 계산된비율(2등이 2명이기 때문에 )입니다. 문제 179 : 클래스타입,이름, 나이, 그옆에 클래스 타입별로 각각 나이가 높은 순서대로 자기의 나이에 대한 순위의 비율이 출력 select class_type, ename, age, address, cume_dist() over ( partition b..
SQL 데이터 분석함수 - 등급 출력 Ntile( 숫자) over( order by ~ desc/asc ) 데이터 분석함수는 그룹함수가 아닌 별도의 함수이다. NTILE(등급 출력하기) → ntile ( 원하는 등급 갯수) over ( order by 컬럼 desc / asc) as 등급 예제 : 직업이 'ANALYST, MANAGER, CLERK 인 사원들의 이름과 직업과 월급과 등급을 출력하는데 등급을 4등급으로 나눠서 출력하세요 select ename, job, sal, ntile(4) over( order by sal desc) 등급 from emp where job in ('ANALYST','MANAGER','CLERK'); 0
SQL 데이터 분석함수 - 순위출력 Rank () over ( partition by /order by ~ desc/asc), Dense_Rank () 데이터 분석함수 → 그룹함수 가 아닌 별도의 함수 이다. RANK (순위) (RANK () OVER (partition by order by desc or asc ) → OVER = 확장하다. →즉 OVER 다음에 나오는 괄호안의 내용으로 확장해서 RANK를 출력하겠다는 뜻. 예제 : 이름과 월급을 출력하고 월급이 높은 순으로 순위를 출력 select ename, sal, rank() over (order by sal desc) as 순위 from emp; → 설명 () 의 내용? 괄호 안의 내용을 확장해서 순위를 출력해라. 예제 : 직업이 애널리스트, 매니저 인 사원들의 이름 직업 월급과 월급에 대한 순위를 출력 select ename, job, sal, rank() over (order by sal ..
SQL 다중행 함수 - 그룹함수 (Count) 건수 COUNT (건수를 세는 함수) count 또한 그룹함수이기때문에 null 값을 무시하고 계산해 준다. → null 값이 없는 컬럼을 기준으로 count 를 해주어야한다. → 만약 null값이 있는 컬럼으로 count 하게 되면 누락 된다. null 값을 세지 않기 때문. 그래서 제일 확실한 방법은 모든 컬럼 (*) 을 선택하면 확실하다. select count(*) from emp; 문제 : 직업이 세일즈 맨인 사원들이 몇명? select count(*) from emp where job = 'SALESMAN'; → 웬만하면 * 을 쓰자!! 문제 : 우리반 통신사가 lg 인 학생들은 전부 몇명인지 카운트 출력 select count(*) from emp14 where lower(telecom) = 'l..
SQL 다중행 함수 - 그룹함수 (SUM) 토탈값 SUM (토탈값) GROUP 함수의 특징 : null 값을 무시한다. 예제 : 커미션의 토탈값을 출력 select sum(comm) from emp; or select sum(nvl (comm,0)) from emp; → 두개의 sql 중 속도가 빠른 sql 은 무엇인가? 첫번째 sql 이 속도가 더 빠르다. NULL 값을 무시하고 계산하기때문에 식이 줄어든다. 두번째 sql 은 null 값이 0으로 치환되어 sum 연산에 포함되어있다. 따라서 만약 데이터가 1억개 넘는다고 하면 0 을 더하는 식이 있기 때문에 연산시간이 너무 늘어나게 된다. 문제 : 직업 , 직업별 토탈월급을 출력 , 직업별토탈월급이 높은 순으로 출력 select job, sum(sal) from emp group by job orde..
SQL 다중행 함수 - 그룹함수 (AVG) 평균값 구하기 AVG (평균 출력) → 만약 출력했을때 소수점이 너무많아서 안나오게 하고싶다면 round 둘러준다. 특징 NULL 값이 있을 경우 avg 는 계산할때 NULL 값을 빼고 계산한다. → 그룹함수의 특징이다. 예제 : 우리반 테이블에서 평균 나이를 출력하세요 select round(avg(age),1) from emp14; 문제 : 직업, 직업별 평균월급 출력 select job, round( avg(sal)) from emp group by job; 문제 : 커미션을 다 더해서(null 포함) 전체 사원수로 나누게 하려면 ? select avg(nvl(comm,0)) from emp; 설명 : null 값을 0으로 치환하여 평균값을 출력한다. 문제 : class_type , class_type별 평균나이..

티스토리툴바