SQL高级处理

学习资料http://datawhale.club/t/topic/472

1.1窗口函数

• RANK、DENSE_RANK、ROW_NUMBER的区别

语法：自行跑感受一下就懂了，以后风格是尽量简洁方便以后找代码套

SELECT  product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (ORDER BY sale_price) AS ranking
       ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
       ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
  FROM product  

SELECT  product_name
       ,product_type
       ,sale_price
       ,RANK() OVER (ORDER BY sale_price) AS ranking
       ,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
       ,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
  FROM product  

2.1聚合函数在窗口函数上的使用

SELECT  product_id
       ,product_name
       ,sale_price
       ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
       ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg  
  FROM product;  

  

 

2.2移动平均

SELECT  product_id
       ,product_name
       ,sale_price
       ,AVG(sale_price) OVER (ORDER BY product_id
                               ROWS 2 PRECEDING) AS moving_avg
       ,AVG(sale_price) OVER (ORDER BY product_id
                               ROWS BETWEEN 1 PRECEDING 
                                        AND 1 FOLLOWING) AS moving_avg  
  FROM product  

PRECEDING（“之前”）， 之前的n行+自身

FOLLOWING（“之后”），之后的n行+自身

ROWS 2 PRECEDING：之前的两行+自身

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：上一行+自身+下一行

2.3运算符

ROLLUP求合计

SELECT  product_type
       ,regist_date
       ,SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type, regist_date WITH ROLLUP  

练习题：

1、

2、

SELECT  product_id
       ,product_name
       ,regist_date
       ,sale_price
       ,MAX(sale_price) OVER (ORDER BY regist_date) AS Current_max_price
  FROM product

3、只针对你制定的列排序