Snowflake中存在/不存在月份的用户累计求和实现问询

原始数据表 #

ID   action_date     data
001   2021-01-20     jams
002   2021-01-23   orange
003   2021-01-19   banana
001   2021-04-11   pineap
002   2021-03-01    grape
004   2021-03-01    apple

处理要求 #

需基于上述原始数据生成累计求和统计表格，规则如下：

• 为每个用户的每一个月份生成记录
• 若用户当月无数据，则沿用上月数据
• 唯一标识为「用户ID+月份」

需要生成的字段说明：

• action_taken_this_month：该ID当月是否在原表中出现（通过判断action_date是否属于当月来确定）
• total_actions：该ID在除当月外的原始表中出现的总次数
• most_recent_data：除当月外，该ID最近一次出现的data值

期望输出结果 #

ID    month_end   action_taken_this_month     total_actions most_recent_data
001  2021-01-31                         1                 0               NA
001  2021-02-28                         0                 1             jams
001  2021-03-31                         0                 1             jams
001  2021-04-30                         1                 1             jams
002  2021-01-31                         1                 0               NA
002  2021-02-28                         0                 1           orange
002  2021-03-31                         1                 1           orange
002  2021-04-30                         0                 2            grape
003  2021-01-31                         1                 1               NA
003  2021-02-28                         0                 1           banana
003  2021-03-31                         0                 1           banana
003  2021-04-30                         0                 1           banana
004  2021-03-31                         1                 1               NA
004  2021-04-30                         0                 1            apple

内容的提问来源于stack exchange，提问作者John Thomas