时间戳

首页 > 解决方案 > 在bigquery中选择除一列之外的所有列

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

问题描述

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

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

标签： sqlgoogle-bigquery

解决方案

你可以试试下面

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

推荐阅读