系统环境：windows2008  Oracle 10g

今天在制作周同比报表的时候，遇到了一个Oracle计算周起始的坑。

报表的需求是，要求对数据进行按周的同比，比如2021年1月1日是周五，那么同比日期就要求也必须是周五，即为2020年1月3日，以此类推。

我的办法是，根据当前日期，先取到同比的年份，再取当前日期是全年的第几周，最后取是周中的第几天。

最初的SQL语句写法：

WHERE
					日期 >= (
					SELECT
						日期字段
					FROM
						某张表
					WHERE
						TO_CHAR(日期字段, YYYY')= TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12),'YYYY')
						AND TO_CHAR(日期字段, 'd')=(
						SELECT
							TO_CHAR(TRUNC(SYSDATE-1, 'mm'), 'd')
						FROM
							dual)
						AND TO_CHAR(日期字段-1, 'ww')=(
						SELECT
							TO_CHAR(TRUNC(SYSDATE-1, 'mm'), 'ww')
						FROM
							dual))
					AND 日期 <=(
					SELECT
						日期字段
					FROM
						某张表
					WHERE
						TO_CHAR(日期字段, 'YYYY')= TO_CHAR(ADD_MONTHS(SYSDATE-1,-12),'YYYY')
						AND TO_CHAR(日期字段, 'd')=(
						SELECT
							TO_CHAR(SYSDATE-1, 'd')
						FROM
							dual)
						AND TO_CHAR(日期字段-1, 'ww')=(
						SELECT
							TO_CHAR(SYSDATE-1, 'ww')
						FROM
							dual))

然后坑就出现了，2021年1月14日Oracle to_char函数计算的全年周数是2，到15日计算的全年的周数就变成3了，明明是一周，可是周数却不同。

经过反复排查和询问度娘，才了解了Oracle关于标准周的定义方式，是以每年1月1日的星期为全年周的起始日，不管这一天是周几，2021年1月1日是星期五，那么所有的标准周都是以星期五开始计算的。

根据Oracle的这个奇怪规定，用两个年份1月1日的星期相减，来修正误差。

DECODE(
    SIGN(TO_CHAR(TRUNC(SYSDATE, 'yy'), 'd')-TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12), 'd')), 
    0, 0, 
    1, TO_CHAR(TRUNC(SYSDATE, 'yy'), 'd')-TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12), 'd'),
    -1, 7-ABS(TO_CHAR(TRUNC(SYSDATE, 'yy'), 'd')-TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12), 'd')
    )
)

修正后的SQL语句写法

​
WHERE
					日期 >= (
					SELECT
						日期字段
					FROM
						某张表
					WHERE
						TO_CHAR(日期字段, YYYY')= TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12),'YYYY')
						AND TO_CHAR(日期字段, 'd')=(
						SELECT
							TO_CHAR(TRUNC(SYSDATE-1, 'mm'), 'd')
						FROM
							dual)
						AND TO_CHAR(日期字段-1, 'ww')=(
						SELECT
							TO_CHAR(TRUNC(SYSDATE-1, 'mm'), 'ww')
						FROM
							dual))
					AND 日期 <=(
					SELECT
						日期字段
					FROM
						某张表
					WHERE
						TO_CHAR(日期字段, 'YYYY')= TO_CHAR(ADD_MONTHS(SYSDATE-1,-12),'YYYY')
						AND TO_CHAR(日期字段, 'd')=(
						SELECT
							TO_CHAR(SYSDATE-1, 'd')
						FROM
							dual)
						AND 
                            TO_CHAR(日期字段-(DECODE(SIGN(TO_CHAR(TRUNC(SYSDATE, 'yy'), 'd')-TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12), 'd')), 0, 0, 1, TO_CHAR(TRUNC(SYSDATE, 'yy'), 'd')-TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12), 'd'),-1, 7-ABS(TO_CHAR(TRUNC(SYSDATE, 'yy'), 'd')-TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12), 'd')))), 'ww')=(
	SELECT
		TO_CHAR(SYSDATE-(DECODE(SIGN(TO_CHAR(TRUNC(SYSDATE, 'yy'), 'd')-TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12), 'd')), 0, 0, 1, TO_CHAR(TRUNC(SYSDATE, 'yy'), 'd')-TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12), 'd'),-1, 7-ABS(TO_CHAR(TRUNC(SYSDATE, 'yy'), 'd')-TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE-1, 'mm'),-12), 'd')))), 'ww')
	FROM
		dual))

​