I have a table of road condition ratings (roads are rated from 1-20; 20 being good). db<>fiddle In a query, for each road, I want to generate rows to fill in the gaps between the years. For a given road, starting at the first row (the earliest inspection), there should be consecutive rows for each year …
Tag: time-series
Track state of time series event in SQL column
This seems really simple but I can’t figure it out, working in SQL Server. I have time series data and I want a column to track the state of ON/OFF events for each row, so when there’s an ON event then the Desired Output column will have a 1 for each subsequent event until there is an OFF event. P…
Is there a way to run posqresql queries in a pandas dataframe?
I have pandas dataframe like this : created_at lat long hex_ID 0 2020-10-13 15:12:18.682905 28.690628 77.323285 883da1ab0bfffff 1 2020-10-12 22:49:05.886170 28.755408 77.112289 883da18e87fffff 2 2020-10-13 15:24:17.692375 28.690571 77.323335 883da1ab0bfffff 3 2020-10-12 23:21:13.700226 28.589922 77.082738 883…
Convert 15min to 10min timeseries in SQL Server
I have a timeseries in a 15min format with four different variables. I need to convert this into 10min timeseries format. Due to some constraints, I need to do this in SQL which I agree is probably …
How do I create cohorts of users from month of first order, then count information about those orders in SQL?
I’m trying to use SQL to: Create user cohorts by the month of their first order Sum the total of all the order amounts bought by that cohort all-time Output the cohort name (its month), the cohort size (total users who made first purchase in that month), total_revenue (all order revenue from the users i…
How to use timebucket_gapfill when rows can have null values?
I have a time series table where measurements are recorded into “wide” rows. Rows may contain all measurements or only some. The other columns are then set to NULL. I would like to use timebucket_gapfill() to “clean” this table and make sure that every row in the output has data in all…
How to pivot time series table without aggregation
I am pulling sensor data from a Teradata table for analysis. Below is what the table looks like. I want to pivot it such that sensor names become columns. There are more than a hundred sensors and …