选择分区的一半作为 sum()

Question

我有一个查询，它正在计算分区的总和，并按类别给我一个运行总计。 这部分效果很好，现在，我只想通过分区的前 50% 的总和。 也许表格示例将显示：

╔═══════╦══════════════════════════╦════════════════════════════╗
║ col_1 ║ sum of partition by      ║ sum of 50% of partition by ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 1     ║ 36  (this is 1+2+3+...8) ║ 10 (1+2+3+4)               ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 2     ║ 35 (this is 2+3+4+....8) ║ 9 (2+3+4)                  ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 3     ║ 34                       ║ 7 (3+4)                    ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 4     ║ 33                       ║ 4                          ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 5     ║ 32                       ║ null                       ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 6     ║ 31                       ║ null                       ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 7     ║ 30                       ║ null                       ║
╠═══════╬══════════════════════════╬════════════════════════════╣
║ 8     ║ 29                       ║ null                       ║
╚═══════╩══════════════════════════╩════════════════════════════╝

现在我正在做

sum(col_)  over(partition by <another col> order by <a third col>) as [sum of partition by ]

然后我稍后需要为这个计算添加另一列超过 25%，以便您了解。

Answer 1

可以通过枚举行和筛选来使用条件逻辑。 以下内容使用标准 SQL 语法：

select x,
sum(x) over (order by x desc),
sum(x) filter (where seqnum <= 0.5 * cnt) over (order by x desc),
sum(x) filter (where seqnum <= 0.25 * cnt) over (order by x desc)
from (select x, count(*) over () as cnt,
row_number() over (order by x) as seqnum
from generate_series(1, 8, 1) gs(x) 
) x
order by x;

这是一个数据库<>小提琴。

虽然是标准的，但Postgres是唯一支持filter的数据库。 逻辑可以很容易地替换为sum(case . . .)。

下面是一个使用 SQL Server 的数据库<>小提琴。 对应的代码为：

with gs as (
select 1 as x union all
select x + 1 from gs where x < 8
)
select x,
sum(x) over (order by x desc),
sum(case when seqnum <= 0.5 * cnt then x end) over (order by x desc),
sum(case when seqnum <= 0.25 * cnt then x end) over (order by x desc)
from (select x, count(*) over () as cnt,
row_number() over (order by x) as seqnum
from gs
) x
order by x;