Question

Is it possible to have multiple WITH clause statements in the BigQuery editor with #standardSQL e.g:

#StandardSQL

#features
WITH features AS (
SELECT
fullVisitorId,
IFNULL(totals.bounces, 0) AS bounces,
IFNULL(totals.timeOnSite, 0) AS time_on_site
FROM
`data-to-insights.ecommerce.web_analytics`
WHERE
totals.newVisits = 1
AND date BETWEEN  20160801  AND  20170430 );

# features labels
WITH features_label AS ( 
SELECT
fullvisitorid,
IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
FROM
`data-to-insights.ecommerce.web_analytics`
GROUP BY fullvisitorid);


# Combine features with feature labels for model training
SELECT * EXCEPT(fullVisitorID)
FROM features
JOIN features_label
USING (fullVisitorId);

我正在带错<代码>Syntax错误:未预料的“;”在[13:44]上,但无法确定多条<代码>与之间。条款是行不通的。

Answer 1

以下简称表

WITH features AS (
  SELECT
  fullVisitorId,
  IFNULL(totals.bounces, 0) AS bounces,
  IFNULL(totals.timeOnSite, 0) AS time_on_site
  FROM `data-to-insights.ecommerce.web_analytics`
  WHERE totals.newVisits = 1
  AND date BETWEEN  20160801  AND  20170430 
), features_label AS ( 
  SELECT
  fullvisitorid,
  IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
  FROM `data-to-insights.ecommerce.web_analytics`
  GROUP BY fullvisitorid
)
SELECT * EXCEPT(fullVisitorID)
FROM features
JOIN features_label
USING (fullVisitorId)

友情链接