创建一个根据Hive中的订单日期更新的标志

Question

我想创建一个标志列"order_type"，它将根据日期差异进行更新。正如下面所附的输入和输出数据示例，我的要求是，如果消费者在最后一次购买后12个月进行了购买，他们应该再次被视为第一次购买者。

以下输入数据-

-----------------------------------------
|customer_id|   order_id    |order_date|
-----------------------------------------
|1234       |   1           |2017-07-06|
|1234       |   2           |2018-09-17|
|1234       |   3           |2018-09-20|
|1234       |   4           |2019-05-16|
|1234       |   5           |2020-09-15|
|-----------|---------------|----------|

输出数据

--------------------------------------------------------|
|customer_id|   order_id    |order_date| order_type     |
--------------------------------------------------------|
|1234       |   1           |2017-07-06| First purchase |
|1234       |   2           |2018-09-17| First purchase |
|1234       |   3           |2018-09-20| Second purchase|
|1234       |   4           |2019-05-16| Second plus purchase
|1234       |   5           |2020-09-15| First purchase |
|-----------|---------------|----------|----------------|

即首次购买-2017-07-06-第一次购买(第二次购买(-2018-09-17(由于第一次和第二次之间的日期差异超过12个月(-第二次购买(第三次购买(-2018-09-20(由于第二次和第三次订单之间的日期差异小于12个月(-第二次加购(第四次购买(-2019-05-16(自第三次和第四次订单之间的日期差异小于12个月(-首次购买(最后一次购买(-2020-09-15(自12个月以来(

Answer 1

使用lag()获取上一个order_date。然后用累积和来定义组和row_number()来枚举：

select t.*,
row_number() over (partition by customer_id, grp order by order_date) as order_type
from (select t.*,
sum(case when prev_order_date > add_months(order_date, -12)
then 0 else 1
end) over (partition by customer_id order by order_date) as grp
from (select t.*,
lag(order_date) over (partition by customer_id order by order_date) as prev_order_date
from t
) t
) t;

这只是将订单类型显示为数字，而不是转换为英语短语。