Oracle数据库11g：SQL计划管理（二）(1)(6)
/*
|| SFA_Queries.sql
||
|| Various queries to produce workload against simulated new
|| Sales Force Administration application
||
*/
-----
-- Set up and initialize bind variables
-----
VARIABLE rgn_abbr VARCHAR2(4);
VARIABLE rgn_desc VARCHAR2(40);
VARIABLE cust_id NUMBER;
BEGIN
:rgn_abbr := 'NE00';
:rgn_desc := 'South%';
:cust_id := 9090;
END;
/
-----
-- Query: SPM_2.2.1
-- Purpose: Summarize quantity sold and revenue within U.S. States
-----
SELECT /*SPM_2.2.1*/
C.cust_state_province
,SUM(sh.quantity_sold)
,SUM(sh.amount_sold)
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
;
【51CTO.COM 专家翻译，转载请注明出处及作者！】
