时间戳

首页 > 解决方案 > 使用 MySQL 查找给定日期的支付期间的工作日数

mysql - 使用 MySQL 查找给定日期的支付期间的工作日数

问题描述

我想使用我正在努力计算的派生值聚合数据：“支付期内的工作日数”。我在下面提供了进一步的描述：

在任何给定的月份，都有两个支付期。第一个周期从当月的第一个工作日开始，并在 15 日之前的最后一个工作日在 COB 结束（例如：它可能在 14 日结束，因为 15 日是星期六）。第二个期间从上一期间结束后的下一个工作日开始，并在该月的最后一个工作日在 COB 结束。如何将日期时间列转换为表示元组所属支付周期的哪一天的整数，这样可以根据“支付周期中的工作日数”聚合数据。

如果可能，我宁愿不需要中间表或用户定义的函数来执行此操作；最好只使用算术。本质上是一个查询，例如：

SELECT (<arithmetic on datetime col here>) as businessDayNumber, count(someCol)
FROM someTbl
GROUP BY businessDayNumber;

这是提供我想要的结果的示例数据：

CREATE TABLE sampleData (
    dataId INT AUTO_INCREMENT PRIMARY KEY,
    dataDt DATE NOT NULL,
    someValue INT NOT NULL
);


INSERT INTO sampleData (dataDt, someValue) VALUES ('2020-01-01', 51),
 ('2020-01-01', 62),
 ('2020-01-01', 23),
 ('2020-01-01', 54),
 ('2020-01-02', 61),
 ('2020-01-02', 35),
 ('2020-01-02', 47),
 ('2020-01-02', 69),
 ('2020-01-02', 32),
 ('2020-01-02', 83),
 ('2020-01-02', 13),
 ('2020-01-03', 51),
 ('2020-01-03', 62),
 ('2020-01-03', 23),
 ('2020-01-03', 54),
 ('2020-01-03', 61),
 ('2020-01-03', 35),
 ('2020-01-06', 54),
 ('2020-01-06', 61),
 ('2020-01-06', 35),
 ('2020-01-06', 47),
 ('2020-01-06', 69),
 ('2020-01-06', 32),
 ('2020-01-06', 83),
 ('2020-01-06', 13),
 ('2020-01-07', 51),
 ('2020-01-07', 62),
 ('2020-01-07', 23),
 ('2020-01-07', 54),
 ('2020-01-07', 61),
 ('2020-01-07', 35),
 ('2020-01-07', 47),
 ('2020-01-07', 69),
 ('2020-01-07', 32),
 ('2020-01-08', 51),
 ('2020-01-08', 62),
 ('2020-01-08', 23),
 ('2020-01-08', 54),
 ('2020-01-08', 61),
 ('2020-01-08', 35),
 ('2020-01-08', 47),
 ('2020-01-08', 69),
 ('2020-01-08', 32),
 ('2020-01-08', 83),
 ('2020-01-08', 13),
 ('2020-01-09', 35),
 ('2020-01-09', 47),
 ('2020-01-09', 69),
 ('2020-01-09', 32),
 ('2020-01-09', 83),
 ('2020-01-09', 13),
 ('2020-01-09', 54),
 ('2020-01-09', 61),
 ('2020-01-09', 35),
 ('2020-01-09', 47),
 ('2020-01-10', 69),
 ('2020-01-10', 32),
 ('2020-01-10', 83),
 ('2020-01-10', 13),
 ('2020-01-10', 51),
 ('2020-01-10', 62),
 ('2020-01-13', 83),
 ('2020-01-13', 13),
 ('2020-01-13', 54),
 ('2020-01-13', 61),
 ('2020-01-13', 35),
 ('2020-01-13', 47),
 ('2020-01-14', 69),
 ('2020-01-14', 32),
 ('2020-01-14', 83),
 ('2020-01-14', 13),
 ('2020-01-14', 51),
 ('2020-01-14', 62),
 ('2020-01-14', 23),
 ('2020-01-14', 54),
 ('2020-01-15', 61),
 ('2020-01-15', 35),
 ('2020-01-15', 47),
 ('2020-01-15', 69),
 ('2020-01-15', 32),
 ('2020-01-16', 51),
 ('2020-01-16', 62),
 ('2020-01-16', 23),
 ('2020-01-16', 54),
 ('2020-01-16', 61),
 ('2020-01-16', 35),
 ('2020-01-16', 47),
 ('2020-01-16', 69),
 ('2020-01-16', 32),
 ('2020-01-16', 83),
 ('2020-01-16', 13),
 ('2020-01-16', 51),
 ('2020-01-16', 62),
 ('2020-01-17', 23),
 ('2020-01-17', 54),
 ('2020-01-17', 61),
 ('2020-01-17', 35),
 ('2020-01-17', 47),
 ('2020-01-17', 69),
 ('2020-01-17', 32),
 ('2020-01-17', 83),
 ('2020-01-17', 13),
 ('2020-01-17', 54),
 ('2020-01-20', 47),
 ('2020-01-20', 69),
 ('2020-01-20', 32),
 ('2020-01-20', 83),
 ('2020-01-20', 13),
 ('2020-01-20', 51),
 ('2020-01-20', 62),
 ('2020-01-20', 23),
 ('2020-01-20', 54),
 ('2020-01-20', 61),
 ('2020-01-20', 35),
 ('2020-01-20', 47),
 ('2020-01-20', 69),
 ('2020-01-20', 32),
 ('2020-01-21', 83),
 ('2020-01-21', 13),
 ('2020-01-21', 54),
 ('2020-01-21', 61),
 ('2020-01-21', 35),
 ('2020-01-21', 47),
 ('2020-01-21', 69),
 ('2020-01-21', 32),
 ('2020-01-21', 83),
 ('2020-01-21', 13),
 ('2020-01-21', 51),
 ('2020-01-21', 62),
 ('2020-01-21', 23),
 ('2020-01-21', 54),
 ('2020-01-21', 61),
 ('2020-01-21', 35),
 ('2020-01-21', 47),
 ('2020-01-21', 69),
 ('2020-01-21', 32),
 ('2020-01-21', 83),
 ('2020-01-21', 13),
 ('2020-01-22', 54),
 ('2020-01-22', 61),
 ('2020-01-22', 35),
 ('2020-01-22', 47),
 ('2020-01-22', 69),
 ('2020-01-22', 32),
 ('2020-01-22', 83),
 ('2020-01-23', 13),
 ('2020-01-23', 51),
 ('2020-01-23', 62),
 ('2020-01-23', 23),
 ('2020-01-23', 54),
 ('2020-01-23', 61),
 ('2020-01-24', 35),
 ('2020-01-24', 47),
 ('2020-01-24', 69),
 ('2020-01-24', 32),
 ('2020-01-25', 35),
 ('2020-01-25', 47),
 ('2020-01-25', 69),
 ('2020-01-27', 35),
 ('2020-01-27', 47),
 ('2020-01-27', 69),
 ('2020-01-27', 32),
 ('2020-01-27', 83),
 ('2020-01-27', 13),
 ('2020-01-27', 51),
 ('2020-01-27', 62),
 ('2020-01-28', 23),
 ('2020-01-28', 54),
 ('2020-01-28', 61),
 ('2020-01-28', 35),
 ('2020-01-28', 47),
 ('2020-01-28', 69),
 ('2020-01-28', 32),
 ('2020-01-29', 69),
 ('2020-01-29', 32),
 ('2020-01-29', 83),
 ('2020-01-29', 13),
 ('2020-01-29', 51),
 ('2020-01-29', 62),
 ('2020-01-29', 23),
 ('2020-01-30', 54),
 ('2020-01-30', 61),
 ('2020-01-30', 35),
 ('2020-01-30', 47),
 ('2020-01-30', 69),
 ('2020-01-30', 32),
 ('2020-01-31', 35),
 ('2020-01-31', 47),
 ('2020-01-31', 69),
 ('2020-01-31', 32),
 ('2020-01-31', 83),
 ('2020-01-31', 13),
 ('2020-01-31', 54),
 ('2020-01-31', 61),
 ('2020-02-02', 47),
 ('2020-02-03', 54),
 ('2020-02-03', 61),
 ('2020-02-04', 35),
 ('2020-02-04', 51),
 ('2020-02-04', 62),
 ('2020-02-04', 23),
 ('2020-02-04', 54),
 ('2020-02-06', 61),
 ('2020-02-06', 35),
 ('2020-02-06', 47),
 ('2020-02-06', 69),
 ('2020-02-07', 23),
 ('2020-02-07', 54),
 ('2020-02-07', 61),
 ('2020-02-07', 35),
 ('2020-02-07', 47),
 ('2020-02-08', 23),
 ('2020-02-08', 54),
 ('2020-02-08', 61),
 ('2020-02-08', 35),
 ('2020-02-08', 47),
 ('2020-02-08', 69),
 ('2020-02-08', 35),
 ('2020-02-08', 47),
 ('2020-02-08', 69),
 ('2020-02-08', 32),
 ('2020-02-09', 83),
 ('2020-02-09', 13),
 ('2020-02-09', 54),
 ('2020-02-09', 61),
 ('2020-02-09', 35),
 ('2020-02-09', 47),
 ('2020-02-09', 69),
 ('2020-02-09', 32),
 ('2020-02-09', 83),
 ('2020-02-09', 13),
 ('2020-02-09', 51),
 ('2020-02-09', 62),
 ('2020-02-09', 23),
 ('2020-02-09', 54),
 ('2020-02-10', 61),
 ('2020-02-10', 35),
 ('2020-02-10', 47),
 ('2020-02-10', 69),
 ('2020-02-10', 32),
 ('2020-02-10', 51),
 ('2020-02-11', 62),
 ('2020-02-11', 23),
 ('2020-02-11', 54),
 ('2020-02-11', 32),
 ('2020-02-11', 83),
 ('2020-02-12', 13),
 ('2020-02-12', 51),
 ('2020-02-13', 62),
 ('2020-02-13', 23),
 ('2020-02-13', 54),
 ('2020-02-13', 61),
 ('2020-02-13', 35),
 ('2020-02-13', 47),
 ('2020-02-14', 69),
 ('2020-02-14', 32),
 ('2020-02-14', 83),
 ('2020-02-14', 13),
 ('2020-02-14', 54),
 ('2020-02-14', 61),
 ('2020-02-14', 35),
 ('2020-02-14', 47),
 ('2020-02-15', 69),
 ('2020-02-15', 32),
 ('2020-02-15', 83),
 ('2020-02-15', 13),
 ('2020-02-15', 51),
 ('2020-02-16', 62),
 ('2020-02-16', 23),
 ('2020-02-16', 54),
 ('2020-02-16', 61),
 ('2020-02-16', 61),
 ('2020-02-16', 35),
 ('2020-02-16', 47),
 ('2020-02-16', 69),
 ('2020-02-16', 32),
 ('2020-02-16', 83),
 ('2020-02-16', 13),
 ('2020-02-16', 51),
 ('2020-02-16', 62),
 ('2020-02-17', 23),
 ('2020-02-18', 35),
 ('2020-02-18', 47),
 ('2020-02-18', 69),
 ('2020-02-18', 32),
 ('2020-02-18', 83),
 ('2020-02-18', 13),
 ('2020-02-18', 51),
 ('2020-02-18', 62),
 ('2020-02-18', 23),
 ('2020-02-18', 54),
 ('2020-02-18', 61),
 ('2020-02-18', 35),
 ('2020-02-18', 47),
 ('2020-02-18', 69),
 ('2020-02-18', 32),
 ('2020-02-19', 51),
 ('2020-02-19', 62),
 ('2020-02-19', 23),
 ('2020-02-19', 54),
 ('2020-02-19', 61),
 ('2020-02-19', 35),
 ('2020-02-20', 47),
 ('2020-02-20', 69),
 ('2020-02-20', 32),
 ('2020-02-20', 83),
 ('2020-02-20', 13),
 ('2020-02-20', 51),
 ('2020-02-20', 62),
 ('2020-02-20', 23),
 ('2020-02-20', 54),
 ('2020-02-20', 61),
 ('2020-02-20', 35),
 ('2020-02-20', 47),
 ('2020-02-20', 69),
 ('2020-02-20', 32),
 ('2020-02-21', 83),
 ('2020-02-21', 13),
 ('2020-02-21', 54),
 ('2020-02-21', 61),
 ('2020-02-21', 35),
 ('2020-02-21', 47),
 ('2020-02-21', 69),
 ('2020-02-21', 32),
 ('2020-02-21', 83),
 ('2020-02-21', 13),
 ('2020-02-21', 51),
 ('2020-02-21', 62),
 ('2020-02-21', 23),
 ('2020-02-21', 54),
 ('2020-02-21', 61),
 ('2020-02-21', 35),
 ('2020-02-21', 47),
 ('2020-02-21', 69),
 ('2020-02-21', 32),
 ('2020-02-21', 83),
 ('2020-02-21', 13),
 ('2020-02-22', 54),
 ('2020-02-22', 61),
 ('2020-02-22', 35),
 ('2020-02-22', 47),
 ('2020-02-22', 69),
 ('2020-02-22', 32),
 ('2020-02-22', 83),
 ('2020-02-23', 13),
 ('2020-02-23', 51),
 ('2020-02-23', 62),
 ('2020-02-23', 23),
 ('2020-02-23', 54),
 ('2020-02-23', 61),
 ('2020-02-24', 35),
 ('2020-02-24', 47),
 ('2020-02-24', 69),
 ('2020-02-24', 32),
 ('2020-02-25', 35),
 ('2020-02-25', 47),
 ('2020-02-25', 69),
 ('2020-02-25', 32),
 ('2020-02-25', 83),
 ('2020-02-25', 13),
 ('2020-02-25', 51),
 ('2020-02-25', 62),
 ('2020-02-25', 23),
 ('2020-02-25', 54),
 ('2020-02-25', 61),
 ('2020-02-26', 35),
 ('2020-02-26', 47),
 ('2020-02-26', 69),
 ('2020-02-26', 32),
 ('2020-02-26', 83),
 ('2020-02-26', 13),
 ('2020-02-26', 54),
 ('2020-02-26', 61),
 ('2020-02-27', 35),
 ('2020-02-27', 47),
 ('2020-02-27', 69),
 ('2020-02-27', 32),
 ('2020-02-27', 83),
 ('2020-02-27', 13),
 ('2020-02-27', 51),
 ('2020-02-27', 62),
 ('2020-02-28', 69),
 ('2020-02-28', 32),
 ('2020-02-29', 69),
 ('2020-02-29', 32),
 ('2020-02-29', 83);

并在 SQL Fiddle中。

标签： mysql

解决方案

WITH cte AS ( SELECT someValue, 
                     DENSE_RANK() OVER (PARTITION BY DATE_FORMAT(dataDt, '%Y%m'), 
                                                     DAY(dataDT) <= 15
                                        ORDER BY dataDT) businessDayNumber
              FROM sampleData 
              WHERE DAYOFWEEK(dataDT) BETWEEN 2 AND 6 )
SELECT businessDayNumber, COUNT(someValue)
FROM cte
GROUP BY businessDayNumber;

小提琴

推荐阅读