Google BigQuery标准SQL批量统计月度CSV表记录数并兼容新增表

Your current query SELECT COUNT(*) FROM months_data.*`` returns the total number of records across all monthly tables, but not the breakdown per month. Here are two efficient ways to get the per-month counts, including year/month formatting and compatibility with future tables:

Method 1: Using Wildcard Tables (Simplest Approach) #

If your tables follow the naming pattern months_data.YYYYMM (e.g., months_data.201802 for February 2018), you can use the _TABLE_SUFFIX pseudo-column to dynamically get counts per table:

SELECT
  -- Convert numeric month to "X月" format (e.g., 02 → 2月)
  CONCAT(CAST(SUBSTR(_TABLE_SUFFIX, 5, 2) AS INT64), '月') AS 月份,
  -- Extract last two digits of the year (e.g., 2018 → 18)
  SUBSTR(_TABLE_SUFFIX, 3, 2) AS 年份,
  COUNT(*) AS 结果
FROM `your-project-id.your-dataset.months_data.*`
-- Optional: Filter to your initial date range (remove this line to include all future tables)
WHERE PARSE_DATE('%Y%m', _TABLE_SUFFIX) BETWEEN DATE('2018-02-01') AND DATE('2019-02-01')
GROUP BY _TABLE_SUFFIX
-- Order results chronologically
ORDER BY PARSE_DATE('%Y%m', _TABLE_SUFFIX);

Key Benefits: #

• Automatically includes new tables (like months_data.201903) as long as they follow the same naming pattern.
• No need for dynamic SQL—simple, readable, and efficient.
• Produces the exact output format you requested (e.g., 5月 18 1170).

Method 2: Dynamic SQL (For More Flexibility) #

If you need greater control over table selection or formatting, use dynamic SQL to generate a union of count queries for each relevant table:

DECLARE sql_query STRING;

-- Generate a union of count statements for each monthly table
SET sql_query = (
  SELECT STRING_AGG(
    FORMAT(
      "SELECT '%s' AS 月份, '%s' AS 年份, COUNT(*) AS 结果 FROM `%s.%s`",
      CONCAT(CAST(SUBSTR(table_name, -2, 2) AS INT64), '月'),
      SUBSTR(table_name, -6, 2),
      table_catalog,
      table_name
    ),
    "\nUNION ALL\n"
  )
  FROM `your-project-id.your-dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_name LIKE 'months_data.%'
    -- Optional: Filter to your initial date range
    AND PARSE_DATE('%Y%m', SUBSTR(table_name, -6)) BETWEEN DATE('2018-02-01') AND DATE('2019-02-01')
);

-- Execute the generated query
EXECUTE IMMEDIATE sql_query;

Important Notes: #

• Replace your-project-id.your-dataset with your actual BigQuery project and dataset names in both methods.
• The WHERE clause filters for your initial date range—remove it if you want to include all existing and future monthly tables automatically.
• Both methods assume your tables are named with a YYYYMM suffix (e.g., months_data.201802). If your table naming is different, adjust the SUBSTR positions to match your pattern.

内容的提问来源于stack exchange，提问作者noobsmcgoobs