sql - 数据分析 | 用 SeaTable 表格快速统计分析订单数据 - SeaTable开发者版

SeaTable 是一个支持多种数据类型的协同表格和信息管理工具。它不仅方便记录大量结构化的数据，还提供了多种数据处理、分析和可视化的功能。本文将介绍如何综合地使用 SeaTable 的 SQL 插件、数据链接功能、函数计算功能，来从一份在线订单数据中分析出每年的复购比例、用户留存比例。

通过本文，我们会看到，相比于 Excel，SeaTable 在数据分析方面有以下的优势：

相比于传统的数据库，SeaTable 则更加直观易用：

数据集介绍

如下图，这是一张软件产品的在线订单数据表，记录了所有订单的详细信息，如 ID、用户名、付费金额、创建时间（首次付费时间）等等。其中软件是以年付费的形式销售的，用户需要每年续费一次以便继续使用软件。Price 记录的是美元价格。

我们添加 SQL 插件，并且放置到工具栏后，就可以快速地进行数据查询了。

首先，我们想查询所有订单的数量，那么 SQL 语句如下：

select count(*) from Orders

count(*) 表示对所有条目数量的计数，结果为 1855，表示目前为止总共的订单数量有 1855 个。

接下来，我们想看一下，这些订单来自多少个不同的用户，那么语句如下：

select count(distinct User) from Orders limit 1000

同上，用 count 函数，distinct 表示去重，相同的值只保留一次。结果为 598，表示总共购买的用户有 598 人。

那么在这么多用户中，我们想看一下， 2020 年付费能力最强的前十个用户是哪些人，则可以写入如下语句：

select User, sum(Paid) from Orders where `Created At` > '2020-01-01' and `Created At` <= '2021-01-01' group by User order by sum(Paid) desc limit 10

我们将付费时间限定为 2020 年，对用户的付费金额求和，并且以付费金额的大小来降序显示十条记录。

SeaTable 中的日期一般用 ISO 国际标准格式来保存。它提供了 ISODate 和 ISOMonth 两个函数来帮助我们按照日期对数据进行统计分析。

当我们想要查看本月的付费金额时，我们可以用时间函数 ISOMonth 来对月份进行过滤。

计算本月付费金额，语句如下：

select sum(Paid) from Orders where ISOMonth(`Created At`) = ISOMonth(today())

计算上月付费金额，语句如下：

select sum(`Paid`)  from Orders where ISOMONTH(`Created At`) = ISOMONTH(dateAdd(today(), -1, "months"))

订单表中的数据是以订单为单位记录的，要分析用户的续订比例，我们首先需要得到一张以用户为单位的表。

我们用下面的 group by 语句，可以得到每个用户的首次购买时间和最后购买时间，查询出结果后，导出为一个新的子表——用户购买时间表。

select User, min(`Created At`), max(`Created At`) from Orders group by User limit 2000

接下来新建 {First year} 和 {Last year} 两个公式列，求出首次购买年份和最后购买年份。

这样我们就可以进行查询了，首先得到 2019 年新增的购买人数为 110 人。

select count(*) from Users where `First year`=2019

然后可以查找出首次购买时间在 2019 年，且最后购买时间是 2019 年之后的人，是 65 人。由此我们得出 2019 年新用户的续订比例为 59.0%。

select count(*) from Users where `First year`=2019 and `Last year`>2019

下面来进行一个比较复杂的分析，也就是 2020 年复购金额占比。我们用数据处理的"自动关联"建立订单表和上面得到的用户表的关联关系。对于 2020 年的每个订单，如果这个用户首次购买时间早于 2020 年，那么我们认为这是一次复购。

因此，首先我们在已得到的用户表中，使用链接列和自动添加链接功能，为 Orders 表和用户购买时间表建立关联，以方便引用 {First year} 列到 Orders 表中进行计算。

建立关联后，我们将 {First year} 列用链接公式引用到 Orders 表中，然后再新建两个公式列，

用日期公式计算出 {Created year}——购买年份
标记出复购的用户：当 {Created year} 大于 {First year} 时，那么我们认为属于复购情况，显示为 1，否则显示为 0

接下来我们就可以进行查询了，首先查询 2020 年订单总金额，为 106627.5 。

然后我们查询 2020 年的复购订单金额，为 89915.5 。因此 2020 年复购金额占比为 84.3%。

以上，我们用 SeaTable 轻松又快速地完成了在线订单数据的分析工作，是不是非常方便呢。SeaTable 的功能不仅丰富，而且也很简单易用，大家可以根据具体场景灵活搭建出自己的业务应用。