【SQL周周练】：利用行车轨迹分析犯罪分子作案地点

大家好，我是“蒋点数分”，多年以来一直从事数据分析工作。从今天开始，与大家持续分享关于数据分析的学习内容。
本文是第 7 篇，也是【SQL 周周练】系列的第 6 篇。该系列是挑选或自创具有一些难度的 SQL 题目，一周至少更新一篇。后续创作的内容，初步规划的方向包括：
后续内容规划

1.利用 Streamlit 实现 Hive 元数据展示、SQL 编辑器、 结合Docker 沙箱实现数据分析 Agent
2.时间序列异常识别、异动归因算法
3.留存率拟合、预测、建模
4.学习 AB 实验、复杂实验设计等
5.自动化机器学习、自动化特征工程
6.因果推断学习
7. ……
欢迎关注，一起学习。
第 6 期题目

题目来源：纯自创题目，受到《The SQL Murder Mystery》的启发，它是一个用 SQL 来寻找凶手的题目；我玩过之后，很受启发，想出一个【SQL 破案系列】，但是我想象力不够、文笔一般。剧情又要设计为 SQL 解题，只能放弃这个方向。今天拿出来一道之前设计的题目：尽管文笔和设计上有很多不足，但有点意思。最关键，我在 8 年的数据分析经历，从来没有在 SQL 中使用过三角函数，而这道题目就需要使用三角函数
一、题目介绍

大家可以先看看故事背景，这是两个多月前写的。写作方法是我提一个梗概，然后让 Deepseek 或者 Qwen 润色和发散；等它们返回来结果，我再吸收和更改；然后再提问再更改，如此往复五次以上。我还没有尝试过 Gemini 2.5 pro 或 GPT 来写。
对故事不感兴趣的同学可以跳过，只是故事情节对题目理解略有帮助：

1. 凌晨三点，T 市西郊分局的走廊映着惨白的荧光，袭来一种不真实感。刑警队长王泽宇脚步急促，径直走向拘留室区域，值班台的警员小李正在踱步抵抗困意。
2. “王队，您亲自来了，要提审谁？” 小李看到王队后一个激灵。话音未落，警员老张默默地拉开通道的铁门。
3. 向两名值班警察点头示意后，王队走入了通道，脚步声不多时便被走廊吞噬。只剩下微弱的电流声在空旷中回荡。
4. 七拐八拐的，王队停在了一间隐蔽的拘留室，这片区域只关押着白辉 —— 一个游走在灰色地带的小混混，也是王队的线人。
5. 隔着铁栅栏望过去，白辉仰面躺在床板上，双眼盯着天花板。显然，已经察觉到了有人过来。
7. “怎么，什么话不能在外边说？非得喝酒闹事，这个点在这儿见面。” 王队有些嗔怪。
8. 白辉佝偻着背坐起身来，无奈笑道，“道上兄弟打个喷嚏，隔天一圈全得流感。今天我收别人的风，明天别人就放我的料。”
9. 王队跨前半步，余光撇了眼走廊的摄像头，压低声音，“说吧。”
10. “来了批过江龙，硬点子。可能 H 省的。” 白辉顿了顿，“老 K 您知道吧？”
11. 王泽宇点头示意他继续，“老 K 的架生和烟花生意别说在 T 市，北方也是一号啊。遇到这帮人，也怕了。”
12. “这伙人找老 K 买枪弹和炸Y，搞黑吃黑？”王队眉头紧锁，脑海中飞速思考。
13. 白辉咧咧嘴，“老 K 这种老狐狸，卖架生，小弟们都背着雷管。”
14. “这种交易钱货两清必须快，没想到这伙人在交易地点附近开车绕圈。” 白辉食指划了两个圈，“外围望风小弟立刻通知老 K，老 K 都准备撤了”
15. “结果一道红色激光瞄准了老 K 的胸口…… “ 白辉差点呛住 ”虽然最后交易成功了，但老 K 到家腿还软着。“
16. “不是老 K 吓坏，这消息能走漏么。” 白辉嘘了口气。
18. 王队追问“还有什么消息。”，焦急情绪溢于言表。
19. 白辉摇摇头，“只听说有一伙人定了两条大飞，三天后。”
21. 三分钟后，王队已经赶到了技术科，分析师 J 正揉着发酸的后颈。
22. “情况紧急，小 J…… 你立刻用道路监控系统分析近期全市车辆的轨迹，排查可疑车辆。” 老王语气急促。
23. “我刚才已经通知了局长，局长命令半小时后开会”。
25. 分析师 J 飞速敲着键盘，道路监视系统采集的海量视频影像，经过算法逐渐转为结构化数据流入集群。
26. “还有 10 分钟”，J 撇了眼屏幕右下角的时间，没注意到王泽宇已经离开，心想。“用这伙人踩点的特征去追踪……”

复制代码

有一张数据仓库的表，里面是道路影像视频资料根据 CV 算法分析得到的（咱这儿就当小说不管现实可行性）。表里有如下的数据：时间，车牌号，纬度，经度（假设摄像头拍到车牌后，根据单目测距算法和摄像头本身的坐标计算出车辆的坐标）
我们假设这个故事中摄像头密度很高，利用不同摄像头记录的车辆的坐标和时间差，计算这一小段距离的平均速度。如果平均速度在某个范围内视为“疑似踩点”，在另一个范围视为“正常行驶”；并且故事设计犯罪分子会驾车不多不少刚好围着作案地点附近绕行一圈，这样利用坐标的均值就可以求得“质心/中心点”。
题目规定就是求出这个“质心/中心点” —— 也就是谋划犯罪的地点。
列名数据类型注释tsstring时间
（为了计算准确，这里精确到微秒）licence_platestring车牌lagtitudedouble纬度longtitudedouble经度is_case_the_jointinyint是否有为踩点
（做题时不用，为了验证数据的
1-是，0-否）部分样例数据（完整生成逻辑参见第三节）
tslicence_platelagtitudelongtitudeis_case_the_joint2025-06-01 09:45:40.846060J-987639.116034117.19455702025-06-01 09:45:44.176520J-987639.116105117.19407402025-06-01 09:45:47.831298J-987639.116346117.19439602025-06-01 09:45:52.131025J-987639.116621117.1948360..............................2025-06-01 10:13:39.177957J-987639.115997117.19536102025-06-01 10:13:43.865901J-987639.11603117.1946750注意，模拟数据时为了简化，只设置了一个辆车即一个车牌，但是我写 SQL 的时候没有忽略掉这个维度，按照有多辆车的写法来处理。
另外，我在模拟数据时，让这个车绕了多个地点 —— 多个地方“踩点”。
二、题目思路


想要答题的同学，可以先思考答案
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！