前年比と前月比の計算方法

データ分析において、時間軸に沿った指標の変化を評価するためには「前年比（Year-on-Year）」や「前月比（Month-on-Month）」の計算が不可欠です。Hiveでは、これらの指標をウィンドウ関数や自己結合を活用して効率的に算出できます。

テストデータの準備

以下の売上データを使用します。

1,2020-04-20,420
2,2020-04-04,800
3,2020-03-28,500
4,2020-03-13,100
5,2020-02-27,300
6,2020-01-07,450
7,2019-04-07,800
8,2019-03-15,1200
9,2019-02-17,200
10,2019-02-07,600
11,2019-01-13,300

Hiveでのテーブル作成とデータ読み込みは次の通りです。

CREATE TABLE sales_data (
  id INT,
  sale_date DATE,
  amount INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INPATH '/path/to/sales.txt' INTO TABLE sales_data;

月次売上の年間比率計算

各月の売上がその年に占める割合を求める場合、サブクエリによる集計結合またはウィンドウ関数で実現可能です。

方法1：サブクエリによる内部結合

SELECT 
  monthly.month_label,
  monthly.month_total,
  yearly.year_total,
  ROUND(monthly.month_total / yearly.year_total, 4) AS proportion
FROM (
  SELECT 
    SUM(amount) AS month_total,
    DATE_FORMAT(sale_date, 'yyyy-MM') AS month_label
  FROM sales_data
  GROUP BY DATE_FORMAT(sale_date, 'yyyy-MM')
) monthly
JOIN (
  SELECT 
    SUM(amount) AS year_total,
    DATE_FORMAT(sale_date, 'yyyy') AS year_label
  FROM sales_data
  GROUP BY DATE_FORMAT(sale_date, 'yyyy')
) yearly
ON SUBSTR(monthly.month_label, 1, 4) = yearly.year_label;

方法2：ウィンドウ関数による一括処理

WITH base AS (
  SELECT 
    SUBSTR(sale_date, 1, 7) AS month_str,
    SUM(amount) OVER (PARTITION BY SUBSTR(sale_date, 1, 7)) AS m_amount,
    SUM(amount) OVER (PARTITION BY SUBSTR(sale_date, 1, 4)) AS y_amount,
    ROW_NUMBER() OVER (PARTITION BY SUBSTR(sale_date, 1, 7)) AS rn
  FROM sales_data
)
SELECT 
  month_str,
  m_amount,
  y_amount,
  ROUND(m_amount * 1.0 / y_amount, 4) AS ratio
FROM base
WHERE rn = 1
ORDER BY month_str;

前年比・前月比の計算

成長率の基本式は以下の通りです。

• 前年比 = (当期値 - 前年同月値) / 前年同月値 × 100%
• 前月比 = (当月値 - 前月値) / 前月値 × 100%

ウィンドウ関数LAGを使ったアプローチ

直近の過去値を取得するためにLAG()関数を利用できます。ただし、データに欠損がある場合、誤った期間の値を参照してしまう可能性があります。

WITH monthly_summary AS (
  SELECT 
    SUBSTR(sale_date, 1, 7) AS current_month,
    SUM(amount) AS current_value
  FROM sales_data
  GROUP BY SUBSTR(sale_date, 1, 7)
),
lagged_data AS (
  SELECT 
    current_month,
    current_value,
    LAG(current_value, 1, 0) OVER (ORDER BY current_month) AS prev_value
  FROM monthly_summary
)
SELECT 
  current_month,
  current_value,
  prev_value,
  CASE 
    WHEN prev_value = 0 THEN NULL 
    ELSE ROUND((current_value - prev_value) * 1.0 / prev_value, 4) 
  END AS mom_growth_rate
FROM lagged_data;

この結果は、連続した月データがない場合に不正確になるため、より厳密な日付演算が必要です。

自己結合による正確な前月比計算

月単位の差分を明示的に計算することで、欠損のあるデータに対しても正確な比較が可能になります。

WITH monthly_stats AS (
  SELECT 
    SUBSTR(sale_date, 1, 7) AS base_month,
    SUM(amount) AS total_amount
  FROM sales_data
  GROUP BY SUBSTR(sale_date, 1, 7)
),
with_prev_month AS (
  SELECT 
    base_month,
    total_amount,
    ADD_MONTHS(CONCAT(base_month, '-01'), -1) AS prior_month_date
  FROM monthly_stats
)
SELECT 
  curr.base_month AS this_month,
  curr.total_amount AS current_sales,
  prev.base_month AS last_month,
  prev.total_amount AS previous_sales,
  CASE 
    WHEN prev.total_amount IS NULL OR prev.total_amount = 0 THEN 0.0
    ELSE ROUND((curr.total_amount - prev.total_amount) * 1.0 / prev.total_amount, 4)
  END AS growth_rate
FROM with_prev_month curr
LEFT JOIN monthly_stats prev
ON SUBSTR(curr.prior_month_date, 1, 7) = prev.base_month
ORDER BY curr.base_month;

この方式では、ADD_MONTHS関数を使って正確に1か月前の日付を生成し、文字列として比較しているため、時間軸の整合性を保ちながら安全に前月比を算出できます。