1.场景描述
在工作中遇到一个指标“扣款成功率（工作日）”，由于涉及扣款，有些结算平台在非工作日可能不会进行资金结算，这可能导致账户余额不足，进而导致扣款失败。因此，计算指标时需要排除这一因素，以准确计算“扣款成功率（工作日）”。
这个指标计算过程，我们肯定需要识别出法定节假日、调休日、正常周末、正常工作日，才能得出最终结果。当然也不止这个场景需要用识别非工作日。
举一些栗子~
- 考勤和请假管理：人力资源部门需要识别工作日和假期，以准确计算员工出勤率、假期余额和薪资。SQL可以帮助提升考勤管理的效率。
- 用户行为分析：电商、游戏等行业分析用户在工作日和非工作日的行为差异，以调整促销活动或流量预测。
- 物流预测：节假日可能影响运输和交货时间。识别非工作日有助于更准确地预测物流时间。
- 计算工作日差：计算两个日期之间的工作日差（排除法定节假日、考虑调休日、正常周末）。
那这个要怎么识别呢？
我们先来厘清一下概念~
- 法定节假日：法律规定的休假日。
- 周末：通常是周六、周日，除非被调为工作日。
- 调休日：通常是调整的工作日，即使在周末，也需要算作工作日。
- 非工作日：包含法定节假日和没有调休的周末。
2.问题描述
- 以2024年为例，某电子商务平台希望分析订单量是否受假期影响。需要识别法定节假日、非工作日（周末）以及调休日，对订单数据进行分析。
- 如何计算两个日期之间的工作日差（剔除法定节假日、考虑调休日和正常周末）
3.如何解决？
- 我们需要在数据库中识别每一笔订单是否发生在法定节假日、周末或调休日，以便分析这些日期的订单情况。
- 需要准备的数据
- 订单数据：包括订单日期、订单金额等字段。
- 节假日表：包含每年的法定节假日、调休日信息，确保每年都能更新。
- 工作日/非工作日信息：通常通过日期表来区分工作日和周末。
- 难点应该就在节假日表，因为每一年的节假日安排都是不一样的，这个需要人工维护或者API导入。
4.数据准备
（1）订单数据表 (orders)
20240101——20240718，每天有五条订单数据，由于数据量过大，用Python生成SQL批量插入语句（注意这个地方你可能用python建完表之后，到mysql workbench上看发现只有一千条数据，可能是你设置数据仅允许1000条数据，这个时候可以：
- 打开 MySQL Workbench。
- 点击“Edit” → “Preferences”。
- 在 SQL Queries 选项卡中，找到 Limit Rows 设置。
- 将默认的限制值改大，或取消勾选 “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