为sql解码行中的最大数目

Question

我在bigquery中使用#standardsql，并试图将每个customer_id的maksimum排序编码为1，其余的是0。

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

​

​

对排名的查询如下

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

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

​

​

谢谢

Answer 1

根据您的示例数据，您的排名是不稳定的，因为您有多个具有相同键值的行。无论如何，您仍然可以在不使用子查询的情况下执行您想做的事情，只需使用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;

做同样事情的一种更传统的方法是使用降序排序：

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;

Answer 2

我们可以包装当前的查询，然后使用MAX作为一个分析函数，由客户划分一个分区，以比较每个客户的每个ranking值与最大ranking。当ranking值等于客户的最大值时，我们为custom_coded分配1，否则就分配0。

SELECT
    customer_id, item_bought, booking_date, ranking,
    CASE WHEN ranking = MAX(ranking) OVER (PARTITION BY customer_id)
         THEN 1 ELSE 0 END AS custom_coded
FROM
(
    SELECT customer_id, item_bought, booking_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY booking_date) ranking
    FROM yourTable
) t;