CODEWITHSUNDEEP
sqlgoogle-bigquery
Thao Ly
Thao Ly

Reputation: 33

Filling a column with similar values in SQL

I'm beginner on StandardSQL

My Table :

enter image description here

I need your help to get the transactionId to be duplicated on each row instead of the Blank.

enter image description here

My Query :

SELECT
    hits.transaction.transactionId,

    ARRAY(
      SELECT DISTINCT AS STRUCT
       v2ProductName AS name,
       SPLIT(v2ProductCategory, '/')[safe_offset(0)] AS axis,
       SPLIT(v2ProductCategory, '/')[safe_offset(1)] AS category,
       SPLIT(v2ProductCategory, '/')[safe_offset(2)] AS subCategory,
       productQuantity AS quantity,
       productPrice as price,
       productSKU AS sku
      FROM
        hits.product
    )AS products,

FROM
    `dl-recommendation-engine.NDA_CHANEL_137002018.ga_sessions_*` as session, 
    UNNEST(hits) AS hits
    WHERE
    _TABLE_SUFFIX BETWEEN '20191122' AND '20191202'
    AND
    hits.transaction.transactionId IS NOT NULL
    AND 
    (SELECT cd.value  FROM hits.customDimensions as cd WHERE cd.index = 10) = "fr_FR"

Upvotes: 0

Views: 154

Answers (1)

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

You already have transactionId on each row. Your query generates rows where each row has transactionId and some array. The BigQuery GUI just formats array elements to separate rows. You probably want to join products directly in from clause, then you obtain one row per product, with transactionId from hit.

Upvotes: 1

Related Questions