untitled .engineer

技術系のブログ（仮）

「プログラマのためのSQL第4版」のサンプルコードをMySQLで動くようにしてみた(38.4 ユリウス通日)

MySQL プログラマのためのSQL

目次

f:id:dupont_kedama:20190228135448p:plain

本エントリの概要

  • 「プログラマのためのSQL第4版」の読書会に参加させてもらってるのですが、たまには予習をしようと思い付きでやってみました。
  • 突発的なやつなので継続はしないつもりです。
  • 本エントリでは動かすことが目的なので内容の理解は後回しです。

第38章 38.4 ユリウス通日 P728

元のSQL

CREATE FUNCTION Julianize
(greg_day IN INTEGER, greg_month IN INTEGER, greg_year IN INTEGER)
RETURN INTEGER
IS
    century INTEGER;
    yearincentury INTEGER;

    tmp_month INTEGER;
    tmp_year INTEGER;
BEGIN
    tmp_month := greg_month;
    tmp_year := greg_year;

    IF (tmp_month > 2)
    THEN tmp_month := tmp_month - 3;
    ELSE tmp_month := tmp_month + 9;
         tmp_year := tmp_year - 1;
    END IF;

    century := tmp_year/100;
    yearincentury := tmp_year - 100 * century;

    RETURN ((146097 * century)/4
    + (1461 * yearincentury)/4
    + (153 * tmp_month + 2)/5 + greg_day + 1721119);
END;

MySQLで動くように変換したSQL

CREATE FUNCTION Julianize
(greg_day INTEGER, greg_month INTEGER, greg_year INTEGER)
RETURNS INTEGER DETERMINISTIC
BEGIN
    DECLARE century INTEGER;
    DECLARE yearincentury INTEGER;
    DECLARE tmp_month INTEGER;
    DECLARE tmp_year INTEGER;
    
    SET tmp_month = greg_month;
    SET tmp_year = greg_year;

    IF (tmp_month > 2)
    THEN SET tmp_month = tmp_month - 3;
    ELSE SET tmp_month = tmp_month + 9;
         SET tmp_year = tmp_year - 1;
    END IF;

    SET century = tmp_year/100;
    SET yearincentury = tmp_year - 100 * century;

    RETURN ((146097 * century)/4
    + (1461 * yearincentury)/4
    + (153 * tmp_month + 2)/5 + greg_day + 1721119);
END;

ポイント

  • 関数のパラメーター greg_day IN INTEGER の部分を greg_day INTEGER のように書きなおします。
  • RETURN INTEGER としているところを RETURNS INTEGER DETERMINISTIC に書き換えます。
  • IS以降で記載している変数宣言をBEGIN以下に移動します。
  • 変数宣言はそれぞれ DECLARE をつけて DECLARE century INTEGER; のようにます。
  • 変数への値の代入は tmp_month := greg_month; から SET tmp_month = greg_month; のように書きなおします。

検証

なにが正しいのか内容を理解していないのでわかりません。
とりあえずWikipediaを見たら

例えば、協定世界時（UTC）での2019年2月24日の JDN は、2458539である。

と書いてあるので 2019/02/24 を試してみます。

mysql> SELECT Julianize(24,2,2019);
+----------------------+
| Julianize(24,2,2019) |
+----------------------+
|              2458540 |
+----------------------+
1 row in set (0.00 sec)

あれ1違います。

結果

  • 動いたけどWikipediaの説明とは1ずれてます。
  • 時間の都合で解析は「また今度」にします。

はい次！

第38章 38.4 ユリウス通日 P729

元のSQL

CREATE FUNCTION JulDate (julian IN INTEGER)
RETURN INTEGER
IS
    z INTEGER;
    r INTEGER;
    g INTEGER;
    a INTEGER;
    b INTEGER;
    c INTEGER;
    greg_year INTEGER;
    greg_month INTEGER;
    greg_day INTEGER;
BEGIN
    z := FLOOR(julian - 1721118.5);
    r := julian - 1721118.5 - z;
    g := z - 0.25;
    a := FLOOR(g/36524.25);
    b := a - FLOOR(a/4.0);
    greg_year := FLOOR((b + g)/365.25);
    c := b + z - FLOOR(365.25 * greg_year);
    greg_month := TRUNC((5 * c + 456)/153);
    greg_day := c - TRUNC((153 * greg_month - 457)/5) + r;

    IF greg_month > 12
    THEN greg_year := greg_year + 1;
         greg_month := greg_month - 12;
    END IF;

    RETURN (greg_year * 1000) + (greg_month * 100) + greg_day;
END;

MySQLで動くように変換したSQL

CREATE FUNCTION JulDate (julian INTEGER)
RETURNS INTEGER DETERMINISTIC
BEGIN
    DECLARE z INTEGER;
    DECLARE r INTEGER;
    DECLARE g INTEGER;
    DECLARE a INTEGER;
    DECLARE b INTEGER;
    DECLARE c INTEGER;
    DECLARE greg_year INTEGER;
    DECLARE greg_month INTEGER;
    DECLARE greg_day INTEGER;
    
    SET z = FLOOR(julian - 1721118.5);
    SET r = julian - 1721118.5 - z;
    SET g = z - 0.25;
    SET a = FLOOR(g/36524.25);
    SET b = a - FLOOR(a/4.0);
    SET greg_year = FLOOR((b + g)/365.25);
    SET c = b + z - FLOOR(365.25 * greg_year);
    SET greg_month = TRUNCATE((5 * c + 456)/153,0);
    SET greg_day = c - TRUNCATE((153 * greg_month - 457)/5,0) + r;

    IF greg_month > 12
    THEN SET greg_year = greg_year + 1;
         SET greg_month = greg_month - 12;
    END IF;

    RETURN (greg_year * 1000) + (greg_month * 100) + greg_day;
END;

ポイント

書き換えるポイントは上のSQLと同じ。

検証

同じくWikipediaを参考にすると

例えば、協定世界時（UTC）での2019年2月24日の JDN は、2458539である。

と書いてあるので 2458539 を試してみます。

mysql> SELECT JulDate(2458539);
+------------------+
| JulDate(2458539) |
+------------------+
|          2019225 |
+------------------+
1 row in set (0.00 sec)

区切りないけど2019/2/25？これって正しいの？
試しにその56日前(2018/12/31を想定)を見てみましょう。

mysql> SELECT JulDate(2458539-56);
+------------------+
| JulDate(2458483) |
+------------------+
|          2019231 |
+------------------+
1 row in set (0.00 sec)

年の桁が一つ少ないですね。

RETURN (greg_year * 1000) + (greg_month * 100) + greg_day;
の部分を
RETURN (greg_year * 10000) + (greg_month * 100) + greg_day;
にすれば

mysql> SELECT JulDate(2458539-56);
+------------------+
| JulDate(2458483) |
+------------------+
|         20181231 |
+------------------+
1 row in set (0.00 sec)

となります。
これはMySQLだからなのかほかのRDBMSも同様なのかは検証していません。
正誤表にはありません。

結果

  • 動いたけど恐らく一部誤記があるのとWikipediaの説明とは1日ずれてます。

検証環境

MySQL8.0.12

人物

読書会で名前が出てくるといつも「誰？」みたいな話になるのでこちらもちょっと調べてみました。

著者によると、

１つ目のSQL文を「最初に書いたのはロバート・タンツェンである（ACM,1980）」だそうです。
この人(Robert Tanzen?)をGoogleやACM(acm.org)のサイトで探してみましたが、見つかりませんでした。
少なくとも現在はそれほど有名な方ではない模様。

２つ目のSQL文は「ピーター・マイヤーによる」そうです。
とりあえず検索して出てきたのはこの人。Microsoft MVP Peter Myers
プロです。（この人かどうかの確証はまったくありませんが。）