Sometimes you'll want to know how many logins there're on your web site and how the daily usage profile looks like. Usually the first attempt to evaluate this is to copy some log files or unload some database table which holds the login information and then import it to Excel or a similar spreadsheet generator.
But in many cases it can be done by the database directly. Here's a DB2 example. Assuming there's a table "events.logins" holding the login events with a date and a time column, then this query would bring up a daily usage profile based on the average data of the last 7 days:
But in many cases it can be done by the database directly. Here's a DB2 example. Assuming there's a table "events.logins" holding the login events with a date and a time column, then this query would bring up a daily usage profile based on the average data of the last 7 days:
select
tmptable.hr as Hour_of_day,
tmptable.cnt as Num_Logins,
substr(
repeat('#', tmptable.cnt * 60 / (
select
max( maxquery.cnt ) as max
from (
select
hour(time) as hr,
count(hour(time)) as cnt
from
events.logins
where
date >= (current_date - 7 days)
group by
hour(time)
) maxquery
)
),1,60) as Logins_Profile
from (
select
hour(time) as hr,
count(hour(time)) as cnt
from
events.logins
where
date >= (current_date - 7 days)
group by
hour(time)
) tmptable
order by
tmptable.hr
;
This query includes two temporary tables. The upper one is used to evaluate the maximum logins per hour, which is necessary to normalize all counts. The second is generating the raw data, means the hour of the day and the number of logins. The result looks like this:
HOUR_OF_DAY NUM_LOGINS LOGINS_PROFILE
----------- ----------- ------------------------------------------------------------
0 36 ##
1 43 ##
2 52 ###
3 55 ###
4 61 ####
5 73 ####
6 119 ########
7 294 ####################
8 625 ##########################################
9 846 #########################################################
10 880 ############################################################
11 812 #######################################################
12 663 #############################################
13 673 #############################################
14 685 ##############################################
15 662 #############################################
16 548 #####################################
17 359 ########################
18 190 #############
19 123 ########
20 112 #######
21 87 #####
22 75 #####
23 48 ###
24 record(s) selected.
