1. /
  2. SQL

SQL

SQL文で「日付の加算・減算」をよく使用します。
カレンダー上の計算であったり月末・月初の取得など。
使用用途は様々ですが、構文自体、非常に簡単ですので是非覚えて使ってみてください。

【構文】（日付の加減算）
DATEADD(日付要素, 日付, 数値)
※日付要素
年：year、四半期：quarter、月：month、年始からの通算日：dayofyear、日：day、週：week、
曜日：weekday、時間：hour、分：minute、秒：second、ミリ秒：millisecond

【2019/01/1の1か月後を求める式】
SELECT DATEADD(month, 1, ’19-01-01′)
結果　’2019-02-01 00:00:00.000′

【2019/01/1の1か月前を求める式】
SELECT DATEADD(month, -1, ’19-01-01′)
結果　’2018-12-01 00:00:00.000′

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

【構文】（日付の差）
DATEDIFF(日付要素, DAY1, DAY2)
※日付要素は、DATEADDと同じ

SELECT DATEDIFF(day, DAY1, DAY2)

【SQL Server】日付の加算・減算

SQL文で「日付の加算・減算」をよく使用します。
カレンダー上の計算であったり月末・月初の取得など。
使用用途は様々ですが、構文自体、非常に簡単ですので是非覚えて使ってみてください。

【構文】（日付の加減算）
DATEADD(日付要素, 日付, 数値)
※日付要素
年：year、四半期：quarter、月：month、年始からの通算日：dayofyear、日：day、週：week、
曜日：weekday、時間：hour、分：minute、秒：second、ミリ秒：millisecond

【2019/01/1の1か月後を求める式】
SELECT DATEADD(month, 1, ’19-01-01′)
結果　’2019-02-01 00:00:00.000′

【2019/01/1の1か月前を求める式】
SELECT DATEADD(month, -1, ’19-01-01′)
結果　’2018-12-01 00:00:00.000′

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

【構文】（日付の差）
DATEDIFF(日付要素, DAY1, DAY2)
※日付要素は、DATEADDと同じ

SELECT DATEDIFF(day, DAY1, DAY2)

【SQL Server】 数値の切り捨て、四捨五入

SQL Serverでは、ROUND関数を使って数値の丸め処理を行います。

【書式】
SELECT ROUND(数値、桁数、オプション)
桁数・・・小数点以下の有効桁数を指定（1の位で丸める場合は0を指定）
オプション・・・0は四捨五入、それ以外の数値は切り捨て扱い

数値123.456を使用するケース例

① 数値123.456を小数点第3位で四捨五入する

SELECT ROUND(123.456, 2, 0)
結果 ＝ 123.460

② 数値123.456を小数点第3位で切り捨てする

SELECT ROUND(123.456, 2, 1)
結果 ＝ 123.450

【SQL Server】 Convertを使った日付フォーマット

プログラミングでSQLを扱っていると頻繁に日付の出力形式をフォーマット変換することが

多くないでしょうか。

SQL Server 限定にはなりますが、SQL Server で日付フォーマットをする際の変換形式を以下に

ひと通りまとめておきたいと思います。

 

Declare @Now Datetime = GETDATE()

① mm/dd/yyyy (形式例：12/01/2018)
　　SELECT CONVERT(NVARCHAR, @Now, 101)

② mm/dd/yy(形式例：12/01/18)
　　SELECT CONVERT(NVARCHAR, @Now, 1)

③ yyyy/mm/dd (形式例：2018/12/01)
　　SELECT CONVERT(NVARCHAR, @Now, 111)

④ yy/mm/dd (形式例：18/12/01)
　　SELECT CONVERT(NVARCHAR, @Now, 11)

⑤ dd/mm/yyyy (形式例：01/12/2018)
　　SELECT CONVERT(NVARCHAR, @Now, 103)

⑥ dd/mm/yy (形式例：01/12/18)
　　SELECT CONVERT(NVARCHAR, @Now, 3)

⑦ yyyymmdd (形式例：20181201)
　　SELECT CONVERT(NVARCHAR, @Now, 112)

⑧ yymmdd (形式例：181201)
　　SELECT CONVERT(NVARCHAR, @Now, 12)

 ⑨ HH:mi:ss (形式例：23:59:59)
　　SELECT CONVERT(NVARCHAR, @Now, 108)

 ⑩ HH:mi:ss:mmm (形式例：23:59:59:123)
　　SELECT CONVERT(NVARCHAR, @Now, 114)

【SQL Server】WHERE EXISTSの使い方

よくwhere existsの意味を勘違いしている方いるのでここでちょっとまとめて記事にしておきます。

例えば、以下のようなexistを使用したクエリがあったとします。

SELECT * FROM TBL_A
WHERE EXISTS (
   SELECT * FROM TBL_B
   WHERE col1 = ‘hoge’
)

一般的なEXISTSを使用したサブクエリを条件にしたものですが、EXISTS内のサブクエリ内の結果が「真」であれば、SELECT * FROM TBL_Aの結果を返す意味になります。
仮にサブクエリ内の結果が「偽」であればSELECT * FROM TBL_Aの結果はTBL_A内のレコード件数の有無に限らず、0件になります。

では、以下のクエリの場合はどうでしょうか？

SELECT * FROM TBL_A
WHERE EXISTS (
   SELECT *  FROM TBL_B
   WHERE col1 = ‘hoge’
)
AND col_a = ‘foo’

この場合、EXISTS内のサブクエリの外条件に対してEXISTS内のサブクエリで存在チェックを返すことに注意です。

EXISTS使用は速度改善に繋がるのか？

よく、速度改善としてEXISTSを使用すると言われていますが、結論から言うと「NO」です。
むしろ遅くなります。

参照するテーブルのレコード数にもよりますが、数万件、数100万件以上のレコードをもつような大きなテーブルを扱う場合は、速度遅延も顕著になってきます。

例えば以下クエリの例で考えてみましょう。
※以下の２つのテーブルを使用するものとします。
STAFF_MST・・・社員マスタ
KINTAI ・・・勤怠テーブル（過去の出勤データ）

SELECT * FROM STAFF_MST As S
WHERE EXISTS(
     SELECT * FROM  KINTAI As K
     S.STAFF_CD = K.STAFF_CD
　WHERE K.DTE >= ‘2017-04-01’
)

一見、見やすさという観点できれいにまとまったクエリになっていますが、実際はどうでしょうか？

極端な話、上記クエリの場合では最大STAFF_MSTのレコード数 ☓ KINTAIテーブルのレコード数分の比較チェック処理が走る構造になっています。比較するお互いのテーブル内レコード数が多くなれば多くなるほど、処理能力は重くなっていきます。正直、速度改善を目的としてEXISTSを利用するメリットはあまりありません。

では、EXISTSを使用せずに速度改善する方法は他にあるのか？

よく、目にするLEFT JOIN もしくはINNER JOIN の方が遥かに優秀です。
見た目は複雑になりますが、速度という観点では実はEXISTSよりも早いです。

上記クエリをINNER JOIN に置き換えた場合

SELECT * FROM STAFF_MST As S
INNER JOIN (
     SELECT * FROM KINTAI As K
     WHERE K.DTE >= ‘2017-04-01’
} ON S.STAFF_CD = K.STAFF_CD

理由はわかりますでしょうか？

まず、上記のクエリの場合ではINNER JOIN内の処理が優先されます。
つまり、KINTAIテーブルの絞り込みが先に行われた上でSTAFF_MSTとの結合が行われることになります。EXISTSのサブクエリを使用するより、INNER JOIN で結合して比較する方が比較対象件数を減らすことで速度の改善が見込めるというわけです。

当然ながら、その威力を体感するにはレコード件数の多いテーブルを相手にする必要がありますが、日頃から、EXISTSで書けるものはINNER JOIN やLEFT JOIN に置き換えられるという事実、速度改善も見込めるという事実、この２つを覚えておいても損はないと思います。