Way to Use Window Function to Find the Duration a Temperature Variable was Above a Threshold
I want to find a way to use a window function to find the duration a temperature variable was above a threshold, the timestamp at which the the temperature was above a threshold, and the range of temperatures when they were above the threshold.
The data is organized as an identifier column, the timestamp column, and the temperature column. Below is an example of the data that I am working with.
Let's say that my threshold temperature is 50. I would thus want to find the time until the temperature goes back below 50, find the timestamp when the temperature was first above the threshold, find the range of these temperatures as the max and min, and find the Identifier that corresponds to the temperature going above the threshold. For example in the above table, I would want the following:
'Identifier: 1, Timestamp of first excursion: 2022-07-08 10:30:00, Temperature Range: 53-59, Excursion Duration: 1 hour 30 minutes'.
This dataset has over 400 million rows so I believe that the windowing function will be much more helpful than Magic ETL.
I am open to any suggestions and please feel free to send me any documentation that you think may be helpful.
i may be missing something here, but I don’t think think I window function is needed.
create a variable for the temperature threshold.
Min(case when ‘temperature’>=variable then ‘temperature’ end)
Do something similar for max.
Min(case when ‘temperature’>=variable then ‘time stamp’ end)
Then graph by identifier
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Hi @ST_-Superman-_ ,
This is not exactly what I am looking for. I want to see how I can lead until a condition occurs so for the above example, I start in row 2 and I want to aggregate until a temperature drops below 50, which would be row 5. From this, I want to gather the first timestamp the temperature was above 50, the first timestamp the temperature drops below 50, the time difference between each of these timestamps, and the maximum and minimum temperatures during the time the temperatures were above 50.
I may have showed a too simplistic example as one identifier could have many temperature excursions above 50.
I thus think that a window function is useful for this problem because once I find out a way to use a lead function based on a condition, I am very confident I could do this.0
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 467 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 80 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 173 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive