Geekly Articles each Day

MySQLおよびMariaDBのカレンダー関数

実践が示すように、カレンダーを使用する多くのシステムは通常、日付と就業日、週末、休日への対応をリストする静的テーブルの形式で構築されます。 通常、問題はプログラマが介入することなくシステムが長時間動作し、完成したカレンダーが単に終了したときに始まります。 次のプロジェクトでは、この状況を少し最適化することにし、たとえば組み込みのタイマーを使用して、自動的に作成または再計算されるカレンダーを作成しました。


特定の範囲の値を返すイテレータが組み込まれているMariaDB（MySQL）に関する情報を多くの人が持っているかどうかはわかりません。 誰も知らない場合は、リンクを読むことができます。


原理は簡単です。 開始、終了、ステップが渡される通常のForループと同じです。 このイテレータは非常に高速で、シーケンス計算を実行できます。


簡単なものから始めましょう。


皆が知っているように、通常のカレンダーは、就業日と週末、および公式の祝日と、週末にこれらの休日が予定変更された場合に再スケジュールされる日で構成されます。 祝日は通常、今年の半ばにロシア連邦政府の命令により転送されます。


2つのテーブルを作成します。 定期的な年次休暇と週末のスケジュール変更。


CREATE TABLE `holidays_periodic` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `hdate` DATE NOT NULL COMMENT '    ', `hdate_remap` DATE NOT NULL COMMENT '        ', `hcomment` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', PRIMARY KEY (`id`) ) COMMENT='    ' COLLATE='utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT AUTO_INCREMENT=1 ; 

 CREATE TABLE `holidays` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `hdate` DATE NOT NULL COMMENT '    ', `hdate_remap` DATE NOT NULL COMMENT '        ', `hcomment` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', `hdate_workday` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '    (0/1)', PRIMARY KEY (`id`), INDEX `IDX_hdate` (`hdate`), INDEX `IDX_hdate_remap` (`hdate_remap`) ) COMMENT='    ' COLLATE='utf8_unicode_ci' ENGINE=InnoDB AUTO_INCREMENT=1 ;

定期的な休日をテーブルに注ぎます


 INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (1, '1970-01-01', '0000-00-00', ' '); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (2, '1970-01-07', '0000-00-00', ' '); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (3, '1970-02-23', '0000-00-00', '  '); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (4, '1970-03-08', '0000-00-00', '  '); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (5, '1970-05-01', '0000-00-00', '   '); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (6, '1970-05-09', '0000-00-00', ' '); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (7, '1970-06-12', '0000-00-00', ' '); INSERT INTO `holidays_periodic` (`id`, `hdate`, `hdate_remap`, `hcomment`) VALUES (8, '1970-11-04', '0000-00-00', '  '); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (1, '2017-01-01', '2017-02-24', ' ', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (2, '2017-01-07', '2017-05-08', ' ', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (3, '2017-02-23', '0000-00-00', '  ', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (4, '2017-03-08', '0000-00-00', '  ', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (5, '2017-05-01', '0000-00-00', '   ', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (6, '2017-05-09', '0000-00-00', ' ', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (7, '2017-06-12', '0000-00-00', ' ', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (8, '2017-11-04', '2017-11-06', '  ', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (9, '2016-01-02', '2016-05-03', NULL, 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (10, '2016-01-03', '2016-03-07', NULL, 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (11, '2016-02-20', '2016-02-22', NULL, 1); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (12, '2016-05-01', '2016-05-02', NULL, 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (13, '2016-06-12', '2016-06-13', NULL, 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (16, '2017-01-02', '0000-00-00', '', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (17, '2017-01-03', '0000-00-00', '', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (18, '2017-01-04', '0000-00-00', '', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (19, '2017-01-05', '0000-00-00', '', 0); INSERT INTO `holidays` (`id`, `hdate`, `hdate_remap`, `hcomment`, `hdate_workday`) VALUES (20, '2017-01-06', '0000-00-00', '', 0);

holidaysテーブルにはhdate_workdayフィールドが含まれていることに注意してください。これは、労働者に再割り当てされた週末から転送が行われた場合に使用されます。 たとえば、2016年2月20日は土曜日になりますが、政府によると、この日は2月22日に延期され、就業日として指定されます。 つまり 土曜日に作業を行い、その後3日間休みます。


SEQUENCEの魔法である初期データがあります。 カレンダーテーブルを作成する


 CREATE TABLE `calendar_byholiday` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `cdate` DATE NULL DEFAULT NULL, `holiday` TINYINT(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), INDEX `IDX_cdate` (`cdate`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT AUTO_INCREMENT=1 ;

テーブルに入力するには、ストアドプロシージャを作成します


 CREATE DEFINER=`root`@`%` PROCEDURE `rebuild_calendar_byholiday`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '    ' BEGIN DECLARE PastWeek DATE; DECLARE DaysLimit INT DEFAULT 365; DECLARE YearDIFF INT DEFAULT 0; #           SET PastWeek=DATE_SUB(CURDATE(),INTERVAL 1 WEEK); #     TRUNCATE TABLE calendar_byholiday; #     SEQUENCE  1  365 #      00:00      holidays INSERT INTO calendar_byholiday (cdate,holiday) SELECT DATE (PastWeek + INTERVAL s.seq DAY) AS cdate, IF(DAYOFWEEK(DATE (PastWeek + INTERVAL s.seq DAY)) >= 2 AND DAYOFWEEK(DATE (PastWeek + INTERVAL s.seq DAY)) <= 6,0,1) as holiday FROM (SELECT seq FROM seq_0_to_365) s LIMIT DaysLimit ; #       SET YearDIFF = YEAR(CURDATE()) - 1970; #       UPDATE calendar_byholiday c, holidays_periodic hp SET c.holiday = 1 WHERE c.cdate=DATE_ADD(hp.hdate,INTERVAL YearDIFF YEAR); #        UPDATE calendar_byholiday c, holidays h SET c.holiday = 1 WHERE (c.cdate=h.hdate OR c.cdate=h.hdate_remap) AND h.hdate_workday=0; #       . UPDATE calendar_byholiday c, holidays h SET c.holiday = 0 WHERE c.cdate=h.hdate AND h.hdate_workday=1; END

次に、単純なリクエストを行うだけです


 call rebuild_calendar_byholiday(); select * from calendar_byholiday;

多くの人はおそらく「だから何？魔法はどこにあるの？」と言うでしょう。 そして、魔法は、DaysLimitの1つのパラメーターとサイクルの最大値を変更するだけで、カレンダーの深さを調整できることです。


 FROM (SELECT seq FROM seq_0_to_365) s LIMIT DaysLimit;

時間内にカレンダーを作成するには、わずかな費用がかかります。


 call rebuild_calendar_byholiday(); /*  : 0  : 0 : 0  1 : 0,032 sec. */

しかし、日付キャストのないカレンダーは機能します。 カレンダーの通常の操作では、特定の時間間隔の後、検索に続く日付を決定します。 たとえば、前の営業日、四半期の最初の日、月の最後の営業日など。 以下に、データ処理ネットワークからのベストプラクティスとベストプラクティスをまとめました。


 CREATE DEFINER=`root`@`%` FUNCTION `date_transform`( `InDate` TIMESTAMP, `Method` INT, `Units` VARCHAR(10), `Duration` INT SIGNED ) RETURNS datetime LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ' ' BEGIN DECLARE ReturnDate DATETIME; DECLARE LastQuarterDate DATE; DECLARE LastYearDate DATE; SET ReturnDate=InDate; CASE WHEN Method = 2 THEN #    SELECT CASE WHEN WEEKDAY(InDate - INTERVAL 1 DAY) = 5 THEN (InDate - INTERVAL 2 DAY) WHEN WEEKDAY(InDate - INTERVAL 1 DAY) = 6 THEN (InDate - INTERVAL 3 DAY) ELSE (InDate - INTERVAL 1 DAY) END INTO ReturnDate; WHEN Method = 3 THEN #    SELECT (InDate - INTERVAL DAYOFMONTH(InDate)-1 DAY) INTO ReturnDate; WHEN Method = 4 THEN #    SELECT STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM InDate),'-',(QUARTER(InDate)-1)*3+1,'-01'),'%Y-%m-%d') INTO ReturnDate; WHEN Method = 5 THEN #    SELECT STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM InDate),'-01-01'),'%Y-%m-%d') INTO ReturnDate; WHEN Method = 6 THEN #    SELECT LAST_DAY(InDate) INTO ReturnDate; WHEN Method = 7 THEN #    SELECT CASE WHEN MOD(MONTH(InDate),3) != 0 THEN LAST_DAY(DATE_ADD(InDate,INTERVAL (3-MOD(MONTH(InDate),3)) MONTH)) ELSE LAST_DAY(InDate) END INTO ReturnDate; WHEN Method = 8 THEN #    SELECT STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM InDate),'-12-31'),'%Y-%m-%d') INTO ReturnDate; WHEN Method = 9 THEN #     SELECT CASE WHEN WEEKDAY(LAST_DAY(InDate)) = 5 THEN DATE_SUB(LAST_DAY(InDate),INTERVAL 1 DAY) WHEN WEEKDAY(LAST_DAY(InDate)) = 6 THEN DATE_SUB(LAST_DAY(InDate),INTERVAL 2 DAY) ELSE LAST_DAY(InDate) END INTO ReturnDate; WHEN Method = 10 THEN #     SELECT CASE WHEN MOD(MONTH(InDate),3) != 0 THEN LAST_DAY(DATE_ADD(InDate,INTERVAL (3-MOD(MONTH(InDate),3)) MONTH)) ELSE LAST_DAY(InDate) END INTO LastQuarterDate; SELECT CASE WHEN WEEKDAY(LAST_DAY(LastQuarterDate)) = 5 THEN DATE_SUB(LAST_DAY(LastQuarterDate),INTERVAL 1 DAY) WHEN WEEKDAY(LAST_DAY(LastQuarterDate)) = 6 THEN DATE_SUB(LAST_DAY(LastQuarterDate),INTERVAL 2 DAY) ELSE LAST_DAY(LastQuarterDate) END INTO ReturnDate; WHEN Method = 11 THEN #     SELECT STR_TO_DATE(CONCAT(EXTRACT(YEAR FROM InDate),'-12-31'),'%Y-%m-%d') INTO LastYearDate; SELECT CASE WHEN WEEKDAY(LAST_DAY(LastYearDate)) = 5 THEN DATE_SUB(LAST_DAY(LastYearDate),INTERVAL 1 DAY) WHEN WEEKDAY(LAST_DAY(LastYearDate)) = 6 THEN DATE_SUB(LAST_DAY(LastYearDate),INTERVAL 2 DAY) ELSE LAST_DAY(LastYearDate) END INTO ReturnDate; WHEN Method = 12 THEN #  SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate) DAY) INTO ReturnDate; WHEN Method = 13 THEN #  SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-1 DAY) INTO ReturnDate; WHEN Method = 14 THEN #  SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-2 DAY) INTO ReturnDate; WHEN Method = 15 THEN #  SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-3 DAY) INTO ReturnDate; WHEN Method = 16 THEN #  SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-4 DAY) INTO ReturnDate; WHEN Method = 17 THEN #  SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-5 DAY) INTO ReturnDate; WHEN Method = 18 THEN #  SELECT DATE_SUB(InDate,INTERVAL WEEKDAY(InDate)-6 DAY) INTO ReturnDate; ELSE #   BEGIN END; END CASE; SELECT CASE WHEN Units='minute' THEN ReturnDate + INTERVAL Duration MINUTE WHEN Units='hour' THEN ReturnDate + INTERVAL Duration HOUR WHEN Units='day' THEN ReturnDate + INTERVAL Duration DAY WHEN Units='week' THEN ReturnDate + INTERVAL Duration WEEK WHEN Units='month' THEN ReturnDate + INTERVAL Duration MONTH WHEN Units='year' THEN ReturnDate + INTERVAL Duration YEAR WHEN Units='workday' THEN get_workday_offset(ReturnDate,Duration) ELSE ReturnDate END INTO ReturnDate; RETURN ReturnDate; END 

 CREATE DEFINER=`root`@`%` FUNCTION `get_workday_offset`( `InDate` DATETIME, `Offset` INT ) RETURNS DATETIME LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE ReturnDate DATETIME DEFAULT NULL; SET Offset=Offset-1; CASE WHEN Offset < 0 THEN SELECT InDate INTO ReturnDate; ELSE BEGIN SELECT cdate INTO ReturnDate FROM calendar_byholiday WHERE cdate >= InDate AND holiday=0 LIMIT Offset,1; END; END CASE; RETURN ReturnDate; END

実際、これは何のためですか？ これは、イベントのさまざまな定期刊行物を計算するために必要です。 たとえば、2017年1月の月の初めから6営業日目を理解する必要があります。


 MariaDB [db]> select date_transform('2017-01-26',3,'workday',6); +--------------------------------------------+ | date_transform('2017-01-26',3,'workday',6) | +--------------------------------------------+ | 2017-01-16 00:00:00 | +--------------------------------------------+ 1 row in set (0.01 sec)

または、2016年12月15日の月曜日の日付。


 MariaDB [db]> select date_transform('2016-12-15',12,0,0); +-------------------------------------+ | date_transform('2016-12-15',12,0,0) | +-------------------------------------+ | 2016-12-12 00:00:00 | +-------------------------------------+ 1 row in set (0.00 sec)

等 したがって、関数は通常のクエリから簡単に呼び出され、非常に柔軟です。 リクエストの形式は次のとおりです。


date_transform（DATETIME、ModifyID、Units、Offset）


どこで
DATETIME-日付または日付と時刻


ModifyID：


 id;name 1;  2;   3;   4;   5;   6;   7;   8;   9;    10;    11;    12; 13; 14; 15; 16; 17; 18;

単位：


 minute hour day week month year workday

オフセット：SIGNED INT


Offsetに負の数を指定すると、計算は逆方向に実行されます。


日付をキャストする機能では、週、月、年の最後の稼働日の計算は、カレンダーに関係なく行われます。 グローバルカレンダー用。 休日を使用してローカルカレンダーにバインドする必要がある場合は、負の値で 'workday'オフセットを使用することをお勧めします。


この資料が開発に役立つことを願っています。


©Aborche 2016
アボルシュ



Source: https://habr.com/ru/post/J318022/

More articles:


All Articles