SQL星期數問題

壹頁書發表於2015-01-02
SQL
MySQL技術內幕 SQL程式設計 48頁

其中討論到一些時間日期的問題,非常有意思
建立2015年1月的日期資料.
create table test
select str_to_date('2014-12-27','%Y-%m-%d')+interval id day as "dt"
from nums where id<=42;


1.計算日期是星期幾


weekday返回值0-6
0表示星期一
1表示星期二
2表示星期三
3表示星期四
4表示星期五
5表示星期六
6表示星期日
我們常用的方式應該是weekday + 1

dayofweek返回值1-7
1表示星期日
2表示星期一
...
7表示星期六
dayofweek是西方人的習慣,他們認為每週的開始是星期日,而我們一般都認為是星期一.



dayname可以根據lc_time_names的設定,返回星期的名稱.


獲取指定的日期是星期幾,除了上面的方式.
還可以用指定日期推導的方式.
比如已經知道2014年12月29日是星期一,（1900年1月1日也是星期一）
那麼採用如下的sql,可以知道2015年1月6日是星期二,1月7日是星期三.

set @a='2015-1-6';
select datediff(@a,'2014-12-29')%7+1 as dayofweek;
set @a='2015-1-7';
select datediff(@a,'2014-12-29')%7+1 as dayofweek;

2.按周分組
之前建立的實驗資料.
細心可以發現,week將2014年12月29日至31日作為2014年的最後一週,
而將2015年1月1日-1月3日作為2015年的第一週.
如果有按周統計的資料,則跨年的周有可能統計為2周,導致資料的誤差.
select dt,week(dt) from test;


對於周的劃分,Oracle和MySQL也是截然不同,
create table test
as
select to_date('2014-12-27','yyyy-mm-dd') + rownum as dt from dual connect by level<=42;

select to_char(dt,'yyyy-mm-dd') dt,to_char(dt,'iw') iw,to_char(dt,'ww') ww from test;

其中 to_char IW和WW的演算法如下

1）ww的演算法為每年1月1日為第一週開始，date+6為每一週結尾

　　例如20050101為第一週的第一天，而第一週的最後一天為20050101+6=20050107

　　公式 每週第一天 ：date + 周 * 7 - 7

　　每週最後一天：date + 周 * 7 - 1

2）iw的演算法為星期一至星期日算一週,(洋人的習慣,參考日曆)
     他的計算方式是ISO的一個標準.
     每天有52周或者53周
    他跨年周的演算法比較麻煩.
    以每天1月1日所在的周為準,若上年的日子多,則為上年的最後一週,若本年的日子多則為本年的第一週   


下面是2015年1月的資料,
2015年1月1日所在的周由
2014年12月29日-2015年1月4日組成
可以看到2014年12.29-12.31有3天
而2015年1月到4日有4天,所以本週劃分為2015年的第一週


若是2012年1月1日,跨年的周由2011.12.26-2012.1.1
2011年有6天,而2012年只有1天,所以這周被劃分在了2011年的最後一週,
而2012年的第一週從2012年1月2日開始.


可以看到不同的資料庫針對周劃分的演算法都不一致,
如果有跨年按周統計的需求,也可以使用根據指定日期推導的方式.
比如1900年1月1日是星期一
我們可以計算從這天到今天週數,這是連續的,可以作為跨年按周統計的依據

select
dt,
floor(datediff(dt,'1900-01-01')/7) as week,
date_add('1900-01-01',interval floor(datediff(dt,'1900-01-01')/7)*7 day) as week_start,
date_add('1900-01-01',interval floor(datediff(dt,'1900-01-01')/7)*7+6 day) as week_end
from test;


3.計算工作日的問題

Oracle處理工作日問題參考
http://blog.itpub.net/29254281/viewspace-775663/

MySQL首先可以用數字輔助表的方式
數字輔助表參考:
http://blog.itpub.net/29254281/viewspace-1362897/

drop procedure pGetWorkDaysByNums;
delimiter $$
create procedure pGetWorkDaysByNums(s datetime,e datetime)
begin
select count(*)
as workdays
from
(
    select s+interval id-1 day dt from nums where id<=datediff(e,s)+1
) as a
where weekday(dt) not in (5,6);
end$$
delimiter ;

call pGetWorkDaysByNums('2015-01-01','2015-01-30');


還有一種方式,有更高的效率
drop procedure pGetWorkDays;
delimiter $$
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
as workdays
from
(
    select datediff(e,s)+1 as days,weekday(s)+1 as wd
) as a;
end$$
delimiter ;

call pGetWorkDays('2015-01-01','2015-01-30');

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1387930/，如需轉載，請註明出處，否則將追究法律責任。

相關文章