我是 SQL 和 Google BigQuery 的新手。我正在尝试运行以下查询以获取名称和计数列表,但是我发现我收到了一个数组错误并且不知道如何修复它。任何帮助表示赞赏。
错误信息:
无法访问在 [5:27] 的类型为 ARRAY 的值上协调的字段
#standardSQL
-- Applications_Per_Assignee
SELECT assignee_harmonized.name AS Assignee_Name, COUNT(*) AS Number_of_Patent_Apps
FROM (
SELECT ANY_VALUE(assignee.harmonized.name) AS Assignee_Name
FROM `patents-public-data.patents.publications` AS patentsdb
GROUP BY Number_of_Patent_Apps
)
GROUP BY assignee_harmonized.name
ORDER BY Number_of_Patent_Apps DESC;
下面是 BigQuery 标准 SQL
#standardSQL
SELECT
ah.name AS Assignee_Name,
COUNT(*) AS Number_of_Patent_Apps
FROM `patents-public-data.patents.publications`,
UNNEST(assignee_harmonized) ah
GROUP BY Assignee_Name
HAVING Number_of_Patent_Apps < 1000
ORDER BY Number_of_Patent_Apps DESC
-- LIMIT 10
带输出
Row Assignee_Name Number_of_Patent_Apps
1 SAMSUNG ELECTRONICS CO LTD 600678
2 CANON KK 579731
3 MATSUSHITA ELECTRIC IND CO LTD 560644
4 HITACHI LTD 531286
5 SIEMENS AG 486276
6 MITSUBISHI ELECTRIC CORP 461673
7 IBM 438822
8 SONY CORP 438039
9 FUJITSU LTD 384270
10 NEC CORP 357193
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句