Hive開窗函數的使用詳解

一、開窗函數的基本概念

開窗函數是一種在一系列數據上執行計算的函數，它將結果作為一組記錄返回，這些記錄與輸入記錄一起形成一個窗口。這個窗口隨著時間而滑動，每次滑動都會重新計算計算。開窗函數分為兩類：聚合函數和分析函數。

二、聚合函數的使用

聚合函數用於計算一系列數據的集合值，如：平均數、總和、最大值或最小值等。常用的聚合函數有：SUM、AVG、COUNT、MAX和MIN。

SELECT COUNT(*) FROM table_name;
SELECT AVG(column_name) FROM table_name;
SELECT SUM(column_name) FROM table_name;

除了基本的聚合函數之外，開窗函數還可以使用窗口函數。窗口函數是一種計算函數，它在某個開窗函數上執行計算，並返回一個值。

SELECT column_name, SUM(column_name) OVER () FROM table_name;
SELECT column_name, COUNT(column_name) OVER (PARTITION BY column2_name) FROM table_name;

三、分析函數的使用

分析函數用於在一組記錄上執行複雜計算，如：排序、排名、分布和累加等。常用的分析函數有：ROW_NUMBER、RANK、DENSE_RANK、NTILE和LEAD/LAG。

ROW_NUMBER函數用於返回一個數值，表示結果集中每行第幾個。RANK和DENSE_RANK函數用於返回結果集中每行的排名，並為具有相同值的行分配相同的排名。NTILE函數用於將結果集分成指定數量的桶，並返回桶號。

SELECT column_name, ROW_NUMBER() OVER (ORDER BY column_name) FROM table_name;
SELECT column_name, RANK() OVER (ORDER BY column_name DESC) FROM table_name;
SELECT column_name, NTILE(3) OVER (ORDER BY column_name) FROM table_name;

LEAD和LAG函數用於返回當前行後或前幾行的指定列的值。它們可以用於計算增量或計算當前時間點之前的值。

SELECT column_name, LAG(column2_name, 1, 0) OVER (ORDER BY column_name) FROM table_name;
SELECT column_name, LEAD(column2_name, 2, 0) OVER (ORDER BY column_name) FROM table_name;

四、窗口說明符的使用

窗口說明符用於定義給定窗口的邊界。Hive支持以下窗口說明符：UNBOUNDED PRECEDING、N PRECEDING、CURRENT ROW、N FOLLOWING和UNBOUNDED FOLLOWING。

UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING分別表示窗口的開始和結束。CURRENT ROW表示窗口包括當前行。N PRECEDING和N FOLLOWING表示窗口範圍從指定數量的行之前或之後的位置開始。

SELECT column_name, SUM(column2_name) OVER (ORDER BY column_name RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM table_name;
SELECT column_name, AVG(column2_name) OVER (ORDER BY column_name ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) FROM table_name;

五、總結

在Hive中，開窗函數是一種強大的工具，用於在一組記錄上執行複雜的計算。聚合函數用於計算一系列數據的集合值，而分析函數用於在一組記錄上執行複雜計算。在使用窗口函數時，我們還可以定義窗口說明符和排序規則。這些功能可以使我們進行更細粒度和更精確的計算。