Problema di script di query di grandi dimensioni SQL

Question

potresti aiutarmi, non capisco cosa c'è che non va nel mio DOVE. Ho provato diverse cose ma non sono riuscito a concludere alla giusta conclusione.

SELECT 
CONCAT(fullvisitorId, "-", CAST(visitstarttime AS STRING)) as session,
RANK() OVER(PARTITION BY CONCAT(fullvisitorId, "-", CAST(visitstarttime AS STRING)) ORDER BY hitNumber ASC) AS Rank, 
(SELECT MAX(cd.value) FROM hits.customDimensions as cd where cd.index = 12) as cd1,
(SELECT cd.value FROM hits.customDimensions as cd where cd.index=29) as division,
hits.hitnumber

FROM 
  `dl-training-bigquery.NDA_CHAN*******51.ga_sessions_20191202` as session,
  unnest(hits) as hits
  
WHERE division = 'fsh'
ORDER BY hitnumber ASC

inserisci qui la descrizione dell'immagine

Answer 1

divisionsembra essere un alias di colonna definito in select.

Ciò non è consentito in SQL (in generale, non solo in BigQuery). Utilizzare una sottoquery o CTE per definire division. Quindi filtra nella query esterna.

Answer 2

Non è possibile riutilizzare una colonna definita nella SELECTclausola nello stesso ambito (a parte la ORDER BYclausola). Qui, l'approccio più semplice è probabilmente quello di spostare le sottoquery nella FROMclausola:

SELECT 
    CONCAT(fullvisitorId, "-", CAST(visitstarttime AS STRING)) as session,
    RANK() OVER(PARTITION BY fullvisitorId, visitstarttime ORDER BY hitNumber) as rank, 
    dim1.cd1,
    dim2.division,
    hits.hitnumber
FROM `dl-training-bigquery.NDA_CHAN*******51.ga_sessions_20191202` as session
CROSS JOIN unnest(hits) as hits
CROSS JOIN (SELECT MAX(cd.value) as cd1 FROM hits.customDimensions as cd where cd.index = 12) as dim1
CROSS JOIN (SELECT cd.value as division FROM hits.customDimensions as cd where cd.index = 29) as dim2  
WHERE dim2.division = 'fsh'
ORDER BY hitnumber ASC

Nota che ho semplificato la PARTITION BYclausola della funzione finestra: non penso che tu abbia bisogno della concatenazione di stringhe qui, puoi semplicemente usare una partizione a due livelli.

Ho anche cambiato i join impliciti in espliciti cross join. Questo non cambia la logica, ma rende più chiaro secondo me l'intento.