为sql解码最大行数 [英] Decode maximum number in rows for sql

问题描述

我在bigquery中使用 #standardsql ，并试图将每个customer_id的最高等级编码为 1 ，其余部分是 0

这是到目前为止的查询结果

排名查询是这样的

  ROW_NUMBER（）OVER（由customer_id ORDER BY booking_date Asc分隔）作为等级
  

我需要的是创建另一个这样的列，它将每个 customerid 的最大排名解码为1，将其下面的数字解码为 0 就像下面的表格

谢谢

unstable ，因为您有多个具有相同键值的行。在任何情况下，只要使用 case ：

 选择吨。*，
 row_number（）over（由customer_id order by booking_date asc分区）作为排名，
（row_number（）超过时的分区情况（按customer_id排序，按booking_date asc划分） = 
 count（*）over（由customer_id分区）
 then 1 else 0 
 end）as custom_coded 
 from t; 
  

更传统的做同样事情的方法是使用递减排序：

  select t。*，
 row_number（）over（由customer_id order by booking_date asc分区）作为排名，
 （当row_number（）超过时（由customer_id order by booking_date desc分区）= 1 
然后1 else 0 
 end）作为custom_coded 
 from t; 
  

I am using the #standardsql in bigquery and trying to code the maksimum ranking of each customer_id as 1, and the rest of it are 0

This is the query result so far

The query for ranking is this

ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY booking_date Asc) as ranking

What i need is to create another column like this where it decode the maximum ranking of each customerid as 1, and the number below it as 0 just like the below table

Thanks

解决方案

Based on your sample data, your ranking is unstable, because you have multiple rows with the same key values. In any case, you can still do what you want without subqueries, just using case:

select t.*,
       row_number() over (partition by customer_id order by booking_date asc) as ranking,
       (case when row_number() over (partition by customer_id order by booking_date asc) =
                  count(*) over (partition by customer_id)
             then 1 else 0
        end) as custom_coded
from t;

A more traditional way of doing essentially the same thing would be to use a descending sort:

select t.*,
       row_number() over (partition by customer_id order by booking_date asc) as ranking,
       (case when row_number() over (partition by customer_id order by booking_date desc) = 1
             then 1 else 0
        end) as custom_coded
from t;

这篇关于为sql解码最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！