表函数

创建表函数

```CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year(y INT64)
AS (
SELECT year, name, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = y
GROUP BY year, name
);
```

```CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year_and_prefix(
y INT64, z STRING)
AS (
SELECT year, name, SUM(number) AS total
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE
year = y
AND STARTS_WITH(name, z)
GROUP BY year, name
);
```

使用表函数

``````SELECT * FROM mydataset.names_by_year(1950)
ORDER BY total DESC
LIMIT 5
``````

``````+------+--------+-------+
| year |  name  | total |
+------+--------+-------+
| 1950 | James  | 86447 |
| 1950 | Robert | 83717 |
| 1950 | Linda  | 80498 |
| 1950 | John   | 79561 |
| 1950 | Mary   | 65546 |
+------+--------+-------+
``````

``````SELECT *
FROM `bigquery-public-data.samples.shakespeare` AS s
JOIN mydataset.names_by_year(1950) AS n
ON n.name = s.word
``````

``````SELECT ARRAY(
SELECT name FROM mydataset.names_by_year(1950)
ORDER BY total DESC
LIMIT 5)
``````

删除表函数

``````DROP TABLE FUNCTION mydataset.names_by_year
``````

限制

• 参数必须是标量值。BigQuery 中的表函数不能将表作为输入参数。

• 查询正文必须是 `SELECT` 语句，并且不能修改任何内容。例如，表函数中不允许使用数据定义语言 (DDL) 和数据操纵语言 (DML) 语句。如果您需要副作用，请考虑改为编写过程

• 表函数必须与其引用的表存储在同一位置。

配额

[{ "type": "thumb-down", "id": "hardToUnderstand", "label":"Hard to understand" },{ "type": "thumb-down", "id": "incorrectInformationOrSampleCode", "label":"Incorrect information or sample code" },{ "type": "thumb-down", "id": "missingTheInformationSamplesINeed", "label":"Missing the information/samples I need" },{ "type": "thumb-down", "id": "translationIssue", "label":"翻译问题" },{ "type": "thumb-down", "id": "otherDown", "label":"其他" }]
[{ "type": "thumb-up", "id": "easyToUnderstand", "label":"易于理解" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"解决了我的问题" },{ "type": "thumb-up", "id": "otherUp", "label":"其他" }]
{"lastModified": "\u6700\u540e\u66f4\u65b0\u65f6\u95f4 (UTC)\uff1a2023-12-09\u3002"}