SQL大查询脚本问题 [英] SQL big query script issue

问题描述

您能帮我吗，我不明白我的问题在哪里.我尝试了几件事，但未能得出正确的结论.

could you help me, I don't understand whats wrong with my WHERE. I tried several things but couldn't end to the right conclusion.

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

在此处输入图片描述

推荐答案

您不能在同一范围(ORDER BY子句分开)中重复使用SELECT子句中定义的列.在这里，最简单的方法可能是将子查询移至FROM子句:

You can't reuse a column defined in the SELECT clause in the same scope (left apart the ORDER BY clause). Here, the simplest approach is probably to move the subqueries to the FROM clause:

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

请注意，我简化了window函数的PARTITION BY子句:我认为这里不需要字符串连接，您可以使用两级分区.

Note that I simplified the PARTITION BY clause of the window function: I don't think you need string concatenation here, you can just use a two-levels partition.

我还将隐式联接更改为显式的cross join.在我看来，这不会改变逻辑，但是可以使意图更加清晰.

I also changed the implicit joins to explicit cross joins. This does not change the logic, but makes the intent clearer in my opinion.

这篇关于SQL大查询脚本问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！