Analytic Function Concepts

Data Windows

• partitioning result set into data windows.
• 例如，从季度或所有数据中找出最大值，并添加为一列
• over (partition by ...) 可以在不对数据进行操纵的前提下，对数据进行分组，并求出分组内的一些值
• example

  SELECT quarter(payment_date) quarter,
         monthname(payment_date) month_nm,
         sum(amount) monthly_sales,
         max(sum(amount)) OVER () max_overall_sales,
         max(sum(amount)) OVER (partition by quarter(payment_date)) max_qrtr_sales
  FROM payment
  WHERE year(payment_date) = 2005
  GROUP BY quarter(payment_date), monthname(payment_date);
  

Localized Sorting

• rank() OVER (ORDER BY ...)
• rank() OVER (partition by ... order by ...)
• example

  SELECT quarter(payment_date) quarter,
         monthname(payment_date) month_nm,
         sum(amount) monthly_sales,
         rank() OVER (partition by quarter(payment_date) 
             order by sum(amount) desc) qrt_sales_rank
  FROM payment
  WHERE year(payment_date) = 2005
  GROUP BY quarter(payment_date), monthname(payment_date)
  ORDER BY 1;