作者:Jean-Nicholas Hould,JeanNicholasHould.com
我最近写了一篇关于为什么你应该 学习 SQL 进行数据分析 的文章。我收到了很多反馈,人们希望看到一些使用 SQL 进行数据分析的具体例子。我决定应用一些我自己的建议,加载其中一个 超赞的数据集 进行一些基本的数据探索查询。
1. 谷歌网络安全证书 - 快速进入网络安全职业生涯。
2. 谷歌数据分析专业证书 - 提升你的数据分析能力
3. 谷歌 IT 支持专业证书 - 支持你的组织 IT
对于这篇文章,我使用了一个开放数据集。百万歌曲数据集是一个*“自由获取的现代流行音乐曲目音频特征和元数据的集合”*。
如果你想跟随,请按照以下步骤操作:
-
下载百万歌曲数据集。我使用了 10K 歌曲子集。
-
下载一个 SQLite 客户端
-
使用 SQLite 打开
subset_track_metadata.db
文件并开始探索
当我开始分析新的数据集时,我会运行一些基本查询以了解数据的组织方式和数值的分布情况。我试图理解我所处理的内容。对于数值数据,我通常会运行一些描述性统计:我会测量中心趋势(均值、中位数、众数)并测量数据的变异水平。这些测量通常是数据探索的良好起点,常常会引发新的问题,推动我的分析。
均值
均值是将一组数字相加后除以该组总数得到的数字。均值对异常值非常敏感。它可能会被远高于或低于其余数据集的值严重影响。
SELECT CAST(AVG(songs.year) as int) as avg_year FROM songs
-- | avg_year |
-- |----------|
-- | 934 |
-
CAST
:在兼容数据类型之间进行运行时数据类型转换。在这种情况下,我将浮点数转换为整数以进行四舍五入。 -
AVG
:聚合函数,返回输入表达式值的均值。 -
as avg_year
:临时重命名列标题 - 仅用于提高可读性,使代码更具人性化。我将在整个帖子中使用这种别名。
中位数
中位数是将有序数据集的上半部分与下半部分分开的数字。中位数有时是衡量中点的更好指标,因为每个数据点的权重相等。
SELECT songs.year as median_year
FROM songs
ORDER BY songs.year
LIMIT 1
OFFSET (SELECT COUNT(*) FROM songs) / 2
-- | median_year |
-- |-------------|
-- | 0 |
-
ORDER BY
:按一列或多列对结果数据集进行排序。排序可以是升序ASC
(默认)或降序DESC
。 -
COUNT
:聚合函数,返回符合条件的行数。 -
LIMIT
:指定从结果数据集中返回的最大行数。 -
OFFSET
:在开始返回行之前跳过 X 行。在这个特定示例中,我们跳过了 5000 行(相当于总行数COUNT(*)
除以 2)。
众数
众数是数据集中出现频率最高的值。
SELECT
songs.year,
COUNT(*) as count
FROM songs
GROUP BY songs.year
ORDER BY COUNT(*) DESC
LIMIT 1
-- | year | count |
-- |------|-------|
-- | 0 | 5320 |
GROUP BY
:与聚合函数如COUNT
、AVG
等一起使用。按一列或多列对结果数据集进行分组。
最小/最大值
数据集中的最小/最大值。
SELECT
MIN(songs.year) as min_year,
MAX(songs.year) as max_year
FROM
songs
-- | min_year | max_year |
-- |----------|----------|
-- | 0 | 2010 |
-
MIN
:聚合函数,返回数据集中最小的值。 -
MAX
:聚合函数,返回数据集中最大的值。
每年的歌曲分布
每年发行歌曲的数量
SELECT
songs.year,
COUNT(*) songs_count
FROM songs
GROUP BY songs.year
ORDER BY songs.year ASC
-- | year | song_count |
-- |------|------------|
-- | 0 | 5320 |
-- | 1926 | 2 |
-- | 1927 | 3 |
-- | ... | ... |
-- | 2009 | 250 |
-- | 2010 | 64 |
这篇文章中的 SQL 查询相对简单。它们不是技术性技巧的结果。它们只是帮助我们理解数据集的简单度量,这正是它们的优点。
在这个特定的数据集中,我们注意到超过一半的数据集中,歌曲的年份为0
。这意味着我们要么在查看非常旧的歌曲数据集,要么在处理缺失值。后者更为现实。如果我们过滤掉年份为0
的歌曲,我们的数据就更有意义了。歌曲的年份范围从1926
到2010
,中位数是2001
年。
数据部分清理后,我们可以开始探索数据集中的其他列,并提出更多问题:每年有多少独特的艺术家创作歌曲?这种情况随时间的变化如何?现在的歌曲是否比以前短?这些简单的度量指标的优点在于,它们可以在查询和过滤器变得越来越复杂时重复使用。通过应用简单的描述性统计度量,我们可以很好地掌握数据,并不断深入探索。
简历:Jean-Nicholas Hould 是来自 加拿大蒙特利尔的数据科学家。作者,见 JeanNicholasHould.com。
原文。经许可转载。
相关:
-
掌握数据科学 SQL 的 7 个步骤
-
数据科学统计学 101
-
构建与购买 – 分析仪表盘