Как вернуть смешанные / множественные данные в BigQuery?

ProgramBox

Как вернуть смешанные / множественные данные в BigQuery?

Post author:admin
Запись опубликована:16 ноября, 2022
Post category:Вопросы по программированию

#sql #google-bigquery

Вопрос:

Я пытаюсь получить количество просмотров страницы для страницы из данных аналитики с помощью BigQuery (запущен только вчера). Оба работают индивидуально.

Если я выполню первый запрос,

 SELECT count(1) AS views, event_date AS date from `project-id.analytics_number.events_*`, UNNEST(event_params) as param1 where event_name = 'page_view' AND param1.value.string_value='/[page]' AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  AND
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) GROUP BY event_date ORDER BY event_date ASC

Он дает мне следующие данные:

 [
  {
    "views": "6",
    "date": "20210101"
  },
  {
    "views": "55",
    "date": "20210102"
  },
  {
    "views": "3",
    "date": "20210103"
  },
  {
    "views": "12",
    "date": "20210104"
  },
  {
    "views": "5",
    "date": "20210105"
  }
]

Затем, если я попытаюсь получить общее количество всех просмотров, используя:

  SELECT count(*) as count from `project-id.analytics_number.events_*`, UNNEST(event_params) 
   as param1 where event_name = 'page_view' AND param1.value.string_value='/[page]'

Это дает мне:

 [
  {
    "count": "81"
  }
]

Это работает так, как я хочу. Теперь я пытаюсь получить данные одновременно. Возможно ли это в одном запросе?

Я пытался это сделать, но не работает.

 with first as (
  SELECT count(1) AS views, event_date AS date from `project-id.analytics_number.events_*`, UNNEST(event_params) as param1 where event_name = 'page_view' AND param1.value.string_value='/[page]' AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  AND
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) GROUP BY event_date ORDER BY event_date ASC
),
second as (
    SELECT count(*) as count from `project-id.analytics_number.events_*`, UNNEST(event_params) 
   as param1 where event_name = 'page_view' AND param1.value.string_value='/[page]'
)
select * from second

Ответ №1:

для повышения производительности

 with first as (
  SELECT count(1) AS views, event_date AS date from `project-id.analytics_number.events_*`, UNNEST(event_params) as param1 where event_name = 'page_view' AND param1.value.string_value='/[page]' AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  AND
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) GROUP BY event_date ORDER BY event_date ASC
)
select views, date from (
select views, date from first
union all
select sum(views) as views, NULL as date from first
) order by date asc

1. Syntax error: Trailing comma after the WITH clause before the SELECT clause is not allowed at [6:1] Извините, я бы отладил, но, к сожалению, у меня недостаточно опыта. 🙁

2. хорошо, убрал запятую для with, она должна работать

3. И последнее, даты не в порядке, я вижу ORDER BY event_date ASC , но это не работает? Также общее количество, приходящееся на первый или последний элемент массива. Есть ли способ исправить это? Я не возражаю, даже если он находится в другом объекте. Или это то, что мы не можем сделать?

4. Огромное спасибо. Извините за беспокойство тоже.

Ответ №2:

Вы можете использовать UNION ALL для их объединения:

 SELECT count(*) AS views, event_date AS date 
FROM `project-id.analytics_number.events_*` CROSS JOIN
     UNNEST(event_params) as param1 
WHERE event_name = 'page_view' AND 
      param1.value.string_value='/[page]' AND
      _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND
                            FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY event_date 
UNION ALL
SELECT count(*) as count, NULL 
FROM `project-id.analytics_number.events_*` CROSS JOIN
     UNNEST(event_params) as param1 
WHERE event_name = 'page_view' AND 
      param1.value.string_value='/[page]'

1. Это работает. Большое вам спасибо. Просто QQ, я иногда вижу общее количество, приходящееся на первый или последний элемент массива. Есть ли способ исправить это? Я не возражаю, даже если он находится в другом объекте.

2. @SubhenduKundu . , , Я бы посоветовал вам задать новый вопрос с соответствующими образцами данных и желаемыми результатами. Я имею в виду, вы можете использовать with offset для получения индекса массива, но тогда вам нужно выяснить, какой из них вы хотите.

3. Как насчет того ORDER BY event_date ASC , что не работает. 🙁