sql - distinct 和 Over 分区

Question

我正在处理 bigquery 中的查询，但遇到以下问题。我的查询如下：

SELECT DISTINCT (concat(rut,created_at)),*,ROW_NUMBER () OVER (PARTITION BY rut ORDER BY created_at ASC) AS ranking 
FROM table 
WHERE DATE(created_at) <= "2020-12-03" 
order by rut,ranking

问题是“DISCINT”并没有消除重复数据，而是如果我消除了OVER PARTITION并将查询保留如下，如果它消除了重复数据。

SELECT DISTINCT (concat(rut,created_at)) ,*
FROM table 
WHERE DATE(created_at) <= "2020-12-03" 
order by rut

显然，这是 disint 和 OVER PARTITION 如何工作的问题，但我找不到解决方案

标签： sqlgoogle-bigquery

Answer 1

你需要这样做：

select * ,ROW_NUMBER () OVER (PARTITION BY rut ORDER BY created_at ASC) AS ranking  
from (
SELECT DISTINCT (concat(rut,created_at)),*
FROM table 
WHERE DATE(created_at) <= "2020-12-03" 
) tt
order by rut,ranking