如何将客户分组到相等的收入组中?

Question

真的很难想出这个答案。有各种提供 Ntiles 的功能。通常，它们为每个 ntile 提供相等的行集。

我需要按(近似(相等的收入集对客户进行分组。通过这种方式，我可以向负责收入前 10% 和接下来 10%

等的客户显示换句话说，所有客户都按收入排序，并分组到收入的十分位数。

create table test_set (name text, revenue numeric);
insert into test_set values ('a1',234.34);
insert into test_set values ('a2',263.60);
insert into test_set values ('a3',316.99);
insert into test_set values ('a4',351.68);
insert into test_set values ('a5',353.07);
insert into test_set values ('a6',406.33;
insert into test_set values ('a7',432.78);
insert into test_set values ('a8',444.88);
insert into test_set values ('a9',452.21);
insert into test_set values ('a10',543.75);
insert into test_set values ('a11',582.43);
insert into test_set values ('a12',670.98);
insert into test_set values ('a13',720.46);
insert into test_set values ('a14',801.69);
insert into test_set values ('a15',803.92);
insert into test_set values ('a16',821.03);
insert into test_set values ('a17',875.57);
insert into test_set values ('a18',930.95);
insert into test_set values ('a19',973.49);
insert into test_set values ('a20',1055.48);
insert into test_set values ('a21',1059.01);
insert into test_set values ('a22',1131.09);
insert into test_set values ('a23',1215.51);
insert into test_set values ('a24',1232.19);
insert into test_set values ('a25',1310.59);
insert into test_set values ('a26',1353.31);
insert into test_set values ('a27',1368.28);
insert into test_set values ('a28',1412.58);
insert into test_set values ('a29',1412.38);
insert into test_set values ('a30',1489.78);
insert into test_set values ('a31',1496.31);
insert into test_set values ('a32',1587.17);
insert into test_set values ('a33',3629.34);
insert into test_set values ('a34',4642.01);
insert into test_set values ('a35',6682.07);
insert into test_set values ('a36',752.70);
insert into test_set values ('a37',3819.59);
insert into test_set values ('a38',2857.22);
insert into test_set values ('a39',873.45);
insert into test_set values ('a40',882.42);

希望有人能帮忙...谢谢

Answer 1

使用累积SUM()窗口函数可能会有所帮助：

演示：数据库<>小提琴

select
name, revenue,
sum(revenue) OVER () as total,
sum(revenue) OVER (ORDER BY revenue) as cumulative,
(sum(revenue) OVER (ORDER BY revenue) / sum(revenue) OVER () * 100) as cumulative_procent,
trunc((sum(revenue) OVER (ORDER BY revenue) / sum(revenue) OVER () * 100) / 10) as group_by_tenth
from test_set
order by revenue

3. 列：计算总收入总和
4. 列：计算按收入排序的累计 SUM
5. 列：采用两个术语来计算递增的 procent 值
6. 列：对于聚类，您可以将此结果截断为第十部分

Answer 2

S-MAN - 就是这样...非常感谢。我略微增强

ceil(((sum(sum(revenue)) OVER (ORDER BY sum(revenue)) / sum(sum(revenue)) OVER () * 100) /10)) as revenue_sets