扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
在本页阅读全文(共3页)
SPM情景#1:
/*
|| Script: SPM_2_1.sql
|| Purpose: Generate several SQL statements that perform Data
|| Warehouse query loads on an Oracle 10gR2 database
|| for capture into a SQL Tuning Set (STS) that will be
|| transferred to an Oracle 11g database to demonstrate
|| SQL Plan Baseline seeding.
*/
-----
-- Query: SPM_2_1.1
-- Purpose: Summarize sales activity within Country and Promotion
-----
SELECT /*SPM_2_1.1*/
CTY.country_total_id
,PR.promo_total_id
,COUNT(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.promotions PR
WHERE S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_total_id
,PR.promo_total_id
-----
-- Query: SPM_2_1.2
-- Purpose: Summarize sales activity within Country and Promotion
-----
SELECT /*SPM_2_1.2*/
CTY.country_id
,CTY.country_subregion_id
,CTY.country_region_id
,CTY.country_total_id
,PR.promo_total_id
,COUNT(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.promotions PR
WHERE S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_id
,CTY.country_subregion_id
,CTY.country_region_id
,CTY.country_total_id
,PR.promo_total_id
-----
-- Query: SPM_2_1.3
-- Purpose: Summarize sales activity within Country, Product,
-- Channel and Promotion
-----
SELECT /*SPM_2_1.3*/
CTY.country_total_id
,P.prod_id
,P.prod_subcategory_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
,COUNT(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.products P
,sh.channels CH
,sh.promotions PR
WHERE S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.prod_id = P.prod_id
AND S.channel_id = CH.channel_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_total_id
,P.prod_id
,P.prod_subcategory_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
-----
-- Query: SPM_2_1.4
-- Purpose: Summarize sales activity within Country, Product,
-- Channel and Promotion
-----
SELECT /*SPM_2_1.4*/
CTY.country_total_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
,COUNT(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.products P
,sh.channels CH
,sh.promotions PR
WHERE S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.prod_id = P.prod_id
AND S.channel_id = CH.channel_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_total_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
-----
-- Query: SPM_2_1.5
-- Purpose: Summarize sales activity within Country, Product,
-- Channel and Promotion
-----
SELECT /*SPM_2_1.5*/
CTY.country_id
,CTY.country_subregion_id
,CTY.country_region_id
,CTY.country_total_id
,P.prod_id
,P.prod_subcategory_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
,COUNT(S.amount_sold)
,SUM(S.amount_sold)
,SUM(S.quantity_sold)
FROM
sh.sales S
,sh.customers C
,sh.countries CTY
,sh.products P
,sh.channels CH
,sh.promotions PR
WHERE S.cust_id = C.cust_id
AND C.country_id = CTY.country_id
AND S.prod_id = P.prod_id
AND S.channel_id = CH.channel_id
AND S.promo_id = PR.promo_id
GROUP BY
CTY.country_id
,CTY.country_subregion_id
,CTY.country_region_id
,CTY.country_total_id
,P.prod_id
,P.prod_subcategory_id
,P.prod_category_id
,P.prod_total_id
,CH.channel_id
,CH.channel_class_id
,CH.channel_total_id
,PR.promo_total_id
SPM情景#2:
/*
|| 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)
如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。
现场直击|2021世界人工智能大会
直击5G创新地带,就在2021MWC上海
5G已至 转型当时——服务提供商如何把握转型的绝佳时机
寻找自己的Flag
华为开发者大会2020(Cloud)- 科技行者