Count users from multiple tables in the same dataset

Question

I have the following four data tables in the same dataset at Google Bigquery:

I need to count users from these four tables, and organize the information into a table like this:

The following query returns the <projectID>:<dataset>.<tableID> path of all existing tables at this moment:

SELECT CONCAT(project_id, ':', dataset_id, '.', table_id) AS paths, 
FROM [<projectID>:<dataset>.__TABLES__] 
WHERE MSEC_TO_TIMESTAMP(creation_time)  < DATE_ADD(CURRENT_TIMESTAMP(), 0, 'DAY')

How to iterate the counting in Google Bigquery for all previous paths?

score 4 · Accepted Answer · answered Jun 22 '17 at 11:51

4

Wildcard tables should do the trick by pulling out the _TABLE_SUFFIX reserved column e.g.

#standardsql
SELECT
  COUNT(*) AS lazy_count,
  _TABLE_SUFFIX AS table
FROM
  `bigquery-public-data.noaa_gsod.*`
GROUP BY
  table

Note: I'm not sure what you are counting, so I've just used a lazy COUNT(*). You could simply change this to whatever column you need.

answered Jun 22 '17 at 11:51

Graham Polley

14,393
4
44
80

thanks for your answer. how transalte it into legacy sql? I try to use TABLE_QUERY() wildcard function. – Mario M. Jun 22 '17 at 11:56
2

Why do you want to use legacy? It's not recommended to use it any more. – Graham Polley Jun 22 '17 at 11:59
Because all my queries are written into legacy sql, and I have no time to migrate all code. Could I combine both queries? #legacySQL SELECT.... FROM ..... #standardSQL SELECT.... FROM.... – Mario M. Jun 22 '17 at 12:01
But that should stop you writing your new queries (like this one) in standard SQL, right? Sorry, but I don't know how to do this in legacy sql. – Graham Polley Jun 22 '17 at 12:03
I'm trying to migrate legacy sql. How to select automatically the previous closest Sunday in standard Sql? Do you know? Something like this: SELECT DATE_SUB(CURRENT_DATE(), INTERVAL DAYOFWEEK(CURRENT_DATE()) DAY) as five_days_ago – Mario M. Jun 22 '17 at 14:47
From this link https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators I know that DATE_SUB() function doesn't support DAYOFWEEK(). Another option? – Mario M. Jun 22 '17 at 15:19

Count users from multiple tables in the same dataset

1 Answers1