Closed10

100日チャレンジ day26 (時系列DB)

llmバックエンド100日チャレンジ
riddle_tecriddle_tec

昨日
https://zenn.dev/gin_nazo/scraps/d25e09ee96e17d

https://blog.framinal.life/entry/2025/04/14/154104

100日チャレンジに感化されたので、アレンジして自分でもやってみます。

やりたいこと

  • 世の中のさまざまなドメインの簡易実装をつくり、バックエンドの実装に慣れる(dbスキーマ設計や、関数の分割、使いやすいインターフェイスの切り方に慣れる
  • 設計力(これはシステムのオーバービューを先に自分で作ってaiに依頼できるようにする
  • 生成aiをつかったバイブコーティングになれる
  • 実際にやったことはzennのスクラップにまとめ、成果はzennのブログにまとめる(アプリ自体の公開は必須ではないかコードはgithubにおく)

できたもの

https://github.com/lirlia/100day_challenge_backend/tree/main/day26_timeseries_db

riddle_tecriddle_tec

SQLiteで時系列DBみたいなことができるらしい

riddle_tecriddle_tec

承知しました。「簡易 時系列データベース on SQLite」でいきましょう！
SQL に精通されているとのことですので、単なる CRUD + α だけではなく、SQLite の機能をもう少し深掘りするような、目新しさを感じられる要素を盛り込んでいきましょう。

特に、SQLite でも比較的新しいバージョンでサポートされたウィンドウ関数 (Window Functions) を活用したり、時系列データ特有の処理（ダウンサンプリングやギャップフィル）を SQL でどう実現するか、といった点に焦点を当てると面白いかもしれません。Prisma を使いつつも、複雑なクエリは prisma.$queryRaw などで生の SQL を書く場面も出てくるでしょう。

実装する機能案（目新しさ重視）:

  1. 基本 API:
    • データ登録 (POST /api/data): key, timestamp (Unix秒), value を登録。
    • 時間範囲データ取得 (GET /api/data): key, start, end を指定して生データを取得。
  2. 集計 API (GET /api/data/aggregated):
    • パラメータ: key, start, end, interval (minute, hour, day), aggregation (avg, max, min, sum, count)。
    • SQLite の strftime や算術演算、GROUP BY、集計関数を活用。
  3. ダウンサンプリング API (GET /api/data/downsampled):
    • パラメータ: key, start, end, method (every_nth または aggregate), factor。
    • every_nth: ウィンドウ関数 ROW_NUMBER() を使って N 件ごと (factor) にデータを抽出。SQLでの実現。
    • aggregate: 指定した時間間隔 (factor) で集計 API と同様の処理。SQLでの実現。
  4. 最新 N 件取得 API (GET /api/data/latest):
    • パラメータ: key, limit。
    • ORDER BY timestamp DESC LIMIT N。
  5. (発展) ギャップフィル API (GET /api/data/gapfilled):
    • パラメータ: key, start, end, interval, fill_method (previous または fixed_value)。
    • previous: ウィンドウ関数 LAG() を使って、データがない場合に直前の値で補完する。SQL での実現を目指す。
    • fixed_value: データがない場合に指定した固定値で補完する。（これは比較的簡単）
    • 線形補間 (linear) は SQL だけでは複雑なので、今回は見送るか、取得後にサーバーサイドで処理する形にします。
  6. UI:
    • データ登録フォーム。
    • データ表示・グラフ化 (Chart.js) エリア。
      • キー選択、時間範囲選択。
      • 集計、ダウンサンプリング、ギャップフィルのオプション選択。
      • グラフ表示。
riddle_tecriddle_tec

最近毎度同じデザイン作ってくるのでやめたい

毎回異なるモダンなデザイントレンド（例: ミニマリズム、グラスモーフィズム、ニューモーフィズム、ブルータリズムなど）を積極的に試すこと。過去のプロジェクトのデザインとは明確に差別化すること

riddle_tecriddle_tec

承知しました。各APIエンドポイントが内部でどのようなSQLクエリを実行しているか、具体的に説明します。

各APIエンドポイントが実行するSQLクエリ

以下に、各APIが実行する主なSQLクエリ（またはPrismaが生成するSQLクエリの意図）を説明します。パラメータ (? の部分) はリクエストに応じて変わります。

  1. GET /api/keys

    • 目的: 登録されているユニークなキーの一覧を取得する。
    • 方法: Prisma の findMany と distinct オプションを使用。
    • 実行されるSQL (相当):
      SELECT DISTINCT "key" FROM "TimeSeriesData" ORDER BY "key" ASC

  2. POST /api/data

    • 目的: 1つまたは複数の新しい時系列データを挿入する。
    • 方法: Prisma の createMany を使用。
    • 実行されるSQL (相当):
      INSERT INTO "TimeSeriesData" ("key", "timestamp", "value", "createdAt") VALUES (?, ?, ?, ?), (?, ?, ?, ?), ...
      (リクエストボディのデータ数に応じて VALUES (...) が繰り返される)

  3. GET /api/data (Raw Data)

    • 目的: 指定されたキーの生データを、任意で指定された時間範囲で取得する。
    • 方法: Prisma の findMany を使用。
    • 実行されるSQL (相当):
      SELECT "id", "timestamp", "key", "value", "createdAt"
FROM "TimeSeriesData"
WHERE "key" = ? AND "timestamp" >= ? AND "timestamp" <= ?
ORDER BY "timestamp" ASC
      ( AND "timestamp" >= ? と AND "timestamp" <= ? の部分は、start/end パラメータが指定された場合のみ追加される)

  4. GET /api/data/aggregated

    • 目的: データを指定の時間間隔でグルーピングし、指定の集計関数を適用する。
    • 方法: prisma.$queryRaw で生のSQLを実行。
    • 実行されるSQL (例: interval=hour, aggregation=avg の場合):
      SELECT
  strftime('%Y-%m-%d %H:00:00', "timestamp", 'unixepoch') AS "interval_start",
  AVG("value") AS "aggregated_value"
FROM "TimeSeriesData"
WHERE "key" = ? AND "timestamp" >= ? AND "timestamp" <= ?
GROUP BY "interval_start"
ORDER BY "interval_start" ASC
      ( strftime のフォーマットは interval パラメータに応じて、AVG の部分は aggregation パラメータに応じて変わる。WHERE 句の timestamp 条件は任意。)

  5. GET /api/data/downsampled

    • 目的: データを間引いて取得する。
    • 方法: prisma.$queryRaw で生のSQLを実行。
      • method=every_nth の場合: ウィンドウ関数 ROW_NUMBER() を使用。
        SELECT "timestamp", "value"
FROM (
  SELECT
    "timestamp",
    "value",
    ROW_NUMBER() OVER (ORDER BY "timestamp" ASC) as "rn"
  FROM "TimeSeriesData"
  WHERE "key" = ? AND "timestamp" >= ? AND "timestamp" <= ?
)
WHERE "rn" % ? = 1 -- factor パラメータで割った余りが 1 の行を抽出
ORDER BY "timestamp" ASC
        ( WHERE 句の timestamp 条件は任意。)
      • method=aggregate の場合: Unixタイムスタンプの整数除算でグルーピング。
        SELECT
  AVG("value") AS "aggregated_value", -- 現在は AVG 固定
  ("timestamp" / ?) AS "time_group" -- factor パラメータで整数除算
FROM "TimeSeriesData"
WHERE "key" = ? AND "timestamp" >= ? AND "timestamp" <= ?
GROUP BY "time_group"
ORDER BY "time_group" ASC
        ( WHERE 句の timestamp 条件は任意。結果はJS側で timestamp: Number(time_group) * factor に変換される。)

  6. GET /api/data/latest

    • 目的: 指定されたキーの最新 N 件のデータを取得する。
    • 方法: Prisma の findMany と orderBy, take を使用。
    • 実行されるSQL (相当):
      SELECT "id", "timestamp", "key", "value", "createdAt"
FROM "TimeSeriesData"
WHERE "key" = ?
ORDER BY "timestamp" DESC -- 最新が先頭に来るように降順
LIMIT ? -- limit パラメータで件数指定
      (取得後、JS側で timestamp の昇順に並び替え直している)

これらの説明で、各APIが内部でどのようなクエリを実行しているかご理解いただけたでしょうか？

riddle_tecriddle_tec

なるほど、SQLって色々できるんだな〜〜〜。
うーんたまにしか使わないので忘れることを考えるとAIにお願いしたい

このスクラップは6ヶ月前にクローズされました