Объединение временных таблиц SQL

sql google-bigquery

55 просмотра

1 ответ

33 Репутация автора

Я вроде новичок в sql. Я использую большой запрос Google. Я хотел бы создать, чтобы объединить две временные таблицы следующим образом. Как я могу сделать это самым простым способом?

(select page_title, integer(substr(page_title, instr(page_title,':')-2,2)) as episode, page_views, unique_users, avg_time_on_page
from 
(Select page_title, event_name, count(*) as page_views, count(distinct(user_id)) as unique_users, avg((next_page_ts-event_time_ts)/(60*1000000)) as avg_time_on_page 
from 
(Select page_title, event_name, user_id, page_url, event_time_ts, lead(event_time_ts) over (partition by user_id, session_id order by event_time_ts) as next_page_ts 
from cooladata
Where date_range(last 7 days) and event_name = "page_load" and ((lower(page_url) CONTAINS "shows-the-amazing-race-episodes" and page_title CONTAINS "עונה 5") or (lower(page_url) CONTAINS "shows-the-amazing-race-episodes" and page_title CONTAINS "פרקים מלאים")) 
)
group by page_title, event_name 
order by page_title, event_name 
) 
where page_views > 100 and unique_users > 100 
order by episode
) as a 



(select integer(substr(page_title,instr(page_title,':')-2,2)) as episode_id, count(*) as video_views
from cooladata
where date_range(last 7 days) and event_name like "%video_start%" and ((lower(page_url) CONTAINS "shows-the-amazing-race-episodes" and page_title CONTAINS "עונה 5") or (lower(page_url) CONTAINS "shows-the-amazing-race-episodes" and page_title CONTAINS "פרקים מלאים"))
group by episode_id
order by episode_id
) as b


select a.page_title, a.page_views, a.unique_users, b.video_views
from a
outer left join b 
on a.episode = b.episode_id
Автор: NANA Источник Размещён: 18.07.2016 12:01

Ответы (1)


0 плюса

77230 Репутация автора

Попробуй ниже. Здесь, как вы можете видеть, все сделано за одно сканирование - никакого объединения не требуется

SELECT 
  page_title, 
  SUM(CASE WHEN event_name = "page_load" THEN event_views END) AS page_views, 
  MAX(CASE WHEN event_name = "page_load" THEN unique_users_for_event END) AS unique_users, 
  MAX(CASE WHEN event_name = "page_load" THEN avg_time_for_event END) AS avg_time_on_page,
  SUM(CASE WHEN event_name <> "page_load" THEN event_views END) AS video_views
FROM (
  SELECT page_title, event_name, 
    COUNT(1) AS event_views, 
    EXACT_COUNT_DISTINCT(user_id) AS unique_users_for_event,     
    AVG((next_page_ts-event_time_ts)/(60*1000000)) AS avg_time_for_event
  FROM (
    SELECT 
      page_title, event_name, user_id, page_url, event_time_ts, 
      LEAD(event_time_ts) OVER (PARTITION BY user_id, session_id, event_name ORDER BY event_time_ts) AS next_page_ts 
    FROM cooladata
    WHERE DATEDIFF(CURRENT_DATE(), DATE(event_time_ts)) < 8 
    AND (event_name = "page_load" OR event_name LIKE "%video_start%")
    AND (
         (LOWER(page_url) CONTAINS "shows-the-amazing-race-episodes" AND page_title CONTAINS "עונה 5") 
      OR (LOWER(page_url) CONTAINS "shows-the-amazing-race-episodes" AND page_title CONTAINS "פרקים מלאים")
    ) 
  )
  GROUP BY page_title, event_name 
) 
GROUP BY page_title
HAVING page_views > 100 AND unique_users > 100  
Автор: Mikhail Berlyant Размещён: 18.07.2016 03:41
Вопросы из категории :
32x32