获取高低一个工作日实际
前言
其实这个文章集体之前有进行过公布和探讨，在上一篇文章中，介绍了如何通过postgresql数据库的sql语句构建一个工作日的表，并且介绍如何应用sql语法获取某一天往前或者往后的工作日或者天然日，然而理论浏览之后发现短少了很多细节，故这里从新梳理一下整个过程，心愿能够给读者一个参考。
本次实际只是集体提供的一个工作日获取的解决方案，如果有更好的解决方案欢送探讨和分享。
上一篇文章链接：https://juejin.cn/post/7023008573827481637
留神应用的数据库为：PostgreSql
前置筹备
在介绍具体的编码和解决逻辑之前，咱们须要筹备表构造和相干的数据。
表设计
首先这里仍然先回顾一下这个工作日表获取的表构造：
-- ------------------------------ Table structure for sa_calendar_table-- ----------------------------DROP TABLE IF EXISTS "public"."sa_calendar_table";CREATE TABLE "public"."sa_calendar_table" ( "calendar_id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL, "calendar_year" varchar(10) COLLATE "pg_catalog"."default", "calendar_month" varchar(10) COLLATE "pg_catalog"."default", "calendar_date" varchar(10) COLLATE "pg_catalog"."default", "day_of_week" varchar(10) COLLATE "pg_catalog"."default", "day_of_month" varchar(10) COLLATE "pg_catalog"."default", "week_of_year" varchar(10) COLLATE "pg_catalog"."default", "month_of_year" varchar(10) COLLATE "pg_catalog"."default", "quarter_of_year" varchar(10) COLLATE "pg_catalog"."default", "is_end_month" varchar(10) COLLATE "pg_catalog"."default", "is_end_quarter" varchar(10) COLLATE "pg_catalog"."default", "is_end_halfayear" varchar(10) COLLATE "pg_catalog"."default", "is_end_year" varchar(10) COLLATE "pg_catalog"."default", "operator_id" varchar(50) COLLATE "pg_catalog"."default", "operator_name" varchar(50) COLLATE "pg_catalog"."default", "operate_date" timestamp(6), "res_attr1" varchar(40) COLLATE "pg_catalog"."default", "res_attr2" varchar(40) COLLATE "pg_catalog"."default", "res_attr3" varchar(40) COLLATE "pg_catalog"."default", "res_attr4" varchar(40) COLLATE "pg_catalog"."default", "is_work_day" varchar(1) COLLATE "pg_catalog"."default")WITH (fillfactor=100);ALTER TABLE "public"."sa_calendar_table" OWNER TO "postgres";COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_id" IS '主键';COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_year" IS '年';COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_month" IS '月';COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_date" IS '日';COMMENT ON COLUMN "public"."sa_calendar_table"."day_of_week" IS '天然周的第几天';COMMENT ON COLUMN "public"."sa_calendar_table"."day_of_month" IS '月的第几天';COMMENT ON COLUMN "public"."sa_calendar_table"."week_of_year" IS '年的第几个天然周';COMMENT ON COLUMN "public"."sa_calendar_table"."month_of_year" IS '年的第几月';COMMENT ON COLUMN "public"."sa_calendar_table"."quarter_of_year" IS '年的第几季';COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_month" IS '是否月末';COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_quarter" IS '是否季末';COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_halfayear" IS '是否半年末';COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_year" IS '是否年末';COMMENT ON COLUMN "public"."sa_calendar_table"."operator_id" IS '操作人ID';COMMENT ON COLUMN "public"."sa_calendar_table"."operator_name" IS '操作人名称';COMMENT ON COLUMN "public"."sa_calendar_table"."operate_date" IS '操作工夫';COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr1" IS '预留字段1';COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr2" IS '预留字段2';COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr3" IS '预留字段3';COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr4" IS '预留字段4';COMMENT ON COLUMN "public"."sa_calendar_table"."is_work_day" IS '是否为工作日，Y是，N否(即节假日)';| 列名称 | 数据类型 | 形容 | 数据长度 | 不能为空 |
|---|---|---|---|---|
| calendar_id | varchar | 主键 | 255 | YES |
| calendar_year | varchar | 年 | 10 | NO |
| calendar_month | varchar | 月 | 10 | NO |
| calendar_date | varchar | 日 | 10 | NO |
| day_of_week | varchar | 天然周的第几天 | 10 | NO |
| day_of_month | varchar | 月的第几天 | 10 | NO |
| week_of_year | varchar | 年的第几个天然周 | 10 | NO |
| month_of_year | varchar | 年的第几月 | 10 | NO |
| quarter_of_year | varchar | 年的第几季 | 10 | NO |
| is_end_month | varchar | 是否月末 | 10 | NO |
| is_end_quarter | varchar | 是否季末 | 10 | NO |
| is_end_halfayear | varchar | 是否半年末 | 10 | NO |
| is_end_year | varchar | 是否年末 | 10 | NO |
| operator_id | varchar | 操作人ID | 50 | NO |
| operator_name | varchar | 操作人名称 | 50 | NO |
| operate_date | timestamp | 操作工夫 | 6 | NO |
| res_attr1 | varchar | 预留字段1 | 40 | NO |
| res_attr2 | varchar | 预留字段2 | 40 | NO |
| res_attr3 | varchar | 预留字段3 | 40 | NO |
| res_attr4 | varchar | 预留字段4 | 40 | NO |
| is_work_day | varchar | 是否为工作日，Y是，N否(即节假日) | 1 | NO |
另外这里再教大家一个技巧，如何应用postgresql获取某一个表的表构造：
Postgresql 获取某一个表的表构造：
SELECT A .attname AS COLUMN_NAME, T.typname AS data_type, d.description AS column_comment, btrim( SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ), '()' ) AS character_maximum_length,CASE WHEN A.attnotnull = 'f' THEN 'NO' WHEN A.attnotnull = 't' THEN 'YES' ELSE'NO' END AS NULLABLE FROM pg_class C, pg_attribute A, pg_type T, pg_description d WHERE C.relname = '这里填表名' AND A.attnum > 0 AND A.attrelid = C.oid AND A.atttypid = T.oid AND d.objoid = A.attrelid AND d.objsubid = A.attnum上面是语句的调用成果，留神下面的语句倡议给所有的字段加上正文之后再执行。
填充数据
有了表构造还不够，这里咱们还须要填充数据，咱们应用如下的sql填充数据内容，sql语句可能稍微简单了些，另外执行过程中可能会呈现缺失函数的状况，因为集体应用过程中没有碰到此问题，所以就跳过了：
INSERT INTO sa_calendar_table ( calendar_id, calendar_year, calendar_month, calendar_date, day_of_week, day_of_month, week_of_year, month_of_year, quarter_of_year, is_end_month, is_end_quarter, is_end_halfayear, is_end_year, operator_id, operator_name, operate_date, res_attr1, res_attr2, res_attr3, res_attr4, is_work_day ) SELECT A.calendar_id,A.calender_year,A.calender_month,A.calendar_date,A.day_of_week,A.day_of_month,A.week_of_year,A.month_of_year,A.quarter_of_year,A.is_end_month,A.is_end_quarter,A.is_end_halfayear,A.is_end_year,A.operator_id,A.operator_name,A.operator_date,A.res_attr1,A.res_attr2,A.res_attr3,A.res_attr4,A.is_work_day FROM ( SELECT gen_random_uuid ( ) AS calendar_id, to_char( tt.DAY, 'yyyy' ) AS calender_year, to_char( tt.DAY, 'yyyy-mm' ) AS calender_month, to_char( tt.DAY, 'yyyy-mm-dd' ) AS calendar_date, EXTRACT ( DOW FROM tt.DAY ) AS day_of_week, to_char( tt.DAY, 'dd' ) AS day_of_month, EXTRACT ( MONTH FROM tt.DAY ) AS month_of_year, EXTRACT ( WEEK FROM tt.DAY ) AS week_of_year, EXTRACT ( QUARTER FROM tt.DAY ) AS quarter_of_year, CASE WHEN tt.DAY = date_trunc( 'month', tt.DAY + INTERVAL '1 month' ) - INTERVAL '1 day' THEN 'Y' ELSE'N' END AS is_end_month, CASE WHEN tt.DAY = date_trunc( 'quarter', tt.DAY + INTERVAL '3 month' ) - INTERVAL '1 day' THEN 'Y' ELSE'N' END AS is_end_quarter, CASE WHEN tt.DAY = date_trunc( 'year', tt.DAY ) + INTERVAL '6 month' - INTERVAL '1 day' THEN 'Y' ELSE'N' END AS is_end_halfayear, CASE WHEN tt.DAY = date_trunc( 'year', tt.DAY ) + INTERVAL '12 month' - INTERVAL '1 day' THEN 'Y' ELSE'N' END AS is_end_year, 'b8617d3d-d2c9-4a2a-93ba-5b2d8b700cb0' AS operator_id, 'admin' AS operator_name, CAST ( CURRENT_DATE AS TIMESTAMP ) AS operator_date, NULL AS res_attr1, NULL AS res_attr2, NULL AS res_attr3, NULL AS res_attr4, CASE WHEN EXTRACT ( DOW FROM tt.DAY ) = 6 THEN 'N' WHEN EXTRACT ( DOW FROM tt.DAY ) = 0 THEN 'N' ELSE'Y' END AS is_work_day FROM ( SELECT generate_series ( ( SELECT ( date_trunc( 'year', now( ) ) + INTERVAL '1 year' ) :: DATE AS next_year_first_date ), ( SELECT ( SELECT ( date_trunc( 'year', now( ) ) + INTERVAL '2 year' ) :: DATE - 1 AS last_year_last_date ) ), '1 d' ) AS DAY ) AS tt ) AS A;执行实现之后，能够看到插入了365天的数据，这里惟一须要改变的中央是：'1 year'和2 year
实战局部
在上一篇文章中，只是简略介绍了一个利用场景，这里持续欠缺此案例的内容，上面来说一下利用的场景，其实需要也比较简单，然而也比拟常见：
- 获取某一天的上一个工作日或者下一个工作日，或者获取天然日
获取工作日sql
首先咱们须要依据以后的天数获取某一天的工作日列表：
SELECT *FROM ( SELECT -ROW_NUMBER ( ) OVER ( ORDER BY T.calendar_date DESC ) AS addDay, T.calendar_date, T.is_work_day FROM sa_calendar_table T WHERE T.calendar_year in (#{nowYear}, #{prevYear}) and T.calendar_date < CAST ( #{targetYyyyMMdd} AS VARCHAR ) UNION SELECT ROW_NUMBER ( ) OVER ( ORDER BY T.calendar_date )-1 AS addDay, T.calendar_date, T.is_work_day FROM sa_calendar_table T WHERE T.calendar_year in (#{nowYear}, #{prevYear}) ANd T.calendar_date >= CAST ( #{targetYyyyMMdd} AS VARCHAR ) ) mmORDER BY calendar_date这里咱们应用一个理论的案例看一下数据的模式：
SELECT *FROM ( SELECT -ROW_NUMBER ( ) OVER ( ORDER BY T.calendar_date DESC ) AS addDay, T.calendar_date, T.is_work_day FROM sa_calendar_table T WHERE T.calendar_year in ('2020', '2021') and T.calendar_date < CAST ('2021-12-12' AS VARCHAR ) UNION SELECT ROW_NUMBER ( ) OVER ( ORDER BY T.calendar_date )-1 AS addDay, T.calendar_date, T.is_work_day FROM sa_calendar_table T WHERE T.calendar_year in ('2020', '2021') ANd T.calendar_date >= CAST ( '2021-12-12' AS VARCHAR ) ) mmORDER BY calendar_date看到这里，我置信大部分读者应该都晓得这是干啥用的了，这里咱们通过0获取到当天，如果是+1则是下一天，而如果是-1则是上一天，如果是工作日，则对于数据进行判断，，依据这样的规定，上面咱们便能够应用代码来实现：
上面是获取下一天工作日的解决，获取下一天的代码如下：
private static final Pattern TD_DAY = Pattern.compile("^(T|D)\\+\\d$"); private static final String WORK_DAY_CONFIG_T = "T"; private static final String IS_WORK_DAY = "Y"; private static final String IS_NOT_WORK_DAY = "N"; private static final String WORK_DAY_CONFIG_D = "D";public String findNextDayByCalendarList(CalendarDataProcessBo calendarDataProcessBo) { Objects.requireNonNull(calendarDataProcessBo, "以后业务传输对象不能为空"); if (StrUtil.isAllNotBlank(new CharSequence[]{calendarDataProcessBo.getBankSettleCycle()}) && !CollectionUtil.isEmpty(calendarDataProcessBo.getCalendarDayDtos())) { // 额定须要往前推的天数 int extDayOfWorkDayCount = calendarDataProcessBo.getExtDayOfWorkDayCount(); // T+N 或者 D+N String bankSettleCycle = calendarDataProcessBo.getBankSettleCycle(); // 上方截图对应的数据列表 List<SaCalendarDayDto> calendarDayDtos = calendarDataProcessBo.getCalendarDayDtos(); boolean matches = TD_DAY.matcher(bankSettleCycle).matches(); // 校验正则的格局 if (!matches) { logger.error("因为正则表达式{}不合乎校验规定{}所以对账定时工作无奈解决工夫，定时工作运行失败", bankSettleCycle, TD_DAY); throw new UnsupportedOperationException(String.format("因为正则表达式%s不合乎校验规定%s所以对账定时工作无奈解决工夫，定时工作运行失败", bankSettleCycle, TD_DAY)); } else { String[] cycDay = bankSettleCycle.split("\\+"); String tOrDday = cycDay[0]; String addDay = cycDay[1]; boolean matchWorkDayEnable; if (Objects.equals(tOrDday, "T")) { matchWorkDayEnable = true; } else { if (!Objects.equals(tOrDday, "D")) { throw new UnsupportedOperationException("无奈解决t+N或者d+N以外的数据"); } matchWorkDayEnable = false; } // 如果须要获取工作日然而下一天不是工作日，则一直的+1往下获取 for(int finDay = Integer.parseInt(addDay) + extDayOfWorkDayCount; finDay < CollectionUtil.size(calendarDayDtos); ++finDay) { Optional<SaCalendarDayDto> first = calendarDayDtos.stream().filter((item) -> { return Objects.equals(item.getAddDay(), String.valueOf(finDay)); }).findFirst(); if (!first.isPresent()) { throw new UnsupportedOperationException("未发现任何工作日或者天然日数据"); } SaCalendarDayDto saCalendarDayDto = (SaCalendarDayDto)first.get(); if (!matchWorkDayEnable || !Objects.equals(saCalendarDayDto.getIsWorkDay(), "N")) { return saCalendarDayDto.getCalendarDate(); } } throw new UnsupportedOperationException("未发现任何工作日或者天然日数据"); } } else { throw new IllegalArgumentException("传递参数有误，请确保所有参数均已传递"); } }这里其实还有别的写法，比方减少一个BOOLEAN变量判断是往前还是往后，然而集体并不喜爱在参数中管制办法的行为，这样很容易出问题。
写在最初
此工作日的实现办法比拟蠢笨也比较简单，如果有好的想法欢送探讨。