使用SQL和Datapane构建交互式Python仪表板

使用SQL和Datapane构建交互式Python仪表板

数据库交互是数据分析中很重要的一个技能。大多数组织将其业务关键数据存储在关系数据库中,如Postgres或MySQL,你需要了解结构化查询语言(SQL)来访问或更新存储在那里的数据。

SQL是一种不可思议的语言——它发明于20世纪70年代,大多数新的数据库项目,如Redshift, Snowflake, CockroachDB仍然选择它作为他们的基础,因为它强大且相对简单。它擅长检索数据,同时可以配合Tableau或Looker等进行可视化结果呈现。

在本文中,将展示如何结合SQL和开源Python库来构建web上的交互式仪表板。

数据集

我们将使用一个名为Chinook的开源数据集,它存储了一家小型音乐商店几年来的购买数据。该数据作为SQLite数据库存储在单个文件中,我们可以直接从本地Python环境中查询它,而无需访问凭证。

下面是一个显示数据库模式的快速表格:

Image by author, inspired by arjunchndr

  • 数据库被分成代表不同逻辑对象的单独表。例如,所有客户都在客户数据库中。这与传统的 Pandas 数据框不同,后者通常只是一个表。
  • 每个表具有一组字段,它定义了表的属性,并且行,其中存储实际数据。这些就像 Excel 中的列和行。
  • 表之间的关系由称为外键的特殊字段表示。例如,每首曲目都有一个专辑 ID 链接到专辑表,这意味着每首曲目都是专辑的一部分。当我们查询数据库中,我们会经常一起一起上自己的外键不同的表。

连接到数据库

我们将首先定义一些用于连接数据库的辅助函数:

将 SQL 查询编写为字符串,然后在 Python 函数中执行它。像这样将两种不同的语言混合在一起有点尴尬,但是一旦掌握了它,效果就会出奇的好!运行上面的代码会给出数据库中所有不同的表名以及每个表中的行数:

构建仪表板

因此,让我们假设您刚开始在 Chinook 担任数据分析师,老板想知道最畅销的曲目,以便他们可以在网站上宣传这些曲目。要回答这个问题,我们需要写一个这样的查询:

我们首先选择我们想要在输出中看到的列。由于这些来自四个不同的表(track、album、artist、invoice_line),我们需要在外键关系上连接这些表——检查模式以查看可能的情况。接下来我们按曲目名称分组,按购买次数最多排序,然后只选择前 10 个结果。

运行查询为我们提供了一个 pandas 数据帧,然后我们将其包装在一个DataTable块中

这会生成一个交互式表格,用户可以对其进行过滤和排序。

更复杂的查询

因为我们最近的成功,老板又问了一个更复杂的问题——“谁是我们业绩最好的销售员工,他们什么时候的销售业绩最好?”

我们可以使用像 Plotly 这样的 Python 可视化库来构建更令人满意的交互式图表,而不是用表格来回答这个问题:

这个查询比前一个查询复杂得多:

  • 我们使用 strftime 截断时间戳,以便我们可以按月分组。当我们绘制数据时,这使数据看起来更好。
  • 我们使用子查询(也称为公共表表达式)来计算中间表 (customer_support_rep_sales)。然后我们在最终查询中调用这个表。
  • 一些操作可以在 Pandas 或 SQL 中完成——例如,我们在 Pandas 中执行 groupby 以删除第二个图表的“月份”列。我更喜欢在 SQL 中进行最复杂的连接和分组,并在必要时仅使用 Python 来格式化结果。

运行上面的代码会给我们以下结果:

看起来每月的表现相当不稳定,没有明显的季节性影响。Jane 是销售业绩最好的人,领先 Steve 约 20%

完整的仪表板

可以尝试编写复杂的 SQL 查询并使用任何 Python 库将它们可视化。在仪表板的完整版本中,这里添加了一些额外的图表和可视化:

  • 总客户、员工和销售曲目的汇总统计
  • 销售类型
  • 按国家划分销售和客户
  • 平均订单百分比差异与按国家/地区划分的平均值
  • 客户生命周期价值

本文中简单讲解了如何通过在 Python 中编写 SQL 语句、使用 Plotly 绘制结果并与 Datapane 共享来构建交互式仪表板。

这是快速原型化想法或构建标准 BI 平台中没有的更复杂的可视化的好方法。

作者:John Micah Reid

免责声明:凡未注明来源或者来源为网络的信息均转自其它平台,是出于传递更多信息之目的,并不意味着赞同其观点或证实其内容的真实性。网站只负责对文章进行整理、排版、编辑,不承担任何法律责任。若有侵权或异议请联系我们删除,谢谢。

发表评论

您的电子邮箱地址不会被公开。