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 #1

Intoduction, data models, partitioned outer join
by

Rodion Mironov

on 12 December 2012

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Advanced Oracle SQL #1

Introduction Data models Partitioned outer jon Rodion Myronov Oracle trainer

Email: Rodion_Myronov@epam.com
Skype: Rodion.Mironov
Gtalk: Rodion.Mironov DB connections Human resources schema Sales history schema Course overview partitioned outer join;
hierarchical queries (CONNECT BY);
hierarchical queries (recursive WITH);
analytic functions (2 lessons + dedicated practice);
GROUP BY extensions;
pivot/unpivot clauses;
model clause (2 lessons + dedicated practice). Problem Syntax &
example Another example Sparse data Sparse data root cause Sparse data VS dense data hard to analyze time series;
hard to read; easy to read or analyze;
not easy to get :) only existing facts are stored in the database;
no fact - no row(s);
standard outer join does not help; days Y_box_sales Note: joins for separate products look pretty select w.day_number_in_week,s.prod_name,s.cnt
from week_days w
left outer join y_box_sales s
on w.day_number_in_week = s.day_number_in_week
order by prod_name, day_number_in_week; select w.day_number_in_week,s.prod_name,s.cnt
from week_days w
left join (select *
from y_box_sales
where prod_name = 'Extension Cable') s on w.day_number_in_week = s.day_number_in_week
order by day_number_in_week; select w.day_number_in_week,s.prod_name,s.cnt
from week_days w
left join (select *
from y_box_sales
where prod_name = 'Y Box') s on w.day_number_in_week = s.day_number_in_week
order by day_number_in_week; If we only could...
write a separate query
for each product;
UNION results. Partitioned join select w.day_number_in_week,s.prod_name,s.cnt
from week_days w
left outer join
y_box_sales s partition by (prod_name)
on w.day_number_in_week = s.day_number_in_week
order by prod_name, day_number_in_week; executes join separately for each value of partitioning column;

execute UNION for result; Number of peope hired by departments per year Goal: for each department show number of employees
hired in each year. with hired_by_dep as
(select department_id, extract(year from hire_date) as year, count(*) as cnt
from employees
group by department_id, extract(year from hire_date)
)
select d.department_name, h.year, nvl(h.cnt,0) as cnt
from departments d
left outer join hired_by_dep h
partition by (year)
on d.department_id = h.department_id
order by d.department_name, h.year Links
and practice Oracle 11g R2 docs SQL reference DWH Guide, part V Partitioned join in DWH Guide Partitioned join tutorial http://www.oracle.com/pls/db112/homepage http://docs.oracle.com/cd/E11882_01/server.112/e26088/toc.htm http://docs.oracle.com/cd/E11882_01/server.112/e25554/part5.htm#i996809 http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#i1014934 http://st-curriculum.oracle.com/obe/db/10g/r2/prod/bidw/outerjoin/outerjoin_otn.htm Sales_view view Create report in Sales History schema.
For each calendar week of year 1998 show number of sold items of each product starting with “Multimedia”. Report should be “dense”.
See example below.
Full transcript