row_number函數用法

1、函數講解2、LeetCode實戰

1、函數講解

(相關資料圖)

語法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

簡單的說，row_number()從1開始，為每條分組記錄返回一個數字，舉例：

ROW_NUMBER() OVER(ORDER BY xlh DESC)

這里的用法是先將xlh列進行降序排序，再將降序后的每條記錄返回一個序號。

row_number() OVER (PARTITION BY COL1 ORDER BY COL2)表示根據COL1分組，在分組內部根據COL2排序，而此函數計算的值就表示每組內部排序后的順序編號（組內連續的唯一的）

初始數據顯示為： 我們的目標是根據部門進行分組，并顯示每個部門的工資等級 SQL語句：

select * ,Row_number() OVER(partition by deptid order by salary DESC) rankFORM employee

最終顯示結果為：

2、LeetCode實戰

題目是LeetCode上的題目： 編寫一個sql查詢，查找所有至少連續出現三次的數字。返回的結果表中的數據可以按任意順序排列。 查詢格式如下面的例子所示： 下面是具體是解題代碼：

select distinct Num as ConsecutiveNums from(    select Num,count(1) as SerialCount from    (select id,Num,        row_number() over(order by id) -         row_number() over(partition by Num order by Id) as SerialNumberSubGroup        from Logs    ) as sub     group by Num,SerialNumberSubGroup    having count(1) >= 3    ) as res

上面的解題是參照題解里以為大佬的解題思路： 具體解題過程參照： 作者：neilsons 鏈接：https://leetcode-cn.com/problems/consecutive-numbers/solution/sql-server-jie-fa-by-neilsons/ 原始數據：

1、對原始數據編號，從1開始使用 row_number() over(表達式) 函數，使用Id來排序既row_number()

SELECT Id,Num,row_number() over(order by id) as SerialNumFROM ContinueNumber

2、使用原始數據另一維度排序，這些num值一樣的分組排序，然后對其編號同樣使用row_number() over(表達式)，參數：（num分組，id排序）row_number() over(partition by num order by id)

SELECT Id,Num,ROW_NUMBER() over(partition by Num order by Id) as SerialGroupFROM ContinueNumber

3、通過上述1和上述2 看一下有什么規律嗎？兩個列（SerialNum，SerialGroup）對應相減，只要連續，相減得到的值是一樣的。不連續相減得到的值也不同。

SELECT Id,Num,      row_number() over(order by id) -      row_number() over(partition by Num order by Id) as SerialNumberSubGroup      FROM ContinueNumber

4、通過上述3，通過列Num和列SerialNumberSubGroup分組，最后拿到Num,就是求得的數據,去重（distinct）指：有可能同一個數字在多處出現三次以上。

SELECT DISTINCT Num FROM (SELECT Num,COUNT(1) as SerialCount FROM (SELECT Id,Num,row_number() over(order by id) -ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroupFROM ContinueNumber) as SubGROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result

這個解題的核心是要想明白一個結論：如果一個num連續出現時，那么它出現的【真實序列】-它出現的次數一定是個定值。因為：

1、假設一個num出現后，它的 真實序列 為 i ，同時假設它是第 k 次出現的； 差值為 i-k.2、當它連續出現一次時，它的 真實序列 一定為 i+1 ; 它的出現次數顯然也會+1，為 k+1 ; 差值為 i+1-(k+1)=i-k.3、當它連續出現第 n 次時，它的 真實序列 一定為 i+n；它出現的次數為 k+n;差值為 i+n-(k+n)=i-k.4、如果它不連續出現，假設m個其他num出現之后，它又出現了，則它的真實序列為 i+n+m，而出現的次數為 k+n+1;差值為 i-k+m-1 。

所以，上述解題大佬的做法其實就是： 第一步重排的serialNum表示：這個num出現的真實序列，也就是說在原始數據中，它是第幾個出現的； 第二步重排的serialGroup表示：這個num是第幾次出現的； 然后其他的步驟就比較容易想了。