J'ai des données qui ont des valeurs manquantes de manière irrégulière et j'aimerais les convertir avec un certain intervalle avec une interpolation de ligne à l'aide de BigQuery Standard SQL.
Plus précisément, j'ai des données comme celles-ci:
# interpolated with interval of 1 +------+--------------------+ | time | value_interpolated | +------+--------------------+ | 1 | 3.0 | | 2 | 3.5 | | 3 | 4.0 | | 4 | 4.5 | | 5 | 5.0 | | 6 | 3.0 | | 7 | 1.0 | | 8 | 4.5 | | 9 | 8.0 | | 10 | 4.0 | +------+--------------------+
et j'aimerais convertir ce tableau comme suit:
# data is missing irregulary +------+-------+ | time | value | +------+-------+ | 1 | 3.0 | | 5 | 5.0 | | 7 | 1.0 | | 9 | 8.0 | | 10 | 4.0 | +------+-------+
Une solution intelligente pour cela?
Supplément: cette question est similaire à cette question dans stackoverflow mais différente en ce que les données manquent de manière irrégulière.
Merci.
3 Réponses :
Voici un exemple de résolution de ce problème dans Postgresql.
https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=c560dd9a8db095920d0a15834b6768f1
with data
as (select time
,lead(time) over(order by time) as next_time
,value
,lead(value) over(order by time) as next_value
,(lead(value) over(order by time)- value) as val_diff
,(lead(time) over(order by time)- time) as time_diff
from t
)
select *
,generate_series- time as grp
,case when generate_series- time = 0 then
value
else value + (val_diff*1.0/time_diff)*(generate_series-time)*1.0
end as val_grp
from data
cross join UNNEST(GENERATE_ARRAY(time, coalesce(next_time-1,time))) as generate_series
Je pense que la syntaxe serait différente dans BigQuery en utilisant UNNEST et GENERATE_ARRAY comme suit. Vous pouvez essayer.
with data
as (select time
,lead(time) over(order by time) as next_time
,value
,lead(value) over(order by time) as next_value
,(lead(value) over(order by time)- value) as val_diff
,(lead(time) over(order by time)- time) as time_diff
from t
)
select *
,generate_series- time as grp
,case when generate_series- time = 0 then
value
else value + (val_diff*1.0/time_diff)*(generate_series-time)*1.0
end as val_grp
from data
cross join generate_series(time, coalesce(next_time-1,time))
+------+-----------------+-----+-------------------------+
| time | generate_series | grp | val_grp |
+------+-----------------+-----+-------------------------+
| 1 | 1 | 0 | 3.0 |
| 1 | 2 | 1 | 3.500000000000000000000 |
| 1 | 3 | 2 | 4.000000000000000000000 |
| 1 | 4 | 3 | 4.500000000000000000000 |
| 5 | 5 | 0 | 5.0 |
| 5 | 6 | 1 | 3.00000000000000000 |
| 7 | 7 | 0 | 1.0 |
| 7 | 8 | 1 | 4.50000000000000000 |
| 9 | 9 | 0 | 8.0 |
| 10 | 10 | 0 | 4.0 |
+------+-----------------+-----+-------------------------+
Dans BigQuery, vous pouvez générer les lignes supplémentaires pour chaque ligne à l'aide de generate_array() . Ensuite, vous pouvez utiliser lead() pour obtenir des informations de la ligne suivante et un peu d'arithmétique pour l'interpolation:
with t as (
select 1 as time, 3.0 as value union all
select 5 , 5.0 union all
select 7 , 1.0 union all
select 9 , 8.0 union all
select 10 , 4.0
),
tt as (
select t.*,
lead(time) over (order by time) as next_time,
lead(value) over (order by time) as next_value
from t
)
select coalesce(n, tt.time) as time,
(case when n = tt.time or n is null then value
else tt.value + (tt.next_value - tt.value) * (n - tt.time) / (tt.next_time - tt.time)
end) as value
from tt left join
unnest(generate_array(tt.time, tt.next_time - 1, 1)) n
on true
order by 1;
Remarque: vous avez une colonne appelée time qui contient un entier. S'il s'agit vraiment d'un type de données date / heure d'un certain type, je vous suggère de poser une nouvelle question avec des exemples de données plus appropriés et les résultats souhaités - si vous ne voyez pas comment adapter cette réponse.
Ci-dessous, pour BigQuery Standard SQL
#standardSQL
select time,
ifnull(value, start_value + (end_value - start_value) / (end_tick - start_tick) * (time - start_tick)) as value_interpolated
from (
select time, value,
first_value(tick ignore nulls) over win1 as start_tick,
first_value(value ignore nulls) over win1 as start_value,
first_value(tick ignore nulls) over win2 as end_tick,
first_value(value ignore nulls) over win2 as end_value,
from (
select time, t.time as tick, value
from (
select generate_array(min(time), max(time)) times
from `project.dataset.table`
), unnest(times) time
left join `project.dataset.table` t
using(time)
)
window win1 as (order by time desc rows between current row and unbounded following),
win2 as (order by time rows between current row and unbounded following)
)
si appliquer aux exemples de données de votre question - la sortie est
Merci. J'ai résolu avec cette réponse. J'ai posté une autre question liée à ce problème (beaucoup plus difficile), j'aimerais que vous la vérifiiez si cela ne vous dérange pas. Le lien pour la nouvelle question est le suivant: stackoverflow.com/questions/64829772/...
sûr. répondu aussi: o)
quelle est la logique de mettre 3,0 dans le temps = 6.
Merci pour le commentaire. Il est calculé comme la valeur moyenne du temps = 5 (la valeur est 5,0) et du temps = 7 (la valeur est 1,0)
Pourriez-vous expliquer comment vous êtes arrivé aux valeurs 3,5,4,4,5 pour le moment (2,3,4)
Merci. Il s'agit de données d'interépolation entre time = 1 (la valeur est 3) et time = 5 (value is 5.0) linerarly. Ainsi, l'intervalle de 0,5 dans le premier 3,5, 4,0, 4,5 est calculé comme suit: (valeur 5,0 - valeur 3,0) / (temps 5 - temps 1) = 2/4 = 0,5.
Merci, en suivant cette logique, la valeur du temps = 8 devrait-elle être (valeur 8,0 - valeur 1,0) / (heure 9 - heure 7) = 7/2 = 3,5