Distinct Value after aggregation

Question

I am a beginner of Standard SQL in Bigquery.
Here would like to keep and output distinct value after aggregation

WITH TBL_TEST AS 
(
  SELECT "AUD HKD AUD HKD" AS CURRENCY UNION ALL
  SELECT "AUD HKD HKD AUD"  UNION ALL
  SELECT "CNY HKD AUD CNY"  UNION ALL
  SELECT "AUD AUD AUD AUD" 
)

The expect output is below, how can it solve with ?
Appreciate each of your sincerely reply.

+------------------+-----------------+
|    CURRENCY      |      OUTPUT     | 
+------------------+-----------------+
| AUD HKD AUD HKD  | AUD HKD         |        
| AUD HKD HKD AUD  | AUD HKD         |    
| CNY HKD AUD CNY  | CNY HKD AUD     |
| AUD AUD AUD AUD  | AUD             |
+------------------+-----------------+

user_5521575 · Answer

With the following code, you get the desired output:

WITH TBL_TEST AS 
(
  SELECT "AUD HKD AUD HKD" AS CURRENCY UNION ALL
  SELECT "AUD HKD HKD AUD"  UNION ALL
  SELECT "CNY HKD AUD CNY"  UNION ALL
  SELECT "AUD AUD AUD AUD" 
)

,split_in_columns as (
SELECT 
  CURRENCY, 
  currency_column 
FROM TBL_TEST, UNNEST(SPLIT(CURRENCY, " ")) currency_column)

,remove_duplicates as (
SELECT 
  DISTINCT * 
FROM split_in_columns)

SELECT 
  STRING_AGG(currency_column, " ") output 
FROM remove_duplicates 
GROUP BY CURRENCY

I think the most important part is the split_in_columns CTE where I'm splitting the currencies in an Array, and unnested that with the original table.

Then, I just removing duplicates and recreating the CURRENCY column as an STRING. If you want the outcome as an Array, you can simply change the STRING_AGG with ARRAY_AGG.

user_5221944 · Answer

Consider below approach

select currency, 
  ( select string_agg(distinct value, ' ')
    from unnest(split(currency, ' ')) value
  ) as output
from tbl_test

if applied to sample data in your question - output si

Distinct Value after aggregation

2 Answers