CODEWITHSUNDEEP
databasegoogle-bigquery
Dev-otchka
Dev-otchka

Reputation: 337

Bigquery: array of values in a field

I was asked to develop an client-side app in javascript that queries some tables stored on BigQuery, so at the moment I'm getting the grip on the query syntax.

My schema has, among others, the following fields:

download_speed: FLOAT
real_address: STRING
timestamp: INTEGER
upload_speed: FLOAT
[...]

I want to find the average upload and download speed aggregated by distinct days and keep track of the different IPs too, so I wrote this query:

SELECT LEFT( FORMAT_UTC_USEC(UTC_USEC_TO_DAY(timestamp*1000000)),10) as date_day,
AVG (download_speed)avg_download,
AVG (upload_speed)avg_upload,
real_address
FROM [xxxxxxxxxxxx:xxxxxxx.xxxx]
GROUP BY date_day, real_address
ORDER BY date_day ASC

and this is ok, but the results are flat and I need to have the average speeds aggregated on a daily basis so I can plot them on a time axis. One possibility could be processing the json returned by the query client-side, but with great datasets and lots of different users with different addresses (as we are supposed to have) this approach sounds suicidal to me.

So I was asking myself if there is a way of saving an array with all the addresses for a given day in a field. I'd like to have this tuple structure returned:

+---------------+-------------------+--------------------+--------------------+
|    date_day   |   avg_download    |   avg_upload       |   real_addr_list   |
+---------------+-------------------+--------------------+--------------------+
|               |                   |                    |   79.20.xx.xx,     |
|   2013-01-13  |   510574.63       |    29599.92        |   151.46.xxx.xx,   |
|               |                   |                    |   151.70.xx.xx     |
+---------------+-------------------+--------------------+--------------------+

Is that possible, or I'm forced to process the result outside BigQuery?

Thanks in advance for your help.

Upvotes: 2

Views: 3048

Answers (1)

Dev-otchka
Dev-otchka

Reputation: 337

Ok, I found that BigQuery supports the function GROUP_CONCAT('str') with the following usage:

Concatenates multiple strings into a single comma-delimited string, rather like SUM() for strings. Use this with a grouping statement and a field name for str to concatenate a list of all string values in a group into a single string

If somebody is interested, the query is:

SELECT LEFT( FORMAT_UTC_USEC(UTC_USEC_TO_DAY(timestamp*1000000)),10) as date_day,
AVG (download_speed)avg_download,
AVG (upload_speed)avg_upload,
GROUP_CONCAT(real_address)as real_address_list
FROM [xxxxxxx:xxxxx.xxxxx]
GROUP BY date_day
ORDER BY date_day ASC

Hoping that this will be useful for somebody else too.

Upvotes: 2

Related Questions