How to calculate the dot product of two 1-d array fields in bigquery?

Maybe more of a standard sql question (it might use some of bigquery’s array functions somehow though), but wondering how to concisely calculate the dot product of two (same-sized) vector fields of a table

I’m looking for what the details are for the sql in the “[ ]” here:

Select [dot product formula involving a.vector1 and a.vector2] from a

thanks

Answer

Below is simple example to demonstrate approach
For BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT [1,2,3,4,5] vector1, [5,4,3,2,1] vector2
)
SELECT *,
  (SELECT SUM(element1 * element2) 
    FROM t.vector1 element1 WITH OFFSET pos
    JOIN t.vector2 element2 WITH OFFSET pos 
    USING(pos)
  ) dot_prodict
FROM `project.dataset.table` t

with result

Row vector1 vector2 dot_prodict  
1   1       5       35   
    2       4        
    3       3        
    4       2        
    5       1