MYSQL系列(4） 关于时间函数的一些写法
date_format(date, format) 函数用于以不同的格式显示日期/时间数据
参数：
date: 合法的日期
format:规定的日期/时间的输出 格式
实例：下面是使用DATE_FORMAT()函数来显示不同的格式，我们使用NOW()来获得当前的日期/时间
date_format(now(), '%b %d %Y %h:%i %p')
date_format(now(), '%m-%d-%Y')
date_format(now(), '%d %b %y')
结果
Dec 29 2008 11:45 pm
12-29-2008
29 Dec 08
date_sub(date, INTERVAL expr type) 函数从日期减去指定的时间间隔
参数：
date：合法的日期表达形式
expr：希望添加的时间间隔
type参数是下面
| TYPE值（还有不常用的，具体查询书籍） |
|---|
| MINUTE |
| HOUR |
| DAY |
| WEEK |
| MONTH |
| QUARTER |
| YEAR |
实例
假设我们有下面表
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | 'Computer' | 2018-12-29 16:25:46.635 |
现在我们希望偶从order_date减去2天
select
OrderId, Date_sub(Orderdate, INTERVAL 2 DAY) AS OrderPayDate
from
Ordders
| OrderId | OrderPayDate |
|---|---|
| 1 | 2008-12-27 16:25:46.635 |
3
now(), curdate(), curtime()
| now() | curdate() | curtime() |
|---|---|---|
| 2019-10-11 22:22:22 | 2019-10-11 | 22:22:22 |
可以看到curdate()取得是年月日，curtime()取得是时分秒
表table 再短 user_id, order_id, pay_time, order_time,order_amount
-
查询过去一个月的付款用户量最高的三天分别是哪天
-
查询昨天每个用户最后付款的订单ID及金额
思路
1.表的形式应该是
day count 2019-01-02 100 2019-01-19 29 2019-01-10 10 - 求出每天的用户数,每天，按照天进行分组
- 条件是过去一个月的付款用户，条件1：付款用户；条件2：过去一个月
- 对每天的数量进行排序求前三个
select
date_format(pay_time, '%Y-%M-%D') 'day', count(distinct user_id)
from
table
where
order_amount > 0 and month(date_format(pay_time, '%Y-%m-%d')) = month(now()) -1
group by date_format(pay_time, '%Y-%m-%d')
order by count(distinct user_id) desc limit 3;
-
思路
1.所求表格
user_id order_id order_amount xx xxx xxxx - 对user_id进行分组，条件是昨天， 最后付款时间
select
user_id, order_id, order_amount
from
table
where
date_format(pay_time, '%Y-%m-%d') = date_sub(curdate(), interval 1 day)
group by user_id;
二、有PV表a,字段 user_id, goods_id;点击表b 字段user_id, goods_id两表，各存放40亿条user_id的goods_id的访问记录，在防止数据倾斜的情况下，写出依据sql，找出两个表共同的user_id 和相应的goods_id
思路：这里数据倾斜指两个大表在关联的时候卡死的情况，空值解决方案
select
*
from
a inner join b
on a.user_id is not null and a.user_id = b.user_id
union all
select
*
from
a
where
a.user_id is null;
三、用户登录表 table 字段：user_id, log_time, session_id, plat
查询近30天，每天平均登录用户数量
思路：
-
要查询的表格
avg_count() - 每天的平均用户数量，对log_time进行分组
- 查出每一天的登录数量
- 求每天平均数量
select
avg(t.day_count)
from
(select date_format(log_time, '%Y-%M-%D'),count(user_id) as day_count
from table
where date_format(log_time, '%Y-%m-%d') >= date_sub(curdate(), interval 30 day)
and date_format(log_time, '%Y-%m-%d') <curdate()
group by date_format(log_time, '%Y-%m-%d')
) t
四、对于通过不同渠道拉新进来的用户，经过一段时间可能会流失，而留下来的用户我们称之为留存用户。分析哟用户留存是拉新和用户运营的重要指标：
用户访问表：person_visit 字段：user_id(用户id), visit_date(访问时间), page_name(访问页面),plat(访问渠道)
-
统计近七天每天到访的新用户数
思路：
-
要求的图表
time count 2019-10-1 100 2.先以用户进行分组，求出每个用户在这七天最小的访问时间，条件是在最近7天内
- 再 以求出的这个最小访问时间分组，求出我们需要进行统计的每天访问用户数量
select t.new_day, count(t.user_id) from (select user_id, min(date_format(visit_date, '%Y-%m-%d')) as new_day from person_visit where date_format(visit_date, '%Y-%m-%d') <= curdate() and date_format(visit_date,'%Y-%m-%d') >= date_sub(curdate(), INTERVAL 7 day) group by user_id ) t group by t.new_day -
-
统计每个渠道7天前的新用户的3日留和7日留存率
-
每个渠道7天前的新用户数
表格形式
plat count_new_user a 90 新用户怎么求，以登录日期为条件过滤求出新用户数
select plat, count(distinct user_id) from person_visit group by plat having min(date_format(visit_date, '%Y-%m-%d')) = date_sub(curdate(), INTERVAL 7 day)-
每个渠道3日留存用户数
思路： id在7天前登录的用户里3日还登录的数目，用一个子查询来实现
select t.plat, count(distinct t.user_id) as '3日用户数' from person_visit as t where user_id in (select plat, user_id from person_visit group by plat having min(date_format(visit_date, '%Y-%m-%d')) = date_sub(curdate(), INTERVAL 7 day) ) and date_format(t.visit_date, '%Y-%m-%d') = date_sub(curdate(), INTERVAL 4 day) group by t.plat;3.每个渠道7日留存用户数
select t.plat, count(distinct t.user_id) as '7日用户数' from person_visit as t where user_id in (select plat, user_id from person_visit group by plat having min(date_format(visit_date, '%Y-%m-%d')) = date_sub(curdate(), INTERVAL 7 day)) and date_format(t.visit_date, '%Y-%m-%d') = date_sub(curdate(), Interval 7 day) group by t.plat;
-
-
