Sql-Server
更智能的 ntile
使用
ntile()視窗函式時，主要問題是它任意分組為大致相等的部分，而不管實際值如何。例如，使用以下查詢：
select id,title,price, row_number() over(order by price) as row_number, rank() over(order by price) as rank, count(*) over(order by price) as count, dense_rank() over(order by price) as dense_rank, ntile(10) over(order by price) as decile from paintings order by price;我會得到 10 組大小大致相同的畫作，價格相同的畫很有可能最終會被放入不同的垃圾箱。
例如：
┌────┬────────────────────────────────────────────┬───────┬────────────┬──────┬───────┬────────────┬────────┐ │ id │ title │ price │ row_number │ rank │ count │ dense_rank │ decile │ ╞════╪════════════════════════════════════════════╪═══════╪════════════╪══════╪═══════╪════════════╪════════╡ │ 11 │ Eyes in the Heat │ 10 │ 1 │ 1 │ 1 │ 1 │ 1 │ │ 19 │ Deux fillettes, fond jaune et rouge │ 11 │ 2 │ 2 │ 2 │ 2 │ 1 │ │ 17 │ Flowers in a Pitcher │ 12 │ 3 │ 3 │ 6 │ 3 │ 1 │ │ 5 │ Composition with Red, Yellow and Blue │ 12 │ 4 │ 3 │ 6 │ 3 │ 2 │ │ 18 │ La lecon de musique (The Music Lesson) │ 12 │ 5 │ 3 │ 6 │ 3 │ 2 │ │ 9 │ The Adoration of the Magi │ 12 │ 6 │ 3 │ 6 │ 3 │ 2 │ │ 29 │ Self-Portrait │ 14 │ 7 │ 7 │ 10 │ 4 │ 3 │ │ 25 │ Symphony in White, No. 1: The White Girl │ 14 │ 8 │ 7 │ 10 │ 4 │ 3 │ │ 30 │ The Anatomy Lecture of Dr. Nicolaes Tulp │ 14 │ 9 │ 7 │ 10 │ 4 │ 3 │ │ 20 │ Les repasseuses (Women Ironing) │ 14 │ 10 │ 7 │ 10 │ 4 │ 4 │ │ 1 │ The Birth of Venus │ 15 │ 11 │ 11 │ 14 │ 5 │ 4 │ │ 12 │ Femme se promenant dans une foret exotique │ 15 │ 12 │ 11 │ 14 │ 5 │ 4 │ │ 24 │ Portrait of the Painter’s Mother │ 15 │ 13 │ 11 │ 14 │ 5 │ 5 │ │ 28 │ Jeunes filles au piano │ 15 │ 14 │ 11 │ 14 │ 5 │ 5 │ │ 7 │ Portrait de l artiste (Self-portrait) │ 16 │ 15 │ 15 │ 17 │ 6 │ 5 │ │ 3 │ The Last Supper │ 16 │ 16 │ 15 │ 17 │ 6 │ 6 │ │ 13 │ Combat of a Tiger and a Buffalo │ 16 │ 17 │ 15 │ 17 │ 6 │ 6 │ │ 4 │ The Creation of Man │ 17 │ 18 │ 18 │ 19 │ 7 │ 6 │ │ 22 │ Le Chemin de Fer │ 17 │ 19 │ 18 │ 19 │ 7 │ 7 │ │ 6 │ Femmes de Tahiti [Sur la plage] │ 18 │ 20 │ 20 │ 24 │ 8 │ 7 │ │ 21 │ Le Bar aux Folies-Berg │ 18 │ 21 │ 20 │ 24 │ 8 │ 7 │ │ 26 │ Lady at the Piano │ 18 │ 22 │ 20 │ 24 │ 8 │ 8 │ │ 15 │ Remembrance of a Garden │ 18 │ 23 │ 20 │ 24 │ 8 │ 8 │ │ 16 │ 1914 │ 18 │ 24 │ 20 │ 24 │ 8 │ 8 │ │ 14 │ Ancient Sound, Abstract on Black │ 19 │ 25 │ 25 │ 28 │ 9 │ 9 │ │ 8 │ The Large Turf │ 19 │ 26 │ 25 │ 28 │ 9 │ 9 │ │ 23 │ On the Beach │ 19 │ 27 │ 25 │ 28 │ 9 │ 9 │ │ 2 │ Portrait of Mona Lisa │ 19 │ 28 │ 25 │ 28 │ 9 │ 10 │ │ 27 │ On the Terrace │ 20 │ 29 │ 29 │ 30 │ 10 │ 10 │ │ 10 │ The She-Wolf │ 20 │ 30 │ 29 │ 30 │ 10 │ 10 │ └────┴────────────────────────────────────────────┴───────┴────────────┴──────┴───────┴────────────┴────────┘請注意，有四個項目 price
12，但其中兩個在有十分位數 1 中，其中兩個在十分位數 2 中。我想把這些項目放在一起，我不關心哪個十分位數。我已經包含了其他視窗函式來進行比較。
似乎
ntile()使用row_number()唯一的並以此為基礎。如果它使用rank()orcount(*)函式會更公平，因為價格相同的物品最終會放在同一個 bin 中。這是 PostgreSQL 和 SQL Server 的行為，並且可能是其餘的行為。
問題是，有沒有辦法做到這一點？
您可以使用
rank()並使用每個 bin 的行數進行整數除法。declare @T table(id int, title varchar(100), price int); insert into @T(id, title, price) values (19, 'Deux fillettes, fond jaune et rouge ', 11), (17, 'Flowers in a Pitcher ', 12), (5 , 'Composition with Red, Yellow and Blue ', 12), (18, 'La lecon de musique (The Music Lesson) ', 12), (9 , 'The Adoration of the Magi ', 12), (29, 'Self-Portrait ', 14), (25, 'Symphony in White, No. 1: The White Girl ', 14), (30, 'The Anatomy Lecture of Dr. Nicolaes Tulp ', 14), (20, 'Les repasseuses (Women Ironing) ', 14), (1 , 'The Birth of Venus ', 15), (12, 'Femme se promenant dans une foret exotique ', 15), (24, 'Portrait of the Painter’s Mother ', 15), (28, 'Jeunes filles au piano ', 15), (7 , 'Portrait de l artiste (Self-portrait) ', 16), (3 , 'The Last Supper ', 16), (13, 'Combat of a Tiger and a Buffalo ', 16), (4 , 'The Creation of Man ', 17), (22, 'Le Chemin de Fer ', 17), (6 , 'Femmes de Tahiti [Sur la plage] ', 18), (21, 'Le Bar aux Folies-Berg ', 18), (26, 'Lady at the Piano ', 18), (15, 'Remembrance of a Garden ', 18), (16, '1914 ', 18), (14, 'Ancient Sound, Abstract on Black ', 19), (8 , 'The Large Turf ', 19), (23, 'On the Beach ', 19), (2 , 'Portrait of Mona Lisa ', 19), (27, 'On the Terrace ', 20), (10, 'The She-Wolf ', 20); declare @BinCount int = 10; declare @BinSize int; select @BinSize = 1 + count(*) / @BinCount from @T; select T.id, T.title, T.price, 1 + rank() over(order by T.price) / @BinSize as decile from @T as T;結果：
id title price decile --- ------------------------------------------- ------ -------------------- 19 Deux fillettes, fond jaune et rouge 11 1 17 Flowers in a Pitcher 12 1 5 Composition with Red, Yellow and Blue 12 1 18 La lecon de musique (The Music Lesson) 12 1 9 The Adoration of the Magi 12 1 29 Self-Portrait 14 3 25 Symphony in White, No. 1: The White Girl 14 3 30 The Anatomy Lecture of Dr. Nicolaes Tulp 14 3 20 Les repasseuses (Women Ironing) 14 3 1 The Birth of Venus 15 4 12 Femme se promenant dans une foret exotique 15 4 24 Portrait of the Painter’s Mother 15 4 28 Jeunes filles au piano 15 4 7 Portrait de l artiste (Self-portrait) 16 5 3 The Last Supper 16 5 13 Combat of a Tiger and a Buffalo 16 5 4 The Creation of Man 17 6 22 Le Chemin de Fer 17 6 6 Femmes de Tahiti [Sur la plage] 18 7 21 Le Bar aux Folies-Berg 18 7 26 Lady at the Piano 18 7 15 Remembrance of a Garden 18 7 16 1914 18 7 14 Ancient Sound, Abstract on Black 19 9 8 The Large Turf 19 9 23 On the Beach 19 9 2 Portrait of Mona Lisa 19 9 27 On the Terrace 20 10 10 The She-Wolf 20 10我並不在意哪個十分位
請注意，帶有樣本數據的 bin 2 和 8 最終為空。