| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257 |
- """
- build_daily_metrics.py
- ======================
- 流式解析 Apple Health export.xml,按日聚合,UPSERT 到 daily_health_metrics 表。
- 幂等:重复跑只覆盖当天数据,不会产生重复行。
- """
- from __future__ import annotations
- from collections import defaultdict
- from datetime import datetime, timedelta
- from pathlib import Path
- import sys
- import pymysql
- from rich.console import Console
- console = Console()
- XML_PATH = Path(r"D:\workspace\Agi\zrd-nb666\campus_health_app\analytics\apple_health\apple_health_export\瀵煎嚭.xml")
- DB = dict(host='localhost', port=3306, user='analytics', password='123456',
- database='campus_health', charset='utf8mb4', autocommit=False)
- # 关心的 Record type 到聚合字段的映射
- QUANTITY_TYPES = {
- "HKQuantityTypeIdentifierStepCount": ("step_count", "sum"),
- "HKQuantityTypeIdentifierActiveEnergyBurned": ("active_energy_kcal", "sum"),
- "HKQuantityTypeIdentifierBasalEnergyBurned": ("basal_energy_kcal", "sum"),
- "HKQuantityTypeIdentifierAppleExerciseTime": ("exercise_minutes", "sum"),
- "HKQuantityTypeIdentifierRestingHeartRate": ("resting_heart_rate", "avg"),
- "HKQuantityTypeIdentifierHeartRateVariabilitySDNN": ("hrv_sdnn", "avg"),
- "HKQuantityTypeIdentifierHeartRate": ("avg_heart_rate", "avg"),
- "HKQuantityTypeIdentifierOxygenSaturation": ("avg_oxygen_saturation", "avg_pct"),
- "HKQuantityTypeIdentifierRespiratoryRate": ("avg_respiratory_rate", "avg"),
- "HKQuantityTypeIdentifierAppleSleepingWristTemperature": ("wrist_temp_c", "avg"),
- }
- SLEEP_VALUES = {
- "HKCategoryValueSleepAnalysisAsleepDeep": "deep_sleep_hours",
- "HKCategoryValueSleepAnalysisAsleepREM": "rem_sleep_hours",
- "HKCategoryValueSleepAnalysisAsleepCore": "core_sleep_hours",
- "HKCategoryValueSleepAnalysisAsleepUnspecified": "core_sleep_hours",
- "HKCategoryValueSleepAnalysisInBed": "sleep_in_bed_hours",
- }
- def parse_dt(s: str | None) -> datetime | None:
- if not s:
- return None
- # "2026-09-11 14:30:52 +0800"
- try:
- return datetime.strptime(s, "%Y-%m-%d %H:%M:%S %z")
- except ValueError:
- return None
- def main():
- console.print(f"[bold]Parsing[/bold] {XML_PATH.name} ({XML_PATH.stat().st_size/1024/1024:.0f} MB)...")
- # daily[date_str][field] = value (sum) 或 (sum, count) 用于 avg
- daily: dict[str, dict] = defaultdict(lambda: defaultdict(float))
- counts: dict[str, dict] = defaultdict(lambda: defaultdict(int))
- sleep_hours: dict[str, dict] = defaultdict(lambda: defaultdict(float))
- workouts: dict[str, set] = defaultdict(set)
- workout_days: dict[str, int] = defaultdict(int)
- menstrual_days: dict[str, str] = {}
- flag_days: dict[str, set] = defaultdict(set) # fatigue, bloating, mood
- from xml.etree.ElementTree import iterparse
- context = iterparse(str(XML_PATH), events=("end",))
- n_records = 0
- n_workouts = 0
- for event, elem in context:
- tag = elem.tag
- if tag == "Record":
- rtype = elem.get("type", "")
- # 跳过非关心类型
- if rtype in QUANTITY_TYPES:
- field, agg = QUANTITY_TYPES[rtype]
- try:
- val = float(elem.get("value", "0"))
- except ValueError:
- elem.clear()
- continue
- dt = parse_dt(elem.get("startDate"))
- if not dt:
- elem.clear()
- continue
- d = dt.strftime("%Y-%m-%d")
- if agg == "sum":
- daily[d][field] += val
- elif agg == "avg_pct":
- # 血氧 value 是 0.96 这种,转百分比;clip 到 0-100 防异常值
- pct = max(0.0, min(100.0, val * 100))
- daily[d][field] += pct
- counts[d][field] += 1
- else: # avg
- daily[d][field] += val
- counts[d][field] += 1
- n_records += 1
- elif rtype == "HKCategoryTypeIdentifierSleepAnalysis":
- val = elem.get("value", "")
- bucket = SLEEP_VALUES.get(val)
- if not bucket:
- elem.clear()
- continue
- sd = parse_dt(elem.get("startDate"))
- ed = parse_dt(elem.get("endDate"))
- if not sd or not ed:
- elem.clear()
- continue
- hours = (ed - sd).total_seconds() / 3600
- if hours <= 0 or hours > 24:
- elem.clear()
- continue
- # 睡眠归到醒来那天(endDate 的日期)
- d = ed.strftime("%Y-%m-%d")
- sleep_hours[d][bucket] += hours
- if bucket != "sleep_in_bed_hours":
- sleep_hours[d]["sleep_duration_hours"] += hours
- n_records += 1
- elif rtype == "HKCategoryTypeIdentifierMenstrualFlow":
- val = elem.get("value", "")
- sd = parse_dt(elem.get("startDate"))
- if not sd:
- elem.clear()
- continue
- d = sd.strftime("%Y-%m-%d")
- # 按流量映射
- flow_map = {
- "HKCategoryValueMenstrualFlowUnspecified": "unspecified",
- "HKCategoryValueMenstrualFlowLight": "light",
- "HKCategoryValueMenstrualFlowMedium": "medium",
- "HKCategoryValueMenstrualFlowHeavy": "heavy",
- }
- menstrual_days[d] = flow_map.get(val, "unspecified")
- n_records += 1
- elif rtype == "HKCategoryTypeIdentifierFatigue":
- sd = parse_dt(elem.get("startDate"))
- if sd:
- flag_days[sd.strftime("%Y-%m-%d")].add("fatigue")
- n_records += 1
- elif rtype == "HKCategoryTypeIdentifierBloating":
- sd = parse_dt(elem.get("startDate"))
- if sd:
- flag_days[sd.strftime("%Y-%m-%d")].add("bloating")
- n_records += 1
- elif rtype == "HKCategoryTypeIdentifierMoodChanges":
- sd = parse_dt(elem.get("startDate"))
- if sd:
- flag_days[sd.strftime("%Y-%m-%d")].add("mood")
- n_records += 1
- elem.clear()
- elif tag == "Workout":
- wtype = elem.get("workoutActivityType", "").replace("HKWorkoutActivityType", "")
- sd = parse_dt(elem.get("startDate"))
- if sd:
- d = sd.strftime("%Y-%m-%d")
- workouts[d].add(wtype)
- workout_days[d] += 1
- n_workouts += 1
- elem.clear()
- # 定期报告进度
- if n_records % 100000 == 0 and n_records:
- console.print(f" ... {n_records} records parsed, {len(daily)} days aggregated")
- console.print(f"[green]✓ Parsed {n_records} records, {n_workouts} workouts, {len(daily)} days[/green]")
- # 求 avg 字段的平均值
- for d, fields in daily.items():
- for field, total in list(fields.items()):
- n = counts[d].get(field, 0)
- if n > 0 and field in ("resting_heart_rate", "hrv_sdnn", "avg_heart_rate",
- "avg_oxygen_saturation", "avg_respiratory_rate", "wrist_temp_c"):
- fields[field] = round(total / n, 2)
- else:
- fields[field] = round(total, 2)
- # 合并所有日期
- all_dates = set(daily) | set(sleep_hours) | set(workout_days) | set(menstrual_days) | set(flag_days)
- console.print(f"[green]✓ Total {len(all_dates)} unique days to upsert[/green]")
- # 写库
- conn = pymysql.connect(**DB)
- sql = """
- INSERT INTO daily_health_metrics (
- metric_date, step_count, active_energy_kcal, basal_energy_kcal, exercise_minutes,
- workout_count, workout_types, resting_heart_rate, hrv_sdnn, avg_heart_rate,
- avg_oxygen_saturation, avg_respiratory_rate,
- sleep_duration_hours, sleep_in_bed_hours, deep_sleep_hours, rem_sleep_hours, core_sleep_hours,
- wrist_temp_c, is_menstruating, menstrual_flow,
- fatigue_reported, bloating_reported, mood_changes
- ) VALUES (
- %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
- )
- ON DUPLICATE KEY UPDATE
- step_count=VALUES(step_count), active_energy_kcal=VALUES(active_energy_kcal),
- basal_energy_kcal=VALUES(basal_energy_kcal), exercise_minutes=VALUES(exercise_minutes),
- workout_count=VALUES(workout_count), workout_types=VALUES(workout_types),
- resting_heart_rate=VALUES(resting_heart_rate), hrv_sdnn=VALUES(hrv_sdnn),
- avg_heart_rate=VALUES(avg_heart_rate), avg_oxygen_saturation=VALUES(avg_oxygen_saturation),
- avg_respiratory_rate=VALUES(avg_respiratory_rate),
- sleep_duration_hours=VALUES(sleep_duration_hours), sleep_in_bed_hours=VALUES(sleep_in_bed_hours),
- deep_sleep_hours=VALUES(deep_sleep_hours), rem_sleep_hours=VALUES(rem_sleep_hours),
- core_sleep_hours=VALUES(core_sleep_hours), wrist_temp_c=VALUES(wrist_temp_c),
- is_menstruating=VALUES(is_menstruating), menstrual_flow=VALUES(menstrual_flow),
- fatigue_reported=VALUES(fatigue_reported), bloating_reported=VALUES(bloating_reported),
- mood_changes=VALUES(mood_changes)
- """
- rows = []
- for d in sorted(all_dates):
- f = daily.get(d, {})
- s = sleep_hours.get(d, {})
- rows.append((
- d,
- int(f.get("step_count", 0)),
- f.get("active_energy_kcal", 0),
- f.get("basal_energy_kcal", 0),
- int(f.get("exercise_minutes", 0)),
- workout_days.get(d, 0),
- ",".join(sorted(workouts.get(d, []))) or None,
- f.get("resting_heart_rate"),
- f.get("hrv_sdnn"),
- f.get("avg_heart_rate"),
- f.get("avg_oxygen_saturation"),
- f.get("avg_respiratory_rate"),
- round(s.get("sleep_duration_hours", 0), 2),
- round(s.get("sleep_in_bed_hours", 0), 2),
- round(s.get("deep_sleep_hours", 0), 2),
- round(s.get("rem_sleep_hours", 0), 2),
- round(s.get("core_sleep_hours", 0), 2),
- f.get("wrist_temp_c"),
- d in menstrual_days,
- menstrual_days.get(d),
- "fatigue" in flag_days.get(d, set()),
- "bloating" in flag_days.get(d, set()),
- "mood" in flag_days.get(d, set()),
- ))
- with conn.cursor() as cur:
- cur.executemany(sql, rows)
- conn.commit()
- console.print(f"[green bold]✓ Upserted {len(rows)} days into daily_health_metrics[/green bold]")
- conn.close()
- if __name__ == "__main__":
- main()
|