Vấn đề tập lệnh truy vấn lớn SQL

Question

bạn có thể giúp tôi, tôi không hiểu có chuyện gì với WHERE của tôi. Tôi đã thử nhiều cách nhưng không thể đưa ra kết luận đúng.

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

nhập mô tả hình ảnh ở đây

Answer 1

divisiondường như là một bí danh cột được xác định trong select.

Điều này không được phép trong SQL (nói chung, không chỉ BigQuery). Sử dụng một truy vấn con hoặc CTE để xác định division. Sau đó lọc trong truy vấn bên ngoài.

Answer 2

Bạn không thể sử dụng lại cột được xác định trong SELECTmệnh đề trong cùng một phạm vi (bên trái ORDER BYmệnh đề). Ở đây, cách tiếp cận đơn giản nhất có lẽ là chuyển các truy vấn con đến FROMmệnh đề:

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

Lưu ý rằng tôi đã đơn giản hóa PARTITION BYmệnh đề của hàm cửa sổ: Tôi không nghĩ rằng bạn cần nối chuỗi ở đây, bạn chỉ có thể sử dụng phân vùng hai cấp.

Tôi cũng đã thay đổi các phép nối ngầm thành cross joins rõ ràng . Điều này không thay đổi logic, nhưng làm cho ý định rõ ràng hơn theo quan điểm của tôi.