SQLiteチュートリアル(IV)。組み込み関数

I. 集計機能。

    SQLite でサポートされている集約関数は、他の多くのリレーショナルデータベースでもサポートされているので、ここではこれ以上例を挙げずに、各集約関数の簡単な説明だけにします。また、すべての集約関数において、distinguish キーワードを関数の引数フィールドのプレフィックスとして使用することで、count(distinguish x) のような計算を行う際にすべての重複するフィールド値が無視されることに注意することが重要です。

<テーブル 機能一覧 説明 avg(x) この関数は、同じ内部参照集合に含まれる数値フィールドの平均値を返します。HELLO' のような数値に変換できない String や BLOB 型のフィールド値については、SQLite はそれらを 0 として扱います。 avg 関数の結果は常に浮動小数点です。唯一の例外は全てのフィールド値が NULL の場合で、その場合この関数の結果も NULL となります。 カウント(x|*)　　 count(x) 関数は、同一グループ内の x フィールドの値が NULL でない行数を返します。 count(*) 関数は、同一グループ内のデータ行数を返します。 group_concat(x[,y]) この関数は、NULL でないすべての x 値を連結した文字列を返します。この関数の y 引数は、各 x 値の間の区切り文字として使用されます。この引数を無視して呼び出された場合、連結の際にはデフォルトの区切り文字 "," が使用されます。そして、個々の文字列間の連結の順序は不確定である。　　 max(x) この関数は、同一グループ内の x フィールドの最大値を返すか、またはフィールドの値がすべて NULL の場合は NULL を返します。 min(x) この関数は、同じグループ内の x フィールドの最小値を返します。フィールドの値がすべて NULL の場合は NULL を返します。 sum(x) この関数は，同じグループ内の x フィールド値の合計を返します．フィールド値が NULL の場合，この関数も NULL を返します．x フィールド値がすべて整数または NULL の場合は整数値，それ以外の場合は浮動小数点値を返します．最後に，すべてのデータ値が整数の場合，結果が上限を超えると "整数オーバーフロー" 例外が投げられることに注意しなければなりません． 合計(x) この関数は標準SQLの一部ではありません。その機能は基本的にsumと同じですが、計算結果はsumよりも合理的です。例えば、sumとは異なり、全てのフィールドがNULLの場合は0.0を返しますし、常に浮動小数点値を返します。この関数は決して例外を発生させません。

II. 中核的な機能

    以下の関数は、すべてSQLiteがデフォルトで提供する組み込み関数で、以下のリストで宣言され、説明されています。

<テーブル 機能一覧 説明 abs(X) XがNULLの場合はNULLを、Xが数値に変換できない文字列の場合は0を返し、Xの値がIntegerの上限を超えた場合は例外quot;Integer Overflow"をスローします。 変更() この関数は、最近実行されたINSERT、UPDATE、DELETE文によって影響を受けたデータ行の数を返します。C/C++の関数sqlite3_changes()を実行しても、同じ結果を得ることができます。 coalesce(X,Y,...)　　 関数の最初の非NULL引数、またはすべての引数がNULLの場合はNULLを返します。 ifnull(X,Y) この関数は、2つの引数を持つ coalesce() 関数と同じです。つまり、最初の関数の引数がNULLでない場合、または両方がNULLの場合、NULLを返します。 長さ(X)　　 引数Xが文字列の場合はその文字数、引数Xが値の場合はその文字列表現の長さ、引数XがNULLの場合はNULLを返します。 ローワー(X) 関数の引数 X を小文字で返します。デフォルトでは、この関数は ASCII 文字にのみ適用されます。 ltrim(X[,Y])　　 オプションの引数Yがない場合、引数Xの左側にあるすべての空白文字を削除する。オプションのYがある場合、Xの左側にありYに現れるすべての文字を削除する。最後に、削除した文字列を返す。 max(X,Y,...) 関数の引数の最大値を返し、引数のいずれかがNの場合はNULLを返します。replace(X,Y,Z)　　 関数の引数 X のすべての部分文字列 Y を文字列型の文字列 Z に置き換え、最後に元の文字列 X を変更せずに、置き換えられた文字列を返します。 ラウンド(X[,Y]) 数値引数XをYスケールで丸めた値を返す。引数Yが存在しない場合、デフォルトの引数値は0である。　　 rtrim(X[,Y])　　 オプションの引数Yがない場合、引数Xの右側にあるすべての空白文字を削除する。オプションのYがある場合、Xの右側にありYに現れるすべての文字を削除する。最後に、削除された文字列を返す。 サブスト(X,Y[,Z]) 引数Zを無視した場合、Y文字目以降のすべての文字が取り出されます。Z の値が負の場合、Y 番目の位置から左へ abs(Z) 文字を取り込む。Y の値が負の場合、X 文字列の終端から最初の abs(Y) 位置の始端まで数えます。　 合計変化量 この関数は、接続が開かれた時点から INSERT、UPDATE、DELETE 文の影響を受けた行の総数を返します。C/C++インターフェイス関数sqlite3_total_changes()でも同じ結果を得ることができます。 トリム(x[,y]) オプションの引数Yがない場合、引数Xの両側にあるすべての空白文字を削除する。オプションの引数Yがある場合、Xの両側にある、Yに現れるすべての文字を削除する。最後に、削除した文字列を返す。  upper(X) 関数の引数 X を大文字で返します。デフォルトでは、この関数は ASCII 文字にのみ適用されます。 タイプオブ(X) Integer, text, real, null"など、関数の引数のデータ型を文字列で返します。

III. 日付と時刻の機能

    SQLiteは、以下のような4つの主な日付と時刻に関連する関数をサポートしています。
    1). 日付(タイムストリング、モディファイア、モディファイア、...)
    2). 時刻(timestring, modifier, modifier, ...)
    3). datetime(timestring, modifier, modifier, ...)
    4). strftime(format, timestring, modifier, modifier, ...)
    上記の4つの関数はすべて引数として時刻の文字列を受け取り、その後に0個以上の修飾子が続きます。strftime()は、Cランタイムライブラリの同名の関数と同じものである。他の3つの関数については、日付関数のデフォルトの書式は "YYYY-MM-DD"、時刻関数のデフォルトの書式は "HH:MM:SS"、日時関数のデフォルトの書式は "YYYY-MM-DD HH:MM:SS" となっています。   
   1. strftime関数の書式情報。

<テーブル フォーマット 説明 %d 月の日。00 %f 分数秒。SS.SSS %H 時間 00-24 %j の日です。001-366 %J ユリウス日数 %m 月です。01-12 %M 分：00～59 %s 1970-01-01からの秒数 %S 秒です。00-59 %w 曜日 0-6 日曜日=0 %W 年の週。00-53 %Y 年です。0000-9999 %% %

 さらに、残りの3つの時間関数は、すべてstrftimeで表現できることに注意する必要がある。

    2. 時刻の文字列のフォーマット。

    以下のリストを参照してください。
    1). YYYY-MM-DD
    2). yyyy-mm-dd hh:mm
    3). yyyy-mm-dd hh:mm:ss
    4). yyyy-mm-dd hh:mm:ss.sss
    5). HH:MM
    6). HH:MM:SS
    7). HH:MM:SS.SSS
    8). 現在
    5) から 7) は時刻の部分を含んでいるだけなので、SQLite は 2000-01-01 を日付と見なします。8) は現在の時刻を表します。
   3. モディファイア

    以下のリストをご覧ください。

    1). NNN日
    2). NNN時間
    3). NNN分
    4). NNNN.NNNN秒
    5). NNN月
    6). NNN年
    7). 月初
    8). 年頭の挨拶
    9). 一日の始まり
    10).weekday N    

    1)～6)は、単純に指定された数の日付または時刻の値を加算または減算し、NNNの値が負の場合は減算、それ以外は加算します。7)～9)は、時刻文字列の指定された日付部分を、現在の月、年、または日の始まりに設定する。10) は日付を次の週 N (日曜日は 0) に進めます。 注意: 修飾子の順番は非常に重要です。SQLite は左から右の順番で修飾子を実行します SQLite は、左から右の順番で修飾子を実行します。
    4. 例