1 / 15

Extended Group By Queries

CSED421 Database Systems Lab. Extended Group By Queries. Group function. GROUP BY Sort the data with distinct value for data of specified columns Limit the window of data processed by the aggregate function Find the job and average salary of each jobs Select job, avg (salary)

candice-marsh
Télécharger la présentation

Extended Group By Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSED421Database Systems Lab Extended GroupBy Queries

  2. Group function • GROUP BY • Sort the data with distinct value for data of specified columns • Limit the window of data processed by the aggregate function • Find the job and average salary of each jobs • Select job, avg(salary) from DevelopTeam group by job; • If you also want to determine the average salary over all jobs, you must run an additional query.

  3. Extended Group • Cause extra rows to be added to the summary output • ROLLUP • CUBE

  4. ROLLUP • WITH ROLLUP • Execute additional implicit GROUP BY queries with columns, which is specified in GROUP BY clauses • Produce group summaries from right to left and a total summary • SQL Example • SELECT job, AVG(sal)FROM EMPGROUP BY jobWITH ROLLUP; • SELECT AVG(sal)FROM EMP;

  5. ROLLUP • If “n” is the number of columns listed in the GROUP BY clause, there will be n+1 levels of summaries. • Group by Continent, Country, City with ROLLUP • (Continent, Country, City) • (Continent, Country) • (Continent) • ()

  6. ROLLUP for Multiple Columns • Results depends on a sequence of columns in GROUP BY clauses. SELECT deptno, job, AVG(sal) FROM EMP GROUP BY deptno, job WITH ROLLUP; ≠ SELECT job, deptno, AVG(sal) FROM EMP GROUP BY job, deptno WITH ROLLUP;

  7. ROLLUP with Conditions • Specify some conditions for ROLLUP SELECT deptno, job, AVG(sal) FROM EMP GROUP BY deptno, job WITH ROLLUP HAVING AVG(sal) > 2000;

  8. CUBE • WITH CUBE • Generate summaries for all combinations of the columns specified in GROUP BY clauses • If “n” is the number of columns listed in the GROUP BY clause, there will be summary combinations. • GROUP BY A, B, C with CUBE • (A, B, C) • (A, B), (A, C), (B, C) • (A), (B), (C), (D) • ()

  9. CUBE • MySQL doesn’t yet support CUBE. SELECT deptno, job, AVG(sal) FROM EMP GROUP BY deptno, job WITH CUBE; SELECT deptno, job, AVG(sal) FROM EMP GROUP BY deptno, job WITH ROLLUP UNION SELECT deptno, job, AVG(sal) FROM EMP GROUP BY job, deptno With ROLLUP;

  10. Practice • EMP table • DEPT table

  11. Practice • Source lab6.sql http://ids.postech.ac.kr/dblab2014/lab6.sql

  12. Practice • 부서명(DNAME) 및 직위(JOB)별 급여(SAL)의 합을 다음과 같이 출력하시오. • 급여의 내림차순으로 정렬 • MySQL에서 ROLLUP과 ORDER BY는 동시에 사용할 수 없으므로 subquery를 사용하여 해결

  13. Practice • 부서명(DNAME) 및 직위(JOB)별 급여(SAL)의 합을 다음과 같이 출력하시오. • 급여의 내림차순으로 정렬 • 급여의 합이 2000이상인 부서 및 직위 그룹을 출력 (Use cube)

  14. Practice • 관리자가 있는 사람들에 대해서 각 부서(DEPTNO) 별 직위(JOB)별로 직원수를 다음과 같은 형태로 출력하시오. • 부서(DEPTNO), 직위(JOB)순으로 정렬

  15. Practice • 관리자의 이름과 관리자가 관리하는 사원들의 직위별 급여 합, 직원 수, 평균 급여를 다음과 같이 출력하시오. • 관리자의 이름은 중복이 없다고 가정

More Related