I have an Oracle SQL view with an ID, TIME_STAMP, LOCATION and a "COMMAND" variable which is used to describe "Time IN", "Time Requested" and "Time Out" as an integer 1, 2, 3, respectively, such as:
| ID | Time | Command | Location |
|---|---|---|---|
| 1 | 00:20:00 | 1 | 51 |
| 2 | 00:22:00 | 1 | 52 |
| 1 | 00:30:00 | 2 | 51 |
| 1 | 00:32:00 | 3 | 51 |
| 2 | 00:40:00 | 2 | 52 |
| 2 | 00:43:00 | 3 | 52 |
| 1 | 00:50:00 | 1 | 52 |
| 1 | 00:52:00 | 2 | 52 |
| 3 | 01:10:00 | 1 | 53 |
| 1 | 01:22:00 | 3 | 52 |
| 3 | 01:40:00 | 2 | 53 |
| 3 | 01:52:00 | 3 | 53 |
I would like to group the IDs of Time IN, REQ, and OUT into one row, for each ID visit to each location, to get the result:
| ID | Time IN | Time REQ | Time OUT | Location |
|---|---|---|---|---|
| 1 | 00:20:00 | 00:30:00 | 00:32:00 | 51 |
| 2 | 00:22:00 | 00:40:00 | 00:43:00 | 52 |
| 1 | 00:50:00 | 00:52:00 | 01:22:00 | 53 |
| 3 | 01:10:00 | 01:40:00 | 01:52:00 | 52 |
I achieved this by searching where command = 1 (all IN instances) and then using a SELECT in the SELECT statement
SELECT
O.ID AS "ID",
O.TIME AS "TIMEIN",
(SELECT
MIN(TIME)
FROM VIEW I
WHERE O.LOCATION = I.LOCATION AND COMMAND = ('2') AND O.ID = I.ID AND O.TIME < I.TIME)
AS "TIMEREQ",
(SELECT
MIN(TIME)
FROM VIEW I
WHERE O.LOCATION = I.LOCATION AND COMMAND = ('3') AND O.ID = I.ID AND O.TIME < I.TIME)
AS "TIMEOUT",
O.LOCATION AS "LOCATION"
FROM VIEW O
WHERE
LOCATION IN ('52','53','54') AND COMMAND IN ('1')
ORDER BY TIME DESC
The results of this takes ~11s for 12,000 rows.
When I then tried to JOIN a table to this, which just contains:
| ID | Comment |
|---|---|
| 1 | Hello, World! |
| 2 | Test comment |
The view never loads, tried up to ~50s, but either way this is too slow and I'm expecting incorrect.
I've tried to use a different approach by using a SELECT statement within the JOIN statement to see if the performance was any better but I'm struggling to get it to work:
SELECT
P.ID AS "ID",
P.TIME AS "TIMEIN",
TIMECOM2 AS "TIMEREQ",
P.LOCATION AS "LOCATION",
P.COMMAND AS "COMMAND"
FROM VIEW P
LEFT JOIN
(SELECT
MAX(C.ID) AS "REQID",
MIN(C.TIME) AS "TIMECOM2"
FROM VIEW C
WHERE C.COMMAND = 2 AND C.LOCATION IN (52, 53, 54) AND C.ID = '2253')
ON (P.ID = REQID) AND TIMECOM2 > P.TIME
WHERE
P.ID = '2253' AND
P.LOCATION IN (52, 53, 54) AND
P.COMMAND = 1
ORDER BY P.TIME, TIMECOM2
I tried many different approaches in the above, but that was the last attempt, and note I only tried with TIMEREQ and picked a specific ID to try to get it to work in the first instance. I think my issue lies in not being able to use VIEW P in the SELECT statement, such as P.TIME > C.TIME in the WHERE statement. I am getting such results:
| ID | TIMEIN | TIMEREQ |
|---|---|---|
| 2253 | 31-OCT-22 22:20:15 | 31-OCT-22 22:40:11 |
| 2253 | 01-NOV-22 09:40:19 | (null) |
| 2253 | 01-NOV-22 11:04:59 | (null) |
| 2253 | 01-NOV-22 18:21:19 | (null) |
| 2253 | 01-NOV-22 19:20:38 | (null) |
Which I don't understand - I can get it to show the MIN or MAX date in each row, or the MIN or MAX in the first row, or all others..
Also, could anyone explain why SELECT statements within SELECT statements are slow, or am I missing something? Obviously I don't know if JOIN is any faster as I failed to get it to work.
CodePudding user response：
select *
from t
pivot (min(time) for command in(1 as time_in,2 as time_req,3 as time_out)) p left join t2 using(ID)
| ID | LOCATION | TIME_IN | TIME_REQ | TIME_OUT | Comment |
|---|---|---|---|---|---|
| 1 | 51 | 00:20:00 | 00:30:00 | 00:32:00 | Hello, World! |
| 1 | 52 | 00:50:00 | 00:52:00 | 01:22:00 | Hello, World! |
| 2 | 52 | 00:22:00 | 00:40:00 | 00:43:00 | Test comment |
| 3 | 53 | 01:10:00 | 01:40:00 | 01:52:00 | null |
CodePudding user response：
Instead of using multiple correlated subqueries, we can try and address this as a gaps-and-islands problem.
An island starts with a command 1 at a given location and id : we can compute this
with a cumulative sum ; the rest is conditional aggregation:
select id, location,
min(time) time_in,
max(case when command = 2 then time end) time_req,
max(case when command = 3 then time end) time_out
from (
select v.*,
sum(case when command = 1 then 1 else 0 end) over(partition by id, location order by time) grp
from myview v
) t
group by id, location, grp
Note that this properly manages incomplete islands (ie that do not have all 3 commands) ; in that event, dates of missing commands will appear as null.