关于postgresql:获取上下一个工作日实践

11次阅读

共计 9656 个字符，预计需要花费 25 分钟才能阅读完成。

获取高低一个工作日实际

前言

​ 其实这个文章集体之前有进行过公布和探讨，在上一篇文章中，介绍了如何通过 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 )

    ) mm
ORDER 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)

    ) mm
ORDER 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 变量判断是往前还是往后，然而集体并不喜爱在参数中管制办法的行为，这样很容易出问题。

写在最初

​ 此工作日的实现办法比拟蠢笨也比较简单，如果有好的想法欢送探讨。

正文完
postgresql
发表至： postgresql
2021-12-20
 0
关于postgresql:PostgreSQL-表的继承
关于postgresql:生于平凡-归于伟大
关于postgresql:回顾历史见证精彩PostgresConfCN2019培训日圆满结束-大会完美收官
关于postgresql:直播回顾日本工程师Amit-Langote畅谈Postgres中的表分区

站内搜索

-「」