
### The problem

Recently, have to resolve below sample data to find ranking per each type, thought of share this, it might have other ways of achieve this but below is what I come out with for how to resolve this problem in SQL.

Below is the sample data to describe this problem.
![result with json array](/images/big-query-group-similar-values/img1.png)

So for instance, for the **_type 3_**, the first should be **_300_** because total is **_50_**, and the rest of the order should be below, also exclude the null.

- 300 -> 50
- 7777 -> 45
- 500 -> 40
- 33333 -> 30
- 200 -> 11
- 100 -> 5

Same as for the **_type 9_**, the order should be below, also ignore the null as well.

- 3000 -> 514
- 77777 -> 458
- 5000 -> 407
- 3000 -> 304
- 333333 -> 304
- 1000 -> 50
- 2000 -> 11

Below is the BQ Script actual solve this problem, ideally just find the duplicate count for either of the column, value_a or value_b, then group data set for value_a and value_b and join the data, finally remove the target duplicate value_a or value_b

```SQL
with sample_data as (
select 1 id, 3 as type, 100 value_a, null value_b, 5 total
union all
select 1 id, 3 as type, 200 value_a, null value_b, 11 total
union all
select 1 id, 3 as type,300 value_a, null value_b, 30 total
union all
select 1 id, 3 as type,300 value_a, 444 value_b, 20 total
union all
select 1 id, 3 as type,null value_a, null value_b, 20 total
union all
select 1 id, 3 as type,null value_a, 33333 value_b, 30 total
union all
select 1 id, 3 as type,500 value_a, null value_b, 40 total
union all
select 1 id, 3 as type,600 value_a, 7777 value_b, 5 total
union all
select 1 id, 3 as type,null value_a, 7777 value_b, 40 total
union all
select 1 id, 9 as type, 1000 value_a, null value_b, 50 total
union all
select 1 id, 9 as type, 2000 value_a, null value_b, 11 total
union all
select 1 id, 9 as type,3000 value_a, null value_b, 307 total
union all
select 1 id, 9 as type,3000 value_a, 4444 value_b, 207 total
union all
select 1 id, 9 as type,null value_a, 333333 value_b, 304 total
union all
select 1 id, 9 as type,5000 value_a, null value_b, 407 total
union all
select 1 id, 9 as type,6000 value_a, 77777 value_b, 54 total
union all
select 1 id, 9 as type,null value_a, 77777 value_b, 404 total
union all
select 2 id, 3 as type,10 value_a, null value_b, 5 total
union all
select 2 id, 3 as type,20 value_a, null value_b, 10 total
union all
select 2 id, 9 as type,null value_a, null value_b, 20 total
)
, agg_value_a_duplicate as (
  select id, type, value_a, count(value_a) count
  from sample_data
  where value_a is not null
  group by id, type, value_a
  having count > 1
)
, agg_value_b_duplicate as (
  select id, type, value_b, count(value_b) count
  from sample_data
  where value_b is not null
  group by id, type, value_b
  having count > 1
)
, remove_value_b as (
  select sample_data.value_b from sample_data
  where sample_data.value_a in (
    select value_a from agg_value_a_duplicate
  )
  and sample_data.value_b is not null
)
, remove_value_a as (
  select sample_data.value_a from sample_data
  where sample_data.value_b in (
    select value_b from agg_value_b_duplicate
  )
  and sample_data.value_a is not null
)
, agg_value_a as (
  select id, type, value_a, sum(total) total
  from sample_data
  where sample_data.value_a is not null
  group by id, type, value_a
)
, agg_value_b as (
  select id, type, value_b, sum(total) total
  from sample_data
  where sample_data.value_b is not null
  group by id, type, value_b
)
, join_value_a_value_b as (
  select id, type, value_a, null as value_b, total from agg_value_a
  union all
  select id, type, null as value_a, value_b, total from agg_value_b
)
select *
from join_value_a_value_b
where (value_b is null or value_b not in (select value_b from remove_value_b))
and (value_a is null or value_a not in (select value_a from remove_value_a))
order by id, type, total desc
```

#### Conclusion

Above script does resolve the issue, but wonder if there's other way?


Use Big Query to aggregate data to find ranking


Here provide simple SQL Query to compare between `page_view`, `session_start` and `scroll` event.
Also, compare the `page_view` with other 2 to see how much difference between each days.

```SQL
with page_view as (
  SELECT event_date, count(1) as page_view_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'page_view'
  group by 1
),
session_start as (
  SELECT event_date, count(1) as session_start_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'session_start'
  group by 1
),
scroll as (
  SELECT event_date, count(1) as scroll_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'scroll'
  group by 1
)
select
page_view.event_date
, page_view.page_view_count
, session_start.session_start_count
, scroll.scroll_count
, safe_subtract(page_view.page_view_count, session_start.session_start_count) page_minus_session
, safe_subtract(page_view.page_view_count, scroll.scroll_count) page_minus_scroll
, round(safe_divide(page_view.page_view_count - session_start.session_start_count, session_start.session_start_count) * 100, 2) page_vs_session_percent
, round(safe_divide(page_view.page_view_count - scroll.scroll_count, scroll.scroll_count) * 100, 2) page_vs_scroll_percent
from page_view
inner join session_start on page_view.event_date = session_start.event_date
inner join scroll on scroll.event_date = page_view.event_date
order by event_date
```


Compare Similar DataSet in BigQuery


You can interact with JSON data with BigQuery, this blog show show to parse the JSON value and load fields with BigQuery.

### Sample JSON

This will be the JSON file using at the blog.

```JSON
{
   "id":"1",
   "first_name":"John",
   "last_name":"Doe",
   "dob":"1968-01-22",
   "addresses":[
      {
         "status":"current",
         "address":"123 First Avenue",
         "city":"Seattle",
         "state":"WA",
         "zip":"11111",
         "numberOfYears":"1"
      },
      {
         "status":"previous",
         "address":"456 Main Street",
         "city":"Portland",
         "state":"OR",
         "zip":"22222",
         "numberOfYears":"5"
      }
   ]
}
```

### Parse the single fields

At very first, assume has the variable contains the JSON string.

```SQL
declare jsonstring default '{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}';
```

To load just single fields from the JSON, we can do something as below, with **_$_** sign.

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
```

```
id | first_name | last_name
1 | John | Doe
```

### Include the JSON Array into the SQL

For the above SQL, if we try this with array, you can see the result has the whole JSON file.

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
json_extract_array(jsonstring, '$.addresses') as addresses
```

![result with json array](/images/big-query-access-json-array/img1.png)

### Display Scalar value from JSON

above script is able to show the JSON, but what if we only need the **_city_**?

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
array_agg(y)
from
  unnest(
  array(
      select
        json_extract_scalar(x, '$.city')
      from unnest(json_extract_array(jsonstring, "$.addresses")) x
    )
  ) y
```

![result with json and city field](/images/big-query-access-json-array/img2.png)

### Keep the filter fields at each row

move of the case, above result meet the result, but what if we want to show id, first_name and last_name at each row?

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
y
from
  unnest(
    array(
    select
    json_extract_scalar(x, '$.city')
    from unnest(json_extract_array(jsonstring, "$.addresses")) x
    )
  ) y
```

![result with json and city field at each row](/images/big-query-access-json-array/img3.png)

#### Conclusion

Above are list of example how to use interact with JSON and Big Query.


How to use BigQuery to Extract JSON Array


### Create a Stored Procedure with Input Parameter

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_do_something`(the_id STRING)
BEGIN

select * from `Test.MyTestTable` where id = the_id;

END;
```

### Execute the Procedure

```SQL
DECLARE the_id STRING DEFAULT '2023-06-24 06:26:03.927610+00';
CALL `Test.sp_do_something`(the_id);
```

### Create Procedure with Multi-statement

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_do_something`(the_id STRING)
BEGIN

create TEMP TABLE my_temp_table
(
  country string,
    device string,
    browser string
);

INSERT INTO
my_temp_table (country,
    device,
    browser)
SELECT
  "US" AS country,
  "mobile" AS device,
  "safari" AS browser;

INSERT INTO
  `Test.MyTestTable` (id,items)
SELECT
  the_id,
  ARRAY[ STRUCT('country' AS KEY,
    my_temp_table.country AS value),
  STRUCT('browser' AS KEY,
    my_temp_table.browser AS value),
  STRUCT('device' AS KEY,
    my_temp_table.device AS value) ]
FROM
  my_temp_table;

select * from `Test.MyTestTable` where id = the_id;

END;
```

### Run the Procedure has multi-statement

Now, notice the id has new record as **_test_100_**.

```SQL
DECLARE the_id STRING DEFAULT 'test_100';
CALL `Test.sp_do_something`(the_id);
```

what if you run below SQL Script, it actually complain that table already exist.

```SQL
CALL `Test.sp_do_something`('2000');
CALL `Test.sp_do_something`('400');
```

One of the way to fix is drop the temp table at the end of the Procedure, this should fix the previous issue.

```
drop TABLE my_temp_table;
```


How to Create Stored Procedure with Big Query SQL


Assume you have these type of data sets, [1,2,3,4,5], [1,2,3,4,5,6,7], [1,2,3,4,5,6,7,8] and all with key as **_xxx_**, you want to find each array's max value from SQL.

### Create Sample Data

First let's create the sample data set, for key **_xxx_**, we want to get the 5,8,9.

```SQL
with base_data as
(
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '9' as id, 'xxx' as key union all
select '1' as id, 'yyy' as key union all
select '2' as id, 'yyy' as key union all
select '3' as id, 'yyy' as key union all
select '4' as id, 'yyy' as key union all
select '5' as id, 'yyy' as key union all
select '6' as id, 'yyy' as key union all
select '1' as id, 'zzz' as key
)
```

### Find list of sequence number with join with **_string_agg_**

This will return sequence number as string.

```SQL
, data_agg_find_sequence_no as (
select
(split(
string_agg(id, '_')
, '1_'
)) as pre_id
, key
from base_data
group by key
```

### Find the last number as max

Here, substr the last 2 character as max number of that sequence.

```SQL
, data_get_max_no_from_sequence as (
select
replace(
substr(x, -2)
, '_'
, '') as id
, key
from data_agg_find_sequence_no, unnest(pre_id) as x
```

### Final Script

This's the full script, should be able to see aggregated key with max number from the sequence number.

```SQL
with base_data as
(
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '9' as id, 'xxx' as key union all
select '1' as id, 'yyy' as key union all
select '2' as id, 'yyy' as key union all
select '3' as id, 'yyy' as key union all
select '4' as id, 'yyy' as key union all
select '5' as id, 'yyy' as key union all
select '6' as id, 'yyy' as key union all
select '1' as id, 'zzz' as key
)
, data_agg_find_sequence_no as (
select
(split(
string_agg(id, '_')
, '1_'
)) as pre_id
, key
from base_data
group by key
)
, data_get_max_no_from_sequence as (
select
replace(
substr(x, -2)
, '_'
, '') as id
, key
from data_agg_find_sequence_no, unnest(pre_id) as x
)
select * from data_get_max_no_from_sequence
where id != '';
```


This blog show how to use Big Query to find max number from a sequence number for a data set.

How to Use Big Query to Find Max Sequence Number


Create a Table so we can insert the **_Record_** Repeat type, or query the data.
The blow sample test table, we created a field name as **_items_**, which is record type with key is key and value both are string.

```SQL
CREATE TABLE `Test.MyTestTable`
(
  id STRING,
  items ARRAY<STRUCT<key STRING, value STRING>>
);

```

### Insert a data into Items Field

This SQL script will insert key/value pair data into the items **_Record_** repeated field.

```SQL
insert into `Test.MyTestTable` (id, items)
select safe_cast(current_timestamp() as string),
array[
  struct('country' as key, "UK" as value),
  struct('browser' as key, "chrome" as value),
  struct('device' as key, "desktop" as value)
]
```

### Query all keys from the dataset

This will return all keys from the table

```SQL
SELECT x.key FROM `Test.MyTestTable`, unnest(items) as x;
```

### Select Insert into the Record Type

Below example is join the **\*my_temp_table** table and as select insert into the **_record_** field.

```SQL
  create TEMP TABLE my_temp_table ( country string,
    device string,
    browser string );
INSERT INTO
  my_temp_table (country,
    device,
    browser)
SELECT
  "US" AS country,
  "mobile" AS device,
  "safari" AS browser;
INSERT INTO
  `Test.MyTestTable` (id,
    items)
SELECT
  SAFE_CAST(CURRENT_TIMESTAMP() AS string),
  ARRAY[ STRUCT('country' AS KEY,
    my_temp_table.country AS value),
  STRUCT('browser' AS KEY,
    my_temp_table.browser AS value),
  STRUCT('device' AS KEY,
    my_temp_table.device AS value) ]
FROM
  my_temp_table
```

### Find all the unique Key from the table

This query is to find unique key from the **_record_** type.

```SQL
SELECT distinct(x.key), count(*) FROM `Test.MyTestTable`, unnest(items) as x
group by 1
order by 2 desc
;
```

### Convert the **_record_** to JSON by using **_to_json_string()_**

```SQL
select id, to_json_string(items) from `Test.MyTestTable`
```


This blog demonstrate how to use GCP Big Query record type.

Category Big Query

Use Big Query to aggregate data to find ranking

Compare Similar DataSet in BigQuery

How to use BigQuery to Extract JSON Array

How to Create Stored Procedure with Big Query SQL

How to Use Big Query to Find Max Sequence Number

How to Use GCP Big Query Record Repeat Type