Блог

Главная - Вопросы по программированию - Как правильно использовать avg и partition by?

Как правильно использовать avg и partition by?

#sql #google-bigquery

#sql #google-bigquery

Вопрос:

У меня есть данные, содержащие user_ids visitStartTime и product prices , которые были просмотрены пользователем. Я пытаюсь получить среднюю и максимальную цену для каждого посещения пользователя, но мой запрос не производит расчет по разделу (user visitStartTime), он производит вычисления user_id только по разделу.

Вот мой запрос:

 select distinct fullVisitorId ,visitStartTime,
    avg(pr) over (partition by visitStartTime,fullVisitorId) as avgPrice,
    max(pr) over (partition by fullVisitorId,visitStartTime) as maxPrice
from dataset

Вот что я получил:

  ----- ---------------------- ----------------- ---------- ---------- -- 
| Row |    fullVisitorId     |    visitStartTi | avgPrice | maxPrice |  |
 ----- ---------------------- ----------------- ---------- ---------- -- 
|   1 |    64217461724617261 |      1538478049 |    484.5 |    969.0 |  |
|   2 |    64217461724617261 |      1538424725 |    484.5 |    969.0 |  |
 ----- ---------------------- ----------------- ---------- ---------- --

Чего мне не хватает в моем запросе?

Пример данных

  --------------- ---------------- --------------- 
| FullVisitorId | VisitStartTime | ProductPrice  |
 --------------- ---------------- --------------- 
|           123 |       72631241 |           100 |
|           123 |       72631241 |           250 |
|           123 |       72631241 |            10 |
|           123 |       73827882 |            70 |
|           123 |       73827882 |            90 |
 --------------- ---------------- ---------------

Желаемый результат:

  ----- --------------- -------------- ---------- ---------- 
| Row | fullVisitorId | visitStartTi | avgPrice | maxPrice |
 ----- --------------- -------------- ---------- ---------- 
|   1 |           123 |     72631241 |    120.0 |    250.0 |
|   2 |           123 |     73827882 |     80.0 |     90.0 |
 ----- --------------- -------------- ---------- ----------

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

1. Я использую BigQuery.

2. Используйте простой агрегированный запрос. Нет необходимости в оконных функциях.

Ответ №1:

В этом случае вам не нужен ‘partition by’.

Попробуйте это:

 select fullVisitorId ,visitStartTime, avg(ProductPrice) avgPrice ,max(ProductPrice) maxPrice
from sample
group by FullVisitorId,VisitStartTime;

(Запрос довольно стандартный, поэтому я думаю, вы можете использовать его в BigQuery)

Вот результат с использованием PostgreSQL: DB<>FIDDLE

Обновить

Также работает со стандартным SQL BigQuery: 

 #standardSQL
SELECT 
  FullVisitorId, 
  VisitStartTime, 
  AVG(ProductPrice) as avgPrice,
  MAX(ProductPrice) as maxPrice
FROM `project.dataset.table`
GROUP BY FullVisitorId, VisitStartTime

Если вы хотите протестировать его:

 #standardSQL
WITH `project.dataset.table` AS (
  SELECT 123 FullVisitorId, 72631241 VisitStartTime, 100 ProductPrice 
  UNION ALL SELECT 123, 72631241, 250
  UNION ALL SELECT 123, 72631241, 10
  UNION ALL SELECT 123, 73827882, 70
  UNION ALL SELECT 123, 73827882, 90
)

SELECT 
  FullVisitorId, 
  VisitStartTime, 
  AVG(ProductPrice) as avgPrice,
  MAX(ProductPrice) as maxPrice
FROM `project.dataset.table`
GROUP BY FullVisitorId, VisitStartTime