Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 23 Jan 2010 @ 12:34:25 GMT


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


Subj:   Re: Workaround to avoid product join
 
From:   Dieter Noeth

Anomy.Anom wrote:

  I am not sure if I can avoid joining the reference table only by extending the CASE though. I need the data to be grouped by every minute- slice, while the main table stores just the ID of a starting and an ending minute. Maybe I miss something here, but I don't think it will be so easy.  


  So OLAP joins the game probably, which I really wanted to avoid. Besides, am not sure if I can use OLAP to get what I want - I'm afraid I will have to rack my brain a bit more.  


Why do you want to avoid OLAP?

Of course you should try to avoid it on a 150 mio row table :-) Just aggregate before you cross join.

Following query should work, you just need a small cross join (to a products table) to create all possible product/minute combinations:

     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)
     FROM
       (
        SELECT
          id_prod
          ,minute_starting AS id_minute

          -- how many sessions started within that minute
          -- only count if it's not already ending within that minute
          ,COUNT(CASE WHEN minute_starting <> minute_ending THEN 1 END) AS cnt
          ,SUM(SEC_FIRST_MIN) AS secs
        FROM a
        GROUP BY 1,2

        UNION ALL

        SELECT
          id_prod
          ,MINUTE_ENDING

          -- how many sessions ended within that minute
          ,-COUNT(*)
          ,SUM(SEC_LAST_MIN)
        FROM a
        WHERE
          -- only sessions which don't end within the same minute
          -- (already aggregated in first select)
          minute_starting <> minute_ending
        GROUP BY 1,2

        UNION ALL

        -- all possible minutes per id_prod
        -- # of products * 1440
        SELECT id_prod, id_minute, 0, 0
        FROM ref_table CROSS JOIN products
       ) dt
     GROUP BY 1,2
     ORDER BY 1,2

Dieter



     
  <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: 23 Jun 2019