sql - 如果有活动日期，则以 0 重新开始行号

Question

如何使用 Active 作为最后登录日期填充上个月登录列？

标签： sqlpostgresql

Answer 1

您可以使用累积最大值来获取最近的active日期。诀窍就是几个月的差异。要获取最近的活动日期：

select t.*,
       max(active) over (partition by userid order by date) as most_recent_active          
from t;

然后是之间的几个月：

select t.*,
       (extract(year from most_recent_active - date) * 12 + 
        extract(month from most_recent_active)
       ) as months_since
from (select t.*,
             max(active) over (partition by userid order by date) as most_recent_active          
      from t
     ) t;