一.对于分别以日、月、年为统计周期的统计问题
1、说明：数据库采用Mysql
2、主要问题：当以日、月、年为统计周期时，需要返回规定天数内所有记录，数据库中不存在的也要返回，所以需要人为添加不存在记录，并将其数量赋值为0
以下为不修改表结构，我所采用的方法：
统计表结构图
统计从七天之前到昨天的所有访问产品的记录
（1）横坐标直接为日期（以日为例）
SELECT GROUP_CONCAT(DISTINCT ub.cycle) AS cycle,SUM(ub.amount) AS amount
FROM
( SELECT DATE_FORMAT(action_time,'%Y-%m-%d') AS cycle,COUNT(action_time) AS amount
FROM user_behavior
WHERE behavior = 'access' AND target = 'product'
AND DATE_FORMAT(action_time,'%Y-%m-%d') >= DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -7 DAY),'%Y-%m-%d')
AND DATE_FORMAT(action_time,'%Y-%m-%d') < DATE_FORMAT(NOW(),'%Y-%m-%d')
GROUP BY cycle
UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d'),0 FROM DUAL
UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -2 DAY),'%Y-%m-%d'),0 FROM DUAL
UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -3 DAY),'%Y-%m-%d'),0 FROM DUAL
UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -4 DAY),'%Y-%m-%d'),0 FROM DUAL
UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -5 DAY),'%Y-%m-%d'),0 FROM DUAL
UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -6 DAY),'%Y-%m-%d'),0 FROM DUAL
UNION SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -7 DAY),'%Y-%m-%d'),0 FROM DUAL
) ub
GROUP BY cycle
ORDER BY cycle
（2）横坐标为【前一周】（7天前~至今）、【前两周】（14天前~7天前）……或其他自定义名称
SELECT GROUP_CONCAT(DISTINCT ub.cycle) AS cycle,SUM(ub.amount) AS amount
FROM
( SELECT
( CASE
WHEN TO_DAYS(NOW())-TO_DAYS(action_time) <= 7 THEN "a"
WHEN TO_DAYS(NOW())-TO_DAYS(action_time) > 7 AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 14 THEN "b"
WHEN TO_DAYS(NOW())-TO_DAYS(action_time) > 14 AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 21 THEN "c"
WHEN TO_DAYS(NOW())-TO_DAYS(action_time) > 21 AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 28 THEN "d"
END) AS cycle,
(
CASE
WHEN TO_DAYS(NOW())-TO_DAYS(action_time) <= 7
THEN (
SELECT COUNT(*) FROM user_behavior
WHERE behavior = 'access' AND target = 'product'
AND TO_DAYS(NOW())-TO_DAYS(action_time) > 0
AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 7
)
WHEN TO_DAYS(NOW())-TO_DAYS(action_time) > 7 AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 14
THEN (
SELECT COUNT(*) FROM user_behavior
WHERE behavior = 'access' AND target = 'product'
AND TO_DAYS(NOW())-TO_DAYS(action_time) > 7
AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 14
)
WHEN TO_DAYS(NOW())-TO_DAYS(action_time) > 14 AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 21
THEN (
SELECT COUNT(*) FROM user_behavior
WHERE behavior = 'access' AND target = 'product'
AND TO_DAYS(NOW())-TO_DAYS(action_time) > 14
AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 21
)
WHEN TO_DAYS(NOW())-TO_DAYS(action_time) > 21 AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 28
THEN (
SELECT COUNT(*) FROM user_behavior
WHERE behavior = 'access' AND target = 'product'
AND TO_DAYS(NOW())-TO_DAYS(action_time) > 21
AND TO_DAYS(NOW())-TO_DAYS(action_time) <= 28
)
END) AS amount
FROM user_behavior
UNION SELECT "a",0 FROM DUAL
UNION SELECT "b",0 FROM DUAL
UNION SELECT "c",0 FROM DUAL
UNION SELECT "d",0 FROM DUAL
) ub
GROUP BY cycle
ORDER BY cycle
注：之所以then后用"a"，"b"等字母表示，纯粹是为了排序，这样的话需要有Service层处理下，当然也可以直接命名为【前一周】、【前两周】等自定义的名称，让前端去排序
以上只是个人基于不动数据的情况下现实，欢迎大神们来提供好的实现方案。