有什么建议吗,可以用什么?
作为解决方法 - 您可以考虑在可视化数据之前做一些额外的工作,如下例所示 (BigQuery Standard SQL)
#standardSQL
WITH `project.dataset.table` AS (
SELECT "Maria's Tortillas" Store, '-118.379096984863,33.9593620300293' Location, 'machaka breakfast burritos' Product, TRUE Ordered, FALSE Sold UNION ALL
SELECT "Maria's Tortillas", '-118.379096984863,33.9593620300293', 'chile relleno plate', TRUE, TRUE UNION ALL
SELECT "Anthony's Gift Shop", '-118.371124267578,33.9462585449219', 'LA commemorative trinkets', FALSE, TRUE
)
SELECT
ANY_VALUE(ST_GEOGFROMTEXT(CONCAT('POINT(', REPLACE(location, ',', ' '), ')'))) Store_Location,
Store Store_Name,
STRING_AGG(CONCAT(Product,' | ',CAST(Ordered AS STRING),' | ',CAST(Sold AS STRING)), ' *** ') Products
FROM `project.dataset.table`
GROUP BY Store
如果你在上面运行BigQuery 地理可视化工具 https://bigquerygeoviz.appspot.com/- 你会得到类似下面的东西
为了给可视化增添一些“趣味”,您可以添加一些额外的内容:
#standardSQL
WITH `project.dataset.table` AS (
SELECT "Maria's Tortillas" Store, '-118.379096984863,33.9593620300293' Location, 'machaka breakfast burritos' Product, TRUE Ordered, FALSE Sold UNION ALL
SELECT "Maria's Tortillas", '-118.379096984863,33.9593620300293', 'chile relleno plate', TRUE, TRUE UNION ALL
SELECT "Anthony's Gift Shop", '-118.371124267578,33.9462585449219', 'LA commemorative trinkets', FALSE, TRUE
)
SELECT
ANY_VALUE(ST_GEOGFROMTEXT(CONCAT('POINT(', REPLACE(location, ',', ' '), ')'))) Store_Location,
Store Store_Name,
CONCAT(
'<table cellpadding="5" style="border-style:solid; border-width:1px;border-collapse:collapse;">',
STRING_AGG(CONCAT('<tr>',td,Product,'</td>',td,CAST(Ordered AS STRING),'</td>',td,CAST(Sold AS STRING),'</td></tr>')),
'</table>'
) Products
FROM `project.dataset.table`, UNNEST(['<td style="border-style:solid;border-width:1px">']) td
GROUP BY Store
因此,结果的格式会更好一点(如下图所示)
您也许能够采用这种“技术”,除非您能找到能够自行满足您所有需求的工具
同时,请记住,不同的工具将以不同的方式处理此类解决方法 - 例如,如果您在Goliath https://console.cloud.google.com/marketplace/details/potens-io/potensio– 您将分别拥有:
and
此外,在 Goliath 中,地理可视化是内置功能,因此您可以交互式地构建多个图层的地理可视化并组合多个查询的结果等,而无需离开 BigQuery Tool
披露:我是一名作者、产品负责人,并领导 Potens.io 工具套件(Goliath 是其中的一部分)的开发 - 这在我的 SO 个人资料中也有明确说明