Summary.Elegant.SQL.Soln

Netters: I received several responses in an effort to solve the following previously posted problem: > create table abcd > ( > timestamp datetime hour to minute not null, /* Unique Key */ > xyz smallint not null > )> > Our application needs this table to be collapsed into records for > each 10 min interval, where value of xyz is the SUM for preceeding 10 > minute period. > > Initial table: Need: > > |08:00|3| 08:00 3 > |08:11|4| 08:20 4 > |08:21|5| > |08:30|6| 08:30 11 > |08:34|7| > |08:36|8| > |08:40|9| 08:40 24 > > Note: A record for 08:10 is missing because there were no initial records > for period between 08:00 to 08:10 > > Is there any elegant SQL solution for achieving this or will I have to write > a C-program to do this ?? If I were to use ESQL/C is following pseudo code > too expensive ? Cathy Kipp's solution... ------------ > select (timestamp - extend ("0:0", hour to minute)) / 10 * 10, sum(xyz) > from abcd > group by 1 was a good place to start but is "truncates" the minutes off the rows needing removal giving us > Initial table: Kathy's Answer > > |08:00|3| 08:00 3 > |08:11|4| 08:10 4 > |08:21|5| 08:20 5 > |08:30|6| 08:30 21 (6+7+8) > |08:34|7| > |08:36|8| > |08:40|9| 08:40 9 > This solution could be tuned if I could "+ UNITS 10 minutes" to bad rows. Unfortunately I have tried & tried & tried .... :-( James L. Gehring 's suggestion... ------------------- > I have a scheduling system but i do not use the Informix time stamp > field. Instead i use a decimal field and use 100th of an hour increments, > doing the little tricks that make it readable to the end user on the > perform screen. Our current implementation depends heavily upon 'datetime' data type for its 'current' & 'extend' functionality. Our customers in the field are known to use "interval = datetime - datetime" construct to accumulate data for a shift etc. Hence, solution may solve the problem but is difficult to explain/use. Dave Cortesi's suggestion .... -------------- > This is interesting. The first thing that strikes me is > your claiming a timestamp with one-minute resolution > is a unique key. Indeed the table cannot be very large > (at most 24x60=1440 rows!). > You are correct in stating that we have only 1440 good-rows & as the problem states few bad-rows but this example is a toy example which does not include real columns as they appear in my schema. The product is made for monitoring real-time data and hence each row has over 100 columns. > Since it cannot be any larger than this, why do you > not simply prepare the table with the full complement > of rows, each having a zero count value, and then > use UPDATE to enter the data as it arrives, rather > than INSERT. Great idea !! But this makes it impossible to differentiate between no activity (zero count) and unloaded application (NULL count). > Beyond that I am puzzled by your willingness to > merge values from the nine entries hh:d1 to hh:d9 > and drop the sum into hh:e0, e==d+1. > > This violates the basic precepts of relational databases, making the > table highly "unnormalized." Any subsequent queries > you make against the table must contain complicated, > arbitrary precautions to avoid using those ten-minute > values. You are right ! Dependancy between rows is allowed only for a day and occurs only when our application is stopped and restarted multiple times during a day. We cannot afford to lose data, so we allow insertions other than that at 10 mins boundary. Over 98% of our rows contain 10-minute data & the collapsing problem needs solving exactly so as to render table truely relational. We reccommend that our users access this table only after "collapsing" or do so if they feel confident about their access methods. Summary: Only way to achieve desired results is by writing ESQL/C procedure. (!!) Thank you all for giving this some thought. ======================================================================== Bharat Shah Voice: (508)-663-7570 Unifi Communications Corp. Fax: (508)-663-7543 4 Federal St, Billerica, Mass 01821, USA uunet!bharat@unifi.com ========================================================================