Stack Overflow
Stack Overflow

Questions tagged [legacy-sql]

Questions related to using "Legacy SQL" for Google BigQuery

78 questions
19
votes
2 answers

Bigquery - json_extract all elements from an array

i'm trying to extract two key from every json in an arry of jsons(using sql legacy) currently i am using json extract function : json_extract(json_column , '$[1].X') AS X, json_extract(json_column , '$[1].Y') AS Y, how can i make it run on every…
am_am
  • 219
  • 1
  • 2
  • 7
11
votes
1 answer

How to use regex in Bigquery

I am unable to apply a proper regex on customtarget column in bigquery. With normal MSSQL: SELECT * from mytable where CustomTargeting like = '%u=%' -- is all okay With Bigquery(legacy-sql) : SELECT REGEXP_EXTRACT(CustomTargeting, r'[^u=\d]') as…
Shivkumar kondi
  • 6,458
  • 9
  • 31
  • 58
10
votes
3 answers

results for last 30 days in bigquery

I am trying to build a query for a tableau dashboard that is connected to Google BigQuery. We have tables for each month of data, but I want to present the last 30 days of data at any given time (so it will have to go across multiple tables). The…
kshoe94
  • 389
  • 2
  • 4
  • 11
3
votes
3 answers

Split a single sql column into five

I'm trying to split one column into up to five around the " > " delimiter but the things I've tried haven'tw orked: I tried select id, compoundColumn, split(compoundColumn," > ")[1] as "first" split(compoundColumn," > ")[2] as "second" from…
J. G.
  • 1,922
  • 1
  • 11
  • 21
3
votes
2 answers

BigQuery: Divided by sum of values in column to find the ratio

I have a simple table with two columns Bin_name (int) and Count_in_this_bin (int) I want to convert it to the ratio of each bin to the total count in all bins. I used the following query in Google BigQuery: SELECT count_in_bin/(SELECT…
Psyduck
  • 637
  • 3
  • 10
  • 22
3
votes
1 answer

Flattening multiple repeated fields in Google BigQuery

I'm trying to flatten data from repeated fields in Big Query. I have had a look at this Querying multiple repeated fields in BigQuery, however I can't seem to get this to work. My data looks like the following: [ { "visitorId": null, …
Tiawy
  • 175
  • 5
  • 11
3
votes
2 answers

ARRAY_AGG(STRUCT(x,y,z)) equivalent in Bigquery legacy SQL

I have a standard SQL query of the following structure SELECT a, ARRAY_AGG(STRUCT(x,y,z)) FROM t GROUP BY a How can write the same query in legacy SQL?
David Rabinowitz
  • 29,904
  • 14
  • 93
  • 125
2
votes
1 answer

How to query same repeated string field for multiple values with BigQuery legacy sql?

I have a table t1 with structure: id: integer name: repeated (string) I have entries with multiple names: 123; name1,name2 124; name1,name3,name4,name5 125; name1,name4,name7 I want to return lines (unique) that have name equal name1 and name4…
Alexandru R
  • 8,560
  • 16
  • 64
  • 98
2
votes
1 answer

Converting Legacy SQL to Standard SQL - Enhannced Ecommerce

I am in no way a coder so I have tried but falling over on this. I want to use this query from Googles Google Analytics Big Query Cookbook Products purchased by customers who purchased product A (Enhanced Ecommerce) I have pasted the code below Into…
John Mitchell
  • 23
  • 3
2
votes
1 answer

List of reserved words for Google BigQuery legacy syntax

I found the list of reserved keywords for Standard SQL, at Standard SQL Lexical Structure - Lexical Structure - Reserved Keywords. But I can't find the equivalent list for Legacy SQL, although there is a reference to it at Legacy SQL Functions and…
lavinio
  • 23,931
  • 5
  • 55
  • 71
2
votes
1 answer

Multiple Left Joins in BigQuery

I'm trying to make a currently working SQL query that I have in BigQuery more streamlines and am running into the following issue: Error: ON clause must be AND of = comparisons of one field name from each table, with all field names prefixed with…
fdc
  • 75
  • 1
  • 9
2
votes
1 answer

Sliding window aggregate for year-week in bigquery

My question is about sliding window sum up in bigquery. I have a table like the following run_id year_week value 001 201451 5 001 201452 8 001 201501 1 001 201505 5 003 …
Syed Arefinul Haque
  • 1,123
  • 2
  • 14
  • 38
2
votes
2 answers

Google Bigquery Legacy SQL - How to return a null or zero if no results returned?

Say I had the following table month region revenue ------ -------- ---------- jan north 100 feb north 150 mar north 250 How would I be able to query the above table to get the following results?: month …
AK91
  • 671
  • 2
  • 13
  • 35
2
votes
1 answer

How do I split a string column into multi rows of single words & word pairs in BigQuery SQL?

I am trying (unsuccessfully) to split a string column in Google BigQuery into rows containing all single words and all word pairs (next to each other & in order). I also need to maintain the ID field for the words from the IndataTable. Both…
Dan
  • 35
  • 2
  • 5
2
votes
2 answers

Number of user that came back within 3 days after playing at least three sessions?

I have data which contains user, eventdate and sessions.I want to separate users who had atleast 3 sessions and came back for new session within 3 days. user eventdate session A 2018-02-05 1 A 2018-02-05 2 A …
VSR
  • 87
  • 2
  • 18
1
2 3 4 5 6