at 我 分析函数,看这些就可以了at

 at

 

select * from orders;

--为什么要group by ??不需要
--当前月和全年的销售额
select month,
       sum(tot_sales) month_sales,
       sum(sum(tot_sales)) over(order by month rows between unbounded preceding and unbounded following) total_sales

  from orders
 group by month;


  

select month,
       tot_sales month_sales,
       sum(tot_sales) over() total_sales,
       sum(tot_sales) over(partition by 1) total_sales2,
        sum(tot_sales) over(order by month rows between unbounded preceding and unbounded following) total_sales3
from orders  
  
--------------------------------------------漂亮的递规  rows/range 语句,显然只有order by的时候才有作用的
--每月递规的的销售额
select month,
      tot_sales month_sales,
       sum(tot_sales) over(order by month desc rows between 0 preceding and unbounded following) all_sales  --order by是亮点
  from orders
order by month;


select month,
       tot_sales month_sales,
       sum(tot_sales) over(order by month  rows between unbounded preceding and 0 following) all_sales  --order by是亮点
  from orders

 
select month,
      tot_sales month_sales,
       sum(tot_sales) over(order by month RANGE BETWEEN UNBOUNDED preceding AND current row) all_sales  --order by是亮点
  from orders

 
 
 SELECT   b.month, SUM (a.tot_sales) month_sales 
    FROM orders a, orders b 
   WHERE a.month<= b.month 
GROUP BY b.month;

select o.month,
       (select sum(o1.tot_sales) from orders o1 where o1.month <= o.month)
  from orders o




 -------------------------------------------
 ---当前月,前一月,后一月的销售额
select month,
              first_value(sum(tot_sales)) over (order by month 
                                     rows between 1 preceding and 1 following) prev_month,
  
              sum(tot_sales) monthly_sales,
  
              last_value(sum(tot_sales)) over (order by month 
                                   rows between 1 preceding and 1 following) next_month,
  
              avg(sum(tot_sales)) over (order by month 
                                  rows between 1 preceding and 1 following) rolling_avg
     from orders
  
   group by month
  order by month;
  
  
  
  
  select month,
         sum(tot_sales) over(order by month rows between 1 preceding and 0 following) - tot_sales pre_month,  --   -1 to 0
         
         tot_sales monthly_sales,
         
         sum(tot_sales) over(order by month rows between 0 preceding and 1 following) - tot_sales next_month ,
    
        avg(tot_sales) over (order by month 
                                    rows between 1 preceding and 1 following) rolling_avg     --   -1  to 1
    from orders;


 select *
   from orders ocurrent, orders onext
  where onext.month(+) = ocurrent.month + 1

select ocurrent.month ,ocurrent.tot_sales ocurrent  ,onext.tot_sales onext ,opre.tot_sales opre
  from orders ocurrent
  left join orders onext on onext.month - 1 = ocurrent.month
  left join orders opre  on opre.month +1 = ocurrent.month
  order by ocurrent.month

  -------------- 按照季度
  select o.month,
         (case
           when o.month < '04' then
            '01'
           when o.month < '07' then
            '02'
           when o.month < '10' then
            '03'
           else
            '04'
         end) q,
         o.tot_sales,
         sum(tot_sales) over(partition by(case
           when o.month < '04' then
            '01'
           when o.month < '07' then
            '02'
           when o.month < '10' then
            '03'
           else
            '04'
         end))
    from orders o
    
    -------------------
    --有多少个月份的sales和当月的sales的差距是在-10000 to 10000之间
  
  select month,
         tot_sales month_sales,
         count(1) over(order by tot_sales RANGE BETWEEN 10000 preceding AND 10000 following) count_Num --order by是亮点
    from orders o
   order by o.month

    

 

 

 

 

分析函数的关键特征是它们允许结果集的单行可以包括计算的多行交叉而不需要在整个结果集上处理GROUP BY。其允许多行和单行数据在一个select语句中而不需要部署子查询。

分析函数的语法

       FUNCTION() OVER (<partition by> < <order by> <windowing clause> >)

       PARTITION BY定义函数将处理的组，如果partition by被忽略，那么整个select返回的数据集将作为一个活动分区。

       ORDER BY排序分区内的数据，可以声明ASC/DESC，以及NULLS FIRST和NULLS LAST标识null的处理。

       WINDOWING CLAUSE不能在每个分区函数上使用，如果可用，必须和ORDER BY一起使用。窗口子句声明了将在函数中计算的当前行周围的行集。窗口可以定义为ROWS或RANGE，ROWS相对于活动分区中的行数，而RANGE则查看包含在这些行中的值。默认为ROWS UNBOUNDED PROCEDING事实上包含了所有行。

分析函数的列表

       分析函数通常分为三类，数组，聚集，其他。

       最常用的函数包括：

 

函数	类型	Partition by	Order By	Window
DENSE_RANK	数组	可选	必须	--
RANK	数组	可选	必须	--
ROW_NUMBER	数组	可选	必须	--
NTILE	数组	可选	必须	--
AVG	聚集	可选	可选	可选
COUNT	聚集	可选	可选	可选
SUM	聚集	可选	可选	可选
RATIO_TO_REPORT	聚集	可选	--	--
FIRST_VALUE	其他	可选	可选	可选
LAST_VALUE	其他	可选	可选	可选
LAG	其他	可选	必须	--
LEAD	其他	可选	必须	--
MAX	其他	可选	可选	可选
MIN	其他	可选	可选	可选