CODEWITHSUNDEEP
google-bigquery
mattes
mattes

Reputation: 9439

BigQuery: Group by table name

My dataset (ds1) looks like the following:

2014_01_01_tableA
2014_01_01_tableB

2014_01_02_tableA
2014_01_02_tableB

The query:

SELECT date, COUNT(1) AS counter 
FROM [ds1.2014_01_01_tableA], [ds1.2014_01_01_tableB], [ds1.2014_01_02_tableA], [ds1.2014_01_02_tableB] 
GROUP BY date;

Returns:

date, counter
2014-01-01, 100
2014-01-02, 200

Is it somehow possible to group by the table name as well? Something like this maybe? ...

SELECT date, COUNT(1) AS counter, TABLE_NAME() AS table_name
FROM [ds1.2014_01_01_tableA], [ds1.2014_01_01_tableB], [ds1.2014_01_02_tableA], [ds1.2014_01_02_tableB] 
GROUP BY table_name, date;

Expected result:

date, counter, table_name
2014-01-01, 50, 2014_01_01_tableA
2014-01-01, 50, 2014_01_01_tableB
2014-01-02, 100, 2014_01_02_tableA
2014-01-02, 100, 2014_01_02_tableB

TABLE_NAME() AS table_name does not exist, unfortunately.

Upvotes: 9

Views: 7292

Answers (2)

Tim Martin
Tim Martin

Reputation: 2519

This is now possible using _TABLE_SUFFIX parameter and table wildcards.

SELECT date, COUNT(1) AS counter, _TABLE_SUFFIX as table_name
FROM `ds1.*`
WHERE _TABLE_SUFFIX IN ('2014_01_01_tableA', '2014_01_01_tableB', '2014_01_02_tableA', '2014_01_02_tableB')
GROUP BY table_name, date

_TABLE_SUFFIX is whatever is captured by the wildcard in the FROM statement. It can be a partial table suffix or complete table name. For example, if you use FROM ds1.2014_01_* the corresponding _TABLE_SUFFIX would be 01_tableA.

Upvotes: 5

Jordan Tigani
Jordan Tigani

Reputation: 26637

Unfortunately, there isn't a way to do this cleanly. I've added an internal feature request.

The ugly way to do it is:

SELECT date, COUNT(1) AS counter, table_name
FROM 
 (SELECT *, "2014_01_01_tableA" as table_name FROM [ds1.2014_01_01_tableA]), 
 (SELECT *, "2014_01_01_tableB" as table_name FROM [ds1.2014_01_01_tableB]), 
 (SELECT *, "2014_01_02_tableA" as table_name FROM [ds1.2014_01_02_tableA]), 
 (SELECT *, "2014_01_02_tableB" as table_name FROM [ds1.2014_01_02_tableB])
GROUP BY table_name, date;

Upvotes: 8

Related Questions