Блог

Главная - Вопросы по программированию - Поиск среднего значения трех дней недели с разбивкой по часам

Поиск среднего значения трех дней недели с разбивкой по часам

#sql #google-bigquery #logic

#sql #google-bigquery #Логические

Вопрос:

У меня есть таблица происхождения-назначения, подобная этой, в Bigquery с днем недели, датой, временем / часом UTC и количеством поездок:

   Origin  Destination   Day     Date    Time Count

NY Station  Downtown    Mon  02.09.2019  15  12
NY Station  Downtown    Mon  02.09.2019  16  10
City libry  Eastside    Mon  02.09.2019  17  10
NY Station  Downtown    Tue  03.09.2019  15  8
NY Station  Downtown    Tue  03.09.2019  16  5
City libry  Eastside    Tue  03.09.2019  17  5
NY Station  Downtown    Wed  04.09.2019  15  8
NY Station  Downtown    Wed  04.09.2019  16  10
City libry  Eastside    Wed  04.09.2019  17  11

Я хочу получить среднее количество для

  • каждая пара отправитель-пункт назначения (Нью-Йоркский вокзал -центр города и Сити Либри-Истсайд)
  • среднее значение за понедельник-среду в каждый данный момент времени

Затем результат должен быть примерно таким

 Origin    Destination   Avg_Day  Period                    Time Avg_Count

NY Station  Downtown    Mon-Wed  Week1 (02.09.19-04.09.19)  15  9,33
NY Station  Downtown    Mon-Wed  Week1 (02.09.19-04.09.19)  16  8,33
City libry  Eastside    Mon-Wed  Week1 (02.09.19-04.09.19)  17  8,67

Игнорируйте столбцы Avg_day и Period, поскольку это просто для справки / показывает, для каких дней и дат я хочу получить среднее значение. Другими словами, цель состоит в том, чтобы иметь представление о средних показателях для каждой пары отправитель-получатель в обычный будний день (в данном случае, определенный как пн-ср) в определенные часы дня. Среднее значение, например, времени 15 для пары Вокзал Нью-Йорка-центр города, равно 9,33, вычисляется путем взятия среднего значения для 15 часов в понедельник, во вторник и в среду (то есть среднее значение 12, 8 и 8).

Я пробовал варианты запросов CASE и WHERE SQL, но даже близко не понимал логику того, как сделать запрос для этого, поэтому нет смысла отправлять какой-либо запрос. Возможно, придется также создать временную таблицу. Кто-нибудь может мне помочь? это ОЧЕНЬ ценится

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

1. Это выглядит как простой запрос агрегации. Какие у вас проблемы?

Ответ №1:

Ниже приведен стандартный SQL BigQuery

 #standardSQL
select
  Origin, 
  Destination, 
  'Mon-Wed' AS Avg_Day,
  FORMAT('Week%i (%s-%s)', week, min_date, max_date) AS Period,
  Time,
  Avg_Count
from (
  SELECT 
    Origin, 
    Destination, 
    'Mon-Wed' AS Avg_Day,
    EXTRACT(WEEK FROM PARSE_DATE('%d.%m.%Y', date)) week, 
    MIN(date) AS min_date, 
    MAX(date) AS max_date,
    Time,
    ROUND(AVG(count), 2) AS Avg_Count
  FROM `project.dataset.table`
  WHERE day IN ('Mon', 'Tue', 'Wed')
  GROUP BY Origin, Destination, Time, week 
)

если применить к образцу данных из вашего вопроса — вывод

 Row Origin      Destination Avg_Day Period                          Time    Avg_Count    
1   NY Station  Downtown    Mon-Wed Week35 (02.09.2019-04.09.2019)  15      9.33     
2   NY Station  Downtown    Mon-Wed Week35 (02.09.2019-04.09.2019)  16      8.33     
3   City libry  Eastside    Mon-Wed Week35 (02.09.2019-04.09.2019)  17      8.67