Pacific-Design.com

    
Home Index

1. Databases

2. Temporary Table

Databases / Temporary Table /

SQL - Temporary Table

CREATE TEMPORARY TABLE table1 ENGINE=MEMORY AS (

    SELECT   t2.project_id
           , t2.platform
           -- , t3.video_id
           , t3.lifetime
           , t3.dt

        FROM ojai.meta_media                t1
        JOIN ojai.meta_instances            t2 ON ( t2.project_id = t1.project_id )
        JOIN total_video_impressions_unique t3 ON ( t3.video_id   = t2.reference  )

        WHERE   t1.client_id = '24'
            AND t2.platform  = 'facebook'
            AND t1.archived  = 0
            AND t3.dt        = '2017-07-04'

        GROUP BY t3.video_id, t3.dt
        ORDER BY t3.dt
    );

SELECT * FROM table1;

SQL - SELECT inside SELECT

SELECT a.project_id, a.platform, a.lifetime, a.dt FROM (

    SELECT   t2.project_id
           , t2.platform
           -- , t3.video_id
           , t3.lifetime
           , t3.dt
                  
        FROM ojai.meta_media                t1  
        JOIN ojai.meta_instances            t2 ON ( t2.project_id = t1.project_id )
        JOIN total_video_impressions_unique t3 ON ( t3.video_id   = t2.reference  )

        WHERE   t1.client_id = '24' 
            AND t2.platform  = 'facebook'
            AND t1.archived  = 0 
            -- AND t3.dt        = '2017-07-04'

        GROUP BY t3.video_id, t3.dt
        ORDER BY t3.dt
) AS a