Home » SQL & PL/SQL » SQL & PL/SQL » Question with an Analytical Query.
Question with an Analytical Query. [message #306305] Thu, 13 March 2008 10:52 Go to next message
rasa
Messages: 45
Registered: February 2006
Member
I wrote a query to get data as follows.

ID	CHN	DTM	        USAGE	      STATUS	DST	HOUR	                COUNTS
XXXA	1	200703110015	9.791999578	0	0	3/11/2007	        1
XXXA	1	200703110030	7.775999665	0	0	3/11/2007	        2
XXXA	1	200703110045	7.703999668	0	0	3/11/2007	        3
XXXA	1	200703110100	8.027999654	0	0	3/11/2007	        4
XXXA	1	200703110115	8.855999619	0	0	3/11/2007 1:00:00 AM	1
XXXA	1	200703110130	7.235999689	0	0	3/11/2007 1:00:00 AM	2
XXXA	1	200703110145	6.119999737	0	0	3/11/2007 1:00:00 AM	3
XXXA	1	200703110200	5.687999755	0	0	3/11/2007 1:00:00 AM	4
XXXA	1	200703110215	6.227999732	0	0	3/11/2007 2:00:00 AM	1
XXXA	1	200703110230	6.587999716	0	0	3/11/2007 2:00:00 AM	2
XXXA	1	200703110245	6.659999713	0	0	3/11/2007 2:00:00 AM	3
XXXA	1	200703110300	6.227999732	0	0	3/11/2007 2:00:00 AM	4


Look at DTM column -- it is stored as VARCHAR2 of a DATE in 15-minute intervals. I need to make sure that there are exactly 4 buckets for each hour. So, in my analytical query, I return counts, which returns 1 for 15-minute, 2 for 30-minute, 3 for 45-minute and 4 for the top of the hour. I then slurp it as a sub-query and then do yet another analytical query to return the MAX for each hour to see if there are bucket-counts that are less than 4 for each hour.

Question: Am I doing the right thing? Is there a better way to do it without having to roll-in 2 Analytical Queries using a sub-query?

Here is the table:

create table EPROFILE
(
  ID  VARCHAR2(14) not null,
  CHN NUMBER(5) not null,
  DTM    VARCHAR2(12) not null,
  USAGE  NUMBER not null,
  STATUS NUMBER(10),
  DST    NUMBER(5) not null
);


Here is the sample data population script:
insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110015', 9.79199957847595, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110030', 7.77599966526031, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110045', 7.70399966835976, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110100', 8.02799965441226, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110115', 8.85599961876869, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110130', 7.23599968850613, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110145', 6.11999973654747, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110200', 5.68799975514412, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110215', 6.22799973189831, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110230', 6.5879997164011, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110245', 6.65999971330166, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110300', 6.22799973189831, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110315', 5.83199974894524, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110330', 6.94799970090389, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110345', 7.48799967765808, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110400', 7.16399969160557, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110415', 8.60399962961674, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110430', 7.66799966990948, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110445', 6.83999970555305, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110500', 6.83999970555305, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110515', 8.02799965441226, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110530', 6.15599973499775, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110545', 6.22799973189831, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110600', 7.12799969315529, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110615', 5.543999761343, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110630', 6.19199973344803, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110645', 8.13599964976311, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110700', 5.11199977993965, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110715', 6.26399973034859, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110730', 6.29999972879887, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110745', 5.14799977838993, 0, 0);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110800', 6.7679997086525, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110815', 5.93999974429607, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110830', 7.12799969315529, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110845', 9.14399960637093, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110900', 4.93199978768826, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110915', 6.55199971795082, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110930', 6.15599973499775, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703110945', 6.69599971175194, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111000', 6.29999972879887, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111015', 7.01999969780445, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111030', 6.91199970245361, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111045', 6.73199971020222, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111100', 6.91199970245361, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111115', 8.3159996420145, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111130', 5.93999974429607, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111145', 9.25199960172176, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111200', 7.77599966526031, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111215', 7.23599968850613, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111230', 10.4759995490313, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111245', 9.64799958467484, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111300', 11.0159995257854, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111315', 9.86399957537651, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111330', 10.0799995660782, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111345', 9.21599960327148, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111400', 7.05599969625473, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111415', 7.88399966061115, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111430', 6.33599972724915, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111445', 8.99999961256981, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111500', 7.88399966061115, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111515', 5.57999975979328, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111530', 7.37999968230724, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111545', 6.33599972724915, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111600', 6.08399973809719, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111615', 7.01999969780445, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111630', 6.29999972879887, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111645', 7.34399968385696, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111700', 5.97599974274635, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111715', 6.73199971020222, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111730', 6.80399970710278, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111745', 5.75999975204468, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111800', 7.37999968230724, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111815', 4.93199978768826, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111830', 6.5879997164011, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111845', 7.6319996714592, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111900', 7.84799966216087, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111915', 6.19199973344803, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111930', 7.70399966835976, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703111945', 6.37199972569942, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112000', 7.6319996714592, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112015', 9.14399960637093, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112030', 6.73199971020222, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112045', 6.80399970710278, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112100', 5.83199974894524, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112115', 5.86799974739552, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112130', 6.19199973344803, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112145', 7.27199968695641, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112200', 8.35199964046478, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112215', 9.50399959087372, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112230', 8.17199964821339, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112245', 6.15599973499775, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112300', 5.21999977529049, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112315', 6.37199972569942, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112330', 7.77599966526031, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703112345', 8.45999963581562, 0, 60);

insert into EPROFILE (ID, CHN, DTM, USAGE, STATUS, DST)
values ('XXXA', 1, '200703120000', 7.81199966371059, 0, 60);

COMMIT;

Here is the query that I wrote:
SELECT Z.ID,
       Z.CHN,
       Z.DTM,
       Z.USAGE,
       Z.STATUS,
       Z.DST,
       Z.HOUR,
       Z.COUNTS,
       MAX(Z.COUNTS) OVER(PARTITION BY Z.HOUR) "MAX_BUCKETS"
FROM   (SELECT A.ID,
               A.CHN,
               A.DTM,
               A.USAGE,
               A.STATUS,
               A.DST,
               TRUNC(TO_DATE(SUBSTR(A.DTM, 1, 12), 'YYYYMMDDHH24MI') -
                     (1 / 1440),
                     'HH') "HOUR",
COUNT(*) 
OVER(
  PARTITION BY TRUNC(TO_DATE(SUBSTR(A.DTM, 1, 12), 'YYYYMMDDHH24MI') - (1 / 1440), 'HH') 
  ORDER BY DTM) "COUNTS"
        FROM   EPROFILE A
        WHERE  A.ID = 'XXXA'
               AND A.CHN = 1
               AND A.DTM BETWEEN '200703110015' AND '200703120000') Z
ORDER  BY Z.DTM;

[Updated on: Thu, 13 March 2008 11:00] by Moderator

Report message to a moderator

Re: Question with an Analytical Query. [message #306307 is a reply to message #306305] Thu, 13 March 2008 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you just count grouping by substr(DTM,1,10)?

Regards
Michel
Re: Question with an Analytical Query. [message #306311 is a reply to message #306307] Thu, 13 March 2008 11:10 Go to previous messageGo to next message
rasa
Messages: 45
Registered: February 2006
Member
Michel Cadot wrote on Thu, 13 March 2008 12:02
Why don't you just count grouping by substr(DTM,1,10)?
Regards
Michel


That will not work because of the last hour! For time starting 23:15, 23:30, 23:45, and 00:00 (next day) should be counted as belonging to the 24th hour of the day. If we go by SUBSTR(DTM, 1, 10), then that will return only 3 buckets for the last hour, although there are 4 buckets to be taken into the reckoning.


Re: Question with an Analytical Query. [message #306318 is a reply to message #306311] Thu, 13 March 2008 11:48 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why 00 is count in the previous hour??? So 1:00 count for hour 0? and 2:00 for hour 1, the same hour than 1:30? Strange!
Anyway, this is just a small change in the logic, convert to a date, substract 1 second and that's it, you just have to group by hour.

Regards
Michel

[Updated on: Thu, 13 March 2008 11:49]

Report message to a moderator

Previous Topic: Quit from procedure.
Next Topic: Stored procedure compilation error
Goto Forum:
  


Current Time: Sat Dec 10 16:50:24 CST 2016

Total time taken to generate the page: 0.08684 seconds