BigQuery Multiple Joins

Question

I have an Apache combined log file that loads into Bigquery. Which has a schema that consists of resource, place_id, ip, start_time, end_time, device, status. I am trying to run a query that counts the number of resources and number of devices and groups them by resource and device.

Table:

resource   |  place_id  |  device  |  ip      |  status  |
-----------------------------------------------------------------
/resource1 | 6750320008 |  android | x.x.x.x  |  200     |
/resource1 | 6750320100 |  ipad    | x.x.x.y  |  200     |
/resource2 | 6750320008 |  android | x.x.x.z  |  200     |

Query:

SELECT resource, device
FROM (
  Select 
    EXACT_COUNT_DISTINCT(resource) AS URL,
    1 AS scalar,
  FROM ([daily_logs.app_logs_data]) 
  WHERE place_id = '6750320008' GROUP BY URL) AS datal
JOIN (
  SELECT
    COUNT(device) as DeviceCount,
    1 AS scalar
  FROM ([daily_logs.app_logs_data]) GROUP BY DeviceCount) AS y
ON datal.scalar=y.scalar

I receive this error: Error: Cannot group by an aggregate.

I am basically tyring to create two tables from the same table that count different items and then I want to join them together but have them be grouped in order like this:

     URL   |  totalresourcecount  |  device  |  totaldevicecount
-----------------------------------------------------------------
/resource1 |          1           |  android |         1 
/resource1 |          1           |  ipad    |         1 
/resource2 |          1           |  android |         1

I have read through the google bigquery syntax help and looked at some examples but nothing has generated the desired result. Thanks in advance!

please present simplified/dummy data that would represent your data and then show expected output. I can easily fix your above query to address that specific error you see - but overall - that query make no sense (at least to me) . so if you provide examples(input and output) and maybe a little more wording - it will help us to help you — Mikhail Berlyant
@MikhailBerlyant First, thanks! I updated the question with a sample data table that I am working with. Part of the problem is that I am rather new to Bigquery so I apologize if I am not to sure on how best to ask the question. I hope the edit helps. — Prof. Falken
No problem. We are here to help :o) So, what is still not clear is what is the logic join - is it by place_id? so for each place_id to show number of resources and devices? try to word your expected logic — Mikhail Berlyant
@MikhailBerlyant According to google doc, JOIN is supposed to join the to queries and place_id is the filter. So I want to filter on place_id, count the resources, and then count the devices that used that resource. The output should show the resource, how many of those resources were counted, show the device that used that resource, and how many of those devices. — Prof. Falken
the way you formulated your logic - both counts are always be the same and is just count of rows with same resource & device - please confirm. or clarify further your counts logic — Mikhail Berlyant

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2017-12-06T19:22:08

Below is for BigQuery Standard SQL and reflects your logic presented in follow-up comments

#standardSQL
SELECT resource, device, COUNT(1) cnt
FROM `project.dataset.yourtable`
WHERE place_id = '6750320008'
GROUP BY resource, device

You can test / play with above using dummy data as below

#standardSQL
WITH `project.dataset.yourtable` AS (
  SELECT '/resource1' resource, '6750320008' place_id, 'android' device, 'x.x.x.x' ip, 200 status UNION ALL
  SELECT '/resource1', '6750320100', 'ipad', 'x.x.x.y', 200 UNION ALL
  SELECT '/resource2', '6750320008', 'android', 'x.x.x.z', 200
)
SELECT resource, device, COUNT(1) cnt
FROM `project.dataset.yourtable`
WHERE place_id = '6750320008'
GROUP BY resource, device

Please note - above is based on how I understood your query logic expressed in your follow-up comments

BigQuery Multiple Joins

1 Answers