본문 바로가기
Study/class note

하둡 / NoSQL 하이브 SQL 사용하기2

chanzae 2022. 3. 24. 11:28

ㅇHive에서 사용하는 데이터 분석함수

1. rank

2. dense_rank

3. ntile

4. listagg(oracle) -------> concat_ws(hive)

5. 누적 데이터 출력

6. lag, lead

 

문제265. 입사년도(4자리), 입사한 연도별로 속한 사원들의 이름을 가로로 출력하시오.

oracle > select to_char(hiredate,'RRRR'), listagg(ename,',') within group (order by ename)
 from emp
 group by to_char(hiredate,'RRRR');
hive> select year(to_date(hiredate)), concat_ws(',',collect_set(ename))
    >  from emp
    >  group by year(to_date(hiredate));

1980    SMITH
1981    TURNER,CLARK,BLAKE,JAMES,WARD,JONES,MARTIN,ALLEN,FORD,KING
1982    MILLER,SCOTT
1983    ADAMS

year(to_date(hiredate))는 emp테이블의 입사일을 날짜형으로 변환(to_date) 한 후에 year함수를 이용해 연도 4자리만 추출하는 것.

해당하는 연도에 입사한 사원들의 이름을 가로로 출력하려면 concat_ws와 collect_set을 같이 사용해서 concat_ws(',', collect_set(ename)) 으로 구현하면 됨.

그리고 마지막에 group by 절을 꼭 사용해야 합니다.

 

 

문제266. 직업, 직업별로 속한 사원들의 이름을 가로로 출력하세요. 

hive> select job, concat_ws(',',collect_set(ename))
    > from emp
    > group by job;

ANALYST FORD,SCOTT
CLERK   ADAMS,MILLER,JAMES,SMITH
MANAGER CLARK,BLAKE,JONES
PRESIDENT       KING
SALESMAN        TURNER,WARD,MARTIN,ALLEN

 

 

문제267. 부서번호, 부서번호별 토탈월급을 출력하는데 맨 아래쪽에 전체 토탈월급이 출력되게 하시오.

oracle > select deptno, sum(sal)
 from emp
 group by rollup(deptno);
hive> select deptno, sum(sal) as sumsal
    > from emp
    > group by deptno with rollup
    > order by sumsal asc;

10      8750
30      9400
20      10875
NULL    29025

 

문제268. 위의 결과를 부서번호가 10,20,30번 순으로 정렬하세요.

hive> select deptno, sum(sal) as sumsal
    > from emp
    > group by deptno with rollup
    > order by deptno;

NULL    29025
10      8750
20      10875
30      9400

null값이 가장 위로 나오는데, 우리는 가장 아래로 보고 싶음.

 

-> isnull() 함수를 이용해서 정렬을 다시 해야함.

hive> select ename, comm, isnull(comm)
    > from emp
    > order by isnull(comm);

WARD    500     false
ALLEN   300     false
TURNER  0       false
MARTIN  1400    false
KING    NULL    true
BLAKE   NULL    true
CLARK   NULL    true
JONES   NULL    true
JAMES   NULL    true
FORD    NULL    true
SMITH   NULL    true
SCOTT   NULL    true
ADAMS   NULL    true
MILLER  NULL    true

isnull(컬럼) = true : null값인 값들만 출력됨.

False가 True보다 우선순위를 갖기 때문에 order by 절에 쓰면 null값을 맨 아래로 보낼 수 잇음.

즉, 정렬을 하면 False가 먼저 나오고 True가 나중에 나옴.

 

hive> select deptno, sum(sal) as sumsal
    > from emp
    > group by deptno with rollup
    > order by isnull(deptno);

10      8750
20      10875
30      9400
NULL    29025

 

문제269. 이번에는 30,20,10,null순으로 출력하시오.

hive> select deptno, sum(sal) as sumsal
    > from emp
    > group by deptno with rollup
    > order by isnull(deptno), deptno desc;

30      9400
20      10875
10      8750
NULL    29025

 

문제270. 입사년도 4자리, 입사년도별 토탈월급을 출력하는데 전체토탈월급이 맨 아래에 출력되게 하시오.

hive> select year(to_date(hiredate)) as hyear, sum(sal) as sumsal
    > from emp
    > group by year(to_date(hiredate)) with rollup
    > order by isnull(hyear);

1980    800
1981    22825
1982    4300
1983    1100
NULL    29025

order by절에 별칭을 넣어줘야함.

 

MariaDB [orcl]> select date_format(hiredate,'%Y'), sum(sal) as sumsal
    -> from emp
    -> group by date_format(hiredate,'%Y') with rollup;
+----------------------------+--------+
| date_format(hiredate,'%Y') | sumsal |
+----------------------------+--------+
| 1980                       |    800 |
| 1981                       |  27325 |
| 1982                       |   4300 |
| 1983                       |   1100 |
| NULL                       |  33525 |
+----------------------------+--------+

MariaDB [orcl]> select year(hiredate), sum(sal) as sumsal
    -> from emp
    -> group by year(hiredate) with rollup;
+----------------+--------+
| year(hiredate) | sumsal |
+----------------+--------+
|           1980 |    800 |
|           1981 |  27325 |
|           1982 |   4300 |
|           1983 |   1100 |
|           NULL |  33525 |
+----------------+--------+
5 rows in set (0.001 sec)

 

 

문제271. 부서번호, 부서번호별 토탈월급을 출력하고 맨 아래에 전체 토탈월급을 출력하는 것을 grouping sets로 구현하시오.

oracle > select deptno, sum(sal)
from emp
group by grouping sets( (deptno), () );

 

hive> select deptno, sum(sal) as sumsal
    > from emp
    > group by deptno grouping sets( (deptno), () )
    > order by sumsal asc;

10      8750
30      9400
20      10875
NULL    29025

hive> select deptno, sum(sal) as sumsal
    > from emp
    > group by deptno grouping sets( (deptno), () )
    > order by isnull(deptno);

10      8750
20      10875
30      9400
NULL    29025

grouping sets 함수 안에 (deptno) 는 부서번호로 그룹핑 하겠다는 뜻이고, ()는 전체 집계를 가리킴.

isnull(deptno)는 deptno가 null이면 True이고 null이 아니면 False인 것인데, order by isnull(deptno)는 False를 먼저 출력하고 True를 나중에 출력합니다.

그래서 null이 맨 밑에 출력되는 것 입니다.

 

 

문제272. 위의 결과를 mariaDB에서 출력하시오.

-> mariaDB는 grouping sets함수가 없고 위의 결과를 보려면 rollup으로 구현해야 합니다.

 

 

문제273. 입사년도(4자리), 입사한 년도별 평균월급을 출력하고 맨 아래에 전체 평균월급을 출력하는 SQL을 grouping sets를 이용해서 hive에서 구현하시오.

oracle > select to_char(hiredate,'RRRR'), avg(sal) as avgsal
from emp
group by grouping sets( (to_char(hiredate,'RRRR')),());
hive> select year(to_date(hiredate)) as hyear, avg(sal) as avgsal
    > from emp
    > group by year(to_date(hiredate)) grouping sets( (year(to_date(hiredate))),() )
    > order by isnull(hyear);

1980    800.0
1981    2282.5
1982    2150.0
1983    1100.0
NULL    2073.214285714286

 

 

문제274. 부서번호, 부서번호별 토탈월급을 출력하는데 토탈월급을 가로로 출력하시오.

oracle > select sum(decode(deptno,10,sal)) as "10",
                sum(decode(deptno,20,sal)) as "20",
                sum(decode(deptno,30,sal)) as "30"
            from emp;
hive> select sum( case when deptno=10 then sal end) dept10,
    >        sum( case when deptno=20 then sal end) dept20,
    >        sum( case when deptno=30 then sal end) dept30
    >   from emp;

dept10  dept20  dept30
8750    10875   9400

 

 

문제275. 직업, 직업별 토탈월급을 가로로 출력하시오.

oracle > select sum( decode(job, 'ANALYST', sal, 0) ) as ANALYST,
       sum( decode(job, 'CLERK', sal, 0) ) as CLERK,
       sum( decode(job, 'MANAGER', sal, 0) ) as MANAGER,
       sum( decode(job, 'SALESMAN', sal, 0) ) as SALESMAN,
       sum( decode(job, 'PRESIDENT', sal, 0) ) as PRESIDENT
 from emp;
hive>  select sum( case when job = 'ANALYST' then sal end) ANALYST,
    >         sum( case when job = 'CLERK' then sal end) CLERK,
    >         sum( case when job = 'MANAGER' then sal end) MANAGER,
    >         sum( case when job = 'SALESMAN' then sal end) SALESMAN,
    >         sum( case when job = 'PRESIDENT' then sal end) PRESIDENT
    > from emp;

analyst clerk   manager salesman        president
6000    4150    8275    5600    5000

 

반응형
저작자표시 비영리 (새창열림)

'Study > class note' 카테고리의 다른 글

하둡 / 스파크 설명, 스파크 설치  (0) 2022.03.28
하둡 / 영화평점에 대한 큰 데이터를 내려받아 hive 에서 분석하기  (0) 2022.03.24
하둡 / NoSQL 하이브 SQL 사용하기  (0) 2022.03.24
하둡 / 하이브 설치  (0) 2022.03.23
하둡 / 하둡 분산 파일 시스템 명령어  (0) 2022.03.23

'Study/class note' Related Articles

티스토리툴바