bigquery, возможен ли "подтабильный"?

Question

В BigQuery с использованием устаревшего SQL я создал чудовищный запрос, который возвращает следующее отображение посещений в день для сайта, который я выпустил 2018-02-26:

Row  date       name    release_date  visits_count
1    20180226   a_name  20180226      2179
2    20180227   a_name  20180226      9522
3    20180228   a_name  20180226      1593   
4    20180301   a_name  20180226      300    
...

Что я действительно хочу, так это

Row  name    release   count_release  count_release+1  count_release_rest
1    a_name  20180226  2179           9522             1893  

Таким образом, я хочу, чтобы фактическое количество посещений на дату выпуска, день после даты выпуска и все подсчеты после этого просто суммировались. Я новичок в BigQuery (и вроде новичок в SQL...). Есть ли способ определить мое первое отображение как "подтаблицу" или что-то подобное, чтобы я мог сделать это, или какой подход вы бы порекомендовали?

Answer 1

Есть много способов достичь этой функциональности. Самый простой способ сделать это - сравнить дату с заявлением.

select name, sum(case when date = relese_date then 1 else 0) as release_count, 
sum(case when date = DATE_ADD(relese_date,1,"DAY") then 1 else 0) as release_count1
sum(case when date > DATE_ADD(relese_date,1,"DAY") then 1 else 0) as release_count_other

Answer 2

Ниже для BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '20180226' date, 'a_name' name, '20180226' release_date, 2179 visits_count UNION ALL
  SELECT '20180227', 'a_name', '20180226', 9522 UNION ALL
  SELECT '20180228', 'a_name', '20180226', 1593 UNION ALL   
  SELECT '20180301', 'a_name', '20180226', 300  
)
SELECT name, release_date, 
  SUM(CASE WHEN date = release_date THEN visits_count END) count_release,
  SUM(CASE WHEN PARSE_DATE('%Y%m%d', date) = DATE_ADD(PARSE_DATE('%Y%m%d', release_date), INTERVAL 1 DAY) THEN visits_count END) count_release_next_day,
  SUM(CASE WHEN PARSE_DATE('%Y%m%d', date) > DATE_ADD(PARSE_DATE('%Y%m%d', release_date), INTERVAL 1 DAY) THEN visits_count END) count_release_rest
FROM `project.dataset.table`
GROUP BY name, release_date   

или выше, можно "рефакторинг", чтобы избежать повторения PARSE_DATE, поэтому запрос выглядит более компактным и более простым в управлении

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '20180226' date, 'a_name' name, '20180226' release_date, 2179 visits_count UNION ALL
  SELECT '20180227', 'a_name', '20180226', 9522 UNION ALL
  SELECT '20180228', 'a_name', '20180226', 1593 UNION ALL   
  SELECT '20180301', 'a_name', '20180226', 300  
)
SELECT name, release_date, 
  SUM(CASE WHEN date = release_date THEN visits_count END) count_release,
  SUM(CASE WHEN visit = release_next_day THEN visits_count END) count_release_next_day,
  SUM(CASE WHEN visit > release_next_day THEN visits_count END) count_release_rest
FROM `project.dataset.table`, 
UNNEST([STRUCT<visit DATE, release_next_day DATE>(
  PARSE_DATE('%Y%m%d', date), 
  DATE_ADD(PARSE_DATE('%Y%m%d', release_date), INTERVAL 1 DAY))]) x
GROUP BY name, release_date      

в обоих случаях результат

Row name    release_date    count_release   count_release_next_day  count_release_rest   
1   a_name  20180226        2179            9522                    1893