Reputation: 374
Get rows with some value next to threshold
I'm processing data from some simulations and I need some support with SQL to get the data I need. Having a reference table with some times, I need to retrieve the state of the simulation at this particular time: this will be the row with nearest (but below) simulation time to the reference time.
Having this example
Simulation TimeS P1 P2 P2F P3 P3F P4 P4F P5 P5F P6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 0.0 1 0 0 0 0 0 0 0 0 0
1 0.0 0 1 0 0 0 0 0 0 0 0
1 28.0 0 1 0 0 0 0 0 0 0 0
1 28.0 0 1 0 0 0 0 0 0 0 0
1 2190.0 0 1 0 0 0 0 0 0 0 0
1 2190.33333 0 1 0 0 0 0 0 0 0 0
2 0.0 1 0 0 0 0 0 0 0 0 0
2 0.0 0 1 0 0 0 0 0 0 0 0
2 28.0 0 1 0 0 0 0 0 0 0 0
2 28.0 0 1 0 0 0 0 0 0 0 0
2 817.859662 0 0 1 0 0 0 0 0 0 0
2 840.0 0 0 1 0 0 0 0 0 0 0
2 840.0 0 0 1 0 0 0 0 0 0 0
2 840.0 0 0 1 0 0 0 0 0 0 0
2 840.0 0 0 1 0 0 0 0 0 0 0
2 1018.65247 0 0 0 1 0 0 0 0 0 0
2 1036.0 0 0 0 1 0 0 0 0 0 0
2 1036.0 0 0 0 1 0 0 0 0 0 0
2 2190.0 0 0 0 1 0 0 0 0 0 0
2 2190.0 0 0 0 1 0 0 0 0 0 0
2 2190.0 0 0 0 1 0 0 0 0 0 0
2 2190.04166 0 0 0 1 0 0 0 0 0 0
2 2190.20833 0 0 0 1 0 0 0 0 0 0
2 2190.25 1 0 0 0 0 0 0 0 0 0
2 2190.25 0 1 0 0 0 0 0 0 0 0
2 2190.33333 0 1 0 0 0 0 0 0 0 0
2 2212.0 0 1 0 0 0 0 0 0 0 0
2 2212.0 0 1 0 0 0 0 0 0 0 0
2 2472.94974 0 0 1 0 0 0 0 0 0 0
2 2492.0 0 0 1 0 0 0 0 0 0 0
2 2492.0 0 0 1 0 0 0 0 0 0 0
2 2492.0 0 0 1 0 0 0 0 0 0 0
2 2492.0 0 0 1 0 0 0 0 0 0 0
and some reference times
value
----------
0
365
730
1095
1460
1825
2190
2555
2920
I need a new table with only the values from the reference table (in this time 9 rows only) with this output
Simulation TimeV P1 P2 P2F P3 P3F P4 P4F P5 P5F P6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 0.0 0 1 0 0 0 0 0 0 0 0
1 365.0 0 1 0 0 0 0 0 0 0 0
1 730.0 0 1 0 0 0 0 0 0 0 0
1 1095.0 0 1 0 0 0 0 0 0 0 0
1 1460.0 0 1 0 0 0 0 0 0 0 0
1 1825.0 0 1 0 0 0 0 0 0 0 0
1 2190.0 0 1 0 0 0 0 0 0 0 0
2 0.0 0 1 0 0 0 0 0 0 0 0
2 365.0 0 0 1 0 0 0 0 0 0 0
2 730.0 0 0 1 0 0 0 0 0 0 0
...
I have tried this, I think the approach is near to the actual solution but is not working as I need. generate_series is a SQLite module I imported to my session to generate a table with numbers from 0 to 3000 with 365 step.
I have limited the output with the where clause.
select P.Simulation, S.value as TimeV, P.Time as TimeS,
"P.Track.Initial" as P1,
"P.Track.Good.U" as P2, "P.Track.Good.Finishing.U" as P2F ,"P.Track.Satisfactory.U" as P3, "P.Track.Satisfactory.Finishing.U" as P3F,
"P.Track.Poor.U" as P4, "P.Track.Poor.Finishing.U" as P4F, "P.Track.VeryPoor.U" as P5, "P.Track.VeryPoor.Finishing.U" as P5F, "P.Track.SuperRed.U" as P6
from generate_series(0, 3000, 365) S
inner join Places_1 P ON TimeV = ( select max(value) from generate_series(0,3000,365) where value <= TimeS )
where P.Simulation <= 2 and TimeS <= 3000
I expected to have only 9 rows per simulation, as I only need the state of the system at the reference times. But I'm getting repeated values for the reference time table...
Simulation TimeV TimeS P1 P2 P2F P3 P3F P4 P4F P5 P5F P6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 0 0.0 1 0 0 0 0 0 0 0 0 0
1 0 0.0 0 1 0 0 0 0 0 0 0 0
1 0 28.0 0 1 0 0 0 0 0 0 0 0
1 0 28.0 0 1 0 0 0 0 0 0 0 0
1 2190 2190.0 0 1 0 0 0 0 0 0 0 0
1 2190 2190.33333 0 1 0 0 0 0 0 0 0 0
...
Upvotes: 0
Views: 60
Answers (2)
Reputation: 374
I got the solution!
select P.Simulation, max(P.Time) as Time, value as RefDay,
"P.Track.Initial" as P1,
"P.Track.Good.U" as P2, "P.Track.Good.Finishing.U" as P2F ,"P.Track.Satisfactory.U" as P3, "P.Track.Satisfactory.Finishing.U" as P3F,
"P.Track.Poor.U" as P4, "P.Track.Poor.Finishing.U" as P4F, "P.Track.VeryPoor.U" as P5, "P.Track.VeryPoor.Finishing.U" as P5F, "P.Track.SuperRed.U" as P6
from Places_1 P, generate_series(0,10950,365) where P.Time <= value group by Simulation,value
With this query I get the data I need. The key is grouping by the reference time.
Upvotes: 0
Reputation: 61
One solution (not the prettiest) is to use unions. This will take a row (or multiple rows) and attach them below one another. Here's how you would do it:
(SELECT [COLUMNS HERE] FROM [TABLE] WHERE TimeS <= [TimeV #1] AND ROWNUM = 1 ORDER BY TimeS DESC)
UNION
(SELECT [SAME COLUMNS] FROM [TABLE] WHERE TimeS <= [TimeV #2] AND ROWNUM = 1 ORDER BY TimeS DESC)
UNION
...
Repeat this for each of the values of TimeV you need.
Upvotes: 1
Related Questions
- Selecting all rows that have a value in the top N rows
- Using the smaller of two values in SQL condition
- SQL query - limit resulting query rows based on non sorted value
- SQLite: LIMIT values depending on a condition
- Getting rows that has max value
- How do I select rows with maximum value?
- SELECT rows from SQLite Database where condition met with limit return rows with smallest IDs
- How do I select rows in an Sqlite table with a limit on the number of rows with a condition?
- How to select all records where ANY two columns are above a certain value
- select where columnx is less than multiple columns based on row value