对于给定产品，对于每个商店，计算其附近所有商店的日销售额之和

Question

给定一个包含列{order_date,store_code,product_id,sales}的daily_summary表和一个包含列{store_code,latitude,longitude}的stores表，我如何:

对于给定的product_id(例如"1234")，对于每个store_code，在附近的商店(半径10公里内)获得相同产品的每日SUM(sales)?输出是一个包含列{store_code,order_date,sales_at_nearby_stores}的表，我特别要求使用BigQuery。

我当前的查询工作，但太慢了。我相信有更快的方法。以下是目前为止的内容:

WITH store_distances AS (
SELECT 
t1.store_code store1,
t2.store_code store2,
ST_DISTANCE(
ST_GEOGPOINT(t1.longitude,t1.latitude),
ST_GEOGPOINT(t2.longitude,t2.latitude)
) AS distance_meters
FROM stores t1
CROSS JOIN stores t2
WHERE t1.store_code != t2.store_code
), nearby_stores_table AS (
SELECT
t1.store1 AS store_code,
STRING_AGG(DISTINCT t2.store2) AS nearby_stores
FROM store_distances t1
LEFT JOIN store_distances t2 USING (store1)
WHERE t2.distance_meters < 10000
GROUP BY t1.store1
ORDER BY t1.store1
), ds_with_nearby_stores AS (
SELECT
order_date, store_code, nearby_stores, sales
FROM daily_summary
LEFT JOIN nearby_stores_table USING (store_code)
WHERE product_id="1234"
)
SELECT DISTINCT
store_code, order_date, 
(
SELECT SUM(sales)
FROM ds_with_nearby_stores t2
WHERE t2.store_code IN UNNEST(SPLIT(t1.nearby_stores)) AND t1.order_date=t2.order_date
) AS sales_at_nearby_stores,
FROM ds_with_nearby_stores t1
ORDER BY store_code, order_date

查询的第一部分生成一个表，其中{store1,store2和distance_meters在2}之间。第二部分生成一个包含{store_code,nearby_stores的表，这是一个逗号分隔的附近商店字符串}。查询的第三部分将第二个表与daily_summary(在product_id上过滤)连接起来，得到一个包含{order_date,store_code,nearby_stores,sales}的表。最后一个解包字符串nearby_stores，并将这些商店的销售额相加，得到{store_code,order_date,sales_at_nearby_stores}

Answer 1

在没有数据和查询完成后显示的查询解释的情况下，很难说这里到底是什么慢。如果完成，请添加查询解释。

它可能很慢的原因之一是它计算所有存储之间的成对距离——创建大的连接，并计算大量的距离。BigQuery优化了空间JOIN，使用ST_DWithin谓词(根据给定的距离过滤)可以更快地完成空间JOIN。前两个cte可以重写为

WITH stores_with_loc AS (
SELECT 
store_code store, 
ST_GEOGPOINT(longitude,latitude) loc
FROM stores 
), nearby_stores_table AS (
SELECT 
t1.store AS store_code,
ARRAY_AGG(DISTINCT IF(t2.store <> t1.store, t2.store, NULL) IGNORE NULLS) AS nearby_stores
FROM stores_with_loc t1
JOIN stores_with_loc t2 
ON ST_DWithin(t1.loc, t2.loc, 10000)
GROUP BY t1.store
)
select * from nearby_stores_table

其他调整:

1. 我使用了ARRAY_AGG，应该比转换为字符串要快

2. 使用常规连接，而不是LEFT JOIN- BigQuery现在只优化了内部空间连接。商店总是连接自己，所以这是可以的。之后我们去掉了ARRAY_AGG表达式中的self-reference。

3. 不要在子查询中使用ORDER BY，它们不会改变任何东西。