如何在不同的資料平臺取前百分之N的記錄?

大雄45 發表於 2022-06-27
導讀 最近幫業務部門梳理業務報表,其中有個需求是就算某指標等待時間最長的前百分之十,其實就是對等待時長進行倒序排序後,取結果集的前百分之十。這個需求在SQL Server和Oracle上都很容易實現,甚至是在MySQL 8.0也很容易實現,只是恰好我們業務資料庫是MySQL 5.7。

先給大家介紹下不同資料庫平臺的實現方法。

SQL Server實現方法

SQL Server上有個TOP Percent的方法可以直接取結果的前(或後)百分之N。

例如有如下一張City表:

如何在不同的資料平臺取前百分之N的記錄?如何在不同的資料平臺取前百分之N的記錄?

我們取前10%的資料記錄可以這樣寫:

SELECT 
TOP 10 PERCENT  
*
FROM City
ORDER BY ID DESC

結果如下:

如何在不同的資料平臺取前百分之N的記錄?如何在不同的資料平臺取前百分之N的記錄?

Oracle實現方法

Oracle有個ROWNUM偽列可以用來幫助我們計算前百分之N。

ROWNUM偽列的特點:

  • ROWNUM是按照記錄插入時的順序排序的。
  • ROWNUM並不實際存在,是對篩選後的結果集的一個排序,如果不存在結果集就不會有ROWNUM。
  • ROWNUM不能用基表名作為字首。
  • 在使用ROWNUM進行查詢時,請注意:

  • 若使用大於號(>),則只能使用(>0),其他均不可以 。
  • 若使用小於號(<),同一般情況 。
  • 若使用等於號(=),則只能使用等於1(=1)我們可以先計算出整個表的記錄行數量。
SELECT COUNT(*) CNT FROM City

然後根據count聚合查詢總條數乘以百分比,來確定要查詢的條數。

SELECT 0.1*COUNT(*) CNT FROM City

最後取出偽列小於共有資料的百分比的資料。

SELECT * FROM CITY
WHERE ID IN
(
SELECT ID FROM 
(
SELECT ID FROM CITY ORDER BY ID DESC
)
WHERE ROWNUM < (SELECT COUNT(*)*0.1 FROM CITY)

注意:Oracle不支援子查詢內ORDER BY,需要在外面再巢狀一層。

MySQL 8.0的實現方法

MySQL 8.0的實現方法主要是藉助視窗函式ROW_NUMBER() OVER()。其實就是給排好序的集合新增一個自增長列,與Oracle的ROWNUM有點類似。

SELECT * FROM
(
SELECT *,
ROW_NUMBER() OVER(ORDER BY ID DESC) rn
FROM  City
ORDER BY ID DESC
) a
WHERE a.rn<=(SELECT 0.1*COUNT(*) FROM City)
MySQL 5.X的實現方法

我們知道MySQL 5.X是沒有開窗函式ROW_NUMBER() OVER()的,那該如何實現呢?

這裡我們需要藉助變數來實現,其實思路還是建立一個自增長列,只是方法不同。

SELECT 
  A.*,
  @row_num:[email protected]_num+1 AS ROW_NUM
FROM 
  City A , (SELECT @row_num:=0) B
ORDER BY ID DESC

這樣我們就可以得到一張有自增長列的結果集了,接下來還是按照上面類似的方法,取前10%即可。

SELECT * FROM
(
SELECT 
  A.*,
  @row_num:[email protected]_num+1 AS ROW_NUM
FROM 
  City A , (SELECT @row_num:=0) B
ORDER BY ID DESC
) C
WHERE C.ROW_NUM<=(@row_num*0.1)

其實MySQL 5.X也挺簡單的,只是當時不怎麼想用變數,想看看有沒有其他辦法,最後發現還是得用變數。

以上就是不同平臺的資料庫求前百分之N的方法了,程式碼可以驗證一下收藏起來留著下次直接套用。

總結

其中有涉及一些知識點,需要小夥伴們自己去進一步瞭解:

  • SQL Server的TOP PERCENT。
  • Oracle的ROWNUM,子查詢排序。
  • ROW_NUMBER() OVER()。
  • MySQL的變數。
  • 原文來自:

    來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69955379/viewspace-2902896/,如需轉載,請註明出處,否則將追究法律責任。