如何使用与组中的值相关的行号

I have my data as follows:

我的数据如下：

Pagetype    member_id    created_at    rownum    
   A            2          date          1            
   B            2          date          2             
   C            2          date          3             
   D            4          date          1            
   B            4          date          2             
   R            4          date          3             
   B            13         date          1             
   S            13         date          2             
   B            13         date          3             

And I would like to add another column to it as follows:

我想在其中添加另一列，如下所示：

Pagetype    member_id    created_at    rownum    DesiredRownum
   A            2          date          1            -1
   B            2          date          2             0
   C            2          date          3             1
   D            4          date          1            -1
   B            4          date          2             0
   R            4          date          3             1
   B            13         date          1             0
   S            13         date          2             1
   B            13         date          3             2

I would like to assign the value 0 to this DesiredColumn whenever PageType is B for a given member_id. Any values of PageType before B for any member_id should be assigned negative values, and any values of PageType after B for any member_id should be asigned increasing positive values.

每当PageType为给定member_id的B时，我想将值0分配给此DesiredColumn。任何member_id的B之前的PageType的任何值都应该被赋值为负值，并且任何member_id的B之后的PageType的任何值都应该被赋值增加正值。

The query I used to get my data is as follows:

我用来获取数据的查询如下：

select pagetype,
member_id,
created_at,
row_number() over(partition by member_id order by created_at)
from table
order by member_id,
created_at

How do I add this new column to my data?

如何将此新列添加到我的数据中？

EDIT: Slight change. The PageType can repeat for any given user. For example, the PageType B repeats for member_id 13. In this case, we would want to calculate values wrt the first occurence of B.

编辑：轻微的变化。 PageType可以为任何给定用户重复。例如，PageType B重复了member_id 13.在这种情况下，我们希望计算出第一次出现的值。

1 个解决方案

#1

After calculating the row numbers, you can get the value for "B" and use that for the calculation:

计算行号后，您可以获取“B”的值并将其用于计算：

select t.*,
       (seqnum -
        max(case when pagetype = 'B' then seqnum end) over (partition by member_id)
       ) as b_diff
from (select pagetype, member_id, created_at,
             row_number() over (partition by member_id order by created_at) as seqnum
      from table
     ) t
order by member_id, created_at