oracle+sql脚本大全,ORACLE+SQL+高级编程脚本

最新推荐文章于 2023-11-28 09:47:12 发布

精准小天使

最新推荐文章于 2023-11-28 09:47:12 发布

阅读量159

点赞数

文章标签： oracle+sql脚本大全

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),

) sales_yr,

TRUNC

( 100

* ratio_to_report (sale) OVER (PARTITION BY product, country, region),

) 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)

精准小天使

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle+sql脚本大全,ORACLE+SQL+高级编程脚本

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_nu...
复制链接

扫一扫