Problème de script de grande requête SQL

Question

pourriez-vous m'aider, je ne comprends pas ce qui ne va pas avec mon O. J'ai essayé plusieurs choses mais je n'ai pas réussi à conclure à la bonne.

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

entrez la description de l'image ici

Answer 1

divisionsemble être un alias de colonne défini dans le select.

Cela n'est pas autorisé dans SQL (en général, pas seulement dans BigQuery). Utilisez une sous-requête ou CTE pour définir division. Puis filtrez dans la requête externe.

Answer 2

Vous ne pouvez pas réutiliser une colonne définie dans la SELECTclause dans la même portée (à part la ORDER BYclause). Ici, l'approche la plus simple consiste probablement à déplacer les sous-requêtes vers la FROMclause:

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

Notez que j'ai simplifié la PARTITION BYclause de la fonction window: je ne pense pas que vous ayez besoin d'une concaténation de chaînes ici, vous pouvez simplement utiliser une partition à deux niveaux.

J'ai également changé les jointures implicites en cross joins explicites . Cela ne change pas la logique, mais rend l'intention plus claire à mon avis.