query hive in bigquery

user_8513639 0
Viewed 285

I want to compute a new column c1, which is the collect_set of column typpl, then concat the result of collect_set, finally delete escape from result. In Hive the query is:

select distinct numcat,numpl,numcr, natcat, cdvign, translate(concat_ws('!',collect_list(cast(typpl as string)) over(partition by numcat,numpl,numcr, natcat, cdvign) ), ' ','') c1 from db.table

How can I convert this query ( concat_ws and collect_list ) in Bigquery standard SQL?

google-bigquery hive sql
2 Answers

how to convert this query ( concat_ws and collect_list ) in Bigquery standard SQL.

Below is for BigQuery Standard SQL

The direct "translation" would look like below

select distinct 
  numcat,
  numpl,
  numcr, 
  natcat, 
  cdvign, 
  translate(
    array_to_string(
      array_agg(typpl) over(partition by numcat,numpl,numcr, natcat, cdvign), '!'
    ), ' ', ''
  ) c1
from `db.table`

meantime, you can simplify "a little" as in below

select distinct 
  numcat,
  numpl,
  numcr, 
  natcat, 
  cdvign, 
  translate(
    string_agg(typpl, '!') over(partition by numcat,numpl,numcr, natcat, cdvign), 
    ' ', ''
  ) c1
from `db.table`
user_52219440

You probably want a simple aggregation:

select numcat, numpl, numcr, natcat, cdvign, 
       array_agg(cast(typpl as string)) as c1
from db.table
group by numcat, numpl,numcr, natcat, cdvign
user_11440350
Related