时间戳

首页 > 解决方案 > GoogleBigQuery SQL：使滚动平均子查询或加入对大型数据集更有效

sql - GoogleBigQuery SQL：使滚动平均子查询或加入对大型数据集更有效

问题描述

首先，我已经弄清楚如何使用子查询或连接来获得我需要的东西。由于缺乏GBQ经验，我一直面临的问题：

  1. GBQ 不允许“相关子查询”
  2. 因为我正在查询的数据量（+500m 行），连接似乎需要大量时间（+3 小时），我猜查询可能效率低下

基本上对于每一行，我正在寻找计算一些行的平均值，其中行应该满足条件 where (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 的效率。还尝试在 5m 行上运行查询的连接版本，它需要 +1 小时，所以我也预计会失败。

标签： sqlgoogle-bigquerycorrelated-subqueryrolling-computation

解决方案

你似乎想要一笔钱。并使用窗口函数：

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

推荐阅读