'Convert event based data to time series
I have inventory events coming into the data warehouse:
| account_id | event_time | item_id | quantity |
|---|---|---|---|
| 1 | 2022-02-01 19:20 | 1 | 2 |
| 1 | 2022-02-05 17:20 | 1 | 4 |
| 1 | 2022-02-09 15:40 | 1 | 3 |
| 1 | 2022-02-22 09:20 | 1 | 0 |
I want to convert it to time series data by filling in the gaps with whatever was the last value:
| account_id | event_date | item_id | quantity |
|---|---|---|---|
| 1 | 2022-02-01 | 1 | 2 |
| 1 | 2022-02-02 | 1 | 2 |
| 1 | 2022-02-03 | 1 | 2 |
| 1 | 2022-02-04 | 1 | 2 |
| 1 | 2022-02-05 | 1 | 4 |
| 1 | 2022-02-06 | 1 | 4 |
| 1 | 2022-02-07 | 1 | 4 |
| 1 | 2022-02-08 | 1 | 4 |
| 1 | 2022-02-09 | 1 | 3 |
| 1 | 2022-02-10 | 1 | 3 |
| 1 | 2022-02-11 | 1 | 3 |
| 1 | 2022-02-12 | 1 | 3 |
| 1 | 2022-02-13 | 1 | 3 |
| 1 | 2022-02-14 | 1 | 3 |
| 1 | 2022-02-15 | 1 | 3 |
| 1 | 2022-02-16 | 1 | 3 |
| 1 | 2022-02-17 | 1 | 3 |
| 1 | 2022-02-18 | 1 | 3 |
| 1 | 2022-02-19 | 1 | 3 |
| 1 | 2022-02-20 | 1 | 3 |
| 1 | 2022-02-21 | 1 | 3 |
| 1 | 2022-02-22 | 1 | 0 |
| 1 | 2022-02-23 | 1 | 0 |
| 1 | 2022-02-24 | 1 | 0 |
| 1 | 2022-02-25 | 1 | 0 |
| 1 | 2022-02-26 | 1 | 0 |
| 1 | 2022-02-27 | 1 | 0 |
| 1 | 2022-02-28 | 1 | 0 |
Can we do this without creating a daily snapshot table? Looking to solve this using SQL than python because we have to build a looker report on top of this. TechStack is BigQuery+Looker
Solution 1:[1]
Consider below approach
select account_id, event_date, item_id, quantity
from (
select *,
lead(event_date) over(partition by account_id, item_id order by event_date) next_event_date
from (
select account_id, date(split(event_time, ' ')[offset(0)]) event_date, item_id, quantity
from your_table
)
), unnest(generate_date_array(event_date, ifnull(next_event_date - 1, '2022-02-28'))) event_date
if applied to sample data in your question - output is
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Mikhail Berlyant |