CODEWITHSUNDEEP
google-bigquery
Riley Lark
Riley Lark

Reputation: 20890

Combining multiple scalar bigquery queries into a single query to generate one table

I have a BiqQuery query that basically takes a date as a parameter and calculates the number of active users our app had near that date.

Right now, if I want to make a graph over a year of active users, I have to run the query 12 times (once per month) and collate the results manually, which is error-prone and time consuming.

Is there a way to make a single bigquery query that runs the subquery 12 times and puts the results on 12 different rows?

For example, if my query is

SELECT COUNT(*) FROM MyTable WHERE activityTime < date '2017-01-01'

How can I get a table like

| Date       |  Count  |
|------------|---------|
| 2017-01-01 |  50000  |
| 2017-02-01 |  40000  |
| 2017-03-01 |  30000  |
| 2017-04-01 |  20000  |
| 2017-05-01 |  10000  |

Upvotes: 1

Views: 236

Answers (2)

Riley Lark
Riley Lark

Reputation: 20890

Elliot taught me UNION ALL and it seemed to do the trick:

SELECT COUNT(*) FROM MyTable WHERE activityTime < date '2017-01-01'
UNION ALL
SELECT COUNT(*) FROM MyTable WHERE activityTime < date '2017-02-01'
UNION ALL
SELECT COUNT(*) FROM MyTable WHERE activityTime < date '2017-03-01'

Maybe there's a nicer way to parameterize the dates in the WHERE clause, but this did the trick for me.

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33765

Supposing that you have a column called date and one called user_id and you want to calculate distinct users on a monthly basis, you can run a query such as:

#standardSQL
SELECT
  DATE_TRUNC(date, MONTH) AS month,
  COUNT(DISTINCT user_id) AS distinct_users
FROM YourTable
GROUP BY month
ORDER BY month ASC;

(Here you can replace YourTable with the subquery that you want to run). As a self-contained example:

#standardSQL
WITH YourTable AS (
  SELECT DATE '2017-06-25' AS date, 10 AS user_id UNION ALL
  SELECT DATE '2017-05-04', 11 UNION ALL
  SELECT DATE '2017-06-20', 10 UNION ALL
  SELECT DATE '2017-04-01', 11 UNION ALL
  SELECT DATE '2017-06-02', 12 UNION ALL
  SELECT DATE '2017-04-13', 10
)
SELECT
  DATE_TRUNC(date, MONTH) AS month,
  COUNT(DISTINCT user_id) AS distinct_users
FROM YourTable
GROUP BY month
ORDER BY month ASC;

Upvotes: 1

Related Questions