sql - Dense Rank Skip Null 值按多个分区

Question

我有一张如下所示的表格：

       Date      User      Product
 11/15/2019       123         NULL
 11/21/2019       123            A
 11/21/2019       123            A
 11/23/2019       123            B

我想运行一个dense_rank 函数来跳过空值。

以下是我目前拥有的：

CASE WHEN PRODUCT IS NOT NULL 
THEN DENSE_RANK() 
OVER (PARTITION BY USER ORDER BY DATE ASC) 
ELSE 1 
END DENSE_RANK_OUTPUT 

我当前的输出：

       Date      User      Product     DENSE_RANK_OUTPUT
 11/15/2019       123         NULL                     1
 11/21/2019       123            A                     2
 11/21/2019       123            A                     2
 11/23/2019       123            B                     3

我想要的输出是：

       Date      User      Product        DESIRED_OUTPUT
 11/15/2019       123         NULL                     1
 11/21/2019       123            A                     1
 11/21/2019       123            A                     1
 11/23/2019       123            B                     2

标签： sqlnullsnowflake-cloud-data-platformpartitiondense-rank

Answer 1

You are close. Just use another key in the partition by:

(CASE WHEN PRODUCT IS NOT NULL 
      THEN DENSE_RANK() OVER (PARTITION BY USER, (PRODUCT IS NOT NULL) ORDER BY DATE ASC) 
      ELSE 1 
 END) as DENSE_RANK_OUTPUT