SQL数据分析入门

概述

本网络研讨会最初发布于2024年11月21日，由Ethan Robish主讲，重点介绍了使用SQL进行数据分析的基础知识和高级技巧。内容涵盖SQL的基本概念、数据库类型、实际查询示例，并引入了DuckDB这一强大的数据分析工具。

SQL基础与优势

SQL（Structured Query Language）是用于查询数据库的标准化语言，具有以下优势：

• 广泛适用性：多数数据库支持SQL标准，知识可跨平台迁移
• 声明式语法：用户只需描述所需结果，由查询引擎优化执行过程
• 强大功能：支持复杂数据操作，尽管查询可能变得冗长
• 互操作性：几乎所有编程语言都提供SQL接口

数据库类型：事务型 vs 分析型

• 事务型数据库（如MySQL、PostgreSQL）：

  • 数据以行格式存储
  • 优化单行读写操作
  • 适合OLTP（联机事务处理）场景

• 分析型数据库（如DuckDB）：

  • 数据通常反规范化存储
  • 支持列式存储，适合大规模数据分析
  • 处理TB级甚至PB级数据

DuckDB介绍

DuckDB是新兴的分析型数据库，具有以下特点：

• 类似SQLite的嵌入式设计，无需单独服务器
• 单文件存储，易于部署和使用
• 支持直接读取CSV、JSON、Parquet等文件格式
• 提供丰富的分析函数和窗口函数支持

数据探索实践

使用2013年蜜罐数据（Marks Geo CSV）演示：

1. 初步查看：SELECT * FROM 'marks_geo.csv' LIMIT 8
2. 结构分析：DESCRIBE SELECT * FROM 'marks_geo.csv'
3. 统计摘要：使用DuckDB特有的SUMMARIZE命令获取描述性统计

SQL查询核心操作

1. SELECT基础：

   • 选择特定列：SELECT host, datetime FROM ...
   • 使用别名：SELECT COUNT(*) AS cnt FROM ...
   • 数学运算：运行时计算能力

2. 数据过滤：

   • WHERE子句：基于条件过滤行
   • IN操作符：多值匹配
   • BETWEEN：范围查询（支持数字和时间戳）

3. 数据聚合：

   • GROUP BY：分组统计（如每台主机的连接数）
   • HAVING：对聚合结果过滤
   • 常用函数：COUNT, SUM, AVG, MIN, MAX等

高级窗口函数

窗口函数允许在保留原始行的同时执行计算：

• 滚动统计：计算移动平均值（如最近3行的平均值）
• 累计求和：按时间顺序计算运行总量
• 百分比计算：某类数据在总体中的占比
• 使用QUALIFY子句（DuckDB特有）过滤窗口函数结果

数据连接（JOIN）操作

通过连接IANA端口服务数据增强原始数据集：

• 左连接：保留所有原始记录，匹配时添加服务信息
• 实际应用：将端口号映射为服务名称（如143端口→IMAP服务）

总结

SQL是强大的数据分析工具，尤其适合处理结构化数据。DuckDB作为轻量级分析数据库，降低了SQL分析的门槛。从基础查询到高级窗口函数，SQL提供了丰富的数据操作能力，值得安全分析师和数据工程师深入学习掌握。