BigQuery错误:遇到"OVER“”OVER“

Question

我试图在sql控制台中执行这个BigQuery语句，但是我得到了一个错误，如下所示。

有人能提出这个建议吗？

"Error: Encountered " "OVER" "OVER "" at line 5, column 99. Was expecting: "END" ..."

SELECT      *
FROM        (SELECT
                  *
                        , CASE 
                            WHEN initiator in ('01_User') THEN RANK() OVER (PARTITION BY item_id, log_date_desc, type ORDER BY log_date_desc DESC)
              ELSE RANK() OVER (PARTITION BY item_id, log_date_desc ORDER BY log_date_desc  DESC) 
              END AS ord
            FROM        temp.step1_itemlogall
            ) AS t
WHERE       ord = 1

谢谢。

Answer 1

我认为问题是在案例陈述中加入分析函数。您可以将其重写为计算这两个函数的内部选择，然后在外部查询中选择正确的函数。例如：

SELECT *
FROM (
  SELECT    
    CASE WHEN initiator IN ('01_User') 
    THEN ord1
    ELSE ord2    
    END AS ord
  FROM (  
  SELECT *,
      RANK() OVER (PARTITION BY item_id, log_date_desc, type
                   ORDER BY log_date_desc DESC) as ord1,
      RANK() OVER (PARTITION BY item_id, log_date_desc 
                   ORDER BY log_date_desc  DESC) as ord2
    FROM temp.step1_itemlogall))
WHERE ord = 1

但是，我认为对错误信息的真正解释是：https://www.youtube.com/watch?v=2OBZf0QdKdE

Answer 2

切换到UI或API调用中的标准SQL，只有遗留SQL引擎引发此错误。