思路分析:
(1)近xx天,最大日期肯定就是最新的一天,故先用max(order_date) over() today计算当天日期
(2)过滤出最近90天的订单并且按照user_id,product_id分组求购买次数;
(3)如果购买次数为1则忽略,大于1参与计数一次分母是总人数
cast(sum(IF(order_time = 1, 0, 1)) / count(1) as decimal(16, 2)) cpr
注:decimal格式数据说明16表示总长度,decimal(16, 2)表示整数部分长度为16-2=14位。小数部分长度为2位,如果整数部分超过了14位则被截断,数据会出现错误。
代码实现:
SELECT product_id,--如果购买次数为1则忽略,大于1参与计数一次;分母是总人数cast(sum(IF(order_time = 1, 0, 1)) / count(1) as decimal(16, 2)) cpr
from(select user_id,product_id,--求购买次数count(1) order_timefrom(SELECT user_id,product_id,order_date,max(order_date) over() todayfrom order_detail) t1where datediff(today, order_date) <= 90group by product_id,user_id) t2
group by product_id
order by cpr desc,product_id;