table
visit_log 统计表 |--------------------------------------------| | log_id | customer_id | add_time | |--------------------------------------------| | 1 | 1 | 2022-08-01 10:00:00 | |--------------------------------------------| | 2 | 2 | 2022-08-01 11:00:00 | |--------------------------------------------| | 3 | 2 | 2022-08-01 12:00:00 | |--------------------------------------------| | 4 | 1 | 2022-08-02 10:00:00 | |--------------------------------------------| add_time里边存的int时间戳，此处为了方便理解，转成了timestamp展示 2022-08-01 10:00:00->1659319200 2022-08-01 11:00:00->1659322800 2022-08-01 12:00:00->1659326400 2022-08-02 10:00:00->1659405600
customer 客户表 |-------------------| |id | customer_name || |-------------------| |1 | 张三 | |-------------------| |2 | 李四 | |-------------------|
在MySQL数据表设计中，时间字段一般都设计为时间戳格式的，开发人员去查看的时候就显得有点不方便。可以使用FROM_UNIXTIME转换成日期格式进行查看。
1.统计每日访问人数
SELECT COUNT(*) AS mcount,FROM_UNIXTIME(d.add_time, “%Y-%m-%d”) AS mdate FROM visit_log AS d INNER JOIN customer AS e ON d.customer_id = e.id GROUP BY mdate;
结果
|-------------------| |mcount | mdate | |-------------------| | 3 | 2022-08-01| |-------------------| | 1 | 2022-08-02| |-------------------|
2.统计每日访问人数，每人每天只统计一次
SELECT COUNT(DISTINCT d.customer_id) AS mcount,FROM_UNIXTIME(d.add_time, “%Y-%m-%d”) AS mdate FROM visit_log AS d INNER JOIN customer AS e ON d.customer_id = e.id GROUP BY mdate;
结果
|-------------------| |mcount | mdate | |-------------------| | 2 | 2022-08-01| |-------------------| | 1 | 2022-08-02| |-------------------|
难度升级：
visit_log 统计表 |--------------------------------------------| | log_id | customer_id | add_time | |--------------------------------------------| | 1 | 1 | 2022-08-01 10:00:00 | |--------------------------------------------| | 2 | 2 | 2022-08-01 11:00:00 | |--------------------------------------------| | 3 | 2 | 2022-08-01 12:00:00 | |--------------------------------------------| | 4 | 1 | 2022-08-02 10:00:00 | |--------------------------------------------| | 5 | 3 | 2022-08-02 10:00:00 | |--------------------------------------------| | 6 | 2 | 2022-08-02 10:00:00 | |--------------------------------------------| add_time里边存的int时间戳，此处为了方便理解，转成了timestamp展示 2022-08-01 10:00:00->1659319200 2022-08-01 11:00:00->1659322800 2022-08-01 12:00:00->1659326400 2022-08-02 10:00:00->1659405600
添加level等级字段
customer 客户表 |---------------------------| |id | customer_name | level | |---------------------------| |1 | 张三 |3 | |---------------------------| |2 | 李四 |4 | |---------------------------| |3 | 王五 |4 | |---------------------------|
1.统计每日访问人数，每人每天只统计一次
SELECT COUNT(DISTINCT d.customer_id) AS mcount,FROM_UNIXTIME(d.add_time, “%Y-%m-%d”) AS mdate FROM visit_log AS d INNER JOIN customer AS e ON d.customer_id = e.id GROUP BY mdate;
结果
|-------------------| |mcount | mdate | |-------------------| | 2 | 2022-08-01| |-------------------| | 3 | 2022-08-02| |-------------------|
2.分日期分等级统计，每人每天只统计一次
SELECT `level`,COUNT(DISTINCT d.customer_id) AS mcount,FROM_UNIXTIME(d.add_time, “%Y-%m-%d”) AS mdate
FROM visit_log AS d
INNER JOIN customer AS e ON d.customer_id = e.id
GROUP BY mdate,e.level;
结果
|----------------------------| | level | mcount | mdate | |----------------------------| | 3 | 1 | 2022-08-01| |----------------------------| | 4 | 1 | 2022-08-02| |----------------------------| | 3 | 1 | 2022-08-01| |----------------------------| | 4 | 2 | 2022-08-02| |----------------------------|
结束。