使用SQL进行数据分析入门指南

概述

本网络研讨会由Ethan Robish主讲，重点介绍使用SQL进行数据分析的基础知识和高级技巧。内容涵盖SQL的基本语法、数据库类型比较，以及通过实际查询示例展示如何有效探索和操作数据。研讨会还引入了DuckDB，一个强大的分析数据库工具，适用于数据科学工作流。

SQL简介

SQL（Structured Query Language）是用于查询数据库的结构化查询语言。它在网络安全和数据库管理中广泛应用，包括数据探索、聚合和窗口函数的使用。SQL是声明式语言，用户描述所需结果，而数据库引擎优化执行过程。

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

事务型数据库（如MySQL、PostgreSQL）：
- 数据以行格式存储，优化单行操作。
- 适用于频繁更新和读取单个记录的场景。
分析型数据库（如DuckDB）：
- 数据以列格式存储，支持快速聚合查询。
- 处理大规模数据（TB级），适合数据分析工作流。

DuckDB：分析型数据库的SQLite

DuckDB是一个轻量级、无服务器的分析数据库，支持从文件直接读取数据（如CSV、JSON、Parquet）。安装简单，只需下载单个二进制文件即可使用。示例命令：

1
2
3
4


-- 启动交互式会话
duckdb
-- 从CSV文件查询数据
SELECT * FROM 'mark_geo.csv' LIMIT 10;

数据探索与基本查询

使用SELECT语句进行数据探索：

SELECT * FROM table：查看所有数据。
DESCRIBE：查看列结构和数据类型。
SUMMARIZE（DuckDB特有）：生成描述性统计（如最小值、最大值、平均值）。示例：

1
2
3
4


-- 查看数据前10行
SELECT * FROM 'mark_geo.csv' LIMIT 10;
-- 使用SUMMARIZE获取统计信息
SUMMARIZE SELECT * FROM 'mark_geo.csv';

数据过滤与聚合

WHERE子句：过滤数据（例如，WHERE country = 'United States'）。
聚合函数：如COUNT、SUM、AVG，用于汇总数据。
GROUP BY和HAVING：分组数据并过滤聚合结果（例如，按主机分组计数）：

1
2
3
4


SELECT host, COUNT(*) AS cnt 
FROM 'mark_geo.csv' 
GROUP BY host 
HAVING cnt > 100000;

窗口函数

窗口函数允许在保留原始行的情况下执行计算（如滚动平均值、运行总计）：

OVER子句定义窗口范围。
示例：计算每台主机的运行总计：

1
2
3


SELECT host, datetime, source_port,
       SUM(source_port) OVER (PARTITION BY host ORDER BY datetime) AS running_total
FROM 'mark_geo.csv';

数据连接（JOIN）

JOIN用于丰富或过滤数据集：

内连接（INNER JOIN）：返回匹配的行。
左连接（LEFT JOIN）：返回左表所有行，右表匹配行。示例：将端口数据与IANA服务信息连接：

1
2
3
4


SELECT m.*, i.service
FROM 'mark_geo.csv' AS m
LEFT JOIN 'iana_ports.csv' AS i 
ON m.protocol = i.transport_protocol AND m.destination_port = i.port;

总结

SQL是数据分析的强大工具，尤其适用于处理结构化数据。DuckDB作为轻量级分析数据库，简化了数据查询和集成流程。通过掌握基本查询、聚合、窗口函数和连接技术，安全专业人员可以更高效地分析日志和威胁数据。