mysql - 将 MySQL 查询转换为 BigQuery 查询

Question

我在将 MySQL 查询转换为 Google Bigquery 查询时遇到问题。这是我的 MySQL 查询

SELECT id
FROM office_details
GROUP BY address
HAVING max(value)
ORDER BY id

此查询在 phpMyAdmin 和我的 php 脚本上运行完美。但是当我将它转换为 bigquery 时

SELECT id
FROM Office_db.office_details
GROUP BY address
HAVING max(value)
ORDER BY id

它说 id 列不在分组依据中，也不聚合。

我需要的是唯一 address 的 id，其中 value 是最大值。例如

+-------------------------+
| id  |  address |  value |
+-------------------------+
| 1   |    a     |   4    |
| 2   |    a     |   3    |
| 3   |    b     |   2    |
| 4   |    b     |   2    |
+-------------------------+

我需要

+----+
| id |
+----+
| 1  |
| 3  |
+----+

Answer 1

#standardSQL
SELECT id FROM (
  SELECT 
    id, address, 
    ROW_NUMBER() OVER(PARTITION BY address ORDER BY value DESC, id) AS flag
  FROM office_details
)
WHERE flag = 1