频道栏目
读书频道 > 数据库 > Mysql > MySQL技术内幕:SQL编程
2.4.3 星期数的问题
2012-11-06 09:34:17     我来说两句
收藏   我要投稿

本文所属图书 > MySQL技术内幕:SQL编程

全书一共10章,全面探讨了MySQL中SQL编程的各种方法、技巧与最佳实践。第1章首先介绍了SQL编程的概念、数据库的应用类型以及SQL查询分析器,然后介绍了SQL编程的三个阶段,希望读者通过本书的学习能达到最后的融...  立即去当当网订购

1.计算日期是星期几 

这个问题看上去非常简单,比如可以使用MySQL数据库内置的WEEKDAY函数来取得星期几。WEEKDAY函数返回值为0~6,0代表Monday,1代表Tuesday,……,6代表Sunday;也可以使用DAYOFWEEK函数,其返回值为1~7,1代表Sunday,2代表Monday,……,7代表Saturday;还可以直接使用DAYNAME函数来返回日期具体的名称。这3个函数的使用如下:
mysql> SET @a='2011-01-01';
Query OK, 0 rows affected (0.00 sec)

mysql> SET WEEKDAY(@a),DAYOFWEEK(@a),DAYNAME(@a)\G;
*************************** 1. row ***************************
  WEEKDAY(@a): 5
DAYOFWEEK(@a): 7
  DAYNAME(@a): Saturday
1 row in set (0.00 sec)

WEEKDAY函数从周一开始,较符合中国人的习惯,但是周一的返回值是0。DAYOFWEEK函数从周日开始,更符合外国人的习惯。DAYNAME函数返回星期的名称,它和参数lc_time_names有所关联,该参数控制返回的日期显示方式,示例如下:
mysql> SELECT DAYNAME(NOW())\G;
*************************** 1. row ***************************
DAYNAME(NOW()): Sunday
1 row in set (0.00 sec)

mysql> SET lc_time_names='zh_CN';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DAYNAME(NOW())\G;
*************************** 1. row ***************************
DAYNAME(NOW()): 星期日
1 row in set (0.00 sec)

这里要介绍另外一个有比较有趣的方法。如果知道“1900-01-01”是周一,那么“1900-01-02”就是周二,以此类推,如果用户想知道某个日期是否是周二,可以通过下面的方法:
mysql> SET @a='2011-01-01';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DATEDIFF(@a,'1900-01-02')%7 = 0\G;
*************************** 1. row ***************************
DATEDIFF(@a,'1900-01-02')%7 = 0: 0
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(@a,'1900-01-06')%7 = 0\G;
*************************** 1. row ***************************
DATEDIFF(@a,'1900-01-06')%7 = 0: 1
1 row in set (0.00 sec)

如果日期是周二,意味着该日期与另一个星期二相差的天数可以被7整除。这里利用了参照日期法来进行判断,可以很容易地得知是否是我们想要的日期。

2.按周分组 

这个问题看似十分简单,却还有些小细节需要注意,这些小细节往往令人抓耳挠腮、烦恼不已。按周分组最容易遇到的问题是按周统计销量,如按周统计某个网络游戏的在线人数、充值记录等。一般的SQL程序员往往会使用WEEK这个函数,但是这个函数存在很多问题。我们先来看下面这个问题:
mysql> SELECT 
  WEEK('2011-01-01'),
  WEEK('2011-01-02'),
  WEEK('2011-01-03')\G;
*************************** 1. row ***************************
WEEK('2011-01-01'): 0
WEEK('2011-01-02'): 1
WEEK('2011-01-03'): 1
1 row in set (0.00 sec)

可以看到WEEK函数将2011-01-01视为第一周,而将2011-01-02和2011-01-03视为第二周。从中国人的理解角度来说,2011-01-01是星期六, 2011-01-01和2011-01-02都应该算是第一周,2011-01-03才是第二周的开始。造成这个问题的原因是WEEK这个函数是按照国外的习惯设计的,将周日视为每星期的开始。

因此WEEK函数并不是一个很好的做每周统计的方法。另外,如果某个周是跨年份的,如2011-01-01,它是2010年到2011年跨年份的一周中的日期,WEEK函数对此显得毫无办法,例如:
mysql> SELECT WEEK('2010-12-31'),WEEK('2011-01-01')\G;
*************************** 1. row ***************************
WEEK('2010-12-31'): 52
WEEK('2011-01-01'): 0
1 row in set (0.00 sec)

可以看到,MySQL数据库将2010-12-31视为2010年的最后一周,而将2011-01-01视为2011年的第一周。此外,WEEK函数还不能解决另一个问题,如果每周的报表要求按周二或周三开始,又应该怎么解决呢?

对于这个问题,我们也可以使用前面的参照法,参照1900-01-01是周一, DATEDIFF (date,'1900-01-01')/7=0表示应该是在同一周。注意这里通过取整来判断是否在同一周,前面是取余操作,用来判断是星期几。如果我们的报表根据按周分组的要求从周二开始,那么我们只需将前面的判断修改为DATEDIFF(date,'1900-01-02')/7=0即可。

下面来看一个例子,先生成一些简单的测试数据,主要用来讨论某周跨年的分组问题。
USE test;
CREATE TABLE sales (
id   INT AUTO_INCREMENT NOT NULL,
date DATETIME NOT NULL,
cost INT UNSIGNED NOT NULL,
PRIMARY KEY(id) );

INSERT INTO sales(date,cost) VALUES ('2010-12-31',100);
INSERT INTO sales(date,cost) VALUES ('2011-01-01',200);
INSERT INTO sales(date,cost) VALUES ('2011-01-02',100);
INSERT INTO sales(date,cost) VALUES ('2011-01-06',100);
INSERT INTO sales(date,cost) VALUES ('2011-01-10',100);

再来看按周分组后的情况。
mysql> SELECT WEEK(date),SUM(cost)
    -> FROM sales
    -> GROUP BY WEEK(date);
+------------+-----------+
| WEEK(date) | SUM(cost) |
+------------+-----------+
|          0   |       200  |
|          1   |       200  |
|          2   |       100  |
|         52   |       100  |
+------------+-----------+
4 rows in set (0.00 sec)

正如我们所料,产生了错误, WEEK函数把2010-12-31作为第52周,而2010-12-31、2011-01-01和2011-01-02应该属于同一周。这个问题的解决方案使用了前面介绍的参考法,具体方案如下:
mysql> SELECT FLOOR(DATEDIFF(date,'1900-01-01')/7) AS a,
    -> SUM(cost)
    -> FROM sales
    -> GROUP BY FLOOR(datediff(date,'1900-01-01')/7);
+------+-----------+
| a    | SUM(cost) |
+------+-----------+
| 5791 |       400 |
| 5792 |       100 |
| 5793 |       100 |
+------+-----------+
3 rows in set (0.00 sec)

这里我们需要使用FLOOR函数进行取整操作,得到的结果是正确的,但是a列返回的是距离1900-01-01的周数,显示不够直观。如果我们需要按照每周的开始和结束来显示,则对上述SQL语句再进行一些小修改:
mysql> SELECT
    -> DATE_ADD('1900-01-01',
    -> INTERVAL FLOOR(DATEDIFF(date,'1900-01-01')/7)*7 DAY)
       AS week_start,
    -> DATE_ADD('1900-01-01',
    -> INTERVAL FLOOR(DATEDIFF(date,'1900-01-01')/7)*7+6 DAY)
       AS week_end,
    -> SUM(cost)
    -> FROM sales
    -> GROUP BY FLOOR(datediff(date,'1900-01-01')/7);
+------------+------------+-----------+
| week_start | week_end   | sum(cost) |
+------------+------------+-----------+
| 2010-12-27 | 2011-01-02 |       400 |
| 2011-01-03 | 2011-01-09 |       100 |
| 2011-01-10 | 2011-01-16 |       100 |
+------------+------------+-----------+
3 rows in set (0.00 sec)

这里我们从周一开始统计每周的开始,如果要从周二或者周三开始统计每周的开始,那么只需要将1900-01-01修改为1900-01-02或者1900-01-03即可。

3.计算工作日的问题 

计算两个星期之间的工作日数量也是一个很常见的任务。我们可能需要包括假期和其他非工作日的特殊时间的辅助时间表。如果只把周末认为非工作日,那么根本就不需要辅助表,下面的存储过程用来计算s和e之间的工作日数。
CREATE PROCEDURE pGetWorkDays (s DATETIME, e DATETIME)
BEGIN
SELECT FLOOR(days/7)*5+days%7
- CASE WHEN 6 BETWEEN wd AND wd+days%7-1 THEN 1 ELSE 0 END
- CASE WHEN 7 BETWEEN wd AND wd+days%7-1 THEN 1 ELSE 0 END
FROM 
(SELECT  DATEDIFF(e,s)+1 AS days,WEEKDAY(s)+1 AS wd ) AS a;
END;

这个解决方案的执行速度非常快,因为它不涉及任何的I/O操作。派生表查询计算s到e之间的天数(days),包括s和e在内,以及s是星期几(wd)。外部查询用于计算指定范围内的所有周的工作日数量(days/7*5)加上非完整一周内的工作日数量(days%7),如果非完整一周内的工作日包含周末,则减去相应的部分。下面我们来测试几个时间段内的工作日数量。
mysql> CALL pGetWorkDays('2005-01-01','2005-12-31') ;
+----------+
| workdays |
+----------+
|      260 |
+----------+
1 row in set (0.01 sec)

mysql> CALL pGetWorkDays('2005-01-01','2006-01-01') ;
+----------+
| workdays |
+----------+
|      260 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL pGetWorkDays('2005-01-01','2006-01-02') ;
+----------+
| workdays |
+----------+
|      261 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

可以看到2005-01-01到2005-12-31之间一共有260个工作日。因为2006-01-01是周日,所以2005-01-01到2006-01-01之间的工作日应该是260个,而2006-01-01是工作日,因此工作日数量变为261天。

您对本文章有什么意见或着疑问吗?请到论坛讨论您的关注和建议是我们前行的参考和动力  
上一篇:2.4.2 重叠问题
下一篇:2.5.1 整型
相关文章
图文推荐
排行
热门
最新书评
特别推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站