我正在尝试将bigquery的日期参数连接到datastudio，因此我在查询中添加了一些日期变量。不过，我在这一天遇到了一些问题。
我的问题是：

SELECT first_item,
  COUNT(*) AS first_purchases,
  SUM(purchases_within_90_days) AS purchased_within_90_days,
  SUM(purchases_within_180_days) AS purchased_within_180_days,
  SUM(purchases_within_270_days) AS purchased_within_270_days,
  SUM(revenue90days) as total_revenue_90,
  SUM(revenue180days) as total_revenue_180,
  SUM(revenue270days) as total_revenue_270
  FROM (

  SELECT email, first_item, processed_at, 
    SUM(purch_90_days) OVER(PARTITION BY email) AS purchases_within_90_days, SUM(rev_90) OVER(PARTITION BY email) AS revenue90days,
    SUM(purch_180days) OVER(PARTITION BY email) AS purchases_within_180_days, SUM(rev_180) OVER(PARTITION BY email) AS revenue180days,
    SUM(purch_270days) OVER(PARTITION BY email) AS purchases_within_270_days, SUM(rev_270) OVER(PARTITION BY email) AS revenue270days
  FROM (

SELECT email, first_item, processed_at, SUM(purchases_within_90_days) as purch_90_days, SUM(purchases_within_180_days) as purch_180days, SUM(purchases_within_270_days) as purch_270days, SUM(revenue_within_90_days) as rev_90, SUM(revenue_within_180_days) as rev_180, SUM(revenue_within_270_days) as rev_270
FROM (

SELECT   email, processed_at, first_item, MAX(CASE WHEN hours_since_first_purchase < 90 * 24 AND hours_since_first_purchase > 0 THEN 1 ELSE 0 END) AS purchases_within_90_days,
  MAX(CASE WHEN hours_since_first_purchase < 180 * 24 AND hours_since_first_purchase > 0 THEN 1 ELSE 0 END) AS purchases_within_180_days,
  MAX(CASE WHEN hours_since_first_purchase < 270 * 24 AND hours_since_first_purchase > 0 THEN 1 ELSE 0 END) AS purchases_within_270_days,
  SUM(CASE WHEN hours_since_first_purchase < 90 * 24 AND hours_since_first_purchase > 0 THEN price ELSE 0 END) AS revenue_within_90_days,
  SUM(CASE WHEN hours_since_first_purchase < 180 * 24 AND hours_since_first_purchase > 0 THEN price ELSE 0 END) AS revenue_within_180_days,
  SUM(CASE WHEN hours_since_first_purchase < 270 * 24 AND hours_since_first_purchase > 0 THEN price ELSE 0 END) AS revenue_within_270_days,
FROM (

 SELECT order_number, email, processed_at, sku, price, hours_since_first_purchase, first_date,
 CASE
   WHEN hours_since_first_purchase = 0 OR hours_since_first_purchase is null then sku
   else null
   end as first_item,
 FROM (

SELECT order_number, customer.id, email, MIN(processed_at) over(partition by email) as first_date, processed_at, title, price,sku,
    CASE
     WHEN ROW_NUMBER() OVER(PARTITION BY customer.id ORDER BY processed_at) = 1 THEN null
      ELSE TIMESTAMP_DIFF(processed_at, FIRST_VALUE(processed_at) OVER(PARTITION BY customer.id ORDER BY processed_at), HOUR)
      END AS hours_since_first_purchase      
FROM (

SELECT * EXCEPT(instance, line_items) FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY id) AS instance
  FROM `table.orders`
), UNNEST(line_items) as item
   -- identify duplicate rows
WHERE instance = 1 
)

order by email desc
  )

where first_date >  PARSE_DATE('%Y%m%d', @DS_START_DATE) and first_date < PARSE_DATE('%Y%m%d', @DS_END_DATE);
--where first_date <= '2019-09-28'--and first_date > '2020-06-07'
)

group by first_item, email, processed_at
)

where email <> ""
group by email, first_item,processed_at
order by processed_at asc
)

order by processed_at asc
  )
  where first_item is not null and first_item <> "" and first_item <> "unknown" and first_item not like '%variant%' and first_item not like '%product%' 
  group by first_item

当我尝试筛选第一个\u date变量时，datastudio给了我一个查询错误。我能做些什么来过滤我添加的这个新变量吗？
我收到错误“”查询返回错误“”
导致此错误的代码行如下：

where first_date >  PARSE_DATE('%Y%m%d', @DS_START_DATE) and first_date < PARSE_DATE('%Y%m%d', @DS_END_DATE)

当我使用以下命令切换该行时，我的查询将完美执行：

where first_date <= '2019-09-28'--and first_date > '2020-06-07'

更新：
这是如此接近工作。当我应用了其中一个过滤器时，它就工作了，但是当我应用了第二个过滤器时，它抛出了相同的错误。
当我添加这一行时，它就起作用了：

where cast(first_date as date) <=  PARSE_DATE('%Y%m%d', @DS_END_DATE)

但当我有这个问题的时候，我又犯了一个错误：

where cast(first_date as date) <=  PARSE_DATE('%Y%m%d', @DS_END_DATE) and cast(first_date as date) >=  PARSE_DATE('%Y%m%d', @DS_START_DATE)