时间戳

首页 > 解决方案 > sql如何将增量显示为总增量的一部分

sql - sql如何将增量显示为总增量的一部分

问题描述

假设我有以下 sql 数据

species         date         observations
Bird1           08-09-19          40
Bird1           06-10-19          50
Bird1           11-11-19          60
Bird2           08-09-19          50
Bird2           06-10-19          90
Bird3           06-10-19          10
Bird3           11-11-19          20

并假设我想显示一个月和一种鸟类，观察的增量变化是什么（相对于上个月），作为该月鸟类观察总增量的一小部分。给定示例数据，我想要以下结果。

species         date         observations      increment_fraction
Bird1           08-09-19          40                    0
Bird1           06-10-19          50                    0.2
Bird1           11-11-19          60                    0.5
Bird2           08-09-19          50                    0
Bird2           06-10-19          90                    0.8
Bird3           06-10-19          10                    0
Bird3           11-11-19          20                    0.5

让我解释一下这些结果。与日期对应的增量分数08-09-19为 0，因为没有更早的记录可用。第二行的增量分数为 0.2，因为在日期和之间的观测值的总增量是 50，而Bird1在和之间的增量变化是 10。增量分数是 10/50 = 0.2。08-09-1906-10-1908-09-1906-10-19

第三行也是如此：日期和之间的总增量是 20，而Bird1的日期和之间的增量是 10。增量分数是 10/20 = 0.5。06-10-1911-11-1906-10-1911-11-19

以下查询将为我提供所需的结果：

with increments_table as (
select species, date, observations, 
observations - lag(observations, 1, observations) over (partition by species 
order by date) as increment
from species_table),

increment_sums as (
select date, sum(increment) as increment_sum
from increments_table
group by date)

select species, date, observations, increment/increment_sum
from increments_table
join increment_sums
on increments_table.date = increment_sums.date

但我想知道这是否可以更紧凑一些。我认为它可以更紧凑，因为它是一个非常基本的操作，但我不确定如何。

问题：有没有办法缩短这个时间？

标签： sqldatabasepresto

解决方案

似乎您用来计算增量的基数始终是第一个可用数据点，在这种情况下，一步解决方案将是：

select species, date, observations, 
(observations / first_value(observations) over (partition by species order by date)) - 1 as increment_fraction
from species_table

当然，如果您的observations列是整数，那么您可能需要将其转换为 afloat/double以便您可以获得十进制小数值。

推荐阅读