Rank() in bigquery

Question

我正在尝试根据产品计数分配排名值（例如，产品 1 的计数为 100，最大应该有等级 1，产品 2 的第二高计数 80 应该有等级 2，依此类推）有，但是当我执行下面的查询时，我收到一个错误说 - 在表中找不到字段"rank_
"。请让我知道我哪里出错
了

SELECT
  ProdName,
  NoofProds,
  rank_
FROM (
  SELECT
    ProdName,
    COUNT(ProdName) AS NoofProds,
    RANK() OVER (PARTITION BY ProdName ORDER BY NoofProds DESC) AS rank_
  FROM
    [prodtable]
  WHERE
    (STRFTIME_UTC_USEC(Timestamp,"%Y%m%d")) = (STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -1, "day"), "%Y%m%d"))
  GROUP BY
    1,
    3
  ORDER BY
    2 DESC)
GROUP BY
  1,
  2,
  3
ORDER BY
  rank_ DESC

Answer 1

试试下面

SELECT
  ProdName,
  NoofProds,
  RANK() OVER(ORDER BY NoofProds DESC) AS rank_ 
FROM
(
  SELECT
    ProdName,
    COUNT(ProdName) AS NoofProds 
  FROM [prodtable]
  WHERE (STRFTIME_UTC_USEC(Timestamp,"%Y%m%d")) = (STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -1, "day"), "%Y%m%d"))
  GROUP BY 1
)
ORDER BY rank_ DESC