日付パラメータの追加

データとレポート
1

多くの場合、特定の期間のデータを抽出したい場合があります。クエリ自体に期間をハードコーディングすることもできますが、レポートを将来性のあるものにするために、日付パラメータを追加できます。

このクエリは、特定の期間内に作成された各カテゴリの投稿の総数（削除された投稿やトピック、プライベートメッセージ、カテゴリのないトピックは除く）をカウントし、カテゴリを投稿総数の降順で並べ替えるように設計されています。

-- [params]
-- date :start_date -- 期間の開始日のパラメータ
-- date :end_date -- 期間の終了日のパラメータ

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- 各カテゴリの投稿数をカウント
FROM posts p
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- 開始日と終了日の間の期間に作成された投稿をフィルタリング（::date は created_at タイムスタンプを日付にキャストします）
  AND t.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND t.archetype <> 'private message'
  AND p.user_id > 0
  AND t.category_id IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC

string ではなく date のパラメータスタイルを使用すると、特に他の人と共有する場合に、パラメータ値の入力がはるかに直感的になります。

この種のクエリで注目すべきもう 1 つのヒントは、created_at を日付にキャストすることです。値はデータベースにタイムスタンプとして保存されているため、日付にキャストしないと、クエリは :end_date 自体の日の結果を取得できません。

Explorer ツリーを使用して、どの値がタイムスタンプとして保存されているかを確認できます。

explorer tree - value type
explorer tree - value type278×281 3.18 KB

このクエリは主に原則を実証するための例です。お役に立てば幸いですが、ご質問があれば以下にお尋ねください。 :slight_smile:

「いいね！」 9
2 
PG::SyntaxError: ERROR:  syntax error at or near ":"
LINE 16: WHERE p.created_at::date BETWEEN :start_date AND :end_date -…

:tired_face:

「いいね！」 4
3

はい、パラメータの横にあるインラインコメントは好まないようです。

-- [params]
-- date :start_date
-- date :end_date

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- 各カテゴリの投稿数をカウント
FROM posts p
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- 開始日と終了日の間に作成された投稿をフィルタリング (::date は created_at タイムスタンプを日付にキャストします)
  AND t.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND t.archetype <> 'private message'
  AND p.user_id > 0
  AND t.category_id IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC

こちらの方がうまくいくようです。:+1: （初めてページをリフレッシュして、パラメータ入力ボックスを認識させる必要がある場合があります）

ガイドのデバッグにご協力いただきありがとうございます。:slight_smile: :trophy:

「いいね！」 7