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 all the way to
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. Please
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 883da112a1fffff 4 2020-10-13 15:43:58.887592 28.649227 77.339063 883da1a941fffff and I want to convert it like this created_at hex_id count 0 2020-10-28 22:00:00 883da11185fffff 4 1 2020-09-09 10:00:00
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 in that cohort), and avg_revenue (the
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 columns, even if the underlying dataset has some
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 …