Блог

Главная - Вопросы по программированию - В BigQuery, как бы вы рассчитали ежемесячные и ежедневные итоги, используя два столбца дат?

В BigQuery, как бы вы рассчитали ежемесячные и ежедневные итоги, используя два столбца дат?

# #sql #google-bigquery

Вопрос:

Допустим, у меня есть этот простой запрос (два столбца даты-это МЕТКА ВРЕМЕНИ).:

 SELECT
Song_Name, Artist_Name, Album_Name, Genre, Sub_Genres, Song_Length_Seconds, On_Platform_DateTime, Off_Platform_DateTime
FROM Music_Platform.Music_Data

И первые 4 строки таблицы, которые приведут к:

Скриншот таблицы

Используя два столбца дат, я хочу суммировать «Song_Length_Seconds» за каждый месяц и день, для которых будут доступны все песни (между двумя датами в столбцах МЕТОК времени).

Например (используя снимок экрана), просто взглянув на первые 4 строки и только на месяц 2020-06, это будет включать только первые 3 строки общей продолжительностью 739,8 секунды.

Сначала я начал с создания временной таблицы за месяц, но я не уверен, как действовать дальше или это даже лучший метод:

 with
  months as (
    select format_date('%Y-%m', month_start) month_key
    from unnest(
      generate_date_array('2020-01-01', '2022-12-01', interval 1 month)
    ) month_start
  )

Комментарии:

1. Я не понимаю. Первая строка доступна в течение 26 дней в июне, поэтому я ожидал бы, что значение будет 26 * 242.2 или что-то в этом роде.

Ответ №1:

 WITH table AS (
    SELECT 242.4 Song_Length_Seconds, TIMESTAMP '2020-06-05 11:00:00 UTC' On_Platform_DateTime
    union all
    SELECT 240.6 Song_Length_Seconds, TIMESTAMP '2020-06-17 05:00:00 UTC' On_Platform_DateTime
    UNION ALL
    SELECT 256.8 Song_Length_Seconds, TIMESTAMP '2020-06-24 05:00:00 UTC' On_Platform_DateTime
    UNION ALL
    SELECT 380.4 Song_Length_Seconds, TIMESTAMP '2020-07-21 05:00:00 UTC' On_Platform_DateTime
)
SELECT
    EXTRACT(YEAR FROM On_Platform_DateTime) year
    , EXTRACT(MONTH FROM On_Platform_DateTime) month
    , SUM(Song_Length_Seconds) Sum_Song_Length_Seconds
FROM
    table
GROUP BY
    year
    , month
Метки: , ,