Archives of the TeradataForum

Message Posted: Sat, 23 Jan 2010 @ 22:21:12 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Workaround to avoid product join

From:		Dieter Noeth

Victor Sokovin wrote:

Dieter, what are you spending the time on, on Saturday? Is the weather so bad? :-)

I already wrote that query on thursday during a training, there's plenty of time when the students have to do some labs :-) I just had to add the comments.

I'll just give one simple example: minutes when no session started or ended.

As far as I can see, those minutes can only come from the last query of the UNION:

          >>     -- all possible minutes per id_prod
          >>     -- # of products * 1440
          >>     SELECT id_prod, id_minute, 0, 0
          >>     FROM ref_table CROSS JOIN products

With usage set to zero (cnt& secs). But there could be a lot of active sessions during those minutes. I'm afraid this usage is lost in

It's not lost, the cumulative sum takes care of that, it carries the previous value over to that minute.

This is the test data i used:

     CREATE TABLE a(
     ID_USER INT
     ,ID_PROD INT
     ,MINUTE_STARTING INT
     ,MINUTE_ENDING INT
     ,SEC_FIRST_MIN INT
     ,SEC_LAST_MIN  INT);

     INS a
     SELECT id_user, id_prod,
        x / 60,  (x+y) / 60, x MOD 60,
       (x+y) MOD 60
     FROM
     (
     SELECT RANDOM(1,1000) AS ID_USER, RANDOM(1,3) AS ID_PROD,
        RANDOM(1,85000) AS x, RANDOM(1,1400) AS y
     WHERE day_of_calendar <= 5000
     ) dt;


     CREATE TABLE Ref_table(id_minute INT) UNIQUE PRIMARY INDEX(id_minute)
     ;

     INS ref_table SEL day_of_calendar
     FROM sys_calendar.calendar
     WHERE day_of_calendar <= 1440
     ;

     CREATE TABLE products(id_prod INT NOT NULL PRIMARY KEY);
     INS products(1);
     INS products(2);
     INS products(3);

The result is the same as Anom's query...

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference