左边是数据源,右边是想呈现的表,其实就是计算一下所有用户本次购买与上次购买时间间隔分布
为了输出该格式的数据,自建了一张表叫ot_buyInfo;包含id(自增),username,date;录入了初始数据:
SQL输出思路
1、首先根据用户名计算出本次购买与上次购买的时间间隔天数
1)先进行排序,排序字段用户(升序)、时间(降序)
SELECT
*
FROM
ot_buyinfo a
ORDER BY
username,
date DESC
2)利用时间函数 DATEDIFF(参数1,参数2)进行时间的减法运算
在本SQL中,第一个参数取主查询的date,第二个参数写个子查询进行查询。最终SQL如下:
SELECT
id,
username,
date,
(
DATEDIFF(
date,(
SELECT
date
FROM
ot_buyinfo
WHERE
username = a.username
AND date < a.date
ORDER BY
date DESC
LIMIT 0,
1
))) jgts
FROM
ot_buyinfo a
ORDER BY
username,
date DESC
效果图:
3)为了确认时间是否能对上,计算是不是有问题。我又在SQL中加了一个子查询。
SELECT
id,
username,
date,
( SELECT date FROM ot_buyinfo WHERE username = a.username AND date < a.date ORDER BY date DESC LIMIT 0, 1 ) '上一次',
(
DATEDIFF(
date,(
SELECT
date
FROM
ot_buyinfo
WHERE
username = a.username
AND date < a.date
ORDER BY
date DESC
LIMIT 0,
1
))) jgts
FROM
ot_buyinfo a
ORDER BY
username,
date DESC
效果图:
4)由3)可以看出,再添加一个jgts IS NOT NULL 条件就可以得出用户两次购买的间隔天数了。
2、购买人数逻辑:
以计算出来的jgts为基数做统计,则在添加一个 GROUP BY jgts
SELECT
jgts, -- 间隔天数
COUNT( 1 ) gmrs -- 购买人数
FROM
(
SELECT
id,
username,
date,
(
DATEDIFF(
date,(
SELECT
date
FROM
ot_buyinfo
WHERE
username = a.username
AND date < a.date
ORDER BY
date DESC
LIMIT 0,
1
))) jgts
FROM
ot_buyinfo a
ORDER BY
username,
date DESC
) r
WHERE
r.jgts IS NOT NULL
GROUP BY
jgts
效果图:
3、计算人数占比:
因已经计算出间隔天数、对应购买人数;那么人数占比就应该是间隔天数对应的购买人数/总人数
先查出ot_buyinfo表有多少用户(去重);
表达式:【购买人数/总人数】
用 convert() 函数对计算出的结果进行处理,保留2位小数
用 concat() 函数拼接一个‘%’符号
SELECT
jgts,
COUNT( 1 ) gmrs,
-- -------------------该位置是计算人数占比
(
CONCAT(
CONVERT ((
COUNT( 1 )/(
SELECT
COUNT( 1 )
FROM
( SELECT DISTINCT username FROM ot_buyinfo ) l -- 统计出本表购买人数一共多少(去重)
)* 100
),
DECIMAL ( 15, 2 ) -- 保留2位小数
),
'%'
)
) rszb
-- -------------------该位置是计算人数占比
FROM
(
SELECT
id,
username,
date,
(
DATEDIFF(
date,(
SELECT
date
FROM
ot_buyinfo
WHERE
username = a.username
AND date < a.date
ORDER BY
date DESC
LIMIT 0,
1
))) jgts
FROM
ot_buyinfo a
ORDER BY
username,
date DESC
) r
WHERE
r.jgts IS NOT NULL
GROUP BY
jgts
最终结果图: