沈阳市建设工程安全监督站网站,qq小程序开发,wordpress变成中文,wordpress什么版本快AIGlasses_for_navigation数据库课程设计案例#xff1a;导航历史管理与时空数据分析 你是不是也好奇#xff0c;每天戴着智能眼镜导航#xff0c;那些走过的路、停留的地点、花费的时间#xff0c;背后到底是怎么被记录和分析的#xff1f;对于学习数据库的同学来说 USE ai_glasses_nav; -- 1. 用户表 CREATE TABLE user ( user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT ‘用户唯一标识‘, username VARCHAR(50) NOT NULL UNIQUE COMMENT ‘用户名‘, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘注册时间‘ ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT‘用户信息表‘; -- 2. 地点标签表 CREATE TABLE location_label ( label_id INT AUTO_INCREMENT PRIMARY KEY COMMENT ‘标签唯一标识‘, user_id INT NOT NULL COMMENT ‘所属用户ID‘, label_name VARCHAR(100) NOT NULL COMMENT ‘标签名称如家‘, category VARCHAR(50) COMMENT ‘分类家、公司、餐饮等‘, latitude DECIMAL(10, 8) NOT NULL COMMENT ‘典型纬度‘, longitude DECIMAL(11, 8) NOT NULL COMMENT ‘典型经度‘, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE, INDEX idx_user_id (user_id), INDEX idx_category (category) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT‘用户自定义地点标签表‘; -- 3. 导航会话表 CREATE TABLE navigation_session ( session_id VARCHAR(64) PRIMARY KEY COMMENT ‘会话唯一标识可用UUID‘, user_id INT NOT NULL COMMENT ‘用户ID‘, start_label_id INT COMMENT ‘起点标签ID‘, end_label_id INT COMMENT ‘终点标签ID‘, start_time DATETIME NOT NULL COMMENT ‘导航开始时间‘, end_time DATETIME COMMENT ‘导航结束时间‘, total_distance DECIMAL(10, 2) COMMENT ‘总路程米‘, estimated_duration INT COMMENT ‘预估耗时秒‘, actual_duration INT COMMENT ‘实际耗时秒‘, FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE, FOREIGN KEY (start_label_id) REFERENCES location_label(label_id) ON DELETE SET NULL, FOREIGN KEY (end_label_id) REFERENCES location_label(label_id) ON DELETE SET NULL, INDEX idx_user_time (user_id, start_time), INDEX idx_start_end_label (start_label_id, end_label_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT‘导航会话主表‘; -- 4. 轨迹点表数据量最大需仔细设计 CREATE TABLE trajectory_point ( point_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT ‘轨迹点唯一标识‘, session_id VARCHAR(64) NOT NULL COMMENT ‘所属会话ID‘, sequence INT NOT NULL COMMENT ‘在本会话中的点序列号‘, latitude DECIMAL(10, 8) NOT NULL COMMENT ‘纬度‘, longitude DECIMAL(11, 8) NOT NULL COMMENT ‘经度‘, recorded_at DATETIME(6) NOT NULL COMMENT ‘记录时间戳高精度‘, speed DECIMAL(6, 2) COMMENT ‘瞬时速度米/秒‘, accuracy DECIMAL(5, 2) COMMENT ‘定位精度米‘, FOREIGN KEY (session_id) REFERENCES navigation_session(session_id) ON DELETE CASCADE, INDEX idx_session_sequence (session_id, sequence), -- 用于按会话顺序查询轨迹 INDEX idx_session_time (session_id, recorded_at), -- 用于按时间范围查询 SPATIAL INDEX idx_coordinate (latitude, longitude) -- 空间索引用于地理位置相关查询如附近点 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT‘导航轨迹点明细表‘;设计要点说明会话ID使用VARCHAR存储UUID确保全局唯一。时空数据latitude和longitude使用DECIMAL类型保证精度。为它们创建了SPATIAL索引这对后续进行地理范围查询如“查找某区域内的所有轨迹点”至关重要。性能考虑在trajectory_point表的session_id和sequence上建立了复合索引可以高效地按会话顺序拉取完整轨迹。recorded_at上的索引便于时间范围查询。数据关联通过外键确保了数据的完整性和一致性。例如删除一个用户其相关的会话和标签也会级联删除。4. 复杂查询实战从数据中挖掘价值表建好了数据也模拟进去了现在让我们扮演数据分析师用SQL来回答一些有趣的业务问题。4.1 热门路径分析业务方想知道用户最常走的路线是哪几条我们可以通过统计起点和终点标签相同的会话数量来发现热门路径。-- 查询最热门的10条导航路径基于地点标签 SELECT ll_start.label_name AS start_location, ll_end.label_name AS end_location, COUNT(*) AS travel_count, AVG(ns.actual_duration) AS avg_duration_seconds FROM navigation_session ns JOIN location_label ll_start ON ns.start_label_id ll_start.label_id JOIN location_label ll_end ON ns.end_label_id ll_end.label_id WHERE ns.start_label_id IS NOT NULL AND ns.end_label_id IS NOT NULL AND ns.actual_duration IS NOT NULL GROUP BY ns.start_label_id, ns.end_label_id ORDER BY travel_count DESC LIMIT 10;这个查询能告诉我们比如“从家到公司”这条路线被导航了多少次平均耗时多久是名副其实的“通勤主干道”。4.2 拥堵时段统计识别出特定路段的拥堵时段对优化导航建议很有帮助。我们假设“从标签A到标签B”为一条固定路段通过分析该路段在不同小时内的平均通行时间与自由流时间比如凌晨时段的平均时间的比值来判断拥堵。-- 分析‘从家到公司‘这条路径在不同小时段的拥堵情况 WITH free_flow AS ( -- 先计算自由流时间例如凌晨2点到5点的平均时间 SELECT AVG(actual_duration) AS base_duration FROM navigation_session ns WHERE ns.start_label_id (SELECT label_id FROM location_label WHERE label_name ‘家‘ LIMIT 1) AND ns.end_label_id (SELECT label_id FROM location_label WHERE label_name ‘公司‘ LIMIT 1) AND HOUR(ns.start_time) BETWEEN 2 AND 5 ) SELECT HOUR(ns.start_time) AS hour_of_day, COUNT(*) AS trip_count, AVG(ns.actual_duration) AS avg_duration, (SELECT base_duration FROM free_flow) AS free_flow_duration, ROUND(AVG(ns.actual_duration) / (SELECT base_duration FROM free_flow), 2) AS congestion_index -- 拥堵指数 FROM navigation_session ns WHERE ns.start_label_id (SELECT label_id FROM location_label WHERE label_name ‘家‘ LIMIT 1) AND ns.end_label_id (SELECT label_id FROM location_label WHERE label_name ‘公司‘ LIMIT 1) AND ns.actual_duration IS NOT NULL GROUP BY hour_of_day HAVING trip_count 5 -- 只统计有足够样本的时段 ORDER BY hour_of_day;这个查询结果会显示一天24小时中每个小时段的“家到公司”行程平均耗时和拥堵指数。指数大于1.5的时段很可能就是早高峰。4.3 用户常去地点挖掘除了明确的标签我们还可以从轨迹数据中直接挖掘用户经常停留的地点驻留点。思路是查找在较小地理范围内、时间间隔密集的轨迹点簇。-- 简化版寻找用户在某次会话中停留超过10分钟的地点区域 SELECT ns.user_id, ns.session_id, ROUND(AVG(tp.latitude), 6) AS cluster_lat, -- 聚类中心纬度 ROUND(AVG(tp.longitude), 6) AS cluster_lng, -- 聚类中心经度 MIN(tp.recorded_at) AS arrive_time, MAX(tp.recorded_at) AS leave_time, TIMESTAMPDIFF(SECOND, MIN(tp.recorded_at), MAX(tp.recorded_at)) AS stay_duration_seconds FROM navigation_session ns JOIN trajectory_point tp ON ns.session_id tp.session_id -- 利用窗口函数将时间连续、位置接近的点分组此为简化逻辑真实场景可能需更复杂聚类算法 GROUP BY ns.user_id, ns.session_id, FLOOR(UNIX_TIMESTAMP(tp.recorded_at) / 600) -- 每10分钟一个时间窗口 HAVING COUNT(*) 30 AND stay_duration_seconds 600 -- 点数大于30且停留超过600秒10分钟 ORDER BY ns.user_id, ns.session_id, arrive_time;这个查询可以帮助我们发现用户可能经常去但未打标签的地点比如一个常去的公园或咖啡馆。5. 项目总结与扩展思考通过这个“AIGlasses_for_navigation导航历史管理”的数据库课程设计案例我们完整走了一遍从需求分析、概念设计E-R图、物理实现建表到数据查询分析的全过程。你不仅学会了如何为时空数据设计表结构特别是轨迹点表的设计与索引还掌握了如何编写复杂的SQL来进行实际业务分析如热门路径和拥堵统计。在实际开发中这个系统还可以从多个方向扩展。例如可以引入Redis来缓存热门路径的查询结果提升接口响应速度可以将轨迹数据同步到大数据平台如Hive使用Spark进行更复杂的轨迹模式挖掘还可以在前端利用地图API如百度地图、高德地图将分析结果可视化让数据更加直观。数据库课程设计的核心在于“设计”二字即如何根据业务需求做出合理的权衡。这个案例提供了一个很好的起点你可以尝试在此基础上增加更多功能比如记录导航时的天气状况、交通事件或者设计更高效的轨迹压缩存储方案。希望这个项目能帮你把书本上的知识变成解决实际问题的能力。获取更多AI镜像想探索更多AI镜像和应用场景访问 CSDN星图镜像广场提供丰富的预置镜像覆盖大模型推理、图像生成、视频生成、模型微调等多个领域支持一键部署。