Use Multiple Joins from one Lookup table and return the top value

user_14809459 0
Viewed 148

I'm trying to take a table with all of my data and return multiple values from a lookup table, but I only want to return the top value that matches (if it matches at all).

I have two tables:

Table One:

Keyword Clicks Impressions
Cheap Dog Food 5 10
Red Running Shoes 10 20

Table Two:

Category1 CategoryTerm1 Category2 CategoryTerm2 Category3 CategoryTerm3
Dog Dog, puppy Clothing Shirt, Shoes, Pants Athletes Running, Exercise
Shoe Shoe, sneaker Animals Dog, Cat, Bear null
Food Food, eat null null null
Red Red, maroon null null null

The first table has a set of keywords with some corresponding metrics. I want to join these two tables so that if something inside "CategoryTermX" (1, 2 or 3) from table two is also inside the "Keyword" from table one, it will return the corresponding "Category" (Category1, Category2, etc.). For example, this is what I'm wanting the results to look like:

New Ideal Table:

Keyword Category1 Category2 Category3 Clicks Impressions
Cheap Dog Food Dog Animals null 5 10
Red Running Shoes Shoe Clothing Athletes 10 20

Each numbered category doesn't correspond horizontally, so whatever is in the first line for Category1 doesn't necessarily correspond with what's in the same line for Category2. But there might be multiple values that correspond from the categories that correspond with a keyword (example: Dog and Food category both match with keyword "cheap dog food"). I only want the query to return the top value as they are ordered in terms of importance (dog is more important than shoe, food is more important than red, etc.).

Here is the query I have, but it runs way to slow and I haven't even tried adding in a third join:

SELECT 
  `TableOne`.Keyword,
  `TableOne`.Impressions,
  `TableOne`.Clicks,
  `TableTwo`.Category_1,
  `TableTwo`.Category_1_Term,
  `TableTwo`.Category_2,
  `TableTwo`.Category_2_Term
FROM `TableOne` 
INNER JOIN `TableTwo` 
  ON `TableOne`.Keyword LIKE CONCAT("%",`TableTwo`.Category_1_Term,"%")
INNER JOIN `TableTwo`
  ON `TableOne`.Keyword LIKE CONCAT("%",`TableTwo`.Category_2_Term,"%")

LIMIT 1000

Any ideas on how to make a query that will give me the right result and not take forever? The query takes so long it often exceeds resources, I haven't been able to run it successfully. Also noting that I'm running this in Google BigQuery.

google-bigquery sql
2 Answers

Below is for BigQuery Standard SQL

#standardSQL
with categories as (
  select key, Category, lower(trim(term)) term from (
    select 1 key, Category1 as Category, CategoryTerm1 as CategoryTerm from `project.dataset.TableTwo` union all
    select 2 key, Category2, CategoryTerm2 from `project.dataset.TableTwo` union all
    select 3 key, Category3, CategoryTerm3
    from `project.dataset.TableTwo`
  ), unnest(split(CategoryTerm, ',')) term  
  where not Category || CategoryTerm is null
)
select Keyword, 
  ( select string_agg(Category, ', ') 
    from unnest(split(lower(Keyword), ' ')) term 
    join categories using(term) where key = 1
  ) as Category1,
  ( select string_agg(Category, ', ') 
    from unnest(split(lower(Keyword), ' ')) term 
    join categories using(term) where key = 2
  ) as Category2,
  ( select string_agg(Category, ', ') 
    from unnest(split(lower(Keyword), ' ')) term 
    join categories using(term) where key = 3
  ) as Category3,
  Clicks, Impressions
from `project.dataset.TableOne`

if to apply to sample data from your question - output is

enter image description here

user_52219440

You need to optimise your request

CONCAT(`TableTwo`.Category_1_Term,"%")

just remove one %

SELECT 
  `TableOne`.Keyword,
  `TableOne`.Impressions,
  `TableOne`.Clicks,
  `TableTwo`.Category_1,
  `TableTwo`.Category_1_Term,
  `TableTwo`.Category_2,
  `TableTwo`.Category_2_Term
FROM `TableOne` 
INNER JOIN `TableTwo` 
  ON `TableOne`.Keyword LIKE CONCAT(`TableTwo`.Category_1_Term,"%")
INNER JOIN `TableTwo`
  ON `TableOne`.Keyword LIKE CONCAT(`TableTwo`.Category_2_Term,"%")

LIMIT 1000

i hope help you

user_105264250
Related