407 lines
13 KiB
Python
407 lines
13 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
移行結果統計情報表示スクリプト
|
||
Docker Compose環境で実行可能
|
||
"""
|
||
|
||
import os
|
||
import sys
|
||
import psycopg2
|
||
from datetime import datetime, timedelta
|
||
import pytz
|
||
from collections import defaultdict
|
||
import json
|
||
|
||
def connect_database():
|
||
"""データベースに接続"""
|
||
try:
|
||
conn = psycopg2.connect(
|
||
host=os.environ.get('DB_HOST', 'postgres-db'),
|
||
port=os.environ.get('DB_PORT', '5432'),
|
||
database=os.environ.get('POSTGRES_DBNAME', 'rogdb'),
|
||
user=os.environ.get('POSTGRES_USER', 'admin'),
|
||
password=os.environ.get('POSTGRES_PASS', 'admin123456')
|
||
)
|
||
return conn
|
||
except Exception as e:
|
||
print(f"❌ データベース接続エラー: {e}")
|
||
return None
|
||
|
||
def get_basic_statistics(cursor):
|
||
"""基本統計情報を取得"""
|
||
print("\n" + "="*80)
|
||
print("📊 移行データ基本統計情報")
|
||
print("="*80)
|
||
|
||
# テーブル別レコード数
|
||
tables = [
|
||
('rog_newevent2', 'イベント'),
|
||
('rog_team', 'チーム'),
|
||
('rog_member', 'メンバー'),
|
||
('rog_entry', 'エントリー'),
|
||
('rog_gpscheckin', 'GPSチェックイン'),
|
||
('rog_checkpoint', 'チェックポイント'),
|
||
('rog_location2025', 'ロケーション2025'),
|
||
('rog_customuser', 'ユーザー')
|
||
]
|
||
|
||
print("\n📋 テーブル別レコード数:")
|
||
print("テーブル名 日本語名 レコード数")
|
||
print("-" * 65)
|
||
|
||
total_records = 0
|
||
for table_name, japanese_name in tables:
|
||
try:
|
||
cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
|
||
count = cursor.fetchone()[0]
|
||
total_records += count
|
||
print(f"{table_name:<25} {japanese_name:<15} {count:>10,}件")
|
||
except Exception as e:
|
||
print(f"{table_name:<25} {japanese_name:<15} {'エラー':>10}")
|
||
|
||
print("-" * 65)
|
||
print(f"{'合計':<41} {total_records:>10,}件")
|
||
|
||
def get_event_statistics(cursor):
|
||
"""イベント別統計情報"""
|
||
print("\n" + "="*80)
|
||
print("🎯 イベント別統計情報")
|
||
print("="*80)
|
||
|
||
# イベント一覧と基本情報
|
||
cursor.execute("""
|
||
SELECT id, event_name, event_date, created_at
|
||
FROM rog_newevent2
|
||
ORDER BY event_date DESC
|
||
""")
|
||
|
||
events = cursor.fetchall()
|
||
if not events:
|
||
print("イベントデータがありません")
|
||
return
|
||
|
||
print(f"\n📅 登録イベント数: {len(events)}件")
|
||
print("\nイベント詳細:")
|
||
print("ID イベント名 開催日 登録日時")
|
||
print("-" * 60)
|
||
|
||
for event in events:
|
||
event_id, event_name, event_date, created_at = event
|
||
event_date_str = event_date.strftime("%Y-%m-%d") if event_date else "未設定"
|
||
created_str = created_at.strftime("%Y-%m-%d %H:%M") if created_at else "未設定"
|
||
print(f"{event_id:>2} {event_name:<15} {event_date_str} {created_str}")
|
||
|
||
# イベント別参加統計
|
||
cursor.execute("""
|
||
SELECT
|
||
e.event_name,
|
||
COUNT(DISTINCT t.id) as team_count,
|
||
COUNT(DISTINCT m.id) as member_count,
|
||
COUNT(DISTINCT en.id) as entry_count
|
||
FROM rog_newevent2 e
|
||
LEFT JOIN rog_team t ON e.id = t.event_id
|
||
LEFT JOIN rog_member m ON t.id = m.team_id
|
||
LEFT JOIN rog_entry en ON t.id = en.team_id
|
||
GROUP BY e.id, e.event_name
|
||
ORDER BY team_count DESC
|
||
""")
|
||
|
||
participation_stats = cursor.fetchall()
|
||
|
||
print("\n👥 イベント別参加統計:")
|
||
print("イベント名 チーム数 メンバー数 エントリー数")
|
||
print("-" * 55)
|
||
|
||
total_teams = 0
|
||
total_members = 0
|
||
total_entries = 0
|
||
|
||
for stat in participation_stats:
|
||
event_name, team_count, member_count, entry_count = stat
|
||
total_teams += team_count
|
||
total_members += member_count
|
||
total_entries += entry_count
|
||
print(f"{event_name:<15} {team_count:>6}件 {member_count:>8}件 {entry_count:>9}件")
|
||
|
||
print("-" * 55)
|
||
print(f"{'合計':<15} {total_teams:>6}件 {total_members:>8}件 {total_entries:>9}件")
|
||
|
||
def get_gps_checkin_statistics(cursor):
|
||
"""GPSチェックイン統計"""
|
||
print("\n" + "="*80)
|
||
print("📍 GPSチェックイン統計情報")
|
||
print("="*80)
|
||
|
||
# 基本統計
|
||
cursor.execute("""
|
||
SELECT
|
||
COUNT(*) as total_checkins,
|
||
COUNT(DISTINCT zekken) as unique_teams,
|
||
COUNT(DISTINCT cp_number) as unique_checkpoints,
|
||
MIN(checkin_time) as earliest_checkin,
|
||
MAX(checkin_time) as latest_checkin
|
||
FROM rog_gpscheckin
|
||
""")
|
||
|
||
basic_stats = cursor.fetchone()
|
||
if not basic_stats or basic_stats[0] == 0:
|
||
print("GPSチェックインデータがありません")
|
||
return
|
||
|
||
total_checkins, unique_teams, unique_checkpoints, earliest, latest = basic_stats
|
||
|
||
print(f"\n📊 基本統計:")
|
||
print(f"総チェックイン数: {total_checkins:,}件")
|
||
print(f"参加チーム数: {unique_teams:,}チーム")
|
||
print(f"利用CP数: {unique_checkpoints:,}箇所")
|
||
|
||
if earliest and latest:
|
||
print(f"最早チェックイン: {earliest.strftime('%Y-%m-%d %H:%M:%S')}")
|
||
print(f"最終チェックイン: {latest.strftime('%Y-%m-%d %H:%M:%S')}")
|
||
|
||
# 時間帯別分析
|
||
cursor.execute("""
|
||
SELECT
|
||
EXTRACT(hour FROM checkin_time) as hour,
|
||
COUNT(*) as count
|
||
FROM rog_gpscheckin
|
||
GROUP BY EXTRACT(hour FROM checkin_time)
|
||
ORDER BY hour
|
||
""")
|
||
|
||
hourly_stats = cursor.fetchall()
|
||
|
||
print(f"\n⏰ 時間帯別チェックイン分布:")
|
||
print("時間 チェックイン数 グラフ")
|
||
print("-" * 40)
|
||
|
||
max_count = max([count for _, count in hourly_stats]) if hourly_stats else 1
|
||
|
||
for hour, count in hourly_stats:
|
||
bar_length = int((count / max_count) * 20)
|
||
bar = "█" * bar_length
|
||
print(f"{int(hour):>2}時 {count:>8}件 {bar}")
|
||
|
||
# CP別利用統計（上位10位）
|
||
cursor.execute("""
|
||
SELECT
|
||
cp_number,
|
||
COUNT(*) as checkin_count,
|
||
COUNT(DISTINCT zekken) as team_count
|
||
FROM rog_gpscheckin
|
||
GROUP BY cp_number
|
||
ORDER BY checkin_count DESC
|
||
LIMIT 10
|
||
""")
|
||
|
||
cp_stats = cursor.fetchall()
|
||
|
||
print(f"\n🏅 CP利用ランキング（上位10位）:")
|
||
print("順位 CP番号 チェックイン数 利用チーム数")
|
||
print("-" * 40)
|
||
|
||
for i, (cp_number, checkin_count, team_count) in enumerate(cp_stats, 1):
|
||
print(f"{i:>2}位 CP{cp_number:>3} {checkin_count:>8}件 {team_count:>7}チーム")
|
||
|
||
def get_team_statistics(cursor):
|
||
"""チーム統計"""
|
||
print("\n" + "="*80)
|
||
print("👥 チーム統計情報")
|
||
print("="*80)
|
||
|
||
# チーム基本統計
|
||
cursor.execute("""
|
||
SELECT
|
||
COUNT(*) as total_teams,
|
||
COUNT(DISTINCT class_name) as unique_classes,
|
||
AVG(CASE WHEN member_count > 0 THEN member_count END) as avg_members
|
||
FROM (
|
||
SELECT
|
||
t.id,
|
||
t.class_name,
|
||
COUNT(m.id) as member_count
|
||
FROM rog_team t
|
||
LEFT JOIN rog_member m ON t.id = m.team_id
|
||
GROUP BY t.id, t.class_name
|
||
) team_stats
|
||
""")
|
||
|
||
team_basic = cursor.fetchone()
|
||
total_teams, unique_classes, avg_members = team_basic
|
||
|
||
print(f"\n📊 基本統計:")
|
||
print(f"総チーム数: {total_teams:,}チーム")
|
||
print(f"クラス数: {unique_classes or 0}種類")
|
||
print(f"平均メンバー数: {avg_members:.1f}人/チーム" if avg_members else "平均メンバー数: データなし")
|
||
|
||
# クラス別統計
|
||
cursor.execute("""
|
||
SELECT
|
||
COALESCE(class_name, '未分類') as class_name,
|
||
COUNT(*) as team_count,
|
||
COUNT(CASE WHEN member_count > 0 THEN 1 END) as active_teams
|
||
FROM (
|
||
SELECT
|
||
t.class_name,
|
||
COUNT(m.id) as member_count
|
||
FROM rog_team t
|
||
LEFT JOIN rog_member m ON t.id = m.team_id
|
||
GROUP BY t.id, t.class_name
|
||
) team_stats
|
||
GROUP BY class_name
|
||
ORDER BY team_count DESC
|
||
""")
|
||
|
||
class_stats = cursor.fetchall()
|
||
|
||
if class_stats:
|
||
print(f"\n🏆 クラス別チーム数:")
|
||
print("クラス名 チーム数 アクティブ")
|
||
print("-" * 35)
|
||
|
||
for class_name, team_count, active_teams in class_stats:
|
||
print(f"{class_name:<15} {team_count:>6}件 {active_teams:>7}件")
|
||
|
||
def get_data_quality_check(cursor):
|
||
"""データ品質チェック"""
|
||
print("\n" + "="*80)
|
||
print("🔍 データ品質チェック")
|
||
print("="*80)
|
||
|
||
checks = []
|
||
|
||
# 1. 重複チェック
|
||
cursor.execute("""
|
||
SELECT COUNT(*) FROM (
|
||
SELECT zekken, cp_number, checkin_time, COUNT(*)
|
||
FROM rog_gpscheckin
|
||
GROUP BY zekken, cp_number, checkin_time
|
||
HAVING COUNT(*) > 1
|
||
) duplicates
|
||
""")
|
||
duplicate_count = cursor.fetchone()[0]
|
||
checks.append(("重複チェックイン", duplicate_count, "件"))
|
||
|
||
# 2. 異常時刻チェック（0時台）
|
||
cursor.execute("""
|
||
SELECT COUNT(*) FROM rog_gpscheckin
|
||
WHERE EXTRACT(hour FROM checkin_time) = 0
|
||
""")
|
||
zero_hour_count = cursor.fetchone()[0]
|
||
checks.append(("0時台チェックイン", zero_hour_count, "件"))
|
||
|
||
# 3. 未来日時チェック
|
||
cursor.execute("""
|
||
SELECT COUNT(*) FROM rog_gpscheckin
|
||
WHERE checkin_time > NOW()
|
||
""")
|
||
future_count = cursor.fetchone()[0]
|
||
checks.append(("未来日時チェックイン", future_count, "件"))
|
||
|
||
# 4. メンバー不在チーム
|
||
cursor.execute("""
|
||
SELECT COUNT(*) FROM rog_team t
|
||
LEFT JOIN rog_member m ON t.id = m.team_id
|
||
WHERE m.id IS NULL
|
||
""")
|
||
no_member_teams = cursor.fetchone()[0]
|
||
checks.append(("メンバー不在チーム", no_member_teams, "チーム"))
|
||
|
||
# 5. エントリー不在チーム
|
||
cursor.execute("""
|
||
SELECT COUNT(*) FROM rog_team t
|
||
LEFT JOIN rog_entry e ON t.id = e.team_id
|
||
WHERE e.id IS NULL
|
||
""")
|
||
no_entry_teams = cursor.fetchone()[0]
|
||
checks.append(("エントリー不在チーム", no_entry_teams, "チーム"))
|
||
|
||
print("\n🧪 品質チェック結果:")
|
||
print("チェック項目 件数 状態")
|
||
print("-" * 40)
|
||
|
||
for check_name, count, unit in checks:
|
||
status = "✅ 正常" if count == 0 else "⚠️ 要確認"
|
||
print(f"{check_name:<15} {count:>6}{unit} {status}")
|
||
|
||
def export_statistics_json(cursor):
|
||
"""統計情報をJSONで出力"""
|
||
print("\n" + "="*80)
|
||
print("📄 統計情報JSON出力")
|
||
print("="*80)
|
||
|
||
statistics = {}
|
||
|
||
# 基本統計
|
||
cursor.execute("SELECT COUNT(*) FROM rog_gpscheckin")
|
||
statistics['total_checkins'] = cursor.fetchone()[0]
|
||
|
||
cursor.execute("SELECT COUNT(DISTINCT zekken) FROM rog_gpscheckin")
|
||
statistics['unique_teams'] = cursor.fetchone()[0]
|
||
|
||
cursor.execute("SELECT COUNT(*) FROM rog_newevent2")
|
||
statistics['total_events'] = cursor.fetchone()[0]
|
||
|
||
# イベント別統計
|
||
cursor.execute("""
|
||
SELECT event_name, COUNT(*) as checkin_count
|
||
FROM rog_newevent2 e
|
||
LEFT JOIN rog_team t ON e.id = t.event_id
|
||
LEFT JOIN rog_gpscheckin g ON t.zekken = g.zekken
|
||
GROUP BY e.id, event_name
|
||
ORDER BY checkin_count DESC
|
||
""")
|
||
|
||
event_stats = {}
|
||
for event_name, count in cursor.fetchall():
|
||
event_stats[event_name] = count
|
||
|
||
statistics['event_checkins'] = event_stats
|
||
statistics['generated_at'] = datetime.now().isoformat()
|
||
|
||
# ファイル出力
|
||
output_file = f"/app/migration_statistics_{datetime.now().strftime('%Y%m%d_%H%M%S')}.json"
|
||
|
||
try:
|
||
with open(output_file, 'w', encoding='utf-8') as f:
|
||
json.dump(statistics, f, ensure_ascii=False, indent=2)
|
||
print(f"✅ 統計情報をJSONで出力しました: {output_file}")
|
||
except Exception as e:
|
||
print(f"❌ JSON出力エラー: {e}")
|
||
|
||
def main():
|
||
"""メイン処理"""
|
||
print("🚀 移行結果統計情報表示スクリプト開始")
|
||
print(f"実行時刻: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
|
||
|
||
# データベース接続
|
||
conn = connect_database()
|
||
if not conn:
|
||
sys.exit(1)
|
||
|
||
try:
|
||
cursor = conn.cursor()
|
||
|
||
# 各統計情報を表示
|
||
get_basic_statistics(cursor)
|
||
get_event_statistics(cursor)
|
||
get_gps_checkin_statistics(cursor)
|
||
get_team_statistics(cursor)
|
||
get_data_quality_check(cursor)
|
||
export_statistics_json(cursor)
|
||
|
||
print("\n" + "="*80)
|
||
print("✅ 統計情報表示完了")
|
||
print("="*80)
|
||
|
||
except Exception as e:
|
||
print(f"❌ 統計処理中にエラーが発生しました: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|
||
finally:
|
||
cursor.close()
|
||
conn.close()
|
||
|
||
if __name__ == "__main__":
|
||
main()
|