主页 / dba / 问题 / 245962
Accepted
Manngo
Asked: 2019-08-22 21:58:57 +0800 CST

更智能的 ntile

  • 772

使用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 的行为，并且可能是其余的行为。

问题是，有没有办法做到这一点？

sql-server postgresql

3 个回答

  1. Best Answer

    您可以使用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 最终为空。

    • 5

  2. 您可以模拟 WIDTH_BUCKET 函数：

    CREATE FUNCTION dbo.width_bucket(@val decimal, @min_val decimal, @max_val decimal, @groups int)
RETURNS int
AS
BEGIN
    DECLARE @res int;

    IF @val = @max_val RETURN @groups;

    RETURN CAST((@val - @min_val) / ((@max_val - @min_val) / @groups) AS int) + 1;
END

SELECT
    id, title, price, dbo.width_bucket(price, 11.0, 20.0, 10)
FROM
    t;

    或者没有函数：

    DECLARE @groups int = 10;

WITH mm AS
(
    SELECT MIN(price) AS min_price, MAX(price) AS max_price
    FROM   t
)
SELECT
    id, title, price,
    CASE
        WHEN 
            price = max_price THEN @groups
        ELSE
            CAST((price - min_price) / ((max_price - min_price) / @groups) AS int) + 1
    END as ngroup
FROM
    t
CROSS APPLY (SELECT min_price, max_price FROM mm) mm;

    注意我已经将价格类型从 int 更改为 decimal。

    编号 | 标题 | 价格 | 组
-: | :-------------------------------------------- | :---- | -----：
19 | 双鱼片，喜欢 jaune et rouge | 11.00 | 1
17 | 投手鲜花| 高分辨率照片| CLIPARTO 12.00 | 2
 5 | 红黄蓝组合 | 12.00 | 2
18 | La lecon de musique (音乐课) | 12.00 | 2
 9 | 贤士的崇拜 | 12.00 | 2
29 | 自画像 | 14.00 | 4
25 | 白色交响曲第一号：白人女孩 | 14.00 | 4
30 | Nicolaes Tulp 博士的解剖学讲座 | 14.00 | 4
20 | Les repassuses (女熨烫) | 14.00 | 4
 1 | 金星的诞生 | 15.00 | 5
12 | Femme se promenant dans une foret exotique | 15.00 | 5
24 | 画家母亲的肖像| 高分辨率照片| CLIPARTO 15.00 | 5
28 | Jeunes Filles au 钢琴 | 15.00 | 5
 7 | 艺术家肖像（自画像） | 16.00 | 6
 3 | 最后的晚餐 | 16.00 | 6
13 | 老虎和水牛的战斗 | 16.00 | 6
 4 | 人的创造| 17.00 | 7
22 | Le Chemin de Fer | 17.00 | 7
 6 | Femmes de Tahiti [Sur la plage] | 18.00 | 8
21 | Le Bar aux Folies-Berg | 18.00 | 8
26 | 钢琴前的女士 | 18.00 | 8
15 | 花园的纪念| 18.00 | 8
16 | 1914 | 18.00 | 8
14 | 古老的声音，黑色的抽象| 高分辨率照片| CLIPARTO 19.00 | 9
 8 | 大草坪 | 19.00 | 9
23 | 在海滩上 | 19.00 | 9
 2 | 蒙娜丽莎的肖像| 高分辨率照片| CLIPARTO 19.00 | 9
27 | 在露台上 | 20.00 | 10
10 | 母狼 | 20.00 | 10

    db<>在这里摆弄

    • 1

  3. 我已经接受了一个答案，但我想说明我是如何将答案付诸实践的。

    WITH data AS (SELECT count(*)/10.0 AS bin FROM prints WHERE price<20)
SELECT
    id,title,price,
    row_number() OVER(ORDER BY price) AS row_number,
    ntile(10) OVER(ORDER BY price) AS decile,
    floor((row_number() OVER(ORDER BY price)-1)/bin)+1 AS row_decile,
    floor((rank() OVER(ORDER BY price)-1)/bin)+1 AS rank_decile,
    floor((count(*) OVER(ORDER BY price)-1)/bin)+1 AS count_decile,
    bin
FROM prints,data
WHERE price<20
ORDER BY price;

    兴趣点：

    • 我已将 bin 大小放入 CTE 而不是变量中，以使结果更加 DB 中性
    • 该WHERE子句只是强制一个尴尬的 bin 大小。
    • 这10.0是生成小数而不是截断的整数。
    • 包括row_number()/bin+1计算以复制本机ntile功能。

    当然，当行数相对较少时，存在丢失十分位数的风险，但至少它将相同的值保持在一起。

    现在的问题是决定哪种选择更符合自己的口味。

    小提琴：http ://sqlfiddle.com/#!17/8bb42/1

    • 0

相关问题