# カスタムフィールド (Custom Field) の設計
## 選択肢
- [[EAV (Entity-Attribute-Value)]]
- 一般的にはアンチパターン
- [[JSON 型]]
- INDEX 貼れる
- [[NoSQL (MongoDB)]]
- Aggregation Pipeline
- [[NoSQL (Cassandra)]]
- 大量データ処理
### まとめ
- **NoSQLが向いているケース**:
- 自由設計項目の種類や選択肢が頻繁に変わり、スキーマを柔軟に扱いたい。
- 非構造化データや大量データに対応する必要がある。
- トランザクションやデータの整合性に対する要求がそれほど高くない。
- MongoDBやCassandraを使って、柔軟で効率的なクロス集計とデータ管理を行いたい場合。
- **RDBMSが向いているケース**:
- データの整合性が重要であり、評価やスコアの正確性が必要。
- クロス集計のクエリが複雑で、SQLによる標準的なクエリ記述が重要。
- データ構造がそれほど頻繁に変わらず、自由設計項目の拡張が制限されている場合。
- 既存システムや技術スタックがRDBMSを前提としている場合。
## 参考：JSON型の`key`ごとの定着率の集計
- [[JSON 型#JSON型 + JSON_TABLE|JOIN_TABLE]] を利用
### データ構造
```sql
CREATE TABLE members (
id INT PRIMARY KEY,
name VARCHAR(100),
custom_keys JSON, -- JSON型で保存したkey
entry_date DATE -- 入社日
);
```
```json
{
"department_id": 10,
"project_id": 20
}
```
### 集計
```sql
SELECT
jt.department_id,
TIMESTAMPDIFF(YEAR, m.entry_date, CURDATE()) AS years_of_service,
COUNT(*) AS total_members,
COUNT(*) / (SELECT COUNT(*) FROM members AS m2
JOIN JSON_TABLE(
m2.custom_keys,
"
quot;
COLUMNS (
department_id INT PATH "$.department_id"
)
) AS jt2
WHERE jt2.department_id = jt.department_id) AS retention_rate
FROM
members AS m
JOIN
JSON_TABLE(
m.custom_keys,
"quot;
COLUMNS (
department_id INT PATH "$.department_id"
)
) AS jt
GROUP BY
jt.department_id, years_of_service
ORDER BY
jt.department_id, years_of_service;
```
### キャッシュテーブル
- [[キャッシュテーブル]]
```sql
CREATE TABLE retention_rate_cache (
department_id INT,
years_of_service INT,
total_members INT,
retention_rate DECIMAL(5, 2),
last_updated TIMESTAMP,
PRIMARY KEY (department_id, years_of_service)
);
```
```sql
-- キャッシュテーブルのデータを一度削除
DELETE FROM retention_rate_cache;
-- 新しい集計データをキャッシュテーブルに挿入
INSERT INTO retention_rate_cache (department_id, years_of_service, total_members, retention_rate, last_updated)
SELECT
jt.department_id,
TIMESTAMPDIFF(YEAR, m.entry_date, CURDATE()) AS years_of_service,
COUNT(*) AS total_members,
COUNT(*) / (SELECT COUNT(*) FROM members AS m2
JOIN JSON_TABLE(
m2.custom_keys,
"quot;
COLUMNS (
department_id INT PATH "$.department_id"
)
) AS jt2
WHERE jt2.department_id = jt.department_id) AS retention_rate,
NOW() AS last_updated
FROM
members AS m
JOIN
JSON_TABLE(
m.custom_keys,
"quot;
COLUMNS (
department_id INT PATH "$.department_id"
)
) AS jt
GROUP BY
jt.department_id, years_of_service;
```
```sql
SELECT
department_id,
years_of_service,
retention_rate
FROM
retention_rate_cache
WHERE
department_id = 10; -- 特定のdepartment_idの定着率を取得
```