ビジネスの中で最も多い質問の1つが、「自社の指標は時間とともに改善されているか？」があります。

この問いには、通常、前週比、前月比、前年比などを用いて回答します。

前年比は、企業の業績など、ある年の通年（一年間）の値が、前年の同じ期間の値に対してどのくらいの割合に相当するかを指し、一般的には、年間を通じた大きな流れを見るのに適しています。さらに季節変動のあるデータを同じ時期で比較できるなど利点も持ち合わせています

この記事では、DB言語を使いながら前年比を算出してみましょう。
算出するためには、以下の標準的な計算プロセスで進めていきます。

– データを年ごとにグループ化
– 変化率の計算
– 年を並列にプロット

データを年ごとにグループ化

ここでは、収益[revenue]を例に、前年比の比較をしてみましょう。年ごとの収益を得るには、年ごとにグループ化し、販売価格[price]を合計します。

select
  extract(year from created_at) as year
  , sum(price) as revenue
from
  purchases
group by
  1

これにより、次のようなグラフが表示されます。

変化率の計算

年ごとにグループ化されたデータを強化するために、素早く変化率計算を行うことができます。

RedshiftとPostgresでは、window関数を使って素早く計算することができます。これを行うには、lag 関数を使用します。正しい行を取得するために、行の順序を設定することを忘れないようにしてください。

lag(revenue) over (order by year)

以下のクエリでは、変化率を求めてみます。

select
  year
  , revenue
  , (revenue / nullif(lag(revenue) 
  over(order by year), 0)) - 1 as percent_change
from
  (
    select
      extract(year from created_at) as year
      , sum(price) as revenue
    from
      purchases
    group by
      1
  )
  annual

この方法で、実際の値と変化率の両方が確認できました。

年を並列にプロット

時には、年ごとを月や日で比較したいことがあります。この例では、この例では、今年と昨年の 1 月を基準として比較してみます。読みやすくするために、”to_char”を使って月の名前を取得します。そして、データを正しい順番に並べるために、月数で並べていきます

select
  extract(year from created_at) as year
  , to_char(created_at, 'Mon') as month
  , sum(price) as revenue
from
  purchases
group by
  1
  , 2
  , extract(month from created_at)
order by
  extract(month from created_at)

さらにもう一歩踏み込んで、年度間の前月比の伸びを比較することもできます。今回は、同じ年の月間の変化率を計算するため、lag関数に”partition by”を使います。

select 
year
  , month
  , revenue
  , (revenue * 1.0 / lag(revenue) 
  over (partition by year order by month_number)) 
    - 1 as percent_change
from (
select
  extract(year from created_at) as year
  , to_char(created_at, 'Mon') as month
  , extract(month from created_at) as month_number
  , sum(price) as revenue
from
  purchases
group by
  1
  , 2
  , 3
)
order by month_number

最後に

これらの方法を用いれば、データを使って期間ごとの指標を計算することができるようになります。ぜひともご活用してみてください。

この投稿に記載されているすべてのデータは情報提供のみを目的としており、正確ではありません。前もってご了承ください。
本記事は、Sisense社の許諾のもと弊社独自の見解を追加し記事化しております。
https://www.sisense.com/blog/calculating-year-over-year-metrics/

※ Sisenseは、Sisense Inc の商標または登録商標です。
※ その他の会社名、製品名は各社の登録商標または商標です。
※ 記事の内容は記事公開時点での情報です。閲覧頂いた時点では異なる可能性がございます。