题目
有这么一张表
表中,code对应的名称被频繁修改,造成了使用的不便,使用者想要找到表中每条数据在enddate所属会计年度的最后一次修改的名称,并形成一个视图。
ps:日本一个会计年度从当年4月1号到次年的3月31号。
思路
会计年的计算
既然是和会计年相关的问题,那么首先要确定会计年。根据会计年的数据特点,判定当前月是否在四月以前,是,则属于上一个会计年,不是,则属于当前年序号对应的会计年:
如:20240301,在4月以前,则会计年是2023年,又如20240501,在4月以后,会计年则是2024年。对应的公式为:
CASE
WHEN ((DATE_PART('MONTH'::"VARCHAR", STARTDATE) >= 4)
AND (DATE_PART('MONTH'::"VARCHAR", STARTDATE) <= 12))
THEN TO_CHAR(STARTDATE, 'YYYY'::"VARCHAR")
ELSE TO_CHAR(ADD_MONTHS(STARTDATE, -12), 'YYYY'::"VARCHAR")
END AS COUNTX
会计年还有一种更简单的算法,观察数据特点,会计年度从当年4月1号到次年的3月31号,那如果在这个日期的基础上往前推3个月,是不是刚好是当年1月1号到次年的12月31号?对应的公式如下:
to_char(ADD_MONTHS(t.SHUURYOUDATE, -3),'YYYY') AS COUNTX
有效的日期范围
注意上面这组数据,前两条数据,每条对应一个会计年,第三条数据,虽然跨了多个会计年,但开始日期是一个会计年的第一个值,且是改组数据的最后一条。这组数据,每一条都能唯一确定一个名称。
这组数据的期待结果比较规律
id | code | name | start | end | countingYear | countingName |
---|---|---|---|---|---|---|
4 | 00002 | name1 | 2019/04/01 | 2020/03/31 | 2019 | name1 |
5 | 00002 | name2 | 2020/04/01 | 2021/03/31 | 2020 | name2 |
6 | 00002 | name3 | 2021/04/01 | 2022/03/31 | 2021 | name3 |
再来看一种情况:
这一组数据则不一样,前两条每条数据都横跨两个会计年,其中的第一个会计年,包含该会计年的最后一个日期,可以唯一确定会计年的有效名称,但是其中的第二个会计年,只包含会计年的起始时间但不包含终了时间。
这时候有两种情况:
- 第一种:如果这个会计年的后续日期在其他数据中没有出现,那么,当前数据对应的名称就是该会计年的有效名称;
- 第二种:如果这个会计年的后续日期在其他数据中出现了,那么,当前数据对应的名称就是无效数据,有效名称在后面的数据中。上图中就是这种情况。上一条数据第二个会计年的结束日期在第二条数据中。这组数据的期待结果就不这么规律了
|id|code| name | start | end | countingYear | countingName
|–|–|–|–|–|–|–|
| 1 | 00001 | name1 | 2019/01/01 | 2019/12/31 | 2018 | name1
| 1 | 00001 | name1 | 2019/01/01 | 2019/12/31 | 2019 | name2
| 2 | 00001 | name2 | 2020/01/01 | 2020/12/31 | 2019| name2
| 2 | 00001 | name2 | 2020/01/01 | 2020/12/31 | 2020| name3
| 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | 2020 | name3
| 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | 2021 | name3
| 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | 2022 | name3
| 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | … | name3
| 3| 00001 | name3 | 2021/01/01 | 9999/12/31 | 9999 | name3
上面的第二种情况还有一个更复杂的表现,如下图:
第一条数据所在会计年的结束日期在第四条数据中出现,第一、第二、第三,这三条数据都是无效数据。期待的结果如下:
id | code | name | start | end | countingYear | countingName |
---|---|---|---|---|---|---|
20 | 00007 | name1 | 2019/01/01 | 2019/01/20 | 2018 | name1 |
20 | 00007 | name2 | 2019/01/21 | 2019/01/31 | 2018 | name2 |
20 | 00007 | name3 | 2019/02/01 | 2019/02/20 | 2018 | name2 |
20 | 00007 | name4 | 2019/02/21 | 9999/12/31 | 2018 | name3 |
20 | 00007 | name4 | 2019/02/21 | 9999/12/31 | 2019 | name3 |
20 | 00007 | name4 | 2019/02/21 | 9999/12/31 | 2020 | name3 |
20 | 00007 | name4 | 2019/02/21 | 9999/12/31 | … | name3 |
20 | 00007 | name4 | 2019/02/21 | 9999/12/31 | 2019 | name3 |
面对这些情况,面对这些数据,想要整理出会计年,第一种思路是表和表之间的自关联,通过开始日期和终止日期、开始日期和开始日期、终止日期和终止日期、开始日期和会计年、终止日期和会计年等各种日期的复杂判断。实际上我也确实做了这个事情,但判断逻辑太多,最终Sql代码的深度达四层。这并不是我想要的。
既然都是日期之间的比较,那么我尝试着把开始日期和终止日期合并到一个列做排序,选出同一个会计年的最大日期,这样应该能确定唯一一个有效名称。于是有了下面的代码。
方法
select max(distinct ttt.STARTDATE) as STARTDATE , max(distinct ttt.ENDDATE) as ENDDATE , ttt.code, ttt.countX, ttt.countY,max(distinct ttt.name)
from
(
(
select
t.ID,t.CODE,t.NAME,t.STARTDATE,t.ENDDATE
,date_format(t.STARTDATE,'%Y%m') as startYM
,date_format(t.ENDDATE,'%Y%m') as endYM
,date_format(DATE_ADD(t.STARTDATE, INTERVAL -3 MONTH),'%Y') as countX
,date_format(DATE_ADD(t.STARTDATE, INTERVAL -3 MONTH),'%Y') as countY
from sqltest t
order by t.CODE
)
union all
(
select
t.ID,t.CODE,t.NAME,t.STARTDATE,t.ENDDATE
,date_format(t.STARTDATE,'%Y%m') as startYM
,date_format(t.ENDDATE,'%Y%m') as endYM
,date_format(DATE_ADD(t.STARTDATE, INTERVAL 9 MONTH),'%Y') as countX
,date_format(DATE_ADD(t.ENDDATE, INTERVAL -3 MONTH),'%Y') as countY
from sqltest t
order by t.CODE
)
) ttt
group by ttt.CODE,ttt.countX,ttt.countY
order by ttt.CODE,ttt.countX,ttt.countY
代码的结果如下:
结尾
日本会计年
会计年度从当年4月1号到次年的3月31号
计算会计年的推荐方法
to_char(ADD_MONTHS(t.SHUURYOUDATE, -3),'YYYY') AS COUNTX