奇怪的 SQL

SQL查询技巧：时间计算与数据统计

最新推荐文章于 2024-07-18 17:21:57 发布

原创最新推荐文章于 2024-07-18 17:21:57 发布 · 683 阅读

0 ·

CC 4.0 BY-SA版权

@debj

文章标签：

#mysql

笔记专栏收录该内容

1 篇文章

订阅专栏

本文介绍了SQL查询中的时间处理方法，包括计算回复时间的差异，创建虚拟表来展示近几个月的数据分布，以及如何进行两个日期间的数据统计。通过CASE语句和BETWEEN操作符，实现了灵活的时间间隔表达和数据统计分析。

文章目录

一、DQL（查询）

一、DQL（查询）

1-1】计算回复时间

# 例如：三天前回复了我，我却把他搁置了
CASE
    WHEN TIMESTAMPDIFF(MINUTE, b.create_date, now()) &lt; 5 THEN '刚刚'
    WHEN TIMESTAMPDIFF(MINUTE, b.create_date, now()) &lt; 60 THEN CONCAT(TIMESTAMPDIFF(MINUTE, b.create_date, now()),'分钟前')
    WHEN TIMESTAMPDIFF(HOUR, b.create_date, now()) &lt; 24 THEN CONCAT(TIMESTAMPDIFF(HOUR, b.create_date, now()),'小时前')
    WHEN TIMESTAMPDIFF(DAY, b.create_date, now()) &lt; 31 THEN CONCAT(TIMESTAMPDIFF(DAY, b.create_date, now()),'天前')
    WHEN TIMESTAMPDIFF(MONTH, b.create_date, now()) &lt; 12 THEN CONCAT(TIMESTAMPDIFF(MONTH, b.create_date, now()),'月前')
    ELSE CONCAT(TIMESTAMPDIFF(YEAR, b.create_date, now()),'年前')
END as time,

1-2】最近几个时间阶段虚拟表

# 例如查询近七天，近 12 个月的数据
SELECT
     DATE_FORMAT(a.t,'%Y-%c') as time,
     COUNT(t.id) as sum
 FROM
     (
         SELECT now() as t union
         SELECT DATE_SUB(now(), INTERVAL + 1 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 2 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 3 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 4 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 5 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 6 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 7 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 8 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 9 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 10 MONTH) as t union
         SELECT DATE_SUB(now(), INTERVAL + 11 MONTH) as t
     ) a
 LEFT JOIN table t
     ON MONTH(t.create_date) = MONTH(a.t)
     AND TIMESTAMPDIFF(YEAR, t.create_date, now()) < 1
     AND IF(MONTH(t.create_date)=MONTH(NOW()), YEAR(t.create_date)=YEAR(NOW()), 1=1)
 GROUP BY MONTH(a.t)
 ORDER BY a.t

1-3】两个日期间的数据统计

BETWEEN 也许会更好

// 使用 java 代码帮助实现，这边为工具类下的方法

/**
 * 获取两个日期中所有天
 *
 * @param startTime 开始时间
 * @param endTime   结束时间
 * @param include   是否包含结束时间
 * @return List<Date> 格式 +2：[ ]、[ )
 */
public static List<Date> betweenDayInclude(Date startTime, Date endTime, Boolean include) {
    List<Date> returnList = new ArrayList<>();

    Calendar start = Calendar.getInstance();
    start.setTime(startTime);
    Calendar end = Calendar.getInstance();
    end.setTime(endTime);
    /*
        避免出现 开始时间 为当天日出的同时 结束日间 为日落时分
        第一阶段：判断出不是同一天，是过去的天 ✓
        第二阶段：同一天进行判断 开始时间 依旧是 结束时间的前面 ✗
        总结：这是个 bug，得排除
     */
    end.set(end.get(Calendar.YEAR), end.get(Calendar.MONTH), end.get(Calendar.DATE), 0, 0, 0);

    while (start.before(end)) {
        returnList.add(start.getTime());
        start.add(Calendar.DAY_OF_YEAR, 1);
    }
    if (Boolean.TRUE.equals(include)) {
        returnList.add(end.getTime());
    }
    return returnList;
}

SELECT
	DATE_FORMAT(b.t,'%m.%e') as time,
	b.*
from 
	(
		<foreach item="l" collection="list" open="" separator=" union " close="">
			select #{l} as t
		</foreach>
	) a
left join table_b b on TIMESTAMPDIFF(DAY, b.create_date, a.t) = 0
GROUP BY DAY(a.t)
order by DAY(a.t)