SQL数据分析实战常用查询语句与聚合函数应用指南-SQL-PHP中文网

sql数据分析的核心在于熟练掌握select、where、group by、having、join等基础查询语句与聚合函数，1. select和from用于指定查询字段和数据来源；2. where实现数据筛选；3. group by配合count、sum、avg等聚合函数进行数据汇总；4. having对聚合结果二次筛选；5. join连接多表整合信息；6. 子查询和cte处理复杂逻辑提升可读性；7. 窗口函数如row_number、lag、avg over实现高级分析；同时需避免select滥用、where中使用列函数、join不当、or过多、大数据distinct及忽略null和数据类型等问题，通过取所需字段、创建索引、分析执行计划、分步调试等方式优化性能。

SQL数据分析实战常用查询语句与聚合函数应用指南

在数据分析的日常里，SQL无疑是我们最得力的助手之一。它不仅仅是一种查询语言，更是一种思考数据、洞察规律的工具。尤其是在面对海量数据时，熟练运用其常用查询语句和聚合函数，能让我们事半功倍，快速从混沌中理出头绪，找到我们真正需要的信息。

SQL数据分析，说白了，就是通过一系列指令，从数据库这个巨大的“仓库”里，把我们想要的数据“取”出来，再按我们的需求进行“加工”和“汇总”。这个过程，从最基础的筛选、排序，到复杂的连接、聚合，每一步都至关重要。我个人觉得，掌握这些基本功，就像是学会了开车，能让你在数据的海洋里自由驰骋。

常用查询语句与聚合函数实践

我们来聊聊那些最常用也最核心的SQL语句和函数。

首先，SELECT 和 FROM 是基石，这不用多说，就像你要从仓库里拿东西，得先知道拿什么（SELECT 列）从哪个仓库拿（FROM 表）。

SELECT
    column1,
    column2
FROM
    your_table;

登录后复制

接着是 WHERE，这是数据筛选的利器。你不想把所有东西都搬出来，只想拿符合特定条件的。比如，我只想看2023年的销售数据，或者某个特定产品类别的。

SELECT
    product_name,
    sales_amount
FROM
    sales_data
WHERE
    sale_date BETWEEN '2023-01-01' AND '2023-12-31'
    AND product_category = 'Electronics';

登录后复制

然后是 GROUP BY 和聚合函数，这是数据分析的灵魂所在。当你需要看“整体”而不是“个体”时，它们就派上用场了。比如，我想知道每个产品的总销售额，或者每个月的平均销售额。COUNT()、SUM()、AVG()、MIN()、MAX() 这些函数，配合 GROUP BY，能瞬间把散乱的数据汇总成有意义的指标。

SELECT
    product_category,
    SUM(sales_amount) AS total_sales,
    COUNT(DISTINCT customer_id) AS unique_customers,
    AVG(price) AS average_price
FROM
    sales_data
GROUP BY
    product_category;

登录后复制

如果聚合后你还需要进一步筛选，那就得用 HAVING 了，它和 WHERE 类似，但作用于 GROUP BY 后的结果。比如，我只想看总销售额超过100万的产品类别。

SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM
    sales_data
GROUP BY
    product_category
HAVING
    SUM(sales_amount) > 1000000;

登录后复制

最后，数据整合离不开 JOIN。在真实世界里，数据往往分散在不同的表里，比如订单信息在一张表，客户信息在另一张表。你需要把它们“连接”起来才能进行更全面的分析。INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN，每种都有其特定的应用场景，理解它们的区别是关键。我个人在做数据分析时，LEFT JOIN 和 INNER JOIN 用得最多，因为它们能很好地处理“主表数据不丢失”和“只看匹配数据”这两种核心需求。

SELECT
    o.order_id,
    c.customer_name,
    o.order_amount
FROM
    orders o
INNER JOIN
    customers c ON o.customer_id = c.customer_id
WHERE
    o.order_date >= '2024-01-01';

登录后复制

SQL数据分析中，如何高效利用子查询与CTE提升复杂查询性能？

说实话，刚开始接触SQL，子查询（Subquery）会让人觉得有点绕，因为它就像在主查询里又套了一个小查询。但它确实非常强大，能帮助我们解决很多分步计算的问题。比如，我想找出那些销售额高于平均销售额的产品，这时候你得先算出平均销售额，再用这个结果去筛选。

SELECT
    product_name,
    sales_amount
FROM
    sales_data
WHERE
    sales_amount > (SELECT AVG(sales_amount) FROM sales_data);

登录后复制

子查询虽然好用，但有时层层嵌套起来，那可读性就有点“一言难尽”了，而且在某些数据库系统中，性能也可能不太理想。这时候，Common Table Expressions (CTE)，也就是我们常说的“公共表表达式”，就显得格外优雅和高效了。

CTE用 WITH 关键字定义，它能把一个复杂的查询拆分成多个逻辑清晰、可重用的“临时结果集”。我个人非常推崇CTE，因为它让SQL代码像搭积木一样，一步步构建起来，不仅可读性大大提升，很多时候还能优化查询计划，减少重复计算。

举个例子，如果我们要找出每个产品类别中销售额最高的那个产品，用子查询可能需要嵌套好几层。但用CTE，你可以先计算出每个类别的最高销售额，再用这个结果去连接原始表。

WITH CategoryMaxSales AS (
    SELECT
        product_category,
        MAX(sales_amount) AS max_category_sales
    FROM
        sales_data
    GROUP BY
        product_category
)
SELECT
    s.product_name,
    s.product_category,
    s.sales_amount
FROM
    sales_data s
INNER JOIN
    CategoryMaxSales cms ON s.product_category = cms.product_category
                         AND s.sales_amount = cms.max_category_sales;

登录后复制

你看，是不是清晰多了？CTE还能用于递归查询，处理一些层级结构的数据，比如组织架构或者评论回复链，这在传统子查询里会非常麻烦。

聚合函数之外，SQL窗口函数在复杂数据分析中的应用场景有哪些？

谈到数据分析，我们刚才提到了聚合函数，它们把多行数据聚合成一行结果。但有时候，我们既想看到聚合结果，又不想丢失原始的行信息，或者想在某个“窗口”内进行计算，比如计算移动平均、排名、累计值等等。这时候，窗口函数（Window Functions）就登场了。

窗口函数和聚合函数很像，都有 SUM(), AVG(), COUNT() 等，但它们后面多了一个 OVER() 子句。这个 OVER() 定义了一个“窗口”，也就是一个数据集的子集，函数就在这个子集上进行计算，但结果会返回到每一行，而不是像 GROUP BY 那样把行数减少。

我第一次接触窗口函数时，感觉就像打开了新世界的大门。它能非常优雅地解决很多过去需要自连接或者复杂子查询才能搞定的问题。

一些常用的窗口函数及其应用场景：

排名函数 (ROW_NUMBER(), RANK(), DENSE_RANK()): 找出每个组内的第一名、前三名等。比如，我想看每个月销售额排名前五的产品。

SELECT
    sale_month,
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY sale_month ORDER BY sales_amount DESC) AS rn
FROM
    monthly_sales
WHERE
    rn <= 5; -- 注意：这个WHERE子句不能直接用在WITH外面，需要用子查询或CTE包一层

登录后复制

正确的使用方式通常是这样：

WITH RankedSales AS (
    SELECT
        sale_month,
        product_name,
        sales_amount,
        ROW_NUMBER() OVER (PARTITION BY sale_month ORDER BY sales_amount DESC) AS rn
    FROM
        monthly_sales
)
SELECT
    sale_month,
    product_name,
    sales_amount
FROM
    RankedSales
WHERE
    rn <= 5;

登录后复制

分析函数 (LEAD(), LAG()): 比较当前行与前一行或后一行的数据。比如，计算环比增长率，或者查看用户上次登录时间。

SELECT
    sale_date,
    sales_amount,
    LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS previous_day_sales,
    (sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date)) / LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS daily_growth_rate
FROM
    daily_sales;

登录后复制

聚合窗口函数 (SUM() OVER(), AVG() OVER()): 计算滚动总和、移动平均。比如，计算过去7天的平均销售额。
```
SELECT
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_moving_avg
FROM
    daily_sales;
```
登录后复制
窗口函数非常灵活，通过 PARTITION BY 定义分组，ORDER BY 定义组内排序，ROWS/RANGE 定义窗口范围，能实现各种复杂的分析需求，是高级数据分析师的必备技能。

在实际SQL数据分析中，我们常犯的错误和性能优化陷阱有哪些？

作为一名真实的数据分析师，我踩过的坑可不少，有些是经验不足，有些是粗心大意。这里总结几个我在实际工作中经常遇到或者看到别人犯的错误和性能陷阱：

*`SELECT 的滥用：** 除非你真的需要所有列，否则请明确列出你需要的字段。SELECT 会增加网络传输负担，也可能导致不必要的磁盘I/O，特别是当表有很多列的时候。我见过很多新手，为了图方便，直接SELECT `，结果在生产环境跑了个几分钟甚至几小时的查询。
WHERE 子句中对列使用函数： 比如 WHERE DATE(order_time) = '2023-01-01'。这样做会导致数据库无法使用该列上的索引，从而进行全表扫描。正确的做法通常是 WHERE order_time >= '2023-01-01' AND order_time
不恰当的 JOIN 条件或缺失索引： JOIN 是连接表的关键，但如果 JOIN 的列上没有合适的索引，或者 JOIN 条件写得不对，导致数据库进行笛卡尔积或者大量的嵌套循环连接，那查询会非常慢。我通常会用 EXPLAIN（或者 EXPLAIN ANALYZE）来查看查询计划，看看是不是走了全表扫描或者索引没用上。
OR 条件过多： 在 WHERE 子句中使用大量 OR 条件，有时会降低查询性能，因为它可能导致数据库无法有效利用索引。在某些情况下，可以考虑使用 IN 或者 UNION ALL 来替代。
大数据量下的 DISTINCT： DISTINCT 操作需要对数据进行排序和去重，当数据量非常大时，这会消耗大量的CPU和内存资源。如果你只是想统计不重复的数量，COUNT(DISTINCT column) 是没问题的，但如果 SELECT DISTINCT * 并且数据量巨大，就得小心了。
没有理解 NULL 的行为： NULL 在SQL里是个特殊的存在，它不等于0，也不等于空字符串，甚至不等于它自己。很多时候，WHERE column = NULL 是不生效的，你得用 WHERE column IS NULL 或 WHERE column IS NOT NULL。我见过不少因为对 NULL 理解偏差导致数据筛选不准确的情况。
忽略数据类型： 隐式类型转换有时会导致索引失效或性能问题。比如拿字符串去和数字列比较，数据库可能需要先将数字列转换为字符串再进行比较，这会消耗额外的资源。