sql怎么分析数据(使用 SQL 分析 iMessage)

SQLite 是一种经常被忽视的 SQL 引擎。一些人认为它是现有的最多产的 SQL 引擎，因为它具有高度的灵活性，并且能够在几乎任何资源有限的平台上运行。与 MySQL、PostgreSQL、MSSQL 或 Oracle 等其他 SQL 引擎不同，SQLite 无需服务器即可运行。SQLite 不依赖于数据目录或持续运行的守护进程：数据库封装在单个文件中。

SQLite 和 iMessage

iMessage 是当今最流行的消息传递平台之一，主要是因为它内置于 iOS 和 Mac 设备中。自发布以来，它已经发生了重大变化。但是，它的核心只是一个即时通讯平台。iMessage 在后台使用 SQLite 来存储有关消息、对话及其参与者的关系数据。

作为 Apple 的老用户，从 2009 年 11 月 10 日第一次使用 iPhone 开始，我就备份和传输了我的 iPhone 数据。因为我一直在数字化囤积我的文本数据，所以我的 iMessage 数据库几乎1GB 大小。

直到几年前，iMessage 的内置搜索功能还非常有限且漏洞百出。尽管它最近有了显着改进，但与几乎任何最终用户工具一样，它在查询方式上非常有限。我们这些经常处理受限前端数据的人通常希望我们能够直接访问 SQL 数据库。幸运的是，iMessage 数据库并非不可访问——事实上，它非常容易访问。

查找 iMessage SQL 数据库

在你的 Mac 上

如果您在 Mac 和 iPhone 上启用了 iMessage，那么您有 2 个不同的数据库可供选择。Mac 上的数据库很容易找到，因为它位于~/Library/Messages/chat.db. 如果您不将 Mac 用于 iMessage，或者在我的情况下，您的 Mac iMessages 没有回溯到那么久，您可以通过对 Mac 执行备份来提取 iPhone 的数据库。

在你的 iPhone 上

按照以下说明提取 iPhone 的 iMessage 数据库：

1. 打开 Finder 并在“位置”下选择您的 iPhone。
2. 找到“备份”部分并选择“将 iPhone 上的所有数据备份到此 Mac”，然后按“立即备份”立即创建新备份。此过程可能需要一段时间。
3. 完成后，您将在/Users/[username]/Library/Application Support/MobileSync/Backup/[backup name]/3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28.
4. 如果您打算使用 Arctype 打开此数据库，您需要复制并重命名该文件，并使用.db扩展名表明它是一个 SQLite 文件。

SQLite 入门

与大多数 SQL 服务器不同，您不需要连接字符串、主机或用户名即可连接到 SQLite 数据库。您需要做的就是将您的 SQL 客户端指向数据库文件。

使用 Arctype

1. 在“连接”下拉列表中，选择“添加新数据源”
2. 选择“SQLite”
3. 找到 SQLite 数据库文件。该文件必须具有 .sqlite3 或 .db 扩展名，Arctype 才能打开它。

更详细的说明可以在Arctype Docs中找到。

使用命令行

在 UNIX 终端中，键入sqlite3 [filename]。

iMessage 架构

关于 Arctype，我最喜欢的部分之一是分析数据库模式是多么容易。我是命令行工具和老式编辑器的长期用户，但有时拥有更具视觉交互性的工具是救命稻草。让我们深入了解 Apple 为 iMessage 创建的架构。今天我们将重点介绍chat、message和handle表，以及一些连接表以连接相关记录。

请注意，我创建了一个名为的自定义视图handle2，它添加了一个id2混淆我的个人联系人的电话号码和电子邮件地址的字段，您将在本文的示例中看到此视图的引用。

深入了解 iMessage

让我们编写一些查询并进行一些观察，如果没有直接的 SQL 访问，这些是不可能的。

用旧信息激起你的怀旧之情

首先，让我们从一个简单的查询开始，以查看您的前 50 条消息。如果您的聊天线程可以追溯到多年前，则没有简单的方法可以从您的 iPhone 或 Mac 访问早期消息。

两个平台上的界面都要求您一次回滚大约 25 条消息。这非常耗时，如果用户在您向后滚动时向您发送新消息，可能会导致崩溃或重置。

幸运的是，我们有自定义 SQL 来拯救我们：

select
   h.id2 as sender_name,
   m.text as message_body
from
   message m
   join handle2 h on h.rowid = m.handle_id
order by
   m.date
limit
   50;

handle.id表示用户的可读标识符。它将是电话号码或电子邮件地址。

用 SQL 评价你的友谊

让我们使用 SQL 找出我们最好的朋友是谁。假设您将友谊的质量视为发送短信数量的函数，这应该非常准确！

首先，让我们将消息数除以from_me不产生回复率的消息数。此查询按消息总数以及回复率显示了我们发送消息的前 10 个人。

乘以 1.0 强制转换为REAL数据类型以避免整数除法，这将导致 1 或 0 而不是小数。您可以使用此处的链接查看 SQLite 中整数除法的规则。

select
    h.id2,
    count(1) as cnt,
    round(
      sum(
        case
          when m.is_from_me then 1
          else 0
        end
      ) * 1.0 / count(1) * 100.0,
      2
    )
from
    message m
    join handle2 h on h.rowid = m.handle_id
group by
    h.id
order by
    cnt desc
limit
    10;

这种分析的一个问题是，发送的消息越少并不一定意味着发送的字数越少。让我们添加更多字段以获得更好的洞察力。

在这里我们可以看到发送和接收的总字符数、发送和接收的文本消息的平均长度、发送和接收字符的总比例以及回复比例。就我而言，我倾向于收到更多消息的人发送的消息也比我长。

select
    h.id,
    count(1) as cnt,
    sum(length(m.text)) as chars,
    sum(length(m.text)) filter (where m.is_from_me) as chars_sent,
    sum(length(m.text)) filter (where not m.is_from_me) as chars_received,

    round(avg(length(m.text)) filter (where m.is_from_me)) as avg_length_sent,
    round(avg(length(m.text)) filter (where not m.is_from_me)) as avg_length_received,

    round((sum(length(m.text)) filter (where m.is_from_me) * 1.0 / sum(length(m.text)) filter (where not m.is_from_me)), 2) as characters_sent_ratio,
    round((count(1) filter (where m.is_from_me)) * 1.0 / (count(1) filter (where not m.is_from_me)), 2) as reply_ratio
from
    message m
    join handle h on h.rowid = m.handle_id
group by
    h.id
order by
    cnt desc
limit
    10; 

此查询大量使用聚合过滤器。WHERE聚合过滤器允许您通过指定一个子句来过滤掉不需要的记录，从而仅对部分数据使用聚合函数。

检查 iMessage 反应

有 2 个较新的 iMessage 功能，在其架构设计的上下文中，它们的实现很有趣。最近宣布，Android 手机将能够正确显示 iMessage 的“反应”。从历史上看，如果您向非 Apple 设备发送 iMessage 响应，它将显示为文本添加而不是图标。

随着与 Android 设备的新兼容性的宣布，我很想知道该功能的当前实现是如何工作的。

我SELECT编辑了一些有反应和没有反应的记录，并比较了结果。我发现该associated_message_type列通常设置为 0，但在有反应的消息中，它是 2000-2005 之间的整数值。我也注意到它associated_message_guid的存在。Apple 似乎将 2000-2005 用于其 5 种反应类型，3000-3005 用于用户删除反应的时间，3 用于 Apple Pay 请求。

根据这项调查，似乎反应是作为 iMessage 发送的，附加了反应的文本等价物以及与父消息的外键关系。这允许非 Apple 设备无缝发送和接收消息。

如果消息是通过 SMS 发送的，那么将反应链接到它所引用的消息的元数据就会丢失。如果设备支持 iMessage，Apple 设备将忽略消息的text一部分，找到相关的消息并添加适当的反应作为视觉覆盖。

请注意，该message表包括 aROWID和 a guid。ROWID是一个典型的自增整数id字段，对于加入本地数据库很有用。但是，对于跨设备的相同消息，自动递增的主键将不同。它GUID是全球唯一的，由消息的作者生成，并发送给它的所有收件人。这允许跨不同数据库、设备和用户的外键引用。有关 GUID 实用程序的更多信息，请查看这篇文章。

查找您最受欢迎的群聊

群聊存储在chat表中。加入表格chat_handle_join，chat_message_join用于将用户和消息分别与群聊相关联。这是一个查询，用于找出最常用的群聊（与 > 1 个成员聊天）及其参与者的身份。

select
    group_concat(distinct h.id2) as participants,
    count(m. "ROWID") as message_count
from
    chat c
    join chat_handle_join chj on chj.chat_id = c."ROWID"
    join handle2 h on h. "ROWID" = chj.handle_id
    join chat_message_join cmj on cmj.chat_id = c."ROWID"
    join message m on m. "ROWID" = cmj.message_id
group by
    c."ROWID"
having
    count(distinct h.id) > 1
order by
    message_count desc
limit
    10

该group_concat函数与 MySQL 同名，PostgreSQL 用户熟悉string_agg，是一个将字符串连接在一起的聚合函数。在此处查看有关如何在 SQLite 中使用它的更多信息。

该HAVING子句类似于WHERE子句，但对聚合函数进行操作。如果您想编写一个以聚合为条件的查询，但不能在您的WHERE子句HAVING中，那么您可以使用.

结论

SQLite 是一个强大的工具，其跨设备和众多用例的广泛影响使其成为最令人印象深刻的软件项目之一。如果您对幕后的内容感到好奇，那么众所周知，SQLite 的源代码组织良好且有趣（嗯，对我们中的一些人来说）可以窥探。

iMessage 只是依赖 SQLite 并被数百万最终用户使用的众多软件之一。您可以免费试用Arctype 等SQL 客户端，并开始探索为您日常使用的工具提供支持的数据库！