如何选择_TABLE_SUFFIX等来区分数据

Question

使用UNION ALL查询多个表，并且各个表没有区分来自每个表的数据的字段，因此我们的想法是用表名或其他标识符填充一个字段，以便根据数据来自哪个表来区分数据。

Select _TABLE_SUFFIX

SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `1_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `2_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `3_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `4_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `5_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `7_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `8_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
ORDER BY
  Entry_No_ ASC

从查询的每个表中选择表名，或使用其他方法来区分数据...

Answer 1

只有当你有一个*扩展时，_table_suffix才能工作：

SELECT _table_suffix suffix, COUNT(*) c
FROM `fh-bigquery.stackoverflow_archive_questions.q*`
GROUP BY 1 
ORDER BY 1 DESC 

​

​

但在执行UNION时，您需要手动‘标记’每个表：

SELECT table, COUNT(*) c
FROM (
  SELECT *, '201703' table
  FROM `fh-bigquery.stackoverflow_archive_questions.q201703` 
  UNION ALL
  SELECT *, '201706' table
  FROM `fh-bigquery.stackoverflow_archive_questions.q201706` 
  UNION ALL
  SELECT *, '201709' table
  FROM `fh-bigquery.stackoverflow_archive_questions.q201709` 
)
GROUP BY 1 
ORDER BY 1 DESC 

​

​