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

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

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

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

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

https://zenn.dev/pei0804/articles/star-schema-design

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

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

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

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

https://qiita.com/Procrustes5/items/83de9df05c47e488a1a0

株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 |

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

```sql
-- 日付ディメンションテーブル
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を初めて使ってみたけど結構便利だった。

https://www.postgresql.jp/document/7.2/user/functions-datetime.html

``` sql
-- 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に追加でビューを作って、そのデータを取得することにした。

```sql
-- 月次平均株価ビューの作成
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ライブラリーを使ったら短いコードでリッチなダッシュボードが作れた。
まだちょっと甘いところはあるが...

![スクリーンショット 2024-09-28 23.05.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3754242/77011ee8-765f-d3cc-9844-ab302a13fb18.png)

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

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

## 今回書いたコード

``` vue
<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>

```