Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Advanced Oracle SQL #7

GROUP BY extensions, advanced aggregate functions
by

Rodion Mironov

on 24 December 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Advanced Oracle SQL #7

Links
and practice Aggregation 1. For EMPLOYEES table, produce a report that will show:
Number of employees in each department;
Total number of employees;

2. For EMPLOYEES table, produce a report that will show:
Total salary for each department;
Total salary for each job_id;
Grand total salary;

3. Using SALES_VIEW (SH schema), produce the following report for Argentina:
Total sales per year (total sales = sum(sales);
For each year – most and least profitable products. Identify profitability by SALES field. GROUP BY clause http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2182483 Functions GROUPING function http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions071.htm#i77498 Problem Rollup Cube Grouping sets GROUPING() FIRST/LAST Typical summary report Some totals Second-level
totals Grand total GROUP BY country, year GROUP BY country No GROUP BY union all union all Obvious solution is not acceptable
too massive;
too slow;
code duplication. SELECT sum(sales) ...
GROUP BY ROLLUP (country, year) SELECT sum(sales) ...
GROUP BY country, year
union all
SELECT sum(sales) ...
GROUP BY country
union all
SELECT sum(sales) ... = select
country
,year
,sum(sales) as sales
from sales_view
group by rollup (country, year)
order by country nulls last
,year nulls last; Note: "rolled out" columns are filled with NULLs GROUPING function allows to distinguish
"natural" NULLs and grouping-produced NULLs select
job_id
,department_id
,count(*)
,grouping(job_id) job_grouping
,grouping(department_id) dep_grouping
from employees
group by rollup (job_id, department_id)
order by job_id nulls last
,department_id nulls last select
job_id
,NVL(TO_CHAR(department_id),
case
when GROUPING(department_id) = 1 then 'All deps'
else 'No dep'
end) as dep_id
,count(*)
from employees
group by rollup (job_id, department_id)
order by job_id nulls last
,GROUPING(department_id)
,department_id nulls last GROUPING(<expr>) returns: 1, if NULL in <expr> is caused by grouping;
0 otherwise (for "natural" NULLs). ROLLUP clause "excludes" grouping criteria one by one, starting from last.
For N criteria it produces N+1 groupings. Such data set can't be produced with ROLLUP:
GROUP BY country, year;
GROUP BY country;
GROUP BY year;
no GROUP BY; CUBE produces all combinations
of grouping expressions,
including "all expression" and "no expressions".

So, CUBE(expr1,...,exprN) results in
2^N different groupings. select
case when grouping(Country) = 1
then 'All countries'
else Country
end as Sales_Country
,case when grouping(year) = 1
then 'All years'
else TO_CHAR(year)
end as Sales_Year
,sum(sales) as sales
from sales_view
group by cube (country, year)
order by grouping(country)
,country
,grouping(year)
,year; most flexible grouping feature;
several GROUP BYs can be set explicitly;
like "CUBE without combinations I don't need". select
case when grouping(Country) = 1
then 'All countries'
else Country
end as Sales_Country
,case when grouping(year) = 1
then 'All years'
else TO_CHAR(year)
end as Sales_Year
,sum(sales) as sales
from sales_view
group by grouping sets (country, year)
order by grouping(country)
,country
,grouping(year)
,year; select
case when grouping(Country) = 1
then 'All countries'
else Country
end as Sales_Country
,case when grouping(year) = 1
then 'All years'
else TO_CHAR(year)
end as Sales_Year
,sum(sales) as sales
from sales_view
group by grouping sets ((country, year),country,())
order by grouping(country)
,country
,grouping(year)
,year; Problem Typical question. For each department show:
department_id;
date when first employee was hired;
name of that employee. select d.department_id, d.min_date, e.last_name
from (
select department_id
,min(hire_date) as min_date
from emp
group by department_id) d
inner join emp e on d.department_id = e.department_id
and d.min_date = e.hire_date; common syntax Allows some aggregate functions to work over first or last rows in group ONLY. MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEV Any
ordering construction Example 1 For each department show:
department_id;
date when first employee was hired;
name of that employee. select
department_id
,min(hire_date) as min_date
,min(last_name) keep (dense_rank first order by hire_date)
as last_name
from emp
group by department_id; Example 2 For each department show:
department_id;
maximum salary;
name of that employee with that salary. select
department_id
,max(salary) as max_Salary
,min(last_name) keep (dense_rank last order by salary)
as last_name
,count(*) keep (dense_rank last order by salary)
as num_people
from emp
group by department_id; FIRST/LAST functions http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions065.htm#i1000901
Full transcript