目录
- 查询当前日期往前三十天当天有多少数据
- 查询最近三十天,每天多少数据
- 给用户赋值一个随机日期
- 查询最近三十天内,每个用户多少数据
- 统计各个年龄段的用户数量
- 查询数据按天分组统计
- 查询过去10天订单量
- 查询过去30天订单量,没有订单量的日期也要显示
- 查询本月1日至今的所有数据按天分组,不显示上月
DATE_FORMAT(created,"%Y-%m-%d") # 对数据经行时间格式化created为数据库时间字段
DATE_SUB(NOW(),INTERVAL 30 DAY) # 时间向前减少30天,NOW可以是数据库字段也可以是NOW()代表当前时间
DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 30 DAY),"%Y-%m-%d") # 时间向后30天并格式化NOW()同理
DATE_FORMAT(STR_TO_DATE(etl_date,'%y%m%d'),'%y%m%d') # 不明,奥义·双重格式化?
查询当前日期往前三十天当天有多少数据
SELECT
DATE_FORMAT(created,"%Y-%m-%d") date,
count(id) total
FROM
follws
WHERE
DATE_FORMAT(created,"%Y-%m-%d") = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 30 DAY),"%Y-%m-%d")
GROUP BY date
#注意这里一定要是 groupby date ,
#不能是created,或者在这里对created进行日期格式化,
#否侧依然是按原数据分组,排序也是如此
查询最近三十天,每天多少数据
SELECT
DATE_FORMAT(created,"%Y-%m-%d") date,
count(id) total
FROM
follws
WHERE
DATE_FORMAT(created,"%Y-%m-%d") <= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 30 DAY),"%Y-%m-%d")
GROUP BY date
给用户赋值一个随机日期
UPDATE pzh_audio_course SET created = CONCAT(FLOOR(2021 + (RAND() * 0)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0))
查询最近三十天内,每个用户多少数据
统计各个年龄段的用户数量
先查出用户年龄
SELECT
DATE_FORMAT(NOW(),"%Y") - DATE_FORMAT(birthday,"%Y") oldm
FROM
pzh_user
然后根据不同的年龄增加一个字段,对应年龄段,
SELECT
CASE
WHEN olds.old > 0 AND olds.old < 18 THEN '未成年'
WHEN olds.old >18 AND olds.olds.old < 35 THEN '青年'
WHEN olds.old >35 AND olds.old < 55 THEN '中年'
WHEN olds.old >55 AND olds.old < 150 THEN '老年人'
ELSE '神仙' END AS '年龄段',olds.old
FROM
(
SELECT
DATE_FORMAT(NOW(),"%Y") - DATE_FORMAT(birthday,"%Y") AS old
FROM
pzh_user
) AS olds
选中部分就是原来查询年龄列表的sql
最后我们再根据新增的年龄段字段进行分组统计数量就好了
SELECT
nnds.nnd,
COUNT(*)
FROM
(
SELECT
CASE
WHEN olds.old > 0 AND olds.old < 18 THEN '未成年'
WHEN olds.old >18 AND olds.old < 35 THEN '青年'
WHEN olds.old >35 AND olds.old < 55 THEN '中年'
WHEN olds.old >55 AND olds.old < 150 THEN '老年人'
ELSE '神仙' END AS 'nnd',olds.old
FROM
(
SELECT
DATE_FORMAT(NOW(),"%Y") - DATE_FORMAT(birthday,"%Y") AS old
FROM
pzh_user
) AS olds
) nnds
GROUP BY
nnds.nnd
查询数据按天分组统计
查询过去10天订单量
SELECT
DATE_FORMAT(created,'%Y-%m-%d') datas,
COUNT(*) AS total
FROM
pzh_user_goods_order
WHERE
created BETWEEN DATE_SUB(NOW(),INTERVAL 10 DAY) AND NOW() #要求日期大于10天前小于当前,你也可以直接大于十天前,这里由于测试数据有太多明后天的数据(无语了)用了between
GROUP BY
DATE_FORMAT(created,'%Y-%m-%d')
查询结果:
查询过去30天订单量,没有订单量的日期也要显示
准备一个辅助表num_aux或者使用mysql自带的一个啥helpxxx的表都行
SELECT
*
FROM
num_aux num
LEFT JOIN (
SELECT
DATE_FORMAT(created,'%Y-%m-%d') datas,
COUNT(*) AS total
FROM
pzh_user_goods_order
WHERE
created BETWEEN DATE_SUB(NOW(),INTERVAL 30 DAY) AND NOW()
GROUP BY
DATE_FORMAT(created,'%Y-%m-%d')
) bs
#这里用当前日期减去辅助表里的顺序数字天数依次关联之前查出来的表的,实现遍历的效果
#相当于是利用顺序数字和当前日期进行运算输出了一个只有指定日期区间的空表,然后在用查出来的数据,
#用日期做外键关联刚才统计出来的每日统计数据,使用leftjoin没有的就显示null
ON bs.datas = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL num.num DAY),'%Y-%m-%d')
WHERE
num.num < 30
查询结果:
最后做一下调整,由于原来的datas
是显示的初始查出来的数据
的日期,本来是没有数据
的所以这里就会出现null
。我们把日期替换成我们通过顺序数字计算出来的日期
;
然后再用IFNULL
关键字,当数量为空时显示0
如下:
SELECT
DATE_FORMAT(DATE_SUB(NOW(),INTERVAL num.num DAY),'%Y-%m-%d') datas,
IFNULL(bs.total,0) total
FROM
num_aux num
LEFT JOIN (
SELECT
DATE_FORMAT(created,'%Y-%m-%d') datas,
COUNT(*) AS total
FROM
pzh_user_goods_order
WHERE
created BETWEEN DATE_SUB(NOW(),INTERVAL 30 DAY) AND NOW()
GROUP BY
DATE_FORMAT(created,'%Y-%m-%d')
) bs
ON bs.datas = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL num.num DAY),'%Y-%m-%d')
WHERE
num.num < 30
查询结果:
查询本月1日至今的所有数据按天分组,不显示上月
只需要在上面的基础之上,把辅助表的日期限遍历上限设定在今天的日
数字就可以了;
子表的sql日期限制只要不少于这个数字就可以,保险起见可以同样使用日
SELECT
DATE_FORMAT(DATE_SUB(NOW(),INTERVAL num.num DAY),'%Y-%m-%d') datas,
IFNULL(bs.total,0) total
FROM
num_aux num
LEFT JOIN (
SELECT
DATE_FORMAT(created,'%Y-%m-%d') datas,
COUNT(*) AS total
FROM
pzh_user_goods_order
WHERE
created BETWEEN DATE_SUB(NOW(),INTERVAL DATE_FORMAT(NOW(),"%d") DAY) AND NOW() # 这里的减少天数就取第几号
GROUP BY
DATE_FORMAT(created,'%Y-%m-%d')
) bs
ON bs.datas = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL num.num DAY),'%Y-%m-%d')
WHERE
num.num < DATE_FORMAT(NOW(),"%d") # 这里的辅助表序号遍历天数就取今天第几号
一个查询优化版本
SELECT
dates.d dates,
IFNULL(total,0)
FROM
(
SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL num DAY),"%Y-%m-%d") d FROM num_aux WHERE num < 7
) dates
LEFT JOIN (
SELECT DATE_FORMAT(created,"%Y-%m-%d") cd,COUNT(*) total
FROM pzh_user WHERE created BETWEEN DATE_SUB(NOW(),INTERVAL 7 DAY) AND NOW()
GROUP BY created
) u
ON dates.d = u.cd
ORDER BY
dates.d ASC