〇、概述
1、常用網站
2、介紹
像聚合函數一樣對一組資料進行分析并傳回結果
3、與聚合函數的不同
聚合函數将一組資料彙總成單個結果,視窗函數為每一行資料都傳回一個結果
一、視窗函數組成
(一)文法
視窗函數() over(partition by xxx order by yyy frame_clause)
frame_clause:架構子句,即視窗範圍
(二)辨別視窗-over
OVER子句用于指定一個資料分析的視窗,表明是一個視窗函數
括号内為空,表示将所有資料作為一個分組進行彙總
(三)分區-PARTITION BY
PARTITION BY用于定義分區,類似于查詢語句中的GROUP BY子句
(四)排序-ORDER BY
ORDER BY用于指定分區内資料的排序方式,類似于查詢語句中的ORDER BY子句
(五)指定視窗大小
frame_clause選項用于指定一個移動的分析視窗
視窗函數不再基于分區進行分析,而是基于視窗内的資料進行分析。
ROWS表示以資料行為機關計算視窗的偏移量,RANGE表示以數值(例如10天、5km等)為機關計算視窗的偏移量。
frame_start和frame_end分别定義視窗的起始位置和結束位置
視窗大小選項的定義:
如:
ROWS
RANGE INTERVAL '5' DAY PRECEDING
BETWEEN UNBOUNDED PRECEDING
AND
CURRENT ROW
二、視窗函數分類
(一)聚合視窗函數
常見的聚合函數可以做視窗函數,如AVG()、SUM()、COUNT()、MAX()以及MIN()等
(二)排序視窗函數
對資料進行分組排名,包括ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、CUME_DIST()以及NTILE()等函數。
使用視窗函數按照dt升序進行排序
row_number 排序結果是 1、2、3、4……
rank 排序結果是 1、2、2、4……
dense_rank 排序結果是 1、2、2、3……
(三)取值視窗函數
用于傳回指定位置上的資料行,包括FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()、NTH_VALUE()等函數。
如:NTH_VALUE(use_time,2) OVER(PARTITION BY exam_id ORDER BY use_time ASC) AS min_use_time