GoogleBigQuery SQL: сделайте скользящий средний подзапрос или присоединяйтесь более эффективно для больших наборов данных

Во-первых, я понял, как получить то, что мне нужно, с помощью подзапроса или соединения. Проблемы, с которыми я столкнулся из-за отсутствия опыта работы с GBQ:

  1. GBQ не разрешает «коррелированные подзапросы»
  2. поскольку объем данных, которые я запрашиваю (+500 млн строк), кажется, что соединение занимает много времени (+3 часа), и я предполагаю, что запрос, вероятно, неэффективен.

В основном для каждой строки я рассчитываю среднее значение некоторых строк, где строки должны соответствовать условию, где (current_row_value-x ‹= other_row_value ‹ current_row_value-1)

Использование https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join в качестве данных и следующий запрос:

SELECT *, (select avg(Quantity) from OrderDetails as table_2 where table_2.OrderId between table_1.OrderId-3 and table_1.OrderId-1) as avg_quant_3 FROM OrderDetails as table_1 order by OrderId asc

Он выводит нужные мне результаты:

Number of Records: 518
OrderDetailID   OrderID ProductID   Quantity    avg_quant_3
1   10248   11  12  null
2   10248   42  10  null
3   10248   72  5   null
4   10249   14  9   27
5   10249   51  40  27
6   10250   41  10  76
7   10250   51  35  76
8   10250   65  15  76
9   10251   22  6   136
10  10251   57  15  136
11  10251   65  20  136
12  10252   20  40  150

Я не могу использовать приведенный выше формат запроса, потому что GBQ не принимает коррелированные подзапросы. Итак, вот версия соединения, где результаты немного отличаются (пропущены строки, где среднее значение не может быть рассчитано), но все же правильные. Я также добавил все столбцы в «группировать по», потому что GBQ не примет запрос с использованием функции агрегации без группировки или агрегирования всех выбранных столбцов:

SELECT table_1.OrderDetailID, table_1.OrderID,table_1.ProductID, table_1.Quantity, sum(table_2.quantity) FROM OrderDetails as table_1
join OrderDetails as table_2 on table_2.OrderId between table_1.OrderId-3 and table_1.OrderId-1  
group by table_1.OrderDetailID, table_1.OrderID,table_1.ProductID, table_1.Quantity

Number of Records: 515
OrderDetailID   OrderID ProductID   Quantity    sum(table_2.quantity)
4   10249   14  9   27
5   10249   51  40  27
6   10250   41  10  76
7   10250   51  35  76
8   10250   65  15  76
9   10251   22  6   136
10  10251   57  15  136
11  10251   65  20  136
12  10252   20  40  150

Проблема здесь в том, что присоединение занимает +3 часа и фактически терпит неудачу из-за того, что занимает слишком много времени. Судя по моему опыту работы с GBQ, объединение занимает много времени, но опять же, я запрашиваю большой набор данных. Мне интересно, есть ли другие способы получить эту информацию с помощью более эффективного запроса, и я надеюсь, что смогу узнать что-то, чтобы стать более эффективным с GBQ в будущем. Также попытался запустить версию запроса для соединения на 5 млн строк, и это занимает +1 час, поэтому я также ожидаю, что потерпит неудачу.


sql google-bigquery rolling-computation correlated-subquery
person Mike    schedule 19.11.2019    source источник
comment
Я просто не понимаю, как AVG() будет производить числа больше, чем что-либо в столбце.   -  person Gordon Linoff    schedule 20.11.2019

Ответы (2)


arrow_upward
0
arrow_downward

Не могли бы вы показать ошибку, которую вы получили при попытке запустить «коррелированный подзапрос»? Ниже запрос работает для меня:

create temp table table1
as select 1 as x, 2 as y
 union all select 3, 4;

create temp table table2
as select 3 x;

select *, (select avg(y) from table1 where table1.x = table2.x)
from table2
order by x;
person Yun Zhang    schedule 19.11.2019

arrow_upward
0
arrow_downward

Вы, кажется, хотите сумму. И с помощью оконных функций:

select t.*,
       sum(quantity) over (order by orderid
                           range between 3 preceding and 1 preceding
                          ) as TheThingYouCallAnAverage
from t;
person Gordon Linoff    schedule 19.11.2019