こんにちは、例の新人です。
今回は、SQLの外部参照制約の削除の仕方について学習したことを記録します。
まずは、外部参照制約がどういうものなのかを記述します。
外部参照制約は、ある表のある列の値は、別の表のある列のある列の値と必ず一致しなければならない時に使います。例えば、５０メートル自由形参加選手の選手名は、水泳大会参加選手の選手名と一致しなければいけません。
そういった時に、以下のような構文で外部参照制約を追加できます。
ALTER TABLE 5０メートル自由形参加選手 ADD FOREIGN KEY（選手名） REFERENCES 水泳大会参加選手（選手名）；
こうすることで、5０メートル自由形参加選手の選手名は水泳大会参加選手の選手名と一致するものでなければいけない制約をかけられます。例えば5０メートル自由形参加選手にINSERT文で「追加ためす」さんを追加しようとしても、水泳大会参加選手の選手名には「追加ためす」さんの名前はありませんので、このデータ更新は無効となります。
この制約を削除するためには、まず制約の名前を知る必要があります。
SQLサーバがメタデータ（あるデータに付随するデータ）取得のために提供する方法の一つである情報スキーマビューに、TABLE_CONSTRAINTSビューがあります。これは、現在のユーザが所有するテーブルに属するすべての制約があります。これに、参照のための標準インターフェイスであるINFORMATION_SCHEMAを加え、以下の構文で外部参照制約の情報を表示できます。
SELECT ＊ FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT＿TYPE ＝ ’FOREIGN KEY’ AND TABLE_NAME ＝ ’5０メートル自由形参加選手’；
ここから5０メートル自由形参加選手の外部参照制約の情報を表示できます。そして、「CONSTRAINT_NAME」が制約の名前になります。
そして、削除するには以下のような構文を用います。
ALTER TABLE 5０メートル自由形参加選手 DROP CONSTRAINT 「制約の名前」；
以上が外部参照制約を削除する方法です。
2017年3月17日金曜日
2017年3月13日月曜日
PostgreSQLのEXTRACT関数の進展
こんにちは、いつもの新人です。
「EXTRACT（EPOCH FROM [時刻]）」の構文でマイクロ秒を含んだUNIXTIMEを取得するには、１度マイクロ秒と切り離してみる必要があるようです。
そのために使うのがDATE_TRUNC関数です。これは、日付の値をどの精度まで表示するのかを選択し、選択したもの以下の情報は０（月、日の場合は１）として出力する関数です。例えば、hour(時)を選択すると何年何月何日何時までを正確に表示し、後の情報は０を出力します。
SELECT EXTRACT（EPOCH FROM DATE_TRUNC（'second',[マイクロ秒を含んだTIMESTAMP]））,TO_CHAR([マイクロ秒を含んだTIMESTAMP],'US') FROM
[テーブル];
上のようなSELECT文の場合、マイクロ秒を含まない日時の情報をUNIXTIMEにしたものと、マイクロ秒のみの値が出力されます。
出力されたその２つを小数点で繋げるようにパラメータを設定すれば、TIMESTAMPと同じマイクロ秒情報のUNIXTIMEを取得できます。
「EXTRACT（EPOCH FROM [時刻]）」の構文でマイクロ秒を含んだUNIXTIMEを取得するには、１度マイクロ秒と切り離してみる必要があるようです。
そのために使うのがDATE_TRUNC関数です。これは、日付の値をどの精度まで表示するのかを選択し、選択したもの以下の情報は０（月、日の場合は１）として出力する関数です。例えば、hour(時)を選択すると何年何月何日何時までを正確に表示し、後の情報は０を出力します。
SELECT EXTRACT（EPOCH FROM DATE_TRUNC（'second',[マイクロ秒を含んだTIMESTAMP]））,TO_CHAR([マイクロ秒を含んだTIMESTAMP],'US') FROM
[テーブル];
上のようなSELECT文の場合、マイクロ秒を含まない日時の情報をUNIXTIMEにしたものと、マイクロ秒のみの値が出力されます。
出力されたその２つを小数点で繋げるようにパラメータを設定すれば、TIMESTAMPと同じマイクロ秒情報のUNIXTIMEを取得できます。
2017年3月10日金曜日
PostgreSQLのEXTRACT関数に苦戦中
こんにちは、例の新人です。
PostgreSQLのEXTRACT関数の設定で苦戦しています。
PostgreSQLでは、「EXTRACT（EPOCH FROM [時刻]）」の構文で1970-01-01 00:00:00からの秒数、つまりUNIXTIMEを取得できるのですが、この設定の場合どうやらマイクロ秒が正確に表示できないようです。
例えば、次のような構文で「2017-03-10 16:55:26.114877」というマイクロ秒を含んだ時刻をUNIXTIME化してみます。
SELECT EXTRACT(EPOCH FROM TO_TIMESTAMP('2017-03-10 16:55:26.114877','YYYY-MM-DD HH24:MI:SS.US'));
結果は以下のようになります。
date_part
------------------
1489132526.11488
マイクロ秒部分「114877」の小数点以下第６位が四捨五入されてしまいました。これを、以下の構文からTIMESTAMPに直してみます。
SELECT TO_TIMESTAMP(1489132526.11488) AT TIME ZONE 'UTC';
結果は以下の通りになります。
timezone
---------------------------
2017-03-10 07:55:26.11488
PostgreSQLのEXTRACT関数の設定で苦戦しています。
PostgreSQLでは、「EXTRACT（EPOCH FROM [時刻]）」の構文で1970-01-01 00:00:00からの秒数、つまりUNIXTIMEを取得できるのですが、この設定の場合どうやらマイクロ秒が正確に表示できないようです。
例えば、次のような構文で「2017-03-10 16:55:26.114877」というマイクロ秒を含んだ時刻をUNIXTIME化してみます。
SELECT EXTRACT(EPOCH FROM TO_TIMESTAMP('2017-03-10 16:55:26.114877','YYYY-MM-DD HH24:MI:SS.US'));
結果は以下のようになります。
date_part
------------------
1489132526.11488
マイクロ秒部分「114877」の小数点以下第６位が四捨五入されてしまいました。これを、以下の構文からTIMESTAMPに直してみます。
SELECT TO_TIMESTAMP(1489132526.11488) AT TIME ZONE 'UTC';
結果は以下の通りになります。
timezone
---------------------------
2017-03-10 07:55:26.11488
元々の時間より、0.000003秒遅くなってしまいました。最初の時刻とUNIXTIMEにしたものを戻した時刻を同じにしたいため、このままではいけません。
下のようなTIMESTAMPをUNIXTIMEに直した後またTIMESTAMPに直す動作をひとまとめにした構文では、元の時刻とUNIXTIMEをTIMESTAMPに復元した時刻が一応は一致します。しかし、この設定は実際の作業では使えません。
SELECT TO_TIMESTAMP(EXTRACT(EPOCH FROM TO_TIMESTAMP('2017-03-10 16:55:26.114877','YYYY-MM-DD HH24:MI:SS.US') AT TIME ZONE 'UTC')) ;
結果は以下の通りになります。
to_timestamp
-------------------------------
2017-03-10 16:55:26.114877+09
-------------------------------
2017-03-10 16:55:26.114877+09
同一の時刻としてUNIXTIMEを取得する方法を模索中です。
2017年3月9日木曜日
UNIXTIMEを扱う上でのPHPとPostgreSQLのタイムゾーン情報指定
どうも、こんにちは。
今回は、UNIXTIMEを扱う上でのPHPとPostgreSQLのタイムゾーン情報の指定について学習したことを記述します。
タイムゾーンは同じ標準時を利用する地域や区分のことで、原子時計を元に修正定義されたUTCが現在の世界標準時となっています。有名なイギリスのグリニッジ天文台を標準とするGMTは地球の潮汐作用により自転速度が劣化することから、世界標準時にはなっていません。
日本はUTCにくらべ９時間ほど遅いため、UNIXTIMEのようなUTCを基準にした時間表現では本来の時間より９時間ずれた情報が取得されてしまうことがあります。
これを防ぐためには、タイムゾーンの情報を任意のプログラムに指定できる必要があります。
PHPにおいてそれを行うのが、date_default_timezone_set関数です。
この関数を使用することにより、例えば次のようなプログラムを実行することができます。
echo date('Y-m-d G:i:s',time());
デフォルトのタイムゾーンがUTCになっている場合、日本で上記のプログラムを実行すると、現在時刻より９時間前の時間を取得してしまいます。
そこで、date_default_timezone_set関数でタイムゾーンを日本のものにしてみます。
date_default_timezone_set(Asia/Tokyo);
echo date('Y-m-d G:i:s',time());
こうすると、現在時刻を取得できます。
関数のかっこ内のパラメータには、タイムゾーンの標準となる場所を指定します。これは、PHPマニュアルサイトの「サポートされるタイムゾーンのリスト」から確認できます。
PostgreSQLの場合には、AT TIME ZONE構文を利用します。
SELECT TO_TIMESTAMP(1489066731);
タイムゾーンの設定が日本になっている場合、上記のSELECT文を実行すると以下のようになります。
＋９というのは、UTCより９時間プラスされている、ということです。現在時刻のUNIXTIME値が1489066731の場合、９時間遅い時間が取得されてしまっていることになります。
AT TIME ZONEで、タイムゾーンをUTCに変更します。
SELECT TO_TIMESTAMP(1489066731) AT TIME ZONE('UTC');
これを実行すると、以下のようになります。
先ほどよりも、９時間早い時間を取得できました。
このように、タイムゾーン情報を指定することで、UNIXTIME時間などの取り扱いがスムーズになります。
今回は、UNIXTIMEを扱う上でのPHPとPostgreSQLのタイムゾーン情報の指定について学習したことを記述します。
タイムゾーンは同じ標準時を利用する地域や区分のことで、原子時計を元に修正定義されたUTCが現在の世界標準時となっています。有名なイギリスのグリニッジ天文台を標準とするGMTは地球の潮汐作用により自転速度が劣化することから、世界標準時にはなっていません。
日本はUTCにくらべ９時間ほど遅いため、UNIXTIMEのようなUTCを基準にした時間表現では本来の時間より９時間ずれた情報が取得されてしまうことがあります。
これを防ぐためには、タイムゾーンの情報を任意のプログラムに指定できる必要があります。
PHPにおいてそれを行うのが、date_default_timezone_set関数です。
この関数を使用することにより、例えば次のようなプログラムを実行することができます。
echo date('Y-m-d G:i:s',time());
デフォルトのタイムゾーンがUTCになっている場合、日本で上記のプログラムを実行すると、現在時刻より９時間前の時間を取得してしまいます。
そこで、date_default_timezone_set関数でタイムゾーンを日本のものにしてみます。
date_default_timezone_set(Asia/Tokyo);
echo date('Y-m-d G:i:s',time());
こうすると、現在時刻を取得できます。
関数のかっこ内のパラメータには、タイムゾーンの標準となる場所を指定します。これは、PHPマニュアルサイトの「サポートされるタイムゾーンのリスト」から確認できます。
PostgreSQLの場合には、AT TIME ZONE構文を利用します。
SELECT TO_TIMESTAMP(1489066731);
タイムゾーンの設定が日本になっている場合、上記のSELECT文を実行すると以下のようになります。
to_timestamp
------------------------
2017-03-09 22:38:51+09
＋９というのは、UTCより９時間プラスされている、ということです。現在時刻のUNIXTIME値が1489066731の場合、９時間遅い時間が取得されてしまっていることになります。
AT TIME ZONEで、タイムゾーンをUTCに変更します。
SELECT TO_TIMESTAMP(1489066731) AT TIME ZONE('UTC');
これを実行すると、以下のようになります。
timezone
---------------------
2017-03-09 13:38:51
先ほどよりも、９時間早い時間を取得できました。
このように、タイムゾーン情報を指定することで、UNIXTIME時間などの取り扱いがスムーズになります。
2017年3月8日水曜日
PSQLでのマイクロ秒の扱い方
お疲れ様です、例の新人です。
今回は、PSQLでのマイクロ秒の扱い方について学んだことを記録します。
PSQLにおいてTIMESTAMPの秒の値を小数点以下まで文字列に直す方法をGOOGLE検索してみると、以下の設定で可能であるとわかりました。
TO_CHAR([TIMESTAMPデータ],'US')
USはマイクロ秒を表す日付/時刻型の書式で、小数点以下第６位まで扱えるようです。
この書式を利用すると、以下のような形でPSQLから小数点以下の値を合わせた時刻データの取得などができます。
SELECT TO_CHAR([TIMESTAMPデータ],'HH24:MI:SS.US') FROM [テーブル名];
今回は、PSQLでのマイクロ秒の扱い方について学んだことを記録します。
PSQLにおいてTIMESTAMPの秒の値を小数点以下まで文字列に直す方法をGOOGLE検索してみると、以下の設定で可能であるとわかりました。
TO_CHAR([TIMESTAMPデータ],'US')
USはマイクロ秒を表す日付/時刻型の書式で、小数点以下第６位まで扱えるようです。
この書式を利用すると、以下のような形でPSQLから小数点以下の値を合わせた時刻データの取得などができます。
SELECT TO_CHAR([TIMESTAMPデータ],'HH24:MI:SS.US') FROM [テーブル名];
2017年3月7日火曜日
外部結合のON句による条件付と、グループ内の最小値データの取得について
こんにちは。まるで知識が追いついていない入社そろそろ2年目です。
初めての今回は、SQL文の外部結合におけるON句による条件付についてと、グループ内の最小値データの取得についてです。
まず、SQL文の外部結合におけるON句による条件付について学んだことを記録します。
これまでは、例えば
SELECT A.id, B.name FROM TABLE.A LEFT OUTER JOIN TABLE-B B ON A.id = B.a_id WHERE B.score > 75;
のような設定をしていたのですが、これは結合された結果に対しての条件指定であり、内部結合と同じような動きになってしまうようです。そのため、「B.score > 75」の条件が偽の場合は「TABLE-B」表からの情報がNULL値になるだけでなく、「TABLE.A」表の情報も表示されなくなってしまうようです。
一方で、下のようにON句に書く場合は、「TABLE-B」表の中で条件付を行い、「B.score > 75」の条件が偽の場合は「TABLE-B」表から取得する情報がNULLになるだけでTABLE.A」表の情報は表示できるようです。
SELECT A.id, B.name FROM TABLE.A LEFT OUTER JOIN TABLE-B B ON A.id = B.a_id AND B.score > 75;
次に、グループ内の最小値データの取得について学んだことを記録します。
例えば、次のような表が2つあるとします。
TABLE FRUIT
ID ｜KIND
ーーーーーーーーーーー
１ ｜りんご
ーーーーーーーーーーー
２ ｜みかん
TABLE SALE
ID ｜FRUIT＿ID ｜PRICE
ーーーーーーーーーーーーーーーーーー
１ ｜１ ｜１００
ーーーーーーーーーーーーーーーーーー
２ ｜１ ｜１２０
ーーーーーーーーーーーーーーーーーー
３ ｜２ ｜２５０
ーーーーーーーーーーーーーーーーーー
４ ｜１ ｜１１０
ーーーーーーーーーーーーーーーーーー
５ ｜２ ｜２００
「SALE」表から、りんごとみかんの価格の最小値を行を表示させます。
SELECT ＊ FROM sale WHERE price IN （SELECT MIN（price） FROM sale GROUP BY fruit_id）;
こうすると、以下のように表示できます。
TABLE SALE
ID ｜FRUIT＿ID ｜PRICE
ーーーーーーーーーーーーーーーーーー
１ ｜１ ｜１００
ーーーーーーーーーーーーーーーーーー
５ ｜２ ｜２００
WHERE句から価格をINで「果物のIDとグループ化した価格の最小値を表示するSELECT文」とつなげることにより、果物ごとの価格の最小値の行を取得できました。
これを、「FRUIT」表と上と同様ON句から外部結合してみます。
SELECT F.id F.kind S.price FROM fruit F LEFT OUTER JOIN SALE S ON F.id ＝ S.fruit_id AND S.price IN （SELECT MIN（price） FROM sale GROUP BY fruit_id）;
こうすると、以下のように表示できます。
ID ｜KIND ｜PRICE
ーーーーーーーーーーーーー
１ ｜りんご ｜１００
ーーーーーーーーーーーーー
２ ｜みかん ｜２００
このように果物の種類ごとに最小価格がわかるようになります。
連結する相手側の最大、最小値を取得するには、このようにグループ化したSELECT文を利用するようです。
初めての今回は、SQL文の外部結合におけるON句による条件付についてと、グループ内の最小値データの取得についてです。
まず、SQL文の外部結合におけるON句による条件付について学んだことを記録します。
これまでは、例えば
SELECT A.id, B.name FROM TABLE.A LEFT OUTER JOIN TABLE-B B ON A.id = B.a_id WHERE B.score > 75;
のような設定をしていたのですが、これは結合された結果に対しての条件指定であり、内部結合と同じような動きになってしまうようです。そのため、「B.score > 75」の条件が偽の場合は「TABLE-B」表からの情報がNULL値になるだけでなく、「TABLE.A」表の情報も表示されなくなってしまうようです。
一方で、下のようにON句に書く場合は、「TABLE-B」表の中で条件付を行い、「B.score > 75」の条件が偽の場合は「TABLE-B」表から取得する情報がNULLになるだけでTABLE.A」表の情報は表示できるようです。
SELECT A.id, B.name FROM TABLE.A LEFT OUTER JOIN TABLE-B B ON A.id = B.a_id AND B.score > 75;
次に、グループ内の最小値データの取得について学んだことを記録します。
例えば、次のような表が2つあるとします。
TABLE FRUIT
ID ｜KIND
ーーーーーーーーーーー
１ ｜りんご
ーーーーーーーーーーー
２ ｜みかん
TABLE SALE
ID ｜FRUIT＿ID ｜PRICE
ーーーーーーーーーーーーーーーーーー
１ ｜１ ｜１００
ーーーーーーーーーーーーーーーーーー
２ ｜１ ｜１２０
ーーーーーーーーーーーーーーーーーー
３ ｜２ ｜２５０
ーーーーーーーーーーーーーーーーーー
４ ｜１ ｜１１０
ーーーーーーーーーーーーーーーーーー
５ ｜２ ｜２００
「SALE」表から、りんごとみかんの価格の最小値を行を表示させます。
SELECT ＊ FROM sale WHERE price IN （SELECT MIN（price） FROM sale GROUP BY fruit_id）;
こうすると、以下のように表示できます。
TABLE SALE
ID ｜FRUIT＿ID ｜PRICE
ーーーーーーーーーーーーーーーーーー
１ ｜１ ｜１００
ーーーーーーーーーーーーーーーーーー
５ ｜２ ｜２００
WHERE句から価格をINで「果物のIDとグループ化した価格の最小値を表示するSELECT文」とつなげることにより、果物ごとの価格の最小値の行を取得できました。
これを、「FRUIT」表と上と同様ON句から外部結合してみます。
SELECT F.id F.kind S.price FROM fruit F LEFT OUTER JOIN SALE S ON F.id ＝ S.fruit_id AND S.price IN （SELECT MIN（price） FROM sale GROUP BY fruit_id）;
こうすると、以下のように表示できます。
ID ｜KIND ｜PRICE
ーーーーーーーーーーーーー
１ ｜りんご ｜１００
ーーーーーーーーーーーーー
２ ｜みかん ｜２００
このように果物の種類ごとに最小価格がわかるようになります。
連結する相手側の最大、最小値を取得するには、このようにグループ化したSELECT文を利用するようです。
登録:
投稿 (Atom)