Simple minds think alike

より多くの可能性を

トップ > gem > 【Ruby,Rails】Blazer gemで年月、年月日(Date)を選択するクエリを作り、時系列で指標を把握できるように

【Ruby,Rails】Blazer gemで年月、年月日(Date)を選択するクエリを作り、時系列で指標を把握できるように

gem ruby

RailsアプリケーションのWebサービスを運用・改善するに当たって様々な指標を把握するために、blazerやadhoqといったgemを追加し、SQL集計ダッシュボードを作っています。 特に年月日を絞り込みの条件に入れると時系列の変化を観測し、サービス改善に役にたつと感じています。

今回は、Blazer gemを使った年月、年月日(Date)を選択するSmart Variablesを使ったクエリの作り方を紹介したいと思います。

年月指定

Blazerの設定ファイル(blazer.yml)に設定を追加

blazer.ymlに以下のようなSmart Variablesを追加しています。Smart Variablesという機能は、集計SQLの中にパラメータ（この例では{recent_months}）を入れると、画面に事前に定義したSmart Variablesのクエリの結果をセレクトボックスとして表示するものです。 Smart Variablesのクエリの結果の1つ目のカラムがセレクトボックスの値になり、表示されるラベルは、2つ目のカラムがラベルになります。。

data_sources:
  main:
    〜〜〜
    smart_variables:
      recent_months:
        select DATE_FORMAT(CURRENT_DATE, '%Y-%m-01'), DATE_FORMAT(CURRENT_DATE, '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -1 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -1 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -2 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -2 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -3 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -3 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -4 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -4 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -5 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -5 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -6 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -6 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -7 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -7 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -8 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -8 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -9 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -9 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -10 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -10 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -11 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -11 MONTH), '%Y-%m')
          UNION ALL
        select DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -12 MONTH), '%Y-%m-01'), DATE_FORMAT(ADDDATE(CURRENT_DATE, INTERVAL -12 MONTH), '%Y-%m')

日付の絞り込みはテーブルの型によりますが、Blazerの画面で以下のようなクエリを追加すると集計できます。

SELECT 
  date,
  count(*)
FROM
  orders
WHERE
  YEAR( date ) = YEAR({recent_months})
    AND
  MONTH( date ) = MONTH({recent_months})
ORDER BY
  date
GROUP BY
  date

画面上のrecent_monthsのセレクトボックスで2020-08を選択すると、以下のクエリが実行されます。

SELECT 
  date,
  count(*)
FROM
  orders
WHERE
  YEAR( date ) = YEAR('2020-08-01')
    AND
  MONTH( date ) = MONTH('2020-08-01')
ORDER BY
  date
GROUP BY
  date

年月日指定

年月日に関してはvariablesの機能で実現できるので、事前に設定ファイルの修正などは必要ありません 集計SQLクエリに{start_time} または {end_time}を含めると、簡単にデータを抽出できます。

{start_time}と{end_time}の両方を指定した場合

以下のように {start_time}と{end_time}の両方を指定した場合、Today, Last 7 Days, Last 30 Days, Custom Rangeから期間を選択できます。デフォルトはLast 30 Days。

SQLは以下のような感じです。

SELECT 
  date,
  count(*)
FROM
  orders
WHERE
  date >= {start_time} 
    AND
  date <= {end_time}
ORDER BY
  date
GROUP BY
  date

Custom Rangeを選択すると開始日・終了日を選択できるようになります。

{start_time}と{end_time}のどちらかを指定した場合

{start_time}と{end_time}のどちらかを指定した場合、日付選択だけできます。

SQLは以下のような感じです。

SELECT 
  count(*)
FROM
  orders
WHERE
  date ={start_time}