MySQL查询有点棘手

嗨,我想在mySQL中设计此查询。

声明:对于2017年期间交易的所有客户,在30天内进行另一笔交易的百分比是多少?

你能告诉我如何设计这种查询吗?

这是在以下位置执行此查询的表的图片:

评论
  • edolor
    edolor 回复

    Just use lead() to get the next date. Then aggregate at the customer level to determine if any transaction in the time period has another within 30 days for that customer.

    最后,再次聚合:

    select avg(case when mindiff < 30 then 1.0 else 0 end) as within_30days
    from (select customerid, min(datediff(next_date - date)) as mindiff
           from (select t.*, lead(date) over (partition by customerid order by date) as next_date
                 from t
                ) t
         ) t
         where date >= '2017-01-01  and date < '2018-01-01'
         group by customerid
        ) c