1, mysql 获取近一周的每天(不包括当天 now() 的)的用户的注册量与对应时间。其中 create_time 字段是 注册的时间段(一般不修改)
SELECT
DATE_FORMAT( create_time, '%Y%m%d' ) AS ymd,
COUNT( * ) AS total
FROM
user
WHERE
user_from IN ( 1, 2, 3, 4, 5 )
AND create_time BETWEEN SUBDATE( DATE( NOW( ) ), INTERVAL 7 DAY )
AND DATE( NOW( ) )
GROUP BY
ymd;
2,mysql 的 case when 使用:
多系统(多网站)统计 注册量 并且按照网站分类
SELECT DATE_FORMAT( create_time, '%Y%m%d' ) AS ymd,
CASE
user_from
WHEN 1 THEN
'网站1'
WHEN 2 THEN
'网站2'
END AS site,
COUNT( * ) AS total
FROM
user
WHERE
user_from IN ( 1, 2)
AND create_time BETWEEN SUBDATE( DATE( NOW( ) ), INTERVAL 7 DAY )
AND DATE( NOW( ) )
GROUP BY
ymd,
user_from;
3,MySQL 正则 与 when case:
参考: https://www.cnblogs.com/clphp/p/6256207.html
Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
这两种方式,可以实现相同的功能。简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,比如写判断式。
我的解决实例:
SELECT
CASE WHEN
substring_index( `name`, " ", 1 ) REGEXP '^pid:[0-9]{5}' THEN
'123'
END AS total
FROM `address`;
========================= ===============================================================
问题一:前七天没有人注册,怎么办?
方法: 日期写出,统计量为零
字段有 user_from(多个网),create_time(创建时间)
count(*) as total --统计注册量数量 ifnull(b.total,0)--没有注册量默认为零 NOW() 代替 '2013-06-10'
select a.count_time,ifnull(b.total,0) as total,b.user_from as site
from (
SELECT SUBDATE(DATE( '2013-06-10' ), interval 7 day) as count_time
union all
SELECT SUBDATE(DATE( '2013-06-10' ), interval 6 day) as count_time
union all
SELECT SUBDATE(DATE( '2013-06-10' ), interval 5 day) as count_time
union all
SELECT SUBDATE(DATE( '2013-06-10' ), interval 4 day) as count_time
union all
SELECT SUBDATE(DATE( '2013-06-10' ), interval 3 day) as count_time
union all
SELECT SUBDATE(DATE( '2013-06-10' ), interval 2 day) as count_time
union all
SELECT SUBDATE(DATE( '2013-06-10' ), interval 1 day) as count_time
) a left join (
select date(create_time) as datetime, count(*) as total, user_from
from fashion_user
where user_from =5
group by date(create_time)
) b on a.count_time = b.datetime;
问题二:统计的当前日期之后的日期 更多怎么办:比如 INTERVAL 7 DAY 改为 INTERVAL 1 month
创建数据库表 num:与 注册统计表搭配使用
CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
select a.count_time,COALESCE(b.total,0) total from(
SELECT ADDDATE(NOW(),c.id) as count_time
from (
select
n1.i + n10.i * 10 + n100.i * 100 AS id
from
num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
) AS c
WHERE
ADDDATE(NOW(),c.id)<= date_add(NOW(),INTERVAL 7 DAY)) a
LEFT JOIN (SELECT left(create_time,10) as udate,count(*) total FROM fashion_user where user_from = 5 #5-网站五
GROUP BY udate) b on a.count_time = b.udate order by a.count_time;