Блог

Учитывая daily_summary таблицу, содержащую столбцы { order_date , store_code , product_id , sales }, и stores таблицу, содержащую столбцы { store_code , latitude , longitude }, как я могу:

Для данного product_id (например, «1234»), для каждого store_code , получите ежедневную SUM(sales) цену за один и тот же товар в близлежащих магазинах (в радиусе 10 км)? Вывод-это таблица со столбцами { 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 которая представляет собой разделенную запятыми строку близлежащих магазинов}. Третья часть запроса соединяет 2-ю таблицу с daily_summary (фильтруется product_id ), что дает нам таблицу с { order_date , store_code , nearby_stores , sales }. Наконец, последний распаковывает строку nearby_stores и суммирует продажи из этих магазинов, давая нам { store_code , order_date , sales_at_nearby_stores }

Трудно сказать, что именно здесь происходит медленно, без данных и без объяснения запроса, которое отображается после завершения запроса. Если он вообще закончится — пожалуйста, добавьте пояснения к запросу.

Одна из причин, по которой он может быть медленным, заключается в том, что он вычисляет все парные расстояния между всеми магазинами, создавая большие соединения и вычисляя тонны расстояний. BigQuery оптимизировал пространственное СОЕДИНЕНИЕ, которое позволяет делать это намного быстрее, используя ST_DWithin предикат, который отфильтровывается по заданному расстоянию. Первые два 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 выражения.
3. Не используйте ORDER BY в подзапросах, они все равно ничего не меняют.