39、掌握 SQL 查询输出优化技巧

掌握 SQL 查询输出优化技巧

1. 创建分组集的要点

在创建分组集时，添加 GROUPING SETS 子句时要注意括号的使用。总是有一对外部括号，然后为每个添加的聚合操作添加一对内部括号。

2. 在聚合查询中创建小计和总计

假设需要一份快速报告，展示按品牌和颜色划分的销售情况，以及所有可能的品牌和销售组合的小计。可以使用以下代码：

SELECT      MakeName, Color, SUM(Cost) AS Cost
FROM        allsales
GROUP BY    GROUPING SETS ((MakeName, Color)
              , (MakeName), (Color), ())
ORDER BY    MakeName, Color;

此查询会按品牌和颜色分组计算销售成本，并提供品牌小计、颜色小计和总计。运行该查询后，结果会展示不同品牌和颜色的销售情况及相应小计。

GROUPING SETS 子句的具体作用如下：
- 总计 ：在 GROUPING SETS 子句中添加空括号 () 可得到总计。
- 品牌小计 ：添加 (MakeName) 可计算每个品牌的销售小计。
- 颜色小计 ：添加 (Color) 可计算每种颜色的销售小计。
- 聚合总计 ：将 MakeName 和 Color 字段用逗号分隔添加到 GROUPING SETS 子句中，可得到品牌和颜色组合的聚合总计。

要理解 GROUPING SETS 查询的结果，需要注意各列中的 NULL 值。若除值列外其他列都为 NULL ，则该行代表总计；若某列出现 NULL 且其他列有值，则该行显示这些元素的总和。

3. 分组集使用的技巧和陷阱

• 排序子句 ：使用分组集时不一定要添加 ORDER BY 子句，但添加后通常能得到更全面的结果。
• 排除最低级别的聚合 ：若不想查看每个品牌和颜色组合的小计，可在 GROUPING SETS 子句中不包含最低级别的聚合。例如，将 GROUP BY 子句修改为：

GROUP BY    GROUPING SETS ((MakeName), (Color), ())

这样只会显示品牌、颜色的总计和总合计。

4. 创建包含总计和小计的清晰表格

为了让董事会更容易理解数据，可使用以下代码：

WITH GroupedSource_CTE
AS
(
SELECT 
MakeName
,Color
,Count(*) AS NumberOfCarsBought
FROM        allsales
WHERE       MakeName IS NOT NULL OR Color IS NOT NULL
GROUP BY    GROUPING SETS ((MakeName), (Color), ())
)
SELECT AggregationType
,Category
,NumberOfCarsBought
FROM
(
SELECT   'GrandTotal' AS AggregationType, NULL AS Category
         ,NumberOfCarsBought, 1 AS SortOrder
FROM     GroupedSource_CTE
WHERE    MakeName IS NULL and Color IS NULL
UNION
SELECT   'Make Subtotals', MakeName, 
NumberOfCarsBought , 2
FROM     GroupedSource_CTE
WHERE    MakeName IS NOT NULL and Color IS NULL 
UNION
SELECT   'Color Subtotals', Color, 
NumberOfCarsBought , 3
FROM     GroupedSource_CTE
WHERE    MakeName IS NULL and Color IS NOT NULL
) SQ
ORDER BY SortOrder, NumberOfCarsBought DESC;

此查询主要分为三个部分：
1. CTE ：聚合每个品牌、每种颜色的汽车销售数量以及所有汽车销售的总计。
2. 子查询 ：通过过滤 MakeName 和 Color 列中的 NULL 值，分离出每种聚合类型（颜色、品牌和总计），并添加一个列指定聚合类型为文本，最后通过 UNION 查询将三个查询结果合并到一个表结构中。
3. 外部查询 ：从派生表中选择三列，并添加 CASE 子句用等号字符串替换输出中的 NULL 值，仅用于突出显示总计。

这个查询展示了如何将 CTE、派生表和 UNION 查询结合使用，还展示了如何动态创建表输出并使用 UNION 查询将不同数据元素组合到一个列中。

5. 在最终输出中替换缩写为完整文本

销售经理需要一份公司所有客户的快速报告，并希望使列表更美观，可使用 REPLACE() 函数。例如：

SELECT     REPLACE(CustomerName, 'Ltd', 'Limited') AS NoAcronymName
FROM       Customer
WHERE      LOWER(CustomerName) LIKE '%ltd%';

此查询会将 CustomerName 字段中的 Ltd 替换为 Limited 。 REPLACE() 函数需要指定三个参数：
1. 要修改数据的字段。
2. 要查找的文本。
3. 要替换的文本。

若需要替换多个单词，可嵌套多个 REPLACE() 函数。例如：

SELECT     REPLACE(
                   REPLACE(
                           REPLACE(CustomerName, 
'Ltd', 'Limited')
                            ,'S.A.', 'Société 
Anonyme')
            ,'Corp', 'Corporation') AS 
PrettierOutput
FROM       Customer;

使用 REPLACE() 函数时需注意，该函数仅应用于 SELECT 子句，不会对底层数据产生任何影响。

6. 核心知识总结

概念	描述
PIVOT	该运算符可用于创建数据透视表。
GROUPING SETS	该运算符可向聚合查询中添加多个小计。
REPLACE()	该函数可将一个或多个字符替换为另一个字符串。

7. 流程总结

graph LR
    A[创建分组集] --> B[在聚合查询中使用GROUPING SETS]
    B --> C[处理小计和总计]
    C --> D[创建清晰表格展示数据]
    D --> E[替换输出中的缩写]

通过以上操作，我们可以优化 SQL 查询输出，使数据展示更加清晰和美观，满足不同的业务需求。无论是计算销售数据的小计和总计，还是处理数据输出的格式，这些技巧都能帮助我们更好地分析和展示数据。

掌握 SQL 查询输出优化技巧（续）

8. SQL 函数与操作的详细应用

在 SQL 中，有许多函数和操作可以帮助我们更好地处理和分析数据。以下是一些常见的 SQL 函数和操作及其应用场景。

8.1 数据过滤与筛选

• 布尔过滤器 ：使用 AND 、 OR 、 NOT 等布尔运算符进行数据过滤。例如：

SELECT * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2';

• 范围过滤 ：使用 BETWEEN 运算符过滤指定范围内的数据。例如：

SELECT * FROM table_name WHERE column1 BETWEEN 10 AND 20;

• 模糊匹配 ：使用 LIKE 运算符进行模糊匹配，结合通配符 % 和 _ 。例如：

SELECT * FROM table_name WHERE column1 LIKE '%keyword%';

8.2 数据聚合

• 求和 ：使用 SUM() 函数计算某列的总和。例如：

SELECT SUM(column1) FROM table_name;

• 平均值 ：使用 AVG() 函数计算某列的平均值。例如：

SELECT AVG(column1) FROM table_name;

• 计数 ：使用 COUNT() 函数计算记录的数量。例如：

SELECT COUNT(*) FROM table_name;

8.3 数据排序

使用 ORDER BY 关键字对查询结果进行排序。可以按升序（ ASC ）或降序（ DESC ）排序。例如：

SELECT * FROM table_name ORDER BY column1 ASC;

9. 处理复杂查询的技巧

在实际应用中，我们可能会遇到复杂的查询需求，需要使用一些技巧来简化和优化查询。

9.1 公共表表达式（CTEs）

CTEs 可以将复杂的查询分解为多个逻辑部分，提高查询的可读性和可维护性。例如：

WITH CTE_Name AS (
    SELECT column1, column2 FROM table_name WHERE condition
)
SELECT * FROM CTE_Name;

9.2 派生表

派生表是在查询中临时创建的表，可以用于复杂的计算和数据处理。例如：

SELECT * FROM (
    SELECT column1, column2 FROM table_name WHERE condition
) AS DerivedTable;

9.3 联合查询（UNION）

使用 UNION 运算符将多个查询结果合并为一个结果集。例如：

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

10. 处理日期和时间数据

日期和时间数据在 SQL 中非常常见，需要掌握一些函数来处理它们。

10.1 获取当前日期和时间

使用 CURRENT_DATE() 函数获取当前系统日期。例如：

SELECT CURRENT_DATE();

10.2 日期计算

使用 DATEADD() 函数进行日期计算。例如，计算当前日期之后 7 天的日期：

SELECT DATEADD(day, 7, CURRENT_DATE());

10.3 日期格式化

使用 DATE_FORMAT() 函数将日期格式化为指定的字符串。例如：

SELECT DATE_FORMAT(CURRENT_DATE(), '%Y-%m-%d');

11. 处理 NULL 值

在 SQL 中，NULL 值是一个特殊的值，表示缺失或未知的数据。我们可以使用一些函数来处理 NULL 值。

11.1 检查 NULL 值

使用 IS NULL 和 IS NOT NULL 运算符检查某列是否为 NULL。例如：

SELECT * FROM table_name WHERE column1 IS NULL;

11.2 替换 NULL 值

使用 COALESCE() 函数将 NULL 值替换为指定的值。例如：

SELECT COALESCE(column1, 'default_value') FROM table_name;

12. 总结与建议

通过以上的介绍，我们了解了 SQL 查询输出优化的多种技巧，包括分组集的使用、数据替换、复杂查询的处理、日期和时间数据的处理以及 NULL 值的处理等。以下是一些总结和建议：
- 合理使用函数 ：根据不同的业务需求，选择合适的 SQL 函数来处理数据，提高查询效率和准确性。
- 优化查询结构 ：使用 CTEs、派生表和联合查询等技巧，将复杂的查询分解为多个逻辑部分，提高查询的可读性和可维护性。
- 注意 NULL 值处理 ：在处理数据时，要注意 NULL 值的存在，并使用合适的函数来处理它们，避免出现意外的结果。

graph LR
    A[数据过滤与筛选] --> B[数据聚合]
    B --> C[数据排序]
    C --> D[处理复杂查询]
    D --> E[处理日期和时间数据]
    E --> F[处理NULL值]

通过不断实践和学习，我们可以熟练掌握这些技巧，更好地处理和分析 SQL 数据，满足各种业务需求。