【SQL】定点観測用クエリを作成する際のポイント

【SQL】定点観測用クエリを作成する際のポイント

どうも、hosigakiです！
今日は、定点観測用のクエリ作成時のポイントについて解説します！

そもそも定点観測とは

「定点観測」とは、

同じ指標を時系列ごとに追っていく観測手法

のことで、別名「時系列観測」とも呼ばれます。

サービスのKPIなどは、時系列の変化を追うため、この手法が使われることが多いです。
例えば、月別売上や日別ユーザー登録数などはその典型的な例ですね！

ちなみに、定点観測ではなく、その瞬間その瞬間に知りたいことを分析する手法は「スポット分析」などと呼ばれ、例えば「仮説Aを考えたけど、本当にそうなっているのか、過去のデータを分析して確かめたい！」ってときの分析がこれに当たります！

定点観測用クエリを作成する際のポイント

定点観測用のクエリは、以下の2点を抑えながら作成します。

  • 常に最新のデータが抽出されるようにする
  • どんな時間間隔で集計するのかを決定する

今回は、以下のユーザーテーブルを用いて、過去3ヶ月から昨日までの日別登録者数を集計しながら、解説していきます！

idnamegenderagecreated_at
1太郎男性402019-5-12 23:52:34
2よしお男性312019-5-12 23:42:03
3SAKI女性342019-5-12 23:59:54
4れいか女性302019-5-13 06:02:20
5ジム男性292019-5-13 06:12:50
...............
7ボブ男性312022-4-12 10:02:40
8健一男性402022-4-12 10:13:00
usersテーブル

日時関数を使って、常に最新データが抽出されるようにしよう！

毎日の更新時にクエリを書き直さなくてもいいように、あらかじめクエリで、自動的に以下の範囲から集計されるようにします。

その日から起算して、過去3ヶ月前から昨日23:59:59まで

これは、以下のようにWHERE句で日時関数を用いることで実現できます。

/*書き方例 その１*/
WHERE
    created_at >= DATETIME_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 3 MONTH)
    AND
    created_at < CURRENT_DATE("Asia/Tokyo")

/*書き方例 その２*/
WHERE
    created_at BETWEEN DATETIME_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 3 MONTH) AND DATETIME(FORMAT_DATE("%F 23:59:59", CURRENT_DATE("Asia/Tokyo")-1))

当日のデータに注意！！

日別で定点観測する際、クエリ更新日当日のデータを集計範囲に含めている方は、注意が必要です。

当日のデータを含めると、まだその日の途中時点のデータが算出されてしまいます。
その結果、以下のようなミスリードに繋がる可能性があります。

  • 折れ線グラフなどで見た際に、「直近がすごく下がっている！」と勘違いしてしまう
  • 抽出データを用いて1日あたりの平均値を算出する場合、実際よりも低い数値がでてしまう

思わぬミスリードに繋がらないようにするためにも、日別の定点観測データ算出の際は、前日までを抽出範囲としておくのがベターです。

FORMAT関数で日時を整形して、日別でカウントしよう！

日別でデータを見たい場合、
①ユーザーの登録日を日付に整形して、
②日付ごとにグループ化してユーザーを集計する
という操作を行います。

以下、そのクエリです。

SELECT
    FORMAT_DATETIME("%F", created_at) AS created_date,
    COUNT(id) AS n_of_users
FROM
    hosigaki.users
WHERE
    created_at >= DATETIME_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 3 MONTH)
    AND
    created_at < CURRENT_DATE("Asia/Tokyo")
GROUP BY
    created_date
ORDER BY
    created_date ASC

これで、過去3ヶ月前から昨日までの日別登録数を定点観測できるクエリが完成しました！
お疲れ様です！

今回のように、定点観測用クエリでは、日時関数を多用します。
日時関数については、こちらでまとめておりますので、よければご参考ください！

【ある程度クエリに慣れてきた方は...】
別記事で、7日間移動平均を集計する方法や、構成比率・累計比率を集計する方法も紹介しています！
これらは「分析関数」という、習得するとめちゃくちゃ便利な関数を取り扱っております。
特に定点観測用クエリの作成時は、分析関数の真価を発揮できるので、ぜひ興味のある方は記事を読んでくださいませ！

ここまで読んでいただき、ありがとうございました！
また次の記事でお会いできれば嬉しいです(^o^)／

-SQL
-, , , , ,