Go Back   Forums @ The Digital Fix > Gadgets and Gizmos > Computing Forum
login with facebook login with steam

Notices

Reply
 
Thread Tools Display Modes
Old 06-04-2009, 15:13   #1
Stig MK2
Trusted User
 
Join Date: Feb 2007
Posts: 382
Thanks: 4
Thanked 3 Times in 3 Posts
(Quick?) Oracle SQL query - How to count transactions per hour in a day

Ok, I've got some SQL to run which counts transactions per day. All good there.

I just can't get my head round how to format the next part. I now need to create some SQL that counts the number of transaction per hour (there are 3 types of transactions, x,y and z). As I need to do this over a month, I don't really want to write hardcoded SQL for each hourly section! Is it possible to write an SQL query to essentially run a report to do this? The table structure is as follows;

unique ID, date_time, transaction type, plus more.

I've spent the morning on google and any help would be great! Ideally, the output should be in the form:-

00:00 - 01:00 10 x transaction, 15 y transactions, 3 z transactions
01:00 - 02:00 1 x transaction, 8 y transactions, 9 z transactions
etc...

Any help appreciated!
Stig MK2 is offline   Reply With Quote
Old 06-04-2009, 15:51   #2
Paddy
Trusted User
 
Join Date: Mar 2001
Location: Belfast
Posts: 179
Thanks: 2
Thanked 4 Times in 4 Posts
can't you select something like

to_char(birthdate, 'DD-MON-YYYY HH24')

and then group /sort by that?

so something like

select transactiontype, to_char(birthdate, 'DD-MON-YYYY HH24') hourstamp, count(*)
from myTable
group by transactiontype,hourstamp

EDIT

just saw your example so

select to_char(birthdate, 'DD-MON-YYYY HH24') hourstamp,transactiontype, count(*)
from myTable
group by hourstamp,transactiontype
order by hourstamp,transactiontype

Last edited by Paddy; 06-04-2009 at 15:52.
Paddy is offline   Reply With Quote
Old 07-04-2009, 09:04   #3
Stig MK2
Trusted User
 
Join Date: Feb 2007
Posts: 382
Thanks: 4
Thanked 3 Times in 3 Posts
thanks for that. It doesn't quite give what I'm looking for... The SQL at the moment is...

select to_char(t.transaction_start_time, 'hh24'), tran_typ, count(*)
from Table_A T, Table_B C
Where t.propertyA = c.propertyA
and TRUNC(t.transaction_start_time = To_date('01-apr-2009, 'dd-mon-yyy hh24:mi:ss')
and ....
and ....
group by t.transaction_start_time, tran_typ
order by t.transaction_start_time, tran_typ


The result is closer to what I want, but the table it produces is meaningless. I'll post whatever solution I come up with. (Also, theres a problem in that the above query returned more than 5000 rows... surely it should have returned 24 rows, 1 for each hour?)
Stig MK2 is offline   Reply With Quote
Old 07-04-2009, 09:19   #4
Paddy
Trusted User
 
Join Date: Mar 2001
Location: Belfast
Posts: 179
Thanks: 2
Thanked 4 Times in 4 Posts
Quote:
Originally Posted by Stig MK2 View Post
Also, theres a problem in that the above query returned more than 5000 rows... surely it should have returned 24 rows, 1 for each hour?
Because you're grouping and ordering by the original date field, not the truncated one. You're probably getting one row per second.

try

select to_char(t.transaction_start_time, 'hh24') truncatedDate, tran_typ, count(*)
from Table_A T, Table_B C
Where t.propertyA = c.propertyA
and TRUNC(t.transaction_start_time = To_date('01-apr-2009, 'dd-mon-yyy hh24:mi:ss')
and ....
and ....
group by truncatedDate, tran_typ
order by truncatedDate, tran_typ
Paddy is offline   Reply With Quote
Old 07-04-2009, 09:37   #5
Stig MK2
Trusted User
 
Join Date: Feb 2007
Posts: 382
Thanks: 4
Thanked 3 Times in 3 Posts
I did try that but it reports that truncatedDate is an invalid identifier. I originally tried with date but realised that's probably a reserved word, tried "mydate" and "xyz" as identifiers to double check but they all produce the same problem. The error isn't thrown in the first line, but at the first reference in the group by line. Is it a syntax problem?

The 1st and 7th lines are now

select to_char(t.transaction_start_time, 'hh24') truncatedDate, tran_typ, count(*)
group by truncatedDate, tran_typ

Edit:

I changed the group by to use t.transaction_start_time (same for the order by) and it's running now. Just need to wait about 15 mins for the query to finish...

Last edited by Stig MK2; 07-04-2009 at 09:42.
Stig MK2 is offline   Reply With Quote
Old 07-04-2009, 10:01   #6
Paddy
Trusted User
 
Join Date: Mar 2001
Location: Belfast
Posts: 179
Thanks: 2
Thanked 4 Times in 4 Posts
try

group by 1,2

instead of the field names. I'm doing this from memory as I don't have an Oracle server to play with.
Paddy is offline   Reply With Quote
Old 07-04-2009, 11:39   #7
dan99
Trusted User
 
Join Date: Sep 2002
Location: Warwickshire
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
You could do it like this....

SELECT truncatedDate,
tran_typ,
COUNT(1)
FROM (SELECT TO_CHAR(t.transaction_start_time, 'HH24') truncatedDate,
tran_typ
FROM table_a t, table_b c
WHERE t.propertya = c.propertya
AND TRUNC(t.transaction_start_time = TO_DATE('01-APR-2009', 'DD-MM-YYYY')
AND ....
AND ....)
GROUP BY truncatedDate, tran_typ
ORDER BY truncatedDate, tran_typ

Only advantage of putting it in a sub query like this is that oracle won't need to re-do the TO_CHAR function when grouping and ordering. Might shave a couple of seconds off the query time.

Dan
dan99 is offline   Reply With Quote
Old 07-04-2009, 12:03   #8
AdamBrunt
Trusted User
 
Join Date: Sep 2000
Location: UK
Posts: 20,746
Thanks: 14
Thanked 45 Times in 31 Posts
Quote:
Originally Posted by Stig MK2 View Post
The 1st and 7th lines are now

select to_char(t.transaction_start_time, 'hh24') truncatedDate, tran_typ, count(*)
group by truncatedDate, tran_typ
Not sure which database server you're running against (SQL Server, Access, MySQL, etc) but typically you can't group by a column alias.

1st and 7th lines should be

select to_char(t.transaction_start_time, 'hh24') truncatedDate, tran_typ, count(*)
group by to_char(t.transaction_start_time, 'hh24'), tran_typ
AdamBrunt is offline   Reply With Quote
Old 07-04-2009, 13:29   #9
Stig MK2
Trusted User
 
Join Date: Feb 2007
Posts: 382
Thanks: 4
Thanked 3 Times in 3 Posts
Spot on Adam, replacing the alias with the full description works a treat. Reports are running fine now guys, thanks a lot, much appreciated.

Only problem is each daily report takes over 15 mins to run (we have peak loads of 4 transactions a second over a 7-10 hour period then down to a few hundred during the night). I've looked at the available index's but there isn't much to work on... Best get multi tasking I guess!

I'll try your subquery Dan, and see how much quicker that is!
Stig MK2 is offline   Reply With Quote
Old 07-04-2009, 14:39   #10
AdamBrunt
Trusted User
 
Join Date: Sep 2000
Location: UK
Posts: 20,746
Thanks: 14
Thanked 45 Times in 31 Posts
IIRC correctly the reason why you can't do group / order by aliases is that the way SQL executes the aliases are defined in the 'last step' ie after the data has been retrieved/sorted so the server doesn't know what they are when it gets to the GROUP BY statement.

I ran a similar test as Dan's subquery and on our SQL Server with a fair few thousand records, there was much difference in the Execution plan.

I wouldn't be surprised if it was the to_char(....) in the select and group by which was causing the bottleneck. As Dan says, with the subquery you're not re-doing the to_char. But this should only save a few seconds (depending on the datasize).

Not sure how much time it would save but you could possibly have a new column in the transaction table with possible values from 1 to 24. When a row is inserted set the value of the new column to to_char(t.transaction_start_time, 'hh24') and then, in your report, group by this new column instead.

Speaking to our DB guys @ work the above is the way to go as, if you're doing a 'transformation' on a column in a select or group by, any index on the column will be ignored ie

select to_char(t.transaction_start_time, 'hh24') truncatedDate, tran_typ, count(*)
group by to_char(t.transaction_start_time, 'hh24'), tran_typ

index on transaction_start_time is ignored

BUT

select t.transaction_start_time, tran_typ, count(*)
group by t.transaction_start_time, tran_typ

index on transaction_start_time is used.
AdamBrunt is offline   Reply With Quote
Old 08-04-2009, 11:50   #11
Stig MK2
Trusted User
 
Join Date: Feb 2007
Posts: 382
Thanks: 4
Thanked 3 Times in 3 Posts
ahh, that makes sense. reports are down to 11 mins each! woo hoo!
Stig MK2 is offline   Reply With Quote
Reply

Bookmarks

Tags
count, Oracle, Query, reports, SQL, sql query, transactions

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
West End shows 48 hour sale - upto 70% off - GET IN QUICK!! STEVEWWF Bargain Forum 8 10-08-2006 11:02
24 Hour Party People DVD Query keele2003 Film Discussion 3 03-03-2005 12:58
ORACLE Query help needed - DECODE ? nmayling Computing Forum 4 24-09-2004 12:32
quick query re: cd-wow gap Suppliers and Shopping Forum 3 02-03-2004 19:25

All times are GMT. The time now is 05:58.

Latest from TDF

Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2014, vBulletin Solutions, Inc.
Copyright ©2000 - 2012 Poisonous Monkey Ltd. Part of The Digital Fix Network