Oracle SQL4-分析函数入门篇章

本文为oracle SQL分析函数入门，如果对分析函数有一定了解，可跳过此文。

另外，本文只是给出了部分分析函数的简单使用案例，未做具体说明。具体使用方法请自行调查。

create table SALE_FACT
(
  PRODUCT VARCHAR2(10),
  COUNTRY VARCHAR2(10),
  YEAR    VARCHAR2(10),
  WEEK    VARCHAR2(10),
  SALE    NUMBER
);
commit;
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('001', 'china', '2017', '01', 100);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('001', 'china', '2017', '02', 200);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('001', 'china', '2017', '03', 300);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('001', 'china', '2016', '01', 100);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('001', 'china', '2016', '02', 200);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('001', 'china', '2016', '03', 300);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('002', 'japan', '2017', '01', 100);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('002', 'japan', '2017', '02', 200);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('002', 'japan', '2017', '03', 300);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('002', 'japan', '2016', '01', 100);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('002', 'japan', '2016', '02', 200);
insert into SALE_FACT (PRODUCT, COUNTRY, YEAR, WEEK, SALE)
values ('002', 'japan', '2016', '03', 300);
commit;

select s.product,
       s.country,
       s.year,
       s.week,
       sum(s.sale) over(partition by s.product, s.country, s.year order by s.week 
         rows between unbounded preceding and current row) as running_sum
  from sale_fact s;

select s.product,
       s.country,
       s.year,
       s.week,
       max(s.sale) over(partition by s.product, s.country, s.year order by week 
         rows between unbounded preceding and unbounded following) as max_sal
  from sale_fact s;

select s.product,
       s.country,
       s.year,
       s.week,
       max(s.sale) over(partition by s.product, s.country, s.year order by week 
         rows between 1 preceding and 1 following) as max_sal
  from sale_fact s;

select s.product,
       s.year,
       s.week,
       s.country,
       s.sale,
       lag(s.sale, 2, null) over(partition by s.product, s.country 
         order by s.year, s.week) as prior_sale
  from sale_fact s;

select s.product,
       s.year,
       s.week,
       s.country,
       s.sale,
       first_value(s.sale) over(partition by s.product, s.country,year 
         order by s.sale desc) as max_sale
  from sale_fact s;

select s.product,
       s.year,
       s.week,
       s.country,
       s.sale,
       nth_value(s.sale,2) over(partition by s.product, s.country,year 
         order by s.sale desc 
         rows between unbounded preceding and unbounded following) as max_sale
  from sale_fact s;

select t.year,t.week,t.top_sale_year,
lag(t.top_sale_year) over(order by year asc) from 
(select distinct 
  first_value(s.year) over(partition by s.product, s.country, s.year 
    order by s.sale desc rows between unbounded preceding and unbounded following) as year,
  first_value(s.week) over(partition by s.product,s.country,s.year
    order by s.sale desc rows between unbounded preceding and unbounded following) as week,
  first_value(s.sale) over(partition by s.product,s.country,s.year
    order by s.sale desc rows between unbounded preceding and unbounded following) as top_sale_year
  from sale_fact s) t;

create table ORDERS
(
  CUSTOMER_ID VARCHAR2(10),
  ORDER_ID    VARCHAR2(10),
  ORDER_DATE  DATE
);
commit;
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('001', 'P01', to_date('07-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('001', 'P02', to_date('04-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('001', 'P03', to_date('26-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('001', 'P04', to_date('17-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('002', 'P01', to_date('27-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('002', 'P02', to_date('18-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('002', 'P03', to_date('09-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('003', 'P01', to_date('05-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('003', 'P02', to_date('19-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('003', 'P03', to_date('11-07-2017', 'dd-mm-yyyy'));
insert into ORDERS (CUSTOMER_ID, ORDER_ID, ORDER_DATE)
values ('004', 'P01', to_date('19-07-2017', 'dd-mm-yyyy'));
commit;

select t.customer_id, avg(t.order_date - t.prev_order_date) as avg_days_between from 
(select o.customer_id,
       o.order_date,
       lag(o.order_date, 1, order_date) 
       over(partition by o.customer_id order by o.order_date desc) as prev_order_date
  from orders o) t
  group by t.customer_id