簡單 Coding

NTILE

NTILE 會依照參數將 SELECT 查詢結果數，切割成幾等份，然後给每一 ROW 資料都給予一個群組編號，組群編號由 OVER ( ORDER BY 欄位) 來排序，另外在Oracle 11g也能當作用百分比來取得查詢結果。

 

以下範例使用 SSMS，

1.先建立方便測試用的暫存表與假資料，

DECLARE @temp TABLE(
	ID INT,
	PRICE INT
);

DECLARE @i INT
DECLARE @Length INT
DECLARE @num INT
SET @i = 1
SET @Length = 10
SET @num = 100;

WHILE (@i <= @Length)
	BEGIN

	INSERT INTO @temp VALUES (@i, @num);

	SET @i = @i+1
	SET @num = @num -10;
	END

2.先練習一下將查詢結果分成四等份

SELECT *, 
		NTILE(4) OVER ( ORDER BY PRICE ) AS PRICE_NTILE
FROM @temp A

執行結果：

3.接著算出平均金額與前75%結果的平均金額

SELECT AVG(B.PRICE) AS PRICE_AVG , AVG(CASE WHEN B.PRICE_NTILE <=3 THEN B.PRICE END) PRICE_75AVG
FROM (
	SELECT *, 
	NTILE(4) OVER ( ORDER BY PRICE ) AS PRICE_NTILE
	FROM @temp A
) B

執行結果：

參考來源: https://msdn.microsoft.com/zh-tw/library/ms175126(v=SQL.120).aspx