Geek Answers Handbook

Reduce time in Oracle / SQL

I have a large Oracle table containing rows representing units of work, with columns for start and end times in addition to other metadata.

I need to generate graphs of the use of this data, taking into account some arbitrary filtering criteria and a reporting period of time. For example, show me a schedule of all Alice’s work for a 24-hour period, starting last Tuesday at 7:00. Each row of the DB will stack vertically in the graph.

I could do this in a high-level language by querying all the potentially relevant lines, the time cut into 1-minute buckets by everyone, and graphically displaying the result. But is there an efficient way to do this slicing in SQL? Or is there an existing Oracle technology that does this?

Thanks!

+5
source share
3 answers

From the point of view of receiving data, you can use "group by" and " truncate 'to crop the data with an interval of 1 minute, for example:

SELECT user_name, truncate(event_time, 'YYYYMMDD HH24MI'), count(*)
FROM job_table
WHERE event_time > TO_DATE( some start date time)
AND user_name IN ( list of users to query )
GROUP BY user_name, truncate(event_time, 'YYYYMMDD HH24MI')

This will give you the results as shown below (assuming 20 liters for alice between 8.00 and 8.01 and 40 lines between 8.01 and 8.02):

Alice  2008-12-16 08:00   20
Alice  2008-12-16 08:01   40
+5
source

It’s best to have a table (a temporary one created on the fly would be great if the temporal slice is dynamic) and then join in.

0
source

. (1/1440 ).

SELECT 
  to_char(Times.time,'hh24:mi'),
  count(*)   
FROM   
  (SELECT
     time
   FROM  
     dual
   WHERE 
     1=2 
   MODEL 
     dimension by ( 0 as key )
     measures     ( sysdate -1 as time )
     rules upsert ( time[ for key from 0 to 1 increment (1/1440) ] = sysdate-1 + cv(key)) ) Times,
  job_table
WHERE 
  begintime <= Times.time 
AND 
  endtime > Times.time 
AND
  user_name = 'Alice'
GROUP BY 
  Times.time 
ORDER BY
  Times.time

, , , . , .

0

More articles:


All Articles