sql - SQL大查询脚本问题
问题描述
你能帮帮我吗,我不明白我的 WHERE 有什么问题。我尝试了几件事,但无法得出正确的结论。
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
解决方案
您不能SELECT
在同一范围内重用子句中定义的列(将子句分开ORDER BY
)。在这里,最简单的方法可能是将子查询移动到FROM
子句:
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
请注意,我简化了PARTITION BY
窗口函数的子句:我认为您不需要在这里进行字符串连接,您可以使用两级分区。
我还将隐式连接更改为显式连接cross join
。这不会改变逻辑,但在我看来使意图更清晰。
推荐阅读
- minecraft - 如何更改特定坐标处的块?
- javascript - Javascript:创建以平均每分钟 1~2 次的随机间隔执行回调函数的函数
- java - java.io.FileReader 不存在
- amazon-web-services - 如果我在 aws 设备场上运行测试脚本期间调用 API,需要将 IP 范围列入白名单(我正在使用放心)
- java - 使用实体方法作为 MapStruct 源
- python - TypeError: write() 参数必须是 str,而不是 Entry
- typescript - Firebase 和 Firestore 的打字稿类型
- visual-studio-code - OV2640 摄像头和 ESP32 的问题
- node.js - 查询不属于特定字段类型的 Mongoose 模型
- ffmpeg - 使用 FFmpeg 解码视频帧并发送到 RabbitMQ