如何使用SQL根据日期计算指定月份的完成百分比

核心计算逻辑为 当前日期在当月的第几天 / 当月总天数，再按需要转为百分比格式即可。不同数据库的日期函数有差异，以下是主流数据库的实现方案：

通用逻辑说明 #

• 分子：提取给定日期的「当月日数」，例如2021-08-18的日数为18
• 分母：提取给定日期的「当月总天数」，例如2021年8月总天数为31
• 结果处理：如果需要百分比格式，乘以100后保留指定小数位即可

MySQL 实现 #

-- 示例输入日期为 '2021-08-18'
SELECT 
  DAY('2021-08-18') / DAY(LAST_DAY('2021-08-18')) AS 完成比例,
  CONCAT(ROUND(DAY('2021-08-18') / DAY(LAST_DAY('2021-08-18')) * 100, 2), '%') AS 完成百分比

LAST_DAY 函数返回当月最后一天的日期，DAY 函数提取日期的日部分。

PostgreSQL 实现 #

-- 示例输入日期为 '2021-08-18'
SELECT 
  EXTRACT(DAY FROM '2021-08-18'::DATE) / EXTRACT(DAY FROM (DATE_TRUNC('month', '2021-08-18'::DATE) + INTERVAL '1 month - 1 day')) AS 完成比例,
  ROUND(EXTRACT(DAY FROM '2021-08-18'::DATE) / EXTRACT(DAY FROM (DATE_TRUNC('month', '2021-08-18'::DATE) + INTERVAL '1 month - 1 day')) * 100, 2) || '%' AS 完成百分比

DATE_TRUNC('month', 日期) 得到当月第一天，加1个月减1天即可得到当月最后一天，EXTRACT(DAY FROM ...) 提取日数。

SQL Server 实现 #

-- 示例输入日期为 '2021-08-18'
SELECT 
  DAY('2021-08-18') * 1.0 / DAY(EOMONTH('2021-08-18')) AS 完成比例,
  CONCAT(ROUND(DAY('2021-08-18') * 1.0 / DAY(EOMONTH('2021-08-18')) * 100, 2), '%') AS 完成百分比

代码中乘以1.0是为了避免整数除法截断结果，EOMONTH 函数返回当月最后一天。

Oracle 实现 #

-- 示例输入日期为 DATE '2021-08-18'
SELECT 
  EXTRACT(DAY FROM DATE '2021-08-18') / EXTRACT(DAY FROM LAST_DAY(DATE '2021-08-18')) AS 完成比例,
  ROUND(EXTRACT(DAY FROM DATE '2021-08-18') / EXTRACT(DAY FROM LAST_DAY(DATE '2021-08-18')) * 100, 2) || '%' AS 完成百分比
FROM DUAL;

Oracle的LAST_DAY 函数直接返回当月最后一天。

注意事项 #

• 所有实现默认输入为合法日期类型，如果输入是字符串格式需要先转为日期类型再计算
• 小数保留位数可以通过调整ROUND函数的第二个参数自定义

内容的提问来源于stack exchange，提问作者Coruscate5