Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 23 Jan 2010 @ 19:29:32 GMT


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


Subj:   Re: Workaround to avoid product join
 
From:   Victor Sokovin

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

I am watching Manchester Utd v. Hull and, as it is not very interesting, keep thinking about your idea. I am not sure all cases are covered.

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

     > SELECT
     >    id_prod
     >    ,id_minute
     >    ,(-- all sessions started < this minute
     >      -- minus all sessions ended <= this minute
     >      -- = active sessions throughout this minute (full 60 seconds)
     >      SUM(SUM(cnt))
     >      OVER (PARTITION BY id_prod
     >            ORDER BY id_minute
     >            ROWS UNBOUNDED PRECEDING)
     >      - SUM(CASE WHEN cnt > 0 THEN cnt ELSE 0 END)
     >     ) * 60
     >     -- seconds from sessions started/ended within this minute
     >      + SUM(secs)

What do you think?

Back to the football. The break is almost over.


Victor



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023