DB2从时间戳分隔每个季度的周数 [英] DB2 separate number of weeks per quarter from timestamp
问题描述
我试图从每季度的时间戳分开几周,所以应该在每季度1-13周之间,所以我使用函数 week()
,但它需要1 -52个星期,所以我把它按照下面的功能划分。
I'm trying to separate weeks from timestamp per quarter so it should be between 1-13 week per quarter so I used function week()
but it takes between 1-52 week as whole year so I made it to be divided by function of quarter like below
select Week (EVENTTIMESTAMP) / QUARTER (EVENTTIMESTAMP) from KAP
这里的结果不正确;例如:
The thing here that results aren't accurate; for example it shows:
time stamp 2014-07-06 12:13:03.018
week number 9
这是不正确的,因为7月是第三季度的第一个月,在6天内应该是Q3的第一个星期不是9个。
which isn't correct because July is first month in Q3 and it's in the 6 days so it should be 1 week from Q3 not 9.
任何建议出错?
推荐答案
你想要一个像$ WEEK modulo 13
在四分之一内获得周数。您将不得不修改模13收益0..12,通过在适当的点添加或减去一个。
You want something like WEEK modulo 13
to get week number within a quarter. You will have to tinker with 'modulo 13 yields 0..12' by adding or subtracting one at appropriate points.
使用ibm db2 sql modulo得到DB2 MOD 函数:
Some minimal Google searching using 'ibm db2 sql modulo' yields DB2 MOD function:
MOD函数将第一个参数除以第二个参数并返回其余值。
The MOD function divides the first argument by the second argument and returns the remainder.
因此 MOD(WEEK(...),13)
您可能需要 MOD(WEEK(...) - 1,13)+ 1
,已经被暗示。
Hence MOD(WEEK(...), 13)
, except you probably need MOD(WEEK(...)-1, 13) + 1
, as intimated already.
您可能需要注意 WEEK()函数:
WEEK函数返回1到54之间的整数表现了一年中的一周。本周从星期天开始,1月1日始终在第一周。
The WEEK function returns an integer in the range of 1 to 54 that represents the week of the year. The week starts with Sunday, and January 1 is always in the first week.
我很好奇他们如何想出我想这需要1月1日为2000年和2028年的闰年的星期六(1月2日是第2周的开始)。请注意,第53周和(偶尔)第54周将显示为星期Q5的1和2,除非你做某事。另外,星期二2000-03-25将是第一季度的结束,星期天2000-03-26将是由$ code> WEEK()函数施加的政权的第二季度的开始和一个简单的 MOD(WEEK(...),13)
计算。您可能需要调整这一点才能满足您的实际需求。
I'm curious about how they can come up with week 54. I suppose it requires 1st January to be a Saturday (so 2nd January is the start of week 2) of a leap year, as in 2000 and 2028. Note that week 53 and (occasionally) week 54 will show up as weeks 1 and 2 of Q5 unless you do something. Also, Saturday 2000-03-25 would be the end of Q1 and Sunday 2000-03-26 would be the start of Q2 under the regime imposed by the WEEK()
function and a simple MOD(WEEK(...), 13)
calculation. You're likely to have to tune this to meet your real requirements.
还有一个 WEEK_ISO()函数:
WEEK_ISO函数返回一个整数,范围为1到53,表示一年中的周。本周从星期一开始,包括七天。第1周是包含星期四的第一周,相当于包含1月4日的第一周。
The WEEK_ISO function returns an integer in the range of 1 to 53 that represents the week of the year. The week starts with Monday and includes seven days. Week 1 is the first week of the year that contains a Thursday, which is equivalent to the first week that contains January 4.
注意根据ISO计划,1月3日可以在上一周的52或53周,12月29日可以在明年的第1周。奇怪的是,似乎没有一个YEAR_ISO()函数来解决这种歧义。
Note that under the ISO scheme, the 3rd of January can be in week 52 or 53 of the previous year, and the 29th of December can be in week 1 of the next year. Curiously, there doesn't seem to be a YEAR_ISO() function to resolve such ambiguities.
这篇关于DB2从时间戳分隔每个季度的周数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!