首页 > 数据库 >SQL如何识别非工作日（法定节假日+正常周末（非调休日））？

SQL如何识别非工作日（法定节假日+正常周末（非调休日））？

时间：2024-09-25 16:22:18浏览次数：3  
标签：02 节假日 法定 2024 SQL date 调休 SELECT

1.场景描述

在工作中遇到一个指标“扣款成功率（工作日）”，由于涉及扣款，有些结算平台在非工作日可能不会进行资金结算，这可能导致账户余额不足，进而导致扣款失败。因此，计算指标时需要排除这一因素，以准确计算“扣款成功率（工作日）”。

这个指标计算过程，我们肯定需要识别出法定节假日、调休日、正常周末、正常工作日，才能得出最终结果。当然也不止这个场景需要用识别非工作日。

举一些栗子~

  • 考勤和请假管理：人力资源部门需要识别工作日和假期，以准确计算员工出勤率、假期余额和薪资。SQL可以帮助提升考勤管理的效率。
  • 用户行为分析：电商、游戏等行业分析用户在工作日和非工作日的行为差异，以调整促销活动或流量预测。
  • 物流预测：节假日可能影响运输和交货时间。识别非工作日有助于更准确地预测物流时间。
  • 计算工作日差：计算两个日期之间的工作日差（排除法定节假日、考虑调休日、正常周末）。

那这个要怎么识别呢？

我们先来厘清一下概念~

  • 法定节假日：法律规定的休假日。
  • 周末：通常是周六、周日，除非被调为工作日。
  • 调休日：通常是调整的工作日，即使在周末，也需要算作工作日。
  • 非工作日：包含法定节假日和没有调休的周末。

2.问题描述

  • 以2024年为例，某电子商务平台希望分析订单量是否受假期影响。需要识别法定节假日、非工作日（周末）以及调休日，对订单数据进行分析。
  • 如何计算两个日期之间的工作日差（剔除法定节假日、考虑调休日和正常周末）

3.如何解决？

  • 我们需要在数据库中识别每一笔订单是否发生在法定节假日、周末或调休日，以便分析这些日期的订单情况。
  • 需要准备的数据
    • 订单数据：包括订单日期、订单金额等字段。
    • 节假日表：包含每年的法定节假日、调休日信息，确保每年都能更新。
    • 工作日/非工作日信息：通常通过日期表来区分工作日和周末。
  • 难点应该就在节假日表，因为每一年的节假日安排都是不一样的，这个需要人工维护或者API导入。

4.数据准备

（1）订单数据表 (orders)

        20240101——20240718，每天有五条订单数据，由于数据量过大，用Python生成SQL批量插入语句（注意这个地方你可能用python建完表之后，到mysql workbench上看发现只有一千条数据，可能是你设置数据仅允许1000条数据，这个时候可以：

  1. 打开 MySQL Workbench。
  2. 点击“Edit” → “Preferences”。
  3. 在 SQL Queries 选项卡中，找到 Limit Rows 设置。
  4. 将默认的限制值改大，或取消勾选 “Limit Rows”，以禁用查询结果的限制。）
  • order_id：订单编号
  • order_date：订单日期
  • order_amount：订单金额
--我用的是Jupyter，所以我先在开始菜单搜索了anaconda prompt，
--然后输入pip install mysql-connector-python

--接着打开Jupyter
import mysql.connector
# 创建连接
connection = mysql.connector.connect(
    host='localhost',       # 你的 MySQL 服务器地址
    user='....',    # 你的 MySQL 用户名
    password='.....',# 你的 MySQL 密码
    database='....' # 你要操作的数据库名称
)
# 创建游标
cursor = connection.cursor()
# 创建表的SQL语句
create_table_query = """
CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    order_amount DECIMAL(10, 2) NOT NULL
);
"""
# 执行创建表语句
cursor.execute(create_table_query)
connection.commit()  # 提交更改
import random
from datetime import datetime, timedelta
# 生成日期范围
start_date = datetime(2024, 1, 1)
end_date = datetime(2024, 12, 31)
delta = timedelta(days=1)

# 插入数据的SQL语句
insert_order_query = "INSERT INTO orders (order_date, order_amount) VALUES (%s, %s)"

# 开始生成数据
current_date = start_date
while current_date <= end_date:
    for _ in range(5):  # 每天插入5条订单数据
        order_amount = round(random.uniform(50, 500), 2)  # 生成50到500之间的随机金额
        cursor.execute(insert_order_query, (current_date.strftime('%Y-%m-%d'), order_amount))
    current_date += delta

# 提交更改
connection.commit()

（2）假期表 (holidays)

  • holiday_date：节假日日期
  • holiday_type：假期类型（如"法定节假日"、"调休日"）

（3）日期表 (calendar)

  • calendar_date：日期
  • is_weekend：是否为周末（TRUE为周末，FALSE为工作日）
  • 此代码可以复用，如果需要2025年，只需要把所有2024改成2025即可
CREATE TABLE calendar (
    calendar_date DATE PRIMARY KEY,   -- 日期
    is_weekend BOOLEAN                -- 是否为周末
);

-- 插入2024年全年的数据，生成从2024-01-01到2024-12-31的所有日期
INSERT INTO calendar (calendar_date, is_weekend)
SELECT
    date AS calendar_date,
    CASE 
        WHEN DAYOFWEEK(date) IN (1, 7) THEN TRUE  -- 判断是否为周末
        ELSE FALSE
    END AS is_weekend
FROM (
    -- 使用递增的序列生成 2024 年每一天的日期
    SELECT '2024-01-01' + INTERVAL seq DAY AS date
    FROM
    (
        -- 生成 0 到 364 的数字序列，对应2024年365天
        SELECT @row := @row + 1 AS seq
        FROM
        (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
        (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
        (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3,
        (SELECT @row := -1) init
    ) seqs
) AS dates
WHERE date <= '2024-12-31';

 5.节假日表——方案一人工维护

CREATE TABLE holidays (
    holiday_date DATE PRIMARY KEY,  -- 这是节假日的日期
    holiday_type VARCHAR(20)        -- 这是节假日的类型，比如"法定节假日"、"调休日"
);

INSERT INTO holidays (holiday_date, holiday_type)
VALUES
('2024-01-01', '法定节假日'),
('2024-02-04', '调休日'),
('2024-02-10', '法定节假日'),
('2024-02-11', '法定节假日'),
('2024-02-12', '法定节假日'),
('2024-02-13', '法定节假日'),
('2024-02-14', '法定节假日'),
('2024-02-15', '法定节假日'),
('2024-02-16', '法定节假日'),
('2024-02-17', '法定节假日'),
('2024-02-18', '调休日'),
('2024-04-04', '法定节假日'),
('2024-04-05', '法定节假日'),
('2024-04-06', '法定节假日'),
('2024-04-07', '调休日'),
('2024-04-28', '调休日'),
('2024-05-01', '法定节假日'),
('2024-05-02', '法定节假日'),
('2024-05-03', '法定节假日'),
('2024-05-04', '法定节假日'),
('2024-05-05', '法定节假日'),
('2024-05-11', '调休日'),
('2024-06-08', '法定节假日'),
('2024-06-09', '法定节假日'),
('2024-06-10', '法定节假日'),
('2024-09-14', '调休日'),
('2024-09-15', '法定节假日'),
('2024-09-16', '法定节假日'),
('2024-09-17', '法定节假日'),
('2024-09-29', '调休日'),
('2024-10-01', '法定节假日'),
('2024-10-02', '法定节假日'),
('2024-10-03', '法定节假日'),
('2024-10-04', '法定节假日'),
('2024-10-05', '法定节假日'),
('2024-10-06', '法定节假日'),
('2024-10-07', '法定节假日'),
('2024-10-12', '调休日');

 

6.方案二：API接入

可以使用一些公开的假期 API 来获取数据。例如，中国的一些常用假期 API 服务包括：

  • 天行数据（提供中国及全球假期 API）
  • APIStore（中国节假日 API）
  • 第三方 GitHub 项目（开源假期数据）

这些服务通常提供 RESTful API 接口，返回的数据格式为 JSON 或 XML。

通常需要在这些 API 平台上注册账号，然后申请使用 API。很多平台会提供一定的免费调用次数，但超过一定调用量可能会收费。

如果申请到了API，在 Python 环境中使用 requests 库来发起 API 请求，调用 API 获取法定节假日信息并将这些假期数据插入 MySQL 数据库的 holidays 表中。

7.问题1解答

  • 每一非工作日订单情况分析
    • 非工作日指的是正常周末（非调休）+节假日
    • 进一步拓展：还可以研究不同节假日的订单情况分析，可以在节假日表里加一个节假日字段
    • 此类问题，其实只需要每一个日期有一个标签，是否是工作日，筛选想要的类别做计算即可
WITH vacation AS(
	SELECT t2.calendar_date 
	,t2.is_weekend
	--此处生成一个字段，是否为工作日，0是工作日，1是非工作日
	,CASE WHEN t3.holiday_type = '法定节假日' AND t2.calendar_date = t3.holiday_date THEN 1 
	WHEN t3.holiday_type = '调休日' AND t2.calendar_date = t3.holiday_date THEN 0
	ELSE t2.is_weekend END AS is_weekday
	FROM calendar t2
	LEFT JOIN holidays t3 ON t2.calendar_date = t3.holiday_date 
)
SELECT t1.order_date
,COUNT(t1.order_id) AS order_num
,SUM(t1.order_amount) AS ttl_amount
FROM orders t1
LEFT JOIN vacation t4 ON t4.calendar_date = t1.order_date
WHERE t4.is_weekday = 1
GROUP BY t1.order_date
--本例中订单数据集是按每个日期5个订单随机生成订单金额，并没有考虑每个具体日期

 

8.问题2解答（工作日差

如果需要计算出两个日期的工作日差，需要剔除法定节假日、正常周末，同时要考虑调休日

--数据准备
CREATE TABLE deductions (
    deduction_id INT PRIMARY KEY,      -- 缴费编号
    start_date DATE,                   -- 首次缴费时间
    end_date DATE,                     -- 最新缴费时间
    status VARCHAR(10)                 -- 缴费状态 ('成功', '失败', '处理中')
);
INSERT INTO deductions (deduction_id, start_date, end_date, status) VALUES
(1, '2024-02-01', '2024-02-02', '成功'),
(2, '2024-02-01', '2024-02-06', '成功'),
(3, '2024-02-03', '2024-02-07', '失败'),
(4, '2024-02-04', '2024-02-05', '成功'),
(5, '2024-02-06', '2024-02-08', '成功'),
(6, '2024-02-06', '2024-02-12', '成功'),
(7, '2024-02-09', '2024-02-12', '成功'),
(8, '2024-02-10', '2024-02-17', '成功'), 
(9, '2024-02-15', '2024-02-16', '成功'),
(10, '2024-02-20', '2024-02-22', '失败'),
(11, '2024-02-23', '2024-02-25', '成功'),
(12, '2024-02-26', '2024-02-27', '成功'),
(13, '2024-02-27', '2024-02-28', '成功'),
(14, '2024-02-28', '2024-03-01', '处理中'),
(15, '2024-02-03', '2024-02-18', '成功'),
(16, '2024-02-03', '2024-02-20', '成功'),
(17, '2024-02-03', '2024-02-05', '成功'),
(18, '2024-02-08', '2024-02-16', '成功'),
(19, '2024-02-22', '2024-02-26', '成功'),
(20, '2024-02-17', '2024-02-20', '成功')
; 
--解答
WITH vacation AS (
    SELECT 
        t2.calendar_date, 
        t2.is_weekend,
        -- 直接根据调休日和法定节假日设定is_workday
        CASE 
            WHEN t3.holiday_type = '法定节假日' THEN 0  
            WHEN t3.holiday_type = '调休日' THEN 1     
            ELSE CASE WHEN t2.is_weekend = 1 THEN 0 ELSE 1 END 
        END AS is_workday
    FROM calendar t2
    LEFT JOIN holidays t3 ON t2.calendar_date = t3.holiday_date
)
SELECT 
    t1.deduction_id, 
    t1.start_date, 
    t1.end_date, 
    COUNT(t2.calendar_date) AS workday_count  -- 统计工作日
FROM deductions t1
LEFT JOIN vacation t2 
    ON t2.calendar_date BETWEEN t1.start_date AND t1.end_date
    AND t2.is_workday = 1  -- 仅统计工作日
WHERE t1.status = '成功'
GROUP BY t1.deduction_id, t1.start_date, t1.end_date;

 

9.需要注意的地方

  • 特殊行业的非工作日定义： 并不是所有行业的非工作日都是周末和法定节假日。比如一些互联网企业或金融机构，虽然大部分法定节假日不进行日常运营，但仍有可能在这些日子进行资金结算或用户处理。这就要求在行业背景下进行自定义的节假日识别。
  • 数据库性能问题： 当处理大规模扣款、订单或考勤数据时，频繁的日期查询（尤其是跨节假日计算）会消耗较大的数据库资源。可以通过索引、优化 SQL 查询语句来减少查询时间。

 

标签：02,节假日,法定,2024,SQL,date,调休,SELECT
From： https://blog.csdn.net/Yaoo415/article/details/142435850

相关文章

  • Linux常用命令（Mysql）
    --删除表内数据(Mysql)usedc;#切换到待删除表所在的数据库truncatetable[表名]#删除表--数据库导入SQL文件数据（Mysql）sourcea.sql;--SQL增删改查insertintostudent(id,name,sex,birth)values('01','赵雷','男','1990');deletefromstudentwhereid=......
  • Windows Server 安装MySQL教程（图文）
    本篇教程，在服务器WindowsServer2016（中文版）上安装MySQL8.0，并记录详细的安装步骤。1、下载安装包在mysql官网上下载安装包下载地址：https://dev.mysql.com/downloads/installer/2、安装步骤下载之后，双击运行mysql-installer-community-8.0.39.0.msi如果双击无反应，那么需要安装一......
  • MySQL SQL基础常见面试题整理
    NOSQL和SQL的区别是什么？SQL数据库是指关系型数据库，主要有：SQLServer，Oracle，MySQL（开源），PostgreSQL（开源）。关系型数据库用于存储结构化数据，这些数据在逻辑上以行和列的二维表形式存在，每一列代表一个数据属性，每一行则代表一个数据实体。NoSQL是指非关系型数据库，主要有MongoDB和......
  • Linux中MySQL配置主主复制操作
    一、GTIDGTID（GlobalTransactionIdentifier）是MySQL的一种用于标识分布式环境中事务的全局唯一标识符。它在MySQL的主从复制场景中尤为重要，尤其是在使用MariaDB或MySQL5.6及更高版本的环境中。GTID由两部分组成：服务器ID（标识执行该事务的服务器）和事务序号（表示在该服务器上执......
  • 解读MySQL8.0数据字典重构源码
    摘要：本文对社区MySQL5.7到8.0演进过程中数据字典DD的重构（缓存，持久化），AtomicDDL的关键实现进行了分析。本文分享自华为云社区《【华为云MySQL技术专栏】MySQL8数据字典重构源码解读》，作者：GaussDB数据库1.背景介绍在MySQL5.7版本的使用实践过程中，我们很容易遇到DDL崩溃后导致数......
  • Windows 11 彻底卸载MySQL和安装
    Windows11彻底卸载MySQL和安装卸载1.关闭MySQL服务快捷键Win+R打开运行窗口输入services.msc打开服务窗口找到MySQL服务，停止此服务2.卸载MySql软件进入控制面板-程序和功能卸载一切和MySQL相关的软件3.删除MySQL相关文件夹如果安装时有指......
  • mysql flaot 不准
    (JDBC-MySql)概述JDBC全称JavaDataBaseConnectivity：java数据库连接在JDBC创建之前java程序员每操作一款关系型数据库就需要学习java连接该数据库代码，由于关系型数据库过多（如:oracle、db2、MySQL......），不可能全部学习，所以java程序员就期望SUN公司能研发出一套可以运行所有关系......
  • Windows安装HeidiSQL教程（图文）
    一、软件简介HeidiSQL是一款开源的数据库管理工具，主要用于管理MySQL、MariaDB、SQLServer、PostgreSQL和SQLite等数据库系统。它提供了直观的用户界面，使用户可以轻松地连接到数据库服务器、执行SQL查询、浏览和编辑数据、管理数据库结构等操作。跨平台支持：HeidiSQL可以在Windows操......
  • SQLServer中，当压缩备份遇到TDE加密的数据库
    压缩备份和TDESQLServer的压缩备份是一个特性，根据实际使用中的观察，压缩比至少在1:5左右，也就是备份时增加了压缩选项后可以至少压缩到数据文件大小的20%甚至更低，可以很大程度上加快备份执行时间，减轻IO压力和节省磁盘存储空间SQLServer的TDE（TransparentDataEncryption）作为数据库......
  • SQL 高级语法 MERGE INTO
    SQL高级语法MERGEINTO 根据与源表相联接的结果，对目标表进行插入、更新、删除等操作。例如，对目标表，如果源表存在的数据则更新，没有的则插入，就可以使用MEREG进行同步。基本语法MERGEINTOtarget_tableUSINGsource_tableONconditionWHENMATCHEDTHENXXXWHENNOT......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99