8-1
/* Formatted on 2013/01/28 15:42 (Formatter Plus v4.8.8) */
create table sales_fact as SELECT country_name country, country_subregion region, prod_name product,
calendar_year YEAR, calendar_week_number week, SUM (amount_sold) sale,
SUM ( amount_sold
* (CASE
WHEN MOD (ROWNUM, 10) = 0
THEN 1.4
WHEN MOD (ROWNUM, 5) = 0
THEN 0.6
WHEN MOD (ROWNUM, 2) = 0
THEN 1.2
ELSE 1
END
)
) receipts
FROM sales a1, times a2, customers a3, countries a4, products a5
WHERE a1.time_id = a2.time_id
AND a1.prod_id = a5.prod_id
AND a1.cust_id = a3.cust_id
AND a3.country_id = a4.country_id
GROUP BY country_name,
country_subregion,
prod_name,
calendar_year,
calendar_week_number
select * from sales_fact
/* Formatted on 2013/02/01 17:29 (Formatter Plus v4.8.8) */
SELECT sales_fact.*,
SUM (sale) OVER (PARTITION BY country, product, region, YEAR ORDER BY week rows between unbounded preceding and current row) sum_sale
FROM sales_fact
WHERE country = 'Australia'
8-2
/* Formatted on 2013/02/01 17:29 (Formatter Plus v4.8.8) */
SELECT sales_fact.*,
SUM (sale) OVER (PARTITION BY country, product, region, YEAR ORDER BY week)
FROM sales_fact
WHERE country = 'Australia'
8-3
/* Formatted on 2013/02/02 13:54 (Formatter Plus v4.8.8) */
SELECT sales_fact.*,
SUM (sale) OVER (PARTITION BY country, product, region, YEAR ORDER BY week ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
sum_sale
FROM sales_fact
WHERE country = 'Australia'
/* Formatted on 2013/01/28 15:21 (Formatter Plus v4.8.8) */
SELECT country_name country, country_subregion region, prod_name product,
calendar_year YEAR, calendar_week_number week,AMOUNT_SOLD,
FROM sales a1, times a2, customers a3, countries a4, products a5
WHERE a1.time_id = a2.time_id
AND a1.prod_id = a5.prod_id
AND a1.cust_id = a3.cust_id
AND a3.country_id = a4.country_id
select SUM (sale) OVER (PARTITION BY product, country, region, YEAR),sales_fact.* from sales_fact WHERE country = 'Australia' AND product = 'Xtend Memory'
8-2
/* Formatted on 2013/01/28 16:22 (Formatter Plus v4.8.8) */
SELECT region,YEAR, week, sale,product,
SUM (sale) OVER (PARTITION BY product, country, region, YEAR ORDER BY week rows between unbounded preceding and current row) running_sum_ytd
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory' order by year,week
8-3
SELECT country,region,product,YEAR, week, sale,
MAX (sale) OVER (PARTITION BY product, country, region, YEAR ORDER BY week ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
max_sale
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY product, country, YEAR, week
8-4
/* Formatted on 2013/02/02 13:54 (Formatter Plus v4.8.8) */
SELECT sales_fact.*,
max (sale) OVER (PARTITION BY country, product, region, YEAR ORDER BY week ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
sum_sale
FROM sales_fact
WHERE country = 'Australia'
8-6
/* Formatted on 2013/02/02 14:10 (Formatter Plus v4.8.8) */
SELECT sales_fact.*,
LAG (sale, 10,sale) OVER (PARTITION BY country, product, region ORDER BY year,week)
pre_sale
FROM sales_fact
WHERE country = 'Australia'
8-7
/* Formatted on 2013/02/02 14:10 (Formatter Plus v4.8.8) */
SELECT sales_fact.*,
LEAD (sale, 10,sale) OVER (PARTITION BY country, product, region ORDER BY year,week)
prior_wk_sale
FROM sales_fact
WHERE country = 'Australia'
8-8
/* Formatted on 2013/02/02 14:35 (Formatter Plus v4.8.8) */
SELECT sales_fact.*,
FIRST_VALUE (sale) OVER (PARTITION BY country, product, region, YEAR ORDER BY sale DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
top_sale_value,
FIRST_VALUE (week) OVER (PARTITION BY country, product, region, YEAR ORDER BY sale DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
top_sale_week
FROM sales_fact
WHERE country = 'Australia'
ORDER BY product, YEAR, week
8-9
/* Formatted on 2013/02/02 14:35 (Formatter Plus v4.8.8) */
SELECT sales_fact.*,
LAST_VALUE (sale) OVER (PARTITION BY country, product, region, YEAR ORDER BY sale DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
low_sale_value,
LAST_VALUE (week) OVER (PARTITION BY country, product, region, YEAR ORDER BY sale DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
low_sale_week
FROM sales_fact
WHERE country = 'Australia'
ORDER BY product, YEAR, week
SELECT country, region, product, YEAR, week, sale,
LAG (sale, 1, sale) OVER (PARTITION BY country, region, product ORDER BY YEAR,
week) prion_wk_sale
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY product, country, YEAR, week
SELECT country, region, product, YEAR, week, sale,
first_value (sale) OVER (PARTITION BY country, region, product,year ORDER BY sale desc) top_sale_week
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY product, country, YEAR, week
/* Formatted on 2013/01/30 09:32 (Formatter Plus v4.8.8) */
SELECT country, region, product, YEAR, week, sale,
FIRST_VALUE (sale) OVER (PARTITION BY country, region, product, YEAR ORDER BY sale DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
top_sale_value,
FIRST_VALUE (week) OVER (PARTITION BY country, region, product, YEAR ORDER BY sale DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
top_sale_week
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY product, country, YEAR, week
/* Formatted on 2013/01/30 09:32 (Formatter Plus v4.8.8) */
SELECT country, region, product, YEAR, week, sale,
LAST_VALUE (sale) OVER (PARTITION BY country, region, product, YEAR ORDER BY sale DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
top_sale_value,
LAST_VALUE (week) OVER (PARTITION BY country, region, product, YEAR ORDER BY sale DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
top_sale_week
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY product, country, YEAR, week
/* Formatted on 2013/01/30 10:24 (Formatter Plus v4.8.8) */
SELECT product, country, region, YEAR, week, sale,
RANK () OVER (PARTITION BY product, country, region, YEAR ORDER BY sale DESC)
sales_rank
FROM sale_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY product, country, YEAR, week
8-11
/* Formatted on 2013/01/30 10:29 (Formatter Plus v4.8.8) */
SELECT *
FROM (SELECT product, country, region, YEAR, week, sale,
RANK () OVER (PARTITION BY product, country, region, YEAR ORDER BY sale DESC)
sales_rank
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY product, country, YEAR, week)
WHERE sales_rank <= 10
ORDER BY 4,7
8-12
/* Formatted on 2013/01/30 10:29 (Formatter Plus v4.8.8) */
SELECT *
FROM (SELECT product, country, region, YEAR, week, sale,
DENSE_RANK () OVER (PARTITION BY product, country, region, YEAR ORDER BY sale DESC)
sales_rank
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY product, country, YEAR, week)
WHERE sales_rank <= 10
ORDER BY 4,7
8-13
/* Formatted on 2013/01/30 10:54 (Formatter Plus v4.8.8) */
SELECT *
FROM (SELECT product, country, region, YEAR, week, sale,
DENSE_RANK () OVER (PARTITION BY product, country, region, YEAR ORDER BY sale DESC)
sales_rank,
ROW_NUMBER () OVER (PARTITION BY product, country, region, YEAR ORDER BY sale DESC)
sales_rn
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY product, country, YEAR, week)
WHERE sales_rank <= 10
ORDER BY 4, 7
8-14
/* Formatted on 2013/01/30 14:10 (Formatter Plus v4.8.8) */
SELECT region, YEAR, week, sale, product,
TRUNC
( 100
* ratio_to_report (sale) OVER (PARTITION BY product, country, region, YEAR),
2
) sales_yr,
TRUNC
( 100
* ratio_to_report (sale) OVER (PARTITION BY product, country, region),
2
) sales_prod
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY YEAR, week
8-18
/* Formatted on 2013/01/30 14:46 (Formatter Plus v4.8.8) */
SELECT YEAR, week, sale,
NTILE (10) OVER (PARTITION BY product, country, region, YEAR ORDER BY sale DESC)
group#
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY YEAR, week
8-19
/* Formatted on 2013/01/28 16:22 (Formatter Plus v4.8.8) */
SELECT region,YEAR, week, sale,product,
stddev (sale) OVER (PARTITION BY product, country, region, YEAR ORDER BY week rows between unbounded preceding and unbounded following) stddv
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory' order by year,week
8-20
select listagg(country,',') within group(order by country desc) from (select distinct country from sales_fact order by country)
8-25
/* Formatted on 2013/02/01 13:43 (Formatter Plus v4.8.8) */
SELECT YEAR, week, top_sale,
LAG (top_sale) OVER (ORDER BY YEAR DESC) pre_top_sale
FROM (SELECT DISTINCT FIRST_VALUE (YEAR) OVER (PARTITION BY country, product, YEAR ORDER BY sale DESC)
YEAR,
FIRST_VALUE (week) OVER (PARTITION BY country, product, YEAR ORDER BY sale DESC)
week,
FIRST_VALUE (sale) OVER (PARTITION BY country, product, YEAR ORDER BY sale DESC)
top_sale
FROM sales_fact
WHERE country = 'Australia' AND product = 'Xtend Memory'
ORDER BY YEAR)
