sql - 关于根据 bigquery 中的 2 个值对数据集进行排名的问题

Question

大家好！stackoverflow 的新手。我在 BigQuery 中遇到 RANK()OVER (PARTITION BY... 的问题。

下表有 3 列。时间戳、代码和 User_id。我希望能够为每个代码对这些进行排名，当有一个新的 user_id 时，它应该将其计为一个新值。

rank    timestamp                      Codes            user_id
1       2020-08-21 17:13:37.457 UTC    XF7RNCNT4HF7XT   JR3k3LODRHHG
2       2020-08-24 01:39:21.625 UTC    XF7RNCNT4HF7XT   26a02014-20ae-43b6-b491-bd7292a143c4
2       2020-08-24 01:39:56.815 UTC    XF7RNCNT4HF7XT   26a02014-20ae-43b6-b491-bd7292a143c4
3       2020-08-26 21:30:47.215 UTC    XF7RNCNT4HF7XT   PC1795338
4       2020-08-29 18:54:48.032 UTC    XF7RNCNT4HF7XT   PC9602007

标签： sqlcountgoogle-bigquerydistinctrank

Answer 1

嗯。. . 我认为您可以使用lag()和累积总和：

select t.*,
       sum(case when prev_code = code then 0 else 1 end) over (partition by code order by timestamp) as ranking
from (select t.*,
             lag(user_id) over (partition by code order by timestamp) as prev_code
      from t
     ) t;

如果一个代码出现多次（由其他用户 id 分隔），上面将计算一个用户 id 两次。如果您想根据第一次出现来计算用户 id，则获取最小时间戳并将其用于dense_rank()：

select t.*,
       dense_rank() over (partition by code order by min_timestamp) as ranking
from (select t.*,
             min(timestamp) over (partition by code, user) as min_timestamp
      from t
     ) t;