CODEWITHSUNDEEP
joingoogle-bigquerysurrogate-key
user23086613
user23086613

Reputation: 37

Create Surrogate Keys, from 2 tables

I need to create surrogate key and add it as a new column to the tables My case is :

Table1:

Product_ID Product_Unique_ID NEW COLUMN with surrogate key
123 123-aaa 123-123-aaaa
123 123-bbb 123-123-bbb
456 null 456
789 789-aaa 789-789-aaaa

contrain is that in Table2 I have only Product_Id without Product Unique ID so how can i create surrogate key for it? if for case 123 there are 2 unique ids ?

Upvotes: 1

Views: 52

Answers (1)

Samuel
Samuel

Reputation: 3528

First, for each Product_ID one surrogate key is to be set. One method is to group table1 by Product_ID and use an aggregation for the surrogate key. This can be any_value, min, max or even array_agg.

WITH
 tbl AS (
 SELECT
 Product_ID,
 NULLIF(Product_ID,"456") || "-" || TEMP AS Product_Unique_ID 
 FROM
 UNNEST(SPLIT("123,456,789")) AS Product_ID WITH OFFSET,
  UNNEST(SPLIT("aaa,bbb")) AS TEMP
 WHERE OFFSET =0 OR TEMP="aaa" ),
 tbl1 AS (
 SELECT *, Product_ID || IFNULL("-"||Product_Unique_ID,"") AS surrogate_key
 FROM tbl),
 tbl2 AS (SELECT *, rand() as some_value_tbl2 FROM UNNEST(SPLIT("123,456,789")) as Product_ID),
 tbl1_grp AS ( SELECT Product_ID, ANY_VALUE(surrogate_key) as surrogate_key FROM tbl1 GROUP BY 1 )

SELECT
 *
FROM tbl2
LEFT JOIN tbl1_grp
USING(Product_ID)

Upvotes: 0

Related Questions