CODEWITHSUNDEEP
google-bigquery
user3447653
user3447653

Reputation: 4158

Left join to populate data from 2 tables in google big query

Below are the 2 tables RawDebug and CarrierDetails. In RawDebug, if DebugData is VER%, then ActualDebugData is Verizon and if DebugData is a number, first we have to replace other characters like (?, ") with ' ', then we have to lookup CarrierDetails table to select its network where Mcc = substr ("310410",0,3) and Mnc = substr ("310410",4,2). This network is then populated to ActualDebugData.

Table RawDebug:

 HardwareId DebugData ActualDebugData
 123        VER%      Verizon
 456        310410?   Bell

Table CarrierDetails:

 Mcc Mnc Network
 310 410 Bell

What I have tried:

 SELECT 
 HardwareId, DebugReason, DebugData, 
 CASE
  WHEN lower(DebugData) LIKE 'ver%' THEN 'Verizon'
  WHEN REGEXP_MATCH(DebugData,'\\d+') THEN c.Network
  ELSE REGEXP_REPLACE(DebugData,'\\?',' ')
 END
 AS ActualDebugData 
 FROM (
   SELECT 
   HardwareId, DebugReason, DebugData, 
   INTEGER(SUBSTR(DebugData,0,3)) AS d1,    INTEGER(SUBSTR(REGEXP_REPLACE(DebugData,'^[a-zA-Z0-9]',' '),4,LENGTH(DebugData)-1)) as d2 
   FROM TABLE_DATE_RANGE([bigdata:RawDebug.T],TIMESTAMP('2016-05-15'),TIMESTAMP('2016-05-15'))
   WHERE DebugReason = 50013
   ) AS d
   LEFT JOIN (
   SELECT 
   Network, Mcc, Mnc
   FROM [bigdata:RawDebug.CarrierDetails] 
   ) AS c
   ON c.Mcc = d.d1 and c.Mnc = d.d2 
   LIMIT 400

Upvotes: 0

Views: 45

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

Please have in mind - answer is usually as good as question is!
Hope this will help, but seeing history of your questions - this might be not the end :o)

SELECT 
  HardwareId, DebugReason, DebugData,
  CASE
    WHEN LOWER(DebugData) LIKE 'ver%' THEN 'Verizon'
    WHEN REGEXP_MATCH(DebugData,'\\d+') THEN c.Network
    ELSE REGEXP_REPLACE(DebugData,'\\?',' ')
  END AS ActualDebugData 
FROM (
  SELECT 
    HardwareId, DebugReason, DebugData, 
    INTEGER(SUBSTR (DebugData, 1, 3)) AS d1,    
    INTEGER(SUBSTR (DebugData, 4, 3)) AS d2 
  FROM //TABLE_DATE_RANGE([bigdata:RawDebug.T],TIMESTAMP('2016-05-15'),TIMESTAMP('2016-05-15'))
      (SELECT 123 AS HardwareId, 'VER%' AS DebugData, 'Verizon' AS ActualDebugData, 50013 AS DebugReason), // sample data
      (SELECT 456 AS HardwareId, '310410?' AS DebugData, 'Bell' AS ActualDebugData, 50013 AS DebugReason)  // sample data
  WHERE DebugReason = 50013
) AS d
LEFT JOIN (
  SELECT 
    Network, Mcc, Mnc
  FROM //[bigdata:RawDebug.CarrierDetails] 
    (SELECT 310 AS Mcc, 410 AS Mnc, 'Bell' AS Network) // sample data
) AS c
ON c.Mcc = d.d1 AND c.Mnc = d.d2 
LIMIT 400

Output s: 

HardwareId  DebugReason DebugData   ActualDebugData  
123         50013       VER%        Verizon  
456         50013       310410?     Bell

Upvotes: 2

Related Questions