Clever Geek Handbook

Nth (n, split ()) in bigquery

I run the following request and keep getting the error:

SELECT NTH(2,split(Web_Address_,'.')) +'.'+NTH(3,split(Web_Address_,'.')) as D , Web_Address_
FROM [Domains.domain 
limit 10

Error message:
Error: (L1: 110): (L1: 119): SELECT clause contains aggregate "D" and fields "Web_Address_" without GROUP BY clause
Job ID: symmetric aura-572: job_axsxEyfYpXbe2gpmlYzH6bKGdtI

I tried using group by clause on field D

and / or Web_address_

but I still get errors in group by

.

Does anyone know why this is so? I have had success before with a similar query.

+3


source to share


2 answers


You probably want to use WITHIN record aggregation rather than GROUP BY

    select concat(p1, '.', p2), Web_Address_ FROM 
(SELECT 
  NTH(2,split(Web_Ad`enter code here`dress_,'.')) WITHIN RECORD p1,
  NTH(3,split(Web_Address_,'.')) WITHIN RECORD p2, Web_Address_ 
FROM (SELECT 'a.b.c' as Web_Address_))


PS If you are just trying to disable the first part of the web address, it will be easier with the RIGHT and INSTR functions.

+2


source


You can also use URL functions : HOST, DOMAIN and TLD



0


source






More articles:


All Articles