question

blackwidow avatar image
blackwidow asked

I need a count of orders

I am using the following query :- with minutes (minute) as ( select cast(trunc(sysdate-1) as timestamp) + interval '1' minute * (level - 1) from dual connect by level <= 1440 ) select to_char(m.minute, 'MM/DD/YYYY HH24:MI') as minute, count(o.order_id) as orders_count from minutes m left join ord_order o on odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day and ((to_char(odcomplete, 'hh24:MI') > to_char(odcreate, 'hh24:MI') and odcreate <= m.minute) or (to_char(odcomplete, 'hh24:MI') = to_char(odcreate, 'hh24:MI') and odcreate < m.minute + interval '1' minute)) and (odcomplete is not null and odcomplete >= m.minute) group by m.minute order by m.minute; I need to get the count of number of orders under processing( i.e between the orderplaced time and ordercompleted time) for every minute for that particular day. I have given an example above of how it should be if the order is placed and completed in the same minute(it should be counted in the same minute). Please suggest some edits as it fails in the UAT environment but works fine when tested with dummy data or INT or MIG2. ;
sqloracle-sql-developer
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@blackwidow It is almost clear except I am not sure why the order # 3 is not counted for 01:12 but is counted for 01:13 and 01:14. From other orders (# 1 and # 2) it appears that it should be counted for that minute. After all, order # 3 started at 01:12 and completed at 01:14 so it seems that it has to be counted in 01:12, 01:13 and 01:14. Please clarify.
0 Likes 0 ·
@blackwidow Thank you for confirming. I guess this means that the order which started 01:12:59 and finished next second at 01:13:00 should be counted ONLY for 01:13 while the order which started at 01:12:01 and finished at 01:14:00 should be counted for 01:13 and 01:14 and NOT for 01:12 even though the order took less than 2 minutes to complete and almost half of that time was within 01:12. 01:12 is nevertheless NOT counted while both 01:13 and 01:14 are. This logic also implies that all orders which started late night and are completed early morning are NEVER counted at all, they are always ignored per your requirements.
0 Likes 0 ·
@blackwidow I will be out for 2 hours and will come up with the solution when I come back.
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
The requirement is to consider just that minute for those orders which were created and completed within the same minute, and to start from the next minute otherwise. The orders are filtered to only include the orders created during the same day in question. I assume that such day is today (current day), if this needs to be changed to, say, yesterday then please modify the select part of the minutes CTE and where clause of orders CTE. The reason I include the orders CTE is to simply avoid an awkward-looking join condition. The orders CTE selects the order start date (with seconds discarded) for those orders which get completed within the same minute, or the order start date (still with seconds discarded) plus one minute for all other orders. The orders' completed date is the completed date with seconds discarded (this is not technically necessary to discard the seconds from completed date, but is done for consistency). Here is the script (please note that the join condition is now simple with timestamp data type on both ends): with minutes (minute) as ( select cast(trunc(sysdate) as timestamp) + interval '1' minute * (level - 1) from dual connect by level <= 1440 ), orders (orderid, odcreate, odcomplete) as ( select orderid, cast( case when to_char(odcreate, 'hh24:mi') = to_char(odcomplete, 'hh24:mi') then trunc(odcreate, 'mi') else trunc(odcreate, 'mi') + interval '1' minute end as timestamp ) odcreate, cast(trunc(odcomplete, 'mi') as timestamp) odcomplete from ord_order where odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day ) select to_char(m.minute, 'hh24:mi') minute, count(o.orderid) orders_count from minutes m left join orders o on m.minute between o.odcreate and o.odcomplete group by m.minute order by m.minute; Based on the sample data in question, the script produces the following result (abridged): Minute Orders_Count ------ ------------ 00:00 0 00:01 0 --- --- 00:12 2 --- --- 01:13 1 01:14 1 01:15 0 --- --- 23:59 0 I found out that [SQL Fiddle][1] provides a pretty robust online IDE for debugging Oracle queries, as well as many others (Postgre, SQLite, MySQL), so I used this site to test the script. **P.S.** There is a comment about possible modifications to the script in order to attempt to improve performance. For some reason the script gets chopped of in the comments (it never happened before, just today), so here is this script: with minutes (minute) as ( select cast(trunc(sysdate) as timestamp) + interval '1' minute * (level - 1) from dual connect by level <= 1440 ) select to_char(m.minute, 'hh24:mi') minute, count(o.orderid) orders_count from minutes m left join ord_order o on m.minute between case when to_char(o.odcreate, 'hh24:mi') = to_char(o.odcomplete, 'hh24:mi') then trunc(o.odcreate, 'mi') else trunc(o.odcreate, 'mi') + interval '1' minute end and o.odcomplete and ( odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day ) group by m.minute order by m.minute; This version makes the join condition somewhat awkward, but it reduces the number of CTE used from 2 to 1, replacing the second CTE with the actual table. Hopefully, this will make the query faster. ***Edit to remove connect by and to move the column alias inside the CTE definition per *** @Usman Butt ***comment. Otherwise, the script breaks in 10g*** Because the query as written appears to error out in 10g, you can try a slightly different approach (same logic). There are 2 possibly questionable parts: **connect by** inside of the CTE definition and the column aliasing inside of the second CTE. The latter is not an issue if the second query is used (which used but only one CTE, not 2). In order to substitute the inherently recursive **connect by**, some arbitrary table is needed. Any table/view will do so long as it has 1440 or more rows. In the scripts below, I opted to use **all\_objects** but again, any table/view with enough rows should do the trick. There are 2 scripts below, they are very similar. The only difference is that the first one uses the timestamp data type while the second - varchar2(5). This should not make any difference in performance but please try both and retain the one you like. Here is the first script: -- method A, using the timestamp with minutes as ( select cast(trunc(sysdate) as timestamp) + interval '1' minute * (rownum - 1) as minute from all_objects where rownum <= 1440 ) select to_char(m.minute, 'hh24:mi') minute, count(o.orderid) orders_count from minutes m left join ord_order o on m.minute between case when to_char(o.odcreate, 'hh24:mi') = to_char(o.odcomplete, 'hh24:mi') then trunc(o.odcreate, 'mi') else trunc(o.odcreate, 'mi') + interval '1' minute end and o.odcomplete and ( odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day ) group by m.minute order by m.minute; Here is the second script: -- method B, using just the time part formatted as hh24:mi (varchar2(5) type) with minutes as ( select lpad(floor((rownum - 1)/60), 2, '0')||':'||lpad(mod((rownum - 1), 60), 2, '0') as minute from all_objects where rownum <= 1440 ) select m.minute, count(o.orderid) orders_count from minutes m left join ord_order o on m.minute between case when to_char(o.odcreate, 'hh24:mi') = to_char(o.odcomplete, 'hh24:mi') then to_char(o.odcreate, 'hh24:mi') else to_char(o.odcreate + interval '1' minute, 'hh24:mi') end and to_char(o.odcomplete, 'hh24:mi') and ( odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day ) group by m.minute order by m.minute; Both produce the same result and should run just fine in 10g. Hope this helps. Oleg [1]: http://sqlfiddle.com/#!4
15 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Oleg Very nice. +1. @blackwidow Please accept it as the answer if it fulfills your requirement.
0 Likes 0 ·
@blackwidow It is not possible to investigate the issue with performance without knowing the details about the table, but please try removing the orders CTE, replacing it with the join with the table. Something like this: *OK, I give up. For some reason the script in these comments always gets shopped off, I am not sure why, it is the first time I have a problem with posting a small script as a part of comment. To cure this problem, the snippet has been added to the bottom part of my answer*. Hopefully, this will improve the query execution time (multiple CTE may cause performance issues).
0 Likes 0 ·
@blackwidow.Yes, there are alternative ways.I will add the updated script to my answer and move this verbage to comments when I get to work this morning.
0 Likes 0 ·
> @Usman Butt :- Thanks for your effort mate. I appreciate it. @blackwidow To be fair, I have not put any effort yet :) All credit goes to @Oleg for helping you out. And you owe him marking his answers as accepted. For performance problem he is helping you out as well, although the question is not about that :)
0 Likes 0 ·
As far as the performance is concerned, I think better would be to break up the query in smaller chunks. How long the query for getting one day orders take i.e. select orderid, cast( case when to_char(odcreate, 'hh24:mi') = to_char(odcomplete, 'hh24:mi') then trunc(odcreate, 'mi') else trunc(odcreate, 'mi') + interval '1' minute end as timestamp ) odcreate, cast(trunc(odcomplete, 'mi') as timestamp) odcomplete from ord_order where odcreate >= cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day" and how many orders are returned for above query? Depending upon the amount of data the performance could be different. you can use the "EXPLAIN PLAN" command to see what could be proving to be the major performance bottleneck ? You could be missing an index? I guess an index on column "odcreate" could be quite beneficial in order to limit the data to be processed.
0 Likes 0 ·
Moreover, since you have the access to the data, you can try different variations like correlated query, different index etc. on UAT and test environments. Another variation you can try is to create a physical table of 1440 rows?
0 Likes 0 ·
Show more comments
@blackwidow Now I get it. So you must be getting error on 10g? What error you are getting? See if the following works with minutes as ( select cast(trunc(sysdate) as timestamp) + interval '1' minute * (level - 1) minutes from dual connect by level = cast(trunc(sysdate) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) + interval '1' day ) select to_char(m.minutes, 'hh24:mi') minute, count(o.orderid) orders_count from minutes m left join orders o on m.minutes between o.odcreate and o.odcomplete group by m.minutes order by m.minutes;
0 Likes 0 ·
@blackwidow @Usman Butt I guess that it is possible to perceive the **connect by** clause as recursive. Actually, I believe that in Oracle they don't always call it CTE but often use the terms like **named query**, **factored subquery**, or **with clause**. This feature has been available for pretty long time in Oracle. The **connect by** is even more mature, they first rolled it out some 40+ odd years ago in version 2 in April of 1977 (no, I was not a database developer yet, I just happen to know this information). The **connect by** is pretty fast considering how much work it needs to do. If it does not work in 10g then we have to find some other way. I will edit my answer later this morning. What is really bothering here is that the query runs fast in UAT and does not even run production because of version difference. This probably means that there is no problem with performance, it is just that the part generating the minutes records needs to be modified, that is all.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.