Works great - thanks!
Marcin Belczewski, September 09, 2002 - 2:40 am UTC
You solved my problem - thanks!!
Aggregate interval for multiple iteration problems
Alan from Annapolis, September 18, 2007 - 11:07 am UTC
Tom,
Excellent examples above! Thanks Tom!
I'm using the avg function to help users manage their expectations of query performance. Id like to use Standard Deviation to show what portion of their query categories should fit with certain elapsed time groups. The sum and average calculations are quite simple after seeing how you did them. I'm trying to figure how to calculate the average, the count, AND iterate over the rows needed to calculate the standard deviation. The formula is simple: sqrt((summation of i=1:count(x(i)-average)^2) / (count-1)).
The count is needed for the average, and the average is needed for the iteration/summation of the variance portion. Do you have any suggestions for implementation?
September 18, 2007 - 4:50 pm UTC
I do not understand - why not just use stddev?
STDDEV() expects a number, not day to sec interval
Alan from Annapolis, September 18, 2007 - 6:27 pm UTC
I'm storing an operation metric, user, version, end and start timestamps.
I used the to_dsinterval(ds_avg(end time - start time)) (see above) to show users the average times their operations take, and use max and min functions to show the bounds.
I'd like to show that for a particular type of metric operation, they should expect it to complete in X time interval 65% of the time, and within Y time interval 95% of the time, based on their empirical data.
My difficulty lies in STDDEV() expecting a numeric value or type implicitly convertable to numeric.
create table mytable (id number,
user_id varchar(35),
operation varchar(35)
app_version number,
end_time timestamp,
start_time timestamp);
Using the user-defined aggregate function and types defined above, I can do the
Select operation,
to_dsinterval(ds_avg(end_time - start_time)) "Average Elapsed Time",
max((end_time - start_time) "Max Elapsed Time",
min((end_time - start_time) "Min Elapsed Time"
from mytable
where
user_id = 'Some User' and
operation = 'Some Operation' and
app_version = 4.5;
I'd like to be able to get the standard deviation interval like this:
to_dsinterval(ds_stddev(end_time - start_time)) "Standard Deviation"
I've considered executing the query to get the sum and avg interval_type values, storing them in a user_context, for use in a user-defined aggregate. Am I overcomplicating this?
September 19, 2007 - 11:39 am UTC
why not just munge the interval into a number - like "raw number of seconds between two points in time" or "number of minutes" or whatever.
There is an easier alternative
A reader, April 25, 2009 - 5:52 pm UTC
Timestamp interval in secs
Tim M, April 13, 2010 - 5:18 pm UTC
What do you think about this function to get the difference in seconds between two intervals? It gets the fractional number of seconds from the actual interval difference then uses the logic of a previous poster to get the truncated number of days difference times 86400 to turn into seconds then adds the two pieces together.
The result of this can easily be sum/avg/whatever.
CREATE OR REPLACE
function elapsed_seconds_from_tms (p_start_tms timestamp, p_stop_tms timestamp)
return number
is
begin
return to_number(substr(to_char(p_stop_tms - p_start_tms),
instr(to_char(p_stop_tms - p_start_tms),'.',-1)))+
round((sysdate + (p_stop_tms - p_start_tms) - sysdate) *86400,0);
end;
/
April 14, 2010 - 8:58 am UTC
... to get the difference in seconds between
two intervals? ...
I don't know about you - but I see no such function? I see one that does timestamps.
I'd just cast to dates and subtract, a lot easier.
Dynamic categories size and names
Lucian Lazar, November 29, 2010 - 2:35 am UTC
Hi Tom,
I have a similar issue - I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.
So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.
Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.
Cand you help me with a SQL algorithm for this?
November 29, 2010 - 4:11 am UTC
this doesn't make sense. I didn't follow the logic here - no creates, no inserts, no example.
what if the first 11 all start with A?
Test case for the above
Lucian Lazar, November 29, 2010 - 3:11 am UTC
Test case:
create table fieldz(colz varchar2(30));
insert into fieldz values ('Revenues A');
insert into fieldz values ('Revenues B');
insert into fieldz values ('Revenues C');
insert into fieldz values ('Revenues D');
insert into fieldz values ('Revenues E');
insert into fieldz values ('Revenues F');
insert into fieldz values ('Revenues G');
insert into fieldz values ('Revenues H');
insert into fieldz values ('Sales A');
insert into fieldz values ('Sales B');
insert into fieldz values ('Sales C');
insert into fieldz values ('Sales D');
insert into fieldz values ('Sales E');
insert into fieldz values ('Sales F');
I don't want 2 pages of 10 items first and 5 items second. I want first page to contain first 8 records (starting with R) and the second page to start from the 9th record (starting with S).
If I had 9 records starting with R and 10th starting with S I would have wanted 9 records on first page.
November 29, 2010 - 4:37 am UTC
and what if you have 11 records with R.
Or what if you had 8 with R, and 1 or 2 with S.
I do not see a SQL based approach to this, not readily in any case.
Reference
Michel Cadot, November 29, 2010 - 4:22 am UTC
RE:Test case for the above
Ryan, November 30, 2010 - 9:18 am UTC
You are asking your storage layer to do presentation layer items. SQL Queries do not return ASP pages. That's what your presentation layer is meant to do. Your query can be helpful by returning the items sorted alphabetically. Then your presentation layer can break the pages accordingly.
November 30, 2010 - 12:05 pm UTC
not true, not true at all.
SQL is not storage. SQL is a programming language. It can and should be used to its maximum as any programming language should. In general, the closer to the "source" you push the logic - the better the performance.
In this particular case - SQL will not be useful in all probability. But in general, if you want to paginate through things on the web - you are best served by USING SQL to get page "N".
This site does, many efficient sites do.
SQL Solution
Michel Cadot, November 30, 2010 - 1:20 pm UTC
RE:Test case for the above
Ryan, December 07, 2010 - 10:19 am UTC
You are (obviously and as usual) correct that SQL is programming language. With respect to the idea that the UI layer should be handling the logic, I have been doing primarily desktop applications and was assuming the entire data set would be populated in a paginated grid.
Given the desired output, wouldn't some simple analytics do the trick?
SELECT colz, DENSE_RANK () OVER (ORDER BY letterz) AS pagez
FROM (SELECT colz, SUBSTR (colz, 1, 1) letterz FROM fieldz)
And, yes, I am truly sorry for continuing the "z" naming convention.
December 07, 2010 - 11:52 am UTC
... the entire data set would be populated in a paginated grid. ...
welcome to the internet where that should never be happening.
your solution would not get their desired results in general. They wanted page numbers assigned to things - not just a simple rank. If 300 things all had the letter 'A', you would assign 1 to them all - they wanted 10 of them on page one, 10 on page 2 and so on.