“ sql日志，记录那些年常见sql语句。分为需求背景、mysql实现、注意事项三大部分。”

一、需求背景

    复购率是客户运营中考核客户质量的重要指标，类似体现功能粘性的回访率等。具体题目如下：

计算每一天新客，在后续30天的复购率和人均复购金额。

二、sql实现：

select   order_date, -- 新增客户时间
     sum(is_fugou)/count(user_id) AS '复购率',
     sum(money)/count(is_fugou) AS '人均复购金额' -- 30日内人均复购率，次日计算30日内购买总金额，不包括不复购的客户
from 
(select 
    t1.user_id,
    t1.order_date,
    max(case when t1.order_date_30>=t2.order_date and t1.order_date<t2.order_date then 1 else 0 end) AS is_fugou,
    sum(case when t1.order_date_30>=t2.order_date and t1.order_date<t2.order_date then 1 else 0 end) AS money -- 复购金额
from 
    (--  新客户的首次下单时间,至少下单30天完成才计算
    select 
        user_id,
        order_date,
        date_add(order_date,INTERVAL 30 DAY) as order_date_30
    from vip_orders 
    where is_newuser=1
    and datediff(NOW(),order_date)>=30
    -- and  date_add(order_date,INTERVAL 30 DAY)<=(NOW()) -- 新客户距离当前的平台周期至少是30天
    group by  user_id,
        order_date, date_add(order_date,INTERVAL 30 DAY) 
    )t1 left join ( -- 老客户购买记录,多对一.
        select 
        user_id,
        order_date,
        sum(money) money
    from vip_orders 
    where is_newuser=0
    group by  user_id,
        order_date
    )t2 on t1.user_id=t2.user_id 
    group by t1.user_id,  t1.order_date)x 
    group by order_date

三、mysql注意事项

1）注意日期格式

获得当前日期+时间（date + time）函数：now() 

除了 now() 函数能获得当前的日期时间外，MySQL 中还有下面的函数：
current_timestamp()   current_timestamp 
localtime()   localtime 
localtimestamp()   localtimestamp    
这些日期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使用 now() 来替代上面列出的函数。

获得当前日期+时间（date + time）函数：sysdate()

sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。

获得当前日期（date）函数：curdate() 

其中，下面的两个日期函数等同于 curdate()：current_date(),current_date

获得当前时间（time）函数：curtime() 

其中，下面的两个时间函数等同于 curtime()：current_time(),current_time

获得当前 UTC 日期时间函数：utc_date(), utc_time(), utc_timestamp() 

因为我国位于东八时区，所以本地时间 = UTC 时间 + 8 小时。UTC 时间在业务涉及多个国家和地区的时候，非常有用。

MySQL 日期时间 Extract（选取） 函数

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒 

set @dt = '2008-09-10 07:15:30.123456'; 
select date(@dt); -- 2008-09-10 
select time(@dt); -- 07:15:30.123456 
select year(@dt); -- 2008 
select quarter(@dt); -- 3 
select month(@dt); -- 9 
select week(@dt); -- 36 
select day(@dt); -- 10 
select hour(@dt); -- 7 
select minute(@dt); -- 15 
select second(@dt); -- 30 
select microsecond(@dt); -- 123456

2、DATE_ADD()用法，注意和Oracle、hql的差异

DATE_ADD(date,INTERVAL expr type)

定义和用法

DATE_ADD() 函数向日期添加指定的时间间隔。

语法

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

实例

假设我们有如下的 "Orders" 表：

现在，我们想要向 "OrderDate" 添加 45 天，这样就可以找到付款日期。

我们使用下面的 SELECT 语句：

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate
FROM Orders

结果：

参考：https://www.cnblogs.com/dj1232090/p/13474783.html

https://www.runoob.com/sql/func-date-add.html

相关推荐

生命周期价值LTV预测

简单可视化用户轨迹分析（含旭日图）

By the way 你的打赏和关注是我的动力哦~