sql - 选择 bigquery 中除一列以外的所有列

Question

我想排除计数以删除以下查询中的重复项

SELECT *, count(1) as count except (count)
  FROM PRD.sites  
  group by site,id ,site_desc,timestamp
  having count(1) < 2

但在 [1:29] 出现错误 Unexpected keyword EXCEPT。我正在使用标准 SQL

Answer 1

你可以试试下面

select * except(count)
from
(
  SELECT *, count(1) as count 
  FROM PRD.sites  
  group by site,id ,site_desc,timestamp
  having count(1) < 2
)X

或者您可以使用 row_number() 查找重复项

SELECT * except(rn)
FROM (
  SELECT
      *,
      ROW_NUMBER()
          OVER (PARTITION BY site,id ,site_desc,timestamp) as rn
  FROM FROM PRD.sites
)
WHERE rn= 1