Archives of the TeradataForum

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

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

Anomy.Anom wrote:

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>>