スタースキーマがわからなくて実際に作ってみた

最近分析のためのデータ構造としてスタースキーマというものがあることを知った。

データをディメンションとファクトで分けて、クエリーをシンプルに書けるようにするため使うとのこと。ディメンショナルモデルともいうらしい。

元のデータを使いやすいようにいろんなテーブルに分けていくイメージだった。

以下の記事がわかりやすくまとめてくれていた。

概念はわかったけど...

これでスタースキーマがどんなものかはわかったけど、結局これでどういうSQLが書けて、どういうAPIの設計をすればいいかがピンと来なかった。

こういう時は実際ものを作ることが一番早いので、株のデータを用いてスタースキーマを作ることにした。

データの準備

株データの取得は以下の記事で行なった。Redshiftはコストが結構かかるので今回はSupabaseで作っている。

Supabaseはデータウェアハウスではないので本格的に開発するときにはあまり向いていなさそう。

株APIから取得したデータは以下のようである。このデータをStockテーブルに入れている。

date	symbol	open	high	low	close	volume
2024-09-13	GOOGL	155.4300	158.3800	155.2100	157.4600	27509172
2024-09-12	GOOGL	153.8000	154.8200	152.6500	154.6900	29695048
2024-09-11	GOOGL	149.9200	151.5000	147.5200	151.1600	29607693
2024-09-10	GOOGL	150.4500	151.2700	148.3400	148.6600	31118765
2024-09-09	GOOGL	152.5100	153.4000	147.2150	148.7100	39260451
2024-09-06	GOOGL	157.3000	157.8300	150.5500	150.9200	37912130

ディメンションとファクトテーブルを作る

とりあえずわかりやすい軸として、日付と銘柄のディメンションを作った。
それに紐づくファクトとして株価テーブルも作った。

-- 日付ディメンションテーブル
CREATE TABLE dim_date (
    date_key SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL,
    day INT NOT NULL,
    day_of_week INT NOT NULL,
    quarter INT NOT NULL
);

-- 銘柄（シンボル）ディメンションテーブル
CREATE TABLE dim_symbol (
    symbol_key SERIAL PRIMARY KEY,
    symbol VARCHAR(10) NOT NULL,
    company_name VARCHAR(100) NOT NULL
);

-- 株価ファクトテーブル
CREATE TABLE fact_stock_price (
    date_key INT NOT NULL,
    symbol_key INT NOT NULL,
    open_price DECIMAL(10, 4) NOT NULL,
    high_price DECIMAL(10, 4) NOT NULL,
    low_price DECIMAL(10, 4) NOT NULL,
    close_price DECIMAL(10, 4) NOT NULL,
    volume BIGINT NOT NULL,
    FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
    FOREIGN KEY (symbol_key) REFERENCES dim_symbol(symbol_key)
);

-- インデックスの作成（パフォーマンス向上のため）
CREATE INDEX idx_fact_stock_price_date ON fact_stock_price(date_key);
CREATE INDEX idx_fact_stock_price_symbol ON fact_stock_price(symbol_key);

作ったテーブルにデータを入れていく

BEがある場合だとBatchタスクで書けるところでもあるが、今回はSupabaseを使っているため、SQLで書いてみた。

PostgresのEXTRACTを初めて使ってみたけど結構便利だった。

-- 1. dim_dateテーブルにデータを挿入（date列をDATE型に変換）
INSERT INTO dim_date (date, year, month, day, day_of_week, quarter)
SELECT DISTINCT
    date::DATE,
    EXTRACT(YEAR FROM date::DATE) AS year,
    EXTRACT(MONTH FROM date::DATE) AS month,
    EXTRACT(DAY FROM date::DATE) AS day,
    EXTRACT(DOW FROM date::DATE) AS day_of_week,
    EXTRACT(QUARTER FROM date::DATE) AS quarter
FROM stock;

-- 2. dim_symbolテーブルにデータを挿入（変更なし）
INSERT INTO dim_symbol (symbol, company_name)
SELECT DISTINCT symbol, 
       CASE 
           WHEN symbol = 'GOOGL' THEN 'Alphabet Inc.'
           ELSE 'Unknown' -- 他の銘柄がある場合は、ここで適切な会社名を設定
       END AS company_name
FROM stock;

-- 3. fact_stock_priceテーブルにデータを挿入（date列をDATE型に変換）
INSERT INTO fact_stock_price (date_key, symbol_key, open_price, high_price, low_price, close_price, volume)
SELECT 
    d.date_key,
    s.symbol_key,
    st.open::DECIMAL(10, 4),
    st.high::DECIMAL(10, 4),
    st.low::DECIMAL(10, 4),
    st.close::DECIMAL(10, 4),
    st.volume::BIGINT
FROM 
    stock st
JOIN 
    dim_date d ON st.date::DATE = d.date
JOIN 
    dim_symbol s ON st.symbol = s.symbol;

-- 4. データ移行の確認
SELECT COUNT(*) FROM fact_stock_price;

-- 5. インデックスの再構築（オプション、大量データの挿入後にパフォーマンスを向上させるため）
REINDEX TABLE fact_stock_price;
ANALYZE fact_stock_price;

ビューを作る

これでスタースキーマの準備ができたので、ダッシュボードを作れるようになった。

しかし、今の状態でAPIを叩いたらフロントエンドでデータの整理が必要になる。
BE側で細かな処理を書けないSupabaseのめんどくさいところ...今回はSupabaseのDBに追加でビューを作って、そのデータを取得することにした。

-- 月次平均株価ビューの作成
CREATE VIEW monthly_avg_price AS
SELECT 
    d.year,
    d.month,
    s.symbol,
    s.company_name,
    AVG(f.close_price) as avg_price
FROM 
    fact_stock_price f
JOIN 
    dim_date d ON f.date_key = d.date_key
JOIN 
    dim_symbol s ON f.symbol_key = s.symbol_key
GROUP BY 
    d.year, d.month, s.symbol, s.company_name
ORDER BY 
    d.year, d.month, s.symbol;

これで使いやすいデータができた。取得するだけでグラフ化できるようになっている。

year	month	symbol	company_name	avg_price
1999	11	AAPL	Apple	90.00714285714285714
1999	11	AMZN	Amazon	76.24190476190476190
1999	11	MSFT	Microsoft	89.46238095238095238
1999	12	AAPL	Apple	103.40000000000000000
1999	12	AMZN	Amazon	91.02863636363636363
1999	12	MSFT	Microsoft	106.18954545454545454
2000	1	AAPL	Apple	103.35950000000000000
2000	1	AMZN	Amazon	68.15050000000000000

ダッシュボードを作る

vue環境上でEChartライブラリーを使ったら短いコードでリッチなダッシュボードが作れた。
まだちょっと甘いところはあるが...

おわりに

分析というには浅いところしか見ていないが、スタースキーマがどんなものかは少しわかったような気がする。

多数のテーブルを巻き込むとクエリーが必要以上に複雑になってしまうから。使うところだけをまとめたテーブルを作ってしまおう、ということだろう。

今回書いたコード

<script setup lang="ts">
import { ref, computed, onMounted, watch } from 'vue';
import { use } from 'echarts/core';
import { CanvasRenderer } from 'echarts/renderers';
import { LineChart } from 'echarts/charts';
import {
  TitleComponent,
  TooltipComponent,
  LegendComponent,
  GridComponent,
} from 'echarts/components';
import VChart from 'vue-echarts';
import { supabase } from "@/utils/supabase";


use([
  CanvasRenderer,
  LineChart,
  TitleComponent,
  TooltipComponent,
  LegendComponent,
  GridComponent,
]);

const stockData = ref([]);
const selectedStocks = ref(['AAPL', 'AMZN', 'MSFT', 'GOOGL']);
const yearRange = ref({ start: 2023, end: 2024 });
const availableStocks = ['AAPL', 'AMZN', 'MSFT', 'GOOGL'];

const fetchMonthlyAvgPrice = async () => {
  const { data, error } = await supabase
      .from('monthly_avg_price')
      .select('*')
      .in('symbol', selectedStocks.value)
      .gte('year', yearRange.value.start)
      .lte('year', yearRange.value.end)
      .order('year')
      .order('month');
  if (error) {
    console.error('Error fetching monthly average price data:', error);
  } else {
    stockData.value = data;
  }
};

const handleStockSelection = (symbol) => {
  if (selectedStocks.value.includes(symbol)) {
    selectedStocks.value = selectedStocks.value.filter(s => s !== symbol);
  } else {
    selectedStocks.value.push(symbol);
  }
};

const handleYearChange = (type, value) => {
  yearRange.value[type] = parseInt(value);
};

const chartOption = computed(() => ({
  title: {
    text: 'Monthly Average Stock Price Comparison'
  },
  tooltip: {
    trigger: 'axis',
    axisPointer: {
      type: 'cross'
    }
  },
  legend: {
    data: selectedStocks.value
  },
  xAxis: {
    type: 'category',
    data: Array.from(new Set(stockData.value.map(item => `${item.year}-${item.month.toString().padStart(2, '0')}`))).sort(),
    axisLabel: {
      formatter: (value) => {
        const [year, month] = value.split('-');
        return `${year}-${month}`;
      }
    }
  },
  yAxis: {
    type: 'value',
    name: 'Average Price',
    axisLabel: {
      formatter: '${value}'
    }
  },
  series: selectedStocks.value.map(symbol => ({
    name: symbol,
    type: 'line',
    data: stockData.value
        .filter(item => item.symbol === symbol)
        .map(item => [
          `${item.year}-${item.month.toString().padStart(2, '0')}`,
          item.avg_price
        ])
  }))
}));

onMounted(fetchMonthlyAvgPrice);

watch([selectedStocks, yearRange], fetchMonthlyAvgPrice);
</script>

<template>
  <div class="monthly-avg-price-dashboard">
    <h1>Monthly Average Stock Price Dashboard</h1>
    <div class="controls">
      <div class="stock-selector">
        <label v-for="symbol in availableStocks" :key="symbol">
          <input
              type="checkbox"
              :checked="selectedStocks.includes(symbol)"
              @change="handleStockSelection(symbol)"
          />
          {{ symbol }}
        </label>
      </div>
      <div class="year-range-selector">
        <input
            type="number"
            v-model="yearRange.start"
            @input="handleYearChange('start', $event.target.value)"
            min="2000"
            max="2024"
        />
        <input
            type="number"
            v-model="yearRange.end"
            @input="handleYearChange('end', $event.target.value)"
            min="2000"
            max="2024"
        />
      </div>
    </div>
    <v-chart class="chart" :option="chartOption" autoresize />
  </div>
</template>

<style scoped>
.monthly-avg-price-dashboard {
  font-family: Arial, sans-serif;
}
.controls {
  margin-bottom: 20px;
}
.stock-selector label {
  margin-right: 10px;
}
.year-range-selector input {
  width: 70px;
  margin-right: 10px;
}
.chart {
  height: 600px;
}
</style>