PLACES_COUNT
函数会根据指定的搜索区域和搜索过滤条件,返回一个表示地点数量的数值。您必须为 PLACES_COUNT
函数指定搜索区域,还可以选择指定其他过滤参数,例如地点类型、营业状态、价格水平等。
由于 PLACES_COUNT
函数返回单个值,因此请使用 SELECT
子句调用该函数。
输入参数:
返回:
- 单个
count
值,以INT64
形式表示。
- 单个
示例:计算搜索半径内的地点数量
最简单的 PLACES_COUNT
函数调用会返回地理区域中所有地点的单个计数。在此示例中,您返回了帝国大厦 1000 米范围内的所有营业场所的数量。
此示例使用 BigQuery ST_GEOGPOINT
函数从点返回 GEOGRAPHY
值。
SELECT `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building 'geography_radius', 1000 -- Radius in meters ) ) as count;
响应包含一个计数:
更典型的调用会对搜索区域应用过滤条件。以下示例使用过滤条件来限制搜索范围,仅返回以下内容的数量:
- 类型为
restaurant
且最低评分为 3 的地点 - 价格水平为低价或中等价位
- 目前可正常运行
- 允许带狗
SELECT `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building 'geography_radius', 1000, -- Radius in meters 'types', ["restaurant"], 'min_rating', 3, 'price_level', ['PRICE_LEVEL_INEXPENSIVE', 'PRICE_LEVEL_MODERATE'], 'business_status', ['OPERATIONAL'], 'allows_dogs', TRUE ) ) as count;
过滤后的响应:
请注意,地点数据集查询会强制执行 5 的最小计数阈值。位置计数函数的一项优势在于,它们可以返回任何数量,包括 0。例如,以下调用会返回 1:
SELECT `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building 'geography_radius', 500, -- Radius in meters 'types', ["restaurant"], 'min_rating', 4.0, 'free_parking_lot', TRUE, 'good_for_watching_sports', TRUE ) ) as count;
示例:使用多边形计算餐厅数量
您可以使用多边形指定搜索区域。使用多边形时,多边形的点必须定义一个闭环,其中多边形的第一个点与最后一个点相同。
此示例使用 BigQuery ST_GEOGFROMTEXT
函数从多边形返回 GEOGRAPHY
值。
DECLARE geo GEOGRAPHY; SET geo = ST_GEOGFROMTEXT('''POLYGON((-73.985708 40.75773,-73.993324 40.750298, -73.9857 40.7484,-73.9785 40.7575, -73.985708 40.75773))'''); -- NYC viewport SELECT `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'geography',geo, -- viewport 'types', ["restaurant"], 'min_rating', 1.0, 'max_rating', 4.5, 'min_user_rating_count', 1, 'max_user_rating_count', 10000, 'price_level', ['PRICE_LEVEL_INEXPENSIVE', 'PRICE_LEVEL_MODERATE'], 'business_status', ['OPERATIONAL'], 'allows_dogs', TRUE ) ) as count;
视口的响应:
示例:使用直线计算餐厅数量
在下一个示例中,您将使用一条由若干个相连的点组成的线来定义搜索区域,并在线周围设置 100 米的搜索半径。 此线条类似于 Routes API 计算出的出行路线。路线可能适用于车辆、自行车或步行者:
DECLARE geo GEOGRAPHY; SET geo = ST_GEOGFROMTEXT('LINESTRING(-73.98903537033028 40.73655649223003,-73.93580216278471 40.80955538843361)'); -- NYC line SELECT `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'geography',geo, -- line 'geography_radius', 100, -- Radius around line 'types', ["restaurant"], 'min_rating', 1.0, 'max_rating', 4.5, 'min_user_rating_count', 1, 'max_user_rating_count', 10000, 'price_level', ['PRICE_LEVEL_INEXPENSIVE', 'PRICE_LEVEL_MODERATE'], 'business_status', ['OPERATIONAL'], 'allows_dogs', TRUE ) ) as count;
相应行的回答:
示例:合并多次调用的结果
您可以合并多次调用 PLACES_COUNT
函数的结果。
例如,您希望获得一个结果,其中显示特定区域内以下价位的餐厅数量:
PRICE_LEVEL_INEXPENSIVE
PRICE_LEVEL_MODERATE
PRICE_LEVEL_EXPENSIVE
PRICE_LEVEL_VERY_EXPENSIVE"
在此示例中,您将创建一个循环,以便针对每个价格水平调用 PLACES_COUNT
函数,并将每次调用的结果插入到临时表中。然后,您查询临时表以显示结果:
-- Create a temp table to hold the results. CREATE TEMP TABLE results (type STRING, count INT64); -- Create a loop that calls PLACES_COUNT for each price level. FOR types IN (SELECT type FROM UNNEST(["PRICE_LEVEL_INEXPENSIVE", "PRICE_LEVEL_MODERATE", "PRICE_LEVEL_EXPENSIVE", "PRICE_LEVEL_VERY_EXPENSIVE"]) as type) DO INSERT INTO results VALUES (types.type, `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'types', ["restaurant"], 'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building 'geography_radius', 1000, -- Radius in meters 'business_status', ['OPERATIONAL'], 'price_level', [types.type] ))); END FOR; -- Query the table of results. SELECT * FROM results;
合并后的回答:
另一种方法是使用 UNION ALL
命令来合并多个 SELECT
语句的结果。以下示例显示的结果与上一个示例相同:
SELECT "PRICE_LEVEL_INEXPENSIVE" as price_level, `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'types', ["restaurant"], 'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building 'geography_radius', 1000, -- Radius in meters 'business_status', ['OPERATIONAL'], 'price_level', ['PRICE_LEVEL_INEXPENSIVE'] ) ) as count UNION ALL SELECT "PRICE_LEVEL_MODERATE" as price_level, `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'types', ["restaurant"], 'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building 'geography_radius', 1000, -- Radius in meters 'business_status', ['OPERATIONAL'], 'price_level', ['PRICE_LEVEL_MODERATE'] ) ) as count UNION ALL SELECT "PRICE_LEVEL_EXPENSIVE" as price_level, `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'types', ["restaurant"], 'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building 'geography_radius', 1000, -- Radius in meters 'business_status', ['OPERATIONAL'], 'price_level', ['PRICE_LEVEL_EXPENSIVE'] ) ) as count UNION ALL SELECT "PRICE_LEVEL_VERY_EXPENSIVE" as price_level, `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT`( JSON_OBJECT( 'types', ["restaurant"], 'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building 'geography_radius', 1000, -- Radius in meters 'business_status', ['OPERATIONAL'], 'price_level', ['PRICE_LEVEL_VERY_EXPENSIVE'] ) ) as count