SQL按值对人口进行排序并按值分组

Question

我必须创建一个报告。我很难弄清楚如何处理它。最重要的是，我没有合适的词汇来表达它，因此寻找解决方案。请多多包涵。

我有很多帐户。帐户必须按值(value)排序。总值(value)底部 5% 的帐户被放置在一个组中(组 #5)。其余 95% 的人口按值(value)(而不是账户数量)分为四个相等的组(组 #1-4)。

帐户的值(value)会随着时间而变化，因此结果也会随着时间而变化。我希望产生这样的输出......

ACC#  |VALUE|GROUP|
------+-----+-----+
2615A | 24  | 1
0793A | 24  | 2
0652A | 12  | 3
6758A | 12  | 3
7764A |  6  | 4
8718A |  6  | 4
0155A |  6  | 4
6923A |  5  | 4
8079A |  3  | 5
2265A |  1  | 5
7421A |  1  | 5

我可以选择在 SQL Server 或 Oracle(11g) 中运行它。无论哪个让我越过终点线。
提前致谢。

Answer 1

我会用 row_number()和 count()窗口函数:

select t.*,
       (case when seqnum <= (cnt * 0.95 * 0.25) then 1
             when seqnum <= (cnt * 0.95 * 0.50) then 2
             when seqnum <= (cnt * 0.95 * 0.75) then 3
             when seqnum <= (cnt * 0.95 * 1.00) then 4
             else 5
        end) as grp
from (select t.*,
             row_number() over (order by value desc, acc) as seqnum,
             count(*) over () as cnt
      from t
     ) t;

注意:具有相同值的行可以在不同的组中——如您的示例数据。如果您不希望出现这种情况，请使用 rank()而不是 row_number() .

编辑:

如果您想要相等的值(value)，只需使用累积总和和总计:

select t.*,
       (case when running_value <= (total_value * 0.95 * 0.25) then 1
             when running_value <= (total_value * 0.95 * 0.50) then 2
             when running_value <= (total_value * 0.95 * 0.75) then 3
             when running_value <= (total_value * 0.95 * 1.00) then 4
             else 5
        end) as grp
from (select t.*,
             sum(value) over (order by value desc, acc) as running_value,
             sum(value) over () as total_value
      from t
     ) t;