Oracle数据库11g：SQL计划管理（二）

 

　　FROM

　　sh.sales SH

　　,sh.customers C

　　,sh.countries T

　　WHERE SH.cust_id = C.cust_id

　　AND C.country_id = T.country_id

　　AND T.country_iso_code IN ('GB','PL')

　　GROUP BY C.cust_state_province

　　-----

　　-- Query: SPM_2.2.2

　　-- Purpose: Show distribution of geographic areas within

　　-- Sales Region and District

　　-----

　　SELECT /*SPM_2.2.2*/

　　SR.abbr,

　　SD.abbr,

　　SZ.geo_id,

　　COUNT(C.cust_id) "Count"

　　FROM

　　sfa.sales_regions SR,

　　sfa.sales_districts SD,

　　sfa.sales_zones SZ,

　　sh.customers C

　　WHERE SD.region_id = SR.region_id

　　AND SZ.district_id = SD.district_id

　　AND C.cust_state_province = SZ.geo_id

　　AND SR.abbr = 'SE00'

　　GROUP BY

　　SR.abbr

　　,SD.abbr

　　,SZ.geo_id

　　-----

　　-- Query: SPM_2.2.3

　　-- Purpose: Accumulate quantities and revenue within Sales Region, District,

　　-- and Territory. Note that this query +should+ take advantage of

　　-- materialized view SFA.MV_SALES_SUMMARY for most effective retrieval

　　-----

　　SELECT /*SPM_2_2.3*/

　　SR.abbr

　　,SD.abbr

　　,SZ.geo_id

　　,C.cust_id

　　,SUM(SH.quantity_sold)

　　,AVG(SH.quantity_sold)

　　,COUNT(SH.quantity_sold)

　　,SUM(SH.amount_sold)

　　,AVG(SH.amount_sold)

　　,COUNT(SH.amount_sold)

　　FROM

　　sfa.sales_regions SR

　　,sfa.sales_districts SD

　　,sfa.sales_zones SZ

　　,sh.customers C

　　,sh.sales SH

　　WHERE SD.region_id = SR.region_id

　　AND SZ.district_id = SD.district_id

　　AND C.cust_state_province = SZ.geo_id

　　AND C.cust_ID = SH.cust_id

　　GROUP BY

　　SR.abbr

　　,SD.abbr

　　,SZ.geo_id

　　,C.cust_id

　　ORDER BY

　　SR.abbr

　　,SD.abbr

　　,SZ.geo_id

　　,C.cust_id

　　-----

　　-- Query: SPM_2.2.4

　　-- Purpose: Accumulate quantities and revenue within Sales Region, District,

　　-- and Territory by querying directly against SFA.MV_SALES_SUMMARY

　　-----

　　SELECT /*SPM_2_2.4*/

　　rgn_abbr

　　,dst_abbr

　　,ter_abbr

　　,cust_id

　　,tot_qty_sold

　　,avg_qty_sold

　　,cnt_qty_sold

　　,tot_amt_sold

　　,avg_amt_sold

　　,cnt_amt_sold

　　FROM

　　sfa.mv_sales_summary

　　WHERE dst_abbr = 'NE20'

　　ORDER BY

　　rgn_abbr

　　,dst_abbr

　　,ter_abbr

　　,cust_id

　　-----

　　-- Query: SPM_2.2.5

　　-- Purpose: Accumulate quantities and revenue within Sales Region and District.

　　-- for a selected Region. Note that this query +should+ take advantage

　　-- of materialized view SFA.MV_SALES_SUMMARY for most effective retrieval

　　-----

　　SELECT /*SPM_2_2.5*/

　　SR.abbr

　　,SD.abbr

　　,SUM(SH.quantity_sold)

　　,AVG(SH.quantity_sold)

　　,COUNT(SH.quantity_sold)

　　,SUM(SH.amount_sold)

　　,AVG(SH.amount_sold)

　　,COUNT(SH.amount_sold)

　　FROM

　　sfa.sales_regions SR

　　,sfa.sales_districts SD

　　,sfa.sales_zones SZ

　　,sh.customers C

　　,sh.sales SH

　　WHERE SD.region_id = SR.region_id

　　AND SZ.district_id = SD.district_id

　　AND C.cust_state_province = SZ.geo_id

　　AND C.cust_ID = SH.cust_id

　　AND SR.abbr = :rgn_abbr

　　GROUP BY

　　SR.abbr

　　,SD.abbr

　　ORDER BY

　　SR.abbr

　　,SD.abbr

　　-----

　　-- Query: SPM_2.2.6

　　-- Purpose: Accumulate quantities and revenue within Sales Region. Note that

　　-- this query +cannot+ take advantage of SFA.MV_SALES_SUMMARY for

　　-- effective retrieval because of the selection criteria against

　　-- Region Description

　　-----

　　SELECT /*SPM_2_2.6*/

　　SR.abbr

　　,SUM(SH.quantity_sold)

　　,AVG(SH.quantity_sold)

　　,COUNT(SH.quantity_sold)

　　,SUM(SH.amount_sold)

　　,AVG(SH.amount_sold)

　　,COUNT(SH.amount_sold)

　　FROM

　　sfa.sales_regions SR

　　,sfa.sales_districts SD

　　,sfa.sales_zones SZ

　　,sh.customers C

　　,sh.sales SH

　　WHERE SD.region_id = SR.region_id

　　AND SZ.district_id = SD.district_id

　　AND C.cust_state_province = SZ.geo_id

　　AND C.cust_ID = SH.cust_id

　　AND C.cust_id = :cust_id

　　AND SR.description LIKE :rgn_desc

　　GROUP BY

　　SR.abbr

　　ORDER BY

　　SR.abbr