build_daily_metrics.py 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257
  1. """
  2. build_daily_metrics.py
  3. ======================
  4. 流式解析 Apple Health export.xml,按日聚合,UPSERT 到 daily_health_metrics 表。
  5. 幂等:重复跑只覆盖当天数据,不会产生重复行。
  6. """
  7. from __future__ import annotations
  8. from collections import defaultdict
  9. from datetime import datetime, timedelta
  10. from pathlib import Path
  11. import sys
  12. import pymysql
  13. from rich.console import Console
  14. console = Console()
  15. XML_PATH = Path(r"D:\workspace\Agi\zrd-nb666\campus_health_app\analytics\apple_health\apple_health_export\瀵煎嚭.xml")
  16. DB = dict(host='localhost', port=3306, user='analytics', password='123456',
  17. database='campus_health', charset='utf8mb4', autocommit=False)
  18. # 关心的 Record type 到聚合字段的映射
  19. QUANTITY_TYPES = {
  20. "HKQuantityTypeIdentifierStepCount": ("step_count", "sum"),
  21. "HKQuantityTypeIdentifierActiveEnergyBurned": ("active_energy_kcal", "sum"),
  22. "HKQuantityTypeIdentifierBasalEnergyBurned": ("basal_energy_kcal", "sum"),
  23. "HKQuantityTypeIdentifierAppleExerciseTime": ("exercise_minutes", "sum"),
  24. "HKQuantityTypeIdentifierRestingHeartRate": ("resting_heart_rate", "avg"),
  25. "HKQuantityTypeIdentifierHeartRateVariabilitySDNN": ("hrv_sdnn", "avg"),
  26. "HKQuantityTypeIdentifierHeartRate": ("avg_heart_rate", "avg"),
  27. "HKQuantityTypeIdentifierOxygenSaturation": ("avg_oxygen_saturation", "avg_pct"),
  28. "HKQuantityTypeIdentifierRespiratoryRate": ("avg_respiratory_rate", "avg"),
  29. "HKQuantityTypeIdentifierAppleSleepingWristTemperature": ("wrist_temp_c", "avg"),
  30. }
  31. SLEEP_VALUES = {
  32. "HKCategoryValueSleepAnalysisAsleepDeep": "deep_sleep_hours",
  33. "HKCategoryValueSleepAnalysisAsleepREM": "rem_sleep_hours",
  34. "HKCategoryValueSleepAnalysisAsleepCore": "core_sleep_hours",
  35. "HKCategoryValueSleepAnalysisAsleepUnspecified": "core_sleep_hours",
  36. "HKCategoryValueSleepAnalysisInBed": "sleep_in_bed_hours",
  37. }
  38. def parse_dt(s: str | None) -> datetime | None:
  39. if not s:
  40. return None
  41. # "2026-09-11 14:30:52 +0800"
  42. try:
  43. return datetime.strptime(s, "%Y-%m-%d %H:%M:%S %z")
  44. except ValueError:
  45. return None
  46. def main():
  47. console.print(f"[bold]Parsing[/bold] {XML_PATH.name} ({XML_PATH.stat().st_size/1024/1024:.0f} MB)...")
  48. # daily[date_str][field] = value (sum) 或 (sum, count) 用于 avg
  49. daily: dict[str, dict] = defaultdict(lambda: defaultdict(float))
  50. counts: dict[str, dict] = defaultdict(lambda: defaultdict(int))
  51. sleep_hours: dict[str, dict] = defaultdict(lambda: defaultdict(float))
  52. workouts: dict[str, set] = defaultdict(set)
  53. workout_days: dict[str, int] = defaultdict(int)
  54. menstrual_days: dict[str, str] = {}
  55. flag_days: dict[str, set] = defaultdict(set) # fatigue, bloating, mood
  56. from xml.etree.ElementTree import iterparse
  57. context = iterparse(str(XML_PATH), events=("end",))
  58. n_records = 0
  59. n_workouts = 0
  60. for event, elem in context:
  61. tag = elem.tag
  62. if tag == "Record":
  63. rtype = elem.get("type", "")
  64. # 跳过非关心类型
  65. if rtype in QUANTITY_TYPES:
  66. field, agg = QUANTITY_TYPES[rtype]
  67. try:
  68. val = float(elem.get("value", "0"))
  69. except ValueError:
  70. elem.clear()
  71. continue
  72. dt = parse_dt(elem.get("startDate"))
  73. if not dt:
  74. elem.clear()
  75. continue
  76. d = dt.strftime("%Y-%m-%d")
  77. if agg == "sum":
  78. daily[d][field] += val
  79. elif agg == "avg_pct":
  80. # 血氧 value 是 0.96 这种,转百分比;clip 到 0-100 防异常值
  81. pct = max(0.0, min(100.0, val * 100))
  82. daily[d][field] += pct
  83. counts[d][field] += 1
  84. else: # avg
  85. daily[d][field] += val
  86. counts[d][field] += 1
  87. n_records += 1
  88. elif rtype == "HKCategoryTypeIdentifierSleepAnalysis":
  89. val = elem.get("value", "")
  90. bucket = SLEEP_VALUES.get(val)
  91. if not bucket:
  92. elem.clear()
  93. continue
  94. sd = parse_dt(elem.get("startDate"))
  95. ed = parse_dt(elem.get("endDate"))
  96. if not sd or not ed:
  97. elem.clear()
  98. continue
  99. hours = (ed - sd).total_seconds() / 3600
  100. if hours <= 0 or hours > 24:
  101. elem.clear()
  102. continue
  103. # 睡眠归到醒来那天(endDate 的日期)
  104. d = ed.strftime("%Y-%m-%d")
  105. sleep_hours[d][bucket] += hours
  106. if bucket != "sleep_in_bed_hours":
  107. sleep_hours[d]["sleep_duration_hours"] += hours
  108. n_records += 1
  109. elif rtype == "HKCategoryTypeIdentifierMenstrualFlow":
  110. val = elem.get("value", "")
  111. sd = parse_dt(elem.get("startDate"))
  112. if not sd:
  113. elem.clear()
  114. continue
  115. d = sd.strftime("%Y-%m-%d")
  116. # 按流量映射
  117. flow_map = {
  118. "HKCategoryValueMenstrualFlowUnspecified": "unspecified",
  119. "HKCategoryValueMenstrualFlowLight": "light",
  120. "HKCategoryValueMenstrualFlowMedium": "medium",
  121. "HKCategoryValueMenstrualFlowHeavy": "heavy",
  122. }
  123. menstrual_days[d] = flow_map.get(val, "unspecified")
  124. n_records += 1
  125. elif rtype == "HKCategoryTypeIdentifierFatigue":
  126. sd = parse_dt(elem.get("startDate"))
  127. if sd:
  128. flag_days[sd.strftime("%Y-%m-%d")].add("fatigue")
  129. n_records += 1
  130. elif rtype == "HKCategoryTypeIdentifierBloating":
  131. sd = parse_dt(elem.get("startDate"))
  132. if sd:
  133. flag_days[sd.strftime("%Y-%m-%d")].add("bloating")
  134. n_records += 1
  135. elif rtype == "HKCategoryTypeIdentifierMoodChanges":
  136. sd = parse_dt(elem.get("startDate"))
  137. if sd:
  138. flag_days[sd.strftime("%Y-%m-%d")].add("mood")
  139. n_records += 1
  140. elem.clear()
  141. elif tag == "Workout":
  142. wtype = elem.get("workoutActivityType", "").replace("HKWorkoutActivityType", "")
  143. sd = parse_dt(elem.get("startDate"))
  144. if sd:
  145. d = sd.strftime("%Y-%m-%d")
  146. workouts[d].add(wtype)
  147. workout_days[d] += 1
  148. n_workouts += 1
  149. elem.clear()
  150. # 定期报告进度
  151. if n_records % 100000 == 0 and n_records:
  152. console.print(f" ... {n_records} records parsed, {len(daily)} days aggregated")
  153. console.print(f"[green]✓ Parsed {n_records} records, {n_workouts} workouts, {len(daily)} days[/green]")
  154. # 求 avg 字段的平均值
  155. for d, fields in daily.items():
  156. for field, total in list(fields.items()):
  157. n = counts[d].get(field, 0)
  158. if n > 0 and field in ("resting_heart_rate", "hrv_sdnn", "avg_heart_rate",
  159. "avg_oxygen_saturation", "avg_respiratory_rate", "wrist_temp_c"):
  160. fields[field] = round(total / n, 2)
  161. else:
  162. fields[field] = round(total, 2)
  163. # 合并所有日期
  164. all_dates = set(daily) | set(sleep_hours) | set(workout_days) | set(menstrual_days) | set(flag_days)
  165. console.print(f"[green]✓ Total {len(all_dates)} unique days to upsert[/green]")
  166. # 写库
  167. conn = pymysql.connect(**DB)
  168. sql = """
  169. INSERT INTO daily_health_metrics (
  170. metric_date, step_count, active_energy_kcal, basal_energy_kcal, exercise_minutes,
  171. workout_count, workout_types, resting_heart_rate, hrv_sdnn, avg_heart_rate,
  172. avg_oxygen_saturation, avg_respiratory_rate,
  173. sleep_duration_hours, sleep_in_bed_hours, deep_sleep_hours, rem_sleep_hours, core_sleep_hours,
  174. wrist_temp_c, is_menstruating, menstrual_flow,
  175. fatigue_reported, bloating_reported, mood_changes
  176. ) VALUES (
  177. %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
  178. )
  179. ON DUPLICATE KEY UPDATE
  180. step_count=VALUES(step_count), active_energy_kcal=VALUES(active_energy_kcal),
  181. basal_energy_kcal=VALUES(basal_energy_kcal), exercise_minutes=VALUES(exercise_minutes),
  182. workout_count=VALUES(workout_count), workout_types=VALUES(workout_types),
  183. resting_heart_rate=VALUES(resting_heart_rate), hrv_sdnn=VALUES(hrv_sdnn),
  184. avg_heart_rate=VALUES(avg_heart_rate), avg_oxygen_saturation=VALUES(avg_oxygen_saturation),
  185. avg_respiratory_rate=VALUES(avg_respiratory_rate),
  186. sleep_duration_hours=VALUES(sleep_duration_hours), sleep_in_bed_hours=VALUES(sleep_in_bed_hours),
  187. deep_sleep_hours=VALUES(deep_sleep_hours), rem_sleep_hours=VALUES(rem_sleep_hours),
  188. core_sleep_hours=VALUES(core_sleep_hours), wrist_temp_c=VALUES(wrist_temp_c),
  189. is_menstruating=VALUES(is_menstruating), menstrual_flow=VALUES(menstrual_flow),
  190. fatigue_reported=VALUES(fatigue_reported), bloating_reported=VALUES(bloating_reported),
  191. mood_changes=VALUES(mood_changes)
  192. """
  193. rows = []
  194. for d in sorted(all_dates):
  195. f = daily.get(d, {})
  196. s = sleep_hours.get(d, {})
  197. rows.append((
  198. d,
  199. int(f.get("step_count", 0)),
  200. f.get("active_energy_kcal", 0),
  201. f.get("basal_energy_kcal", 0),
  202. int(f.get("exercise_minutes", 0)),
  203. workout_days.get(d, 0),
  204. ",".join(sorted(workouts.get(d, []))) or None,
  205. f.get("resting_heart_rate"),
  206. f.get("hrv_sdnn"),
  207. f.get("avg_heart_rate"),
  208. f.get("avg_oxygen_saturation"),
  209. f.get("avg_respiratory_rate"),
  210. round(s.get("sleep_duration_hours", 0), 2),
  211. round(s.get("sleep_in_bed_hours", 0), 2),
  212. round(s.get("deep_sleep_hours", 0), 2),
  213. round(s.get("rem_sleep_hours", 0), 2),
  214. round(s.get("core_sleep_hours", 0), 2),
  215. f.get("wrist_temp_c"),
  216. d in menstrual_days,
  217. menstrual_days.get(d),
  218. "fatigue" in flag_days.get(d, set()),
  219. "bloating" in flag_days.get(d, set()),
  220. "mood" in flag_days.get(d, set()),
  221. ))
  222. with conn.cursor() as cur:
  223. cur.executemany(sql, rows)
  224. conn.commit()
  225. console.print(f"[green bold]✓ Upserted {len(rows)} days into daily_health_metrics[/green bold]")
  226. conn.close()
  227. if __name__ == "__main__":
  228. main()