乐趣区

关于sql:数据分析-用-SeaTable-表格快速统计分析订单数据

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

通过本文,咱们会看到,相比于 Excel,SeaTable 在数据分析方面有以下的劣势:

  • 反对 SQL 查问,能够实现绝对简单的数据查问。
  • 反对数据的扩表关联。一些场景下,只有扩表关联才能够实现数据的剖析,比方订单表须要和用户表关联。
  • 可能解决 10 万行甚至百万行以上的记录。

相比于传统的数据库,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 年,对用户的付费金额求和,并且以付费金额的大小来降序显示十条记录。

用 ISOMonth 函数剖析本月和上月数据

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"))

剖析 2019 年首次购买的用户的续订比例

订单表中的数据是以订单为单位记录的,要剖析用户的续订比例,咱们首先须要失去一张以用户为单位的表。

咱们用上面的 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 年的每个订单,如果这个用户首次购买工夫早于 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 的性能不仅丰盛,而且也很简略易用,大家能够依据具体场景灵便搭建出本人的业务利用。

退出移动版