时间戳

首页 > 解决方案 > 如何聚合列并计算 SQL 中的累积总和（R 中的 mutate() 和 cumsum()）

sql - 如何聚合列并计算 SQL 中的累积总和（R 中的 mutate() 和 cumsum()）

问题描述

数据如下：

road_id 数数 厘米
路111 123 502
路123 345 234
路124 3256 23498

我的目标是计算“体积公里交通”，计算每行的百分比，按计数排序，并计算累积总和。我知道如何在 R 中做到这一点，但很难在 SQL 中重现。

为了实现这一点，我在 R 中的代码是（它工作正常）：

data_perc <- data %>% 
             mutate(kilometers = centimeters/100000) %>%
             mutate(vkt = count*kilometers) %>%
             mutate(vktperc = vkt/sum(vkt)) %>%
             arrange(desc(count)) %>%
             mutate(vktcumsum = cumsum(vktperc))

在 SQL 中，我尝试了这个但有错误：

select road_id, count, geom, centimeters/100000 as kilometers, cap1.y as vkt, cap2.z as vktperc, sum(vktperc) as vktcumsum
from roaddata rd1
inner join roaddata rd2 on rd1.road_id >= rd2.road_id
cross apply (select count*kilometers as product) cap1(y)
cross apply (select (vkt/sum(vkt))*100 as percentage) cap2(z)
group by road_id, count
order by count desc;

标签： sqlcumulative-sum

解决方案

如果我理解正确，你想要这样的东西：

select rd.*,
       centimeters / 100000 as kilometers, 
       count * centimeters / 100000 as vkt,
       ( sum(count * centimeters / 100000) over (order by count) /
         sum(count * centimeters / 100000) over ()
       ) as vktcumsum      
from roaddata rd;

但是如果没有结果和清晰的解释（不是代码！），很难知道这是否真的是你想要做的。

推荐阅读