在SQL中自动执行文件名

Question

我想看看我是否可以将多个表合并为1。我已经完成了一半，但还没有完全完成，我不想手动完成。我用的是bigquery，他们有noaa。Gsod数据集从1929年到2022年。我想把每个月不同地点的温度数据合并成一个数据集。到目前为止，我已经设法找到了如何将3个或多个数据集合并到一个表中:

WITH avgtemptable1 AS (
SELECT stn, year, mo, ROUND(AVG(temp)) AS avg_temp
FROM `bigquery-public-data.noaa_gsod.gsod1955`
GROUP BY stn, year, mo 
), avgtemptable2 AS (
SELECT stn, year, mo, ROUND(AVG(temp)) AS avg_temp
FROM `bigquery-public-data.noaa_gsod.gsod1956`
GROUP BY stn, year, mo 
), avgtemptable3 AS (
SELECT stn, year, mo, ROUND(AVG(temp)) AS avg_temp
FROM `bigquery-public-data.noaa_gsod.gsod1957`
GROUP BY stn, year, mo 
)
SELECT *
FROM avgtemptable1
UNION ALL
SELECT *
FROM avgtemptable2
UNION ALL
SELECT *
FROM avgtemptable3

从这里可以明显看出，对近100个数据集执行此操作将是重复的，并且可以在循环中完成。但我在while循环中无法得到FROM bigquery-public-data.noaa_gsod.gsod1956。我试着把它串起来，但没有用。这是我写的，无法执行

DECLARE name STRING DEFAULT 'bigquery-public-data.noaa_gsod.gsod';
SELECT stn,year,mo,temp,(SELECT CONCAT('`',name,'1955','`') AS name2)
FROM name2

计划是，一旦我可以自动提取表的名称，然后我可以将其放入while循环中，并以自动的方式提取所有必要的信息。

Answer 1

从多个表中提取数据不需要循环。相反，您可以使用通配符，如下所示:

SELECT
stn, year, mo, ROUND(AVG(temp)) AS avg_temp
FROM
`bigquery-public-data.noaa_gsod.gsod*`
WHERE
_TABLE_SUFFIX BETWEEN '1955' AND '1957'
GROUP BY
stn, year, mo