導入
データベース管理の現場において、アプリケーション側で複雑なデータ加工を行うことはパフォーマンス低下やコードの肥大化を招きます。SQLiteには強力な組み込み関数が豊富に用意されており、SQLだけでデータの集計、文字列操作、型変換を完結させることが可能です。本記事では、実務で頻出するSQLite関数を整理し、効率的なクエリ設計のヒントを紹介します。
基礎知識
SQLiteの関数は、大きく分けて「集計関数」「日付・時刻関数」「コア関数」の3つに分類されます。
集計関数は、複数の行から単一の結果を導き出すために使用し、主にGROUP BY句と組み合わせて使われます。コア関数は、文字列操作や数値計算など、個々のデータに対して柔軟な加工を行うために使用します。これらを適切に組み合わせることで、アプリケーションのロジックを簡素化し、データベースから「欲しい形のデータ」を直接引き出すことができます。
実装/解決策
実務で特によく使うのは、NULL値のハンドリングや文字列の結合、そして計算の集計です。例えば、NULLが入っているカラムを計算に使うと結果もNULLになってしまいますが、ifnull関数を使うことでデフォルト値を設定できます。また、group_concat関数を使えば、関連テーブルのデータを一行にまとめて出力することも可能です。
サンプルプログラム
以下のコードは、SQLiteの各種関数を使用して、データの整形と集計を行う例です。
— 1. NULLを0に置換して合計を算出
SELECT SUM(ifnull(price, 0)) AS total_price FROM sales;
— 2. 文字列のトリムと大文字変換、および部分取得
— ‘ item_name ‘ を ‘ITEM_NAME’ に変換し、頭の4文字を取得
SELECT substr(upper(trim(product_name)), 1, 4) AS short_name FROM products;
— 3. 複数の行の文字列をカンマ区切りで連結
— カテゴリごとに紐づくタグを一括で取得
SELECT category_id, group_concat(tag_name, ‘, ‘) AS tags
FROM product_tags
GROUP BY category_id;
— 4. 最後に挿入したIDを取得（トランザクション終了後に実行）
SELECT last_insert_rowid();
応用・注意点
実務でSQLiteを扱う際、特に注意すべきは「型」の扱いです。SQLiteは動的型付けを採用しているため、typeof関数を使用してデータの型を確認する癖をつけておくと、予期せぬ計算ミスを防げます。
また、日付関数（date, datetimeなど）は非常に強力ですが、SQLiteには「日付型」という概念が存在せず、文字列や数値として保存されます。そのため、日付比較を行う際は、必ず標準的な文字列形式（YYYY-MM-DD）で保存し、strftime関数で適切に変換・抽出を行う設計にしてください。クエリを構築する際は、まず小規模なデータで挙動を確認し、インデックスが効くクエリになっているかEXPLAIN QUERY PLANで確認することをお勧めします。
コメント