google-bigquery - BigQuery 数据模型，用于按旅行时间计算最近地点

Question

我有以下要求并且是 BigQuery 的新手。

要求，

我们有很多商店，也有竞争对手的商店。当我选择竞争对手的商店和旅行时间（比如，15 分钟内、30 分钟内、45 分钟内、60 分钟内）时，它必须在所选的旅行时间内返回我们的商店。在这里，旅行时间是基于标准时间而不是基于交通，我们将只有 4 个选项（15、30、45、60）。

我的数据模型基于我的知识，

**competitor_store_id | traveltime | stores(comma_separated)**

  Example Data,

C1 | 15 | S1,S2,S3,S4,S5  ---> Here there are 5 stores with in 15 mins
C2 | 30 | S6,S7,S8,S9,S10  --> Here there are 10 stores within 30 mins travel time. So storing delta b/w 15 mins and 30 mins, so the data will be 15mins+30mins for 30 mins traveltime

有没有更好的方法？

样品要求：-

stores/C1?time=15

输出：-

S1,S2,S3,S4,S5 

样品要求：-

stores/C1?time=30

输出：-

S1,S2,S3,S4,S5,S6,S7,S8,S9,S10

标签： google-bigquery

Answer 1

在您上传示例请求和输出后，我了解到您需要一个查询来创建一个包含您共享的示例请求和输出的视图。因此，我能够提出一个有用的查询。为了复制您的案例，我使用了以下示例数据。

WITH data as (
SELECT "C1" as competitor_store_id, 15 AS traveltime, "S1,S2,S3,S4,S5" as stores UNION ALL
SELECT "C1" as competitor_store_id, 30 AS traveltime, "S6,S7,S8,S9,S10" as stores UNION ALL
SELECT "C2" as competitor_store_id, 15 AS traveltime, "S1,S2,S3,S4,S5" as stores UNION ALL
SELECT "C2" as competitor_store_id, 30 AS traveltime, "S6,S7" as stores UNION ALL
SELECT "C2" as competitor_store_id, 45 AS traveltime, "S8" as stores 
)

使用STRING_AGG()方法，查询如下：

select competitor_store_id, STRING_AGG(stores) as stores from data 
where traveltime <= 30
group by 1

而输出，

因此，您将能够为每个competitor_store_id. 在上面的示例中，我使用 30 作为traveltime. 因此，如您所见，在输出中，C2( ) 在 45 处的S8未显示为。而对于 C1，列出了所有 10 家商店。competitor_store_idtraveltime