【面试数据岗必知必会——sql中的行转列、列转行与json函数】

面试数据岗必知必会 —— SQL中的行转列、列转行与JSON函数

导言：
在数据科学和数据分析领域，SQL（Structured Query Language）是处理和管理关系型数据库的标准语言。掌握SQL的高级技巧，如行转列（Pivot）、列转行（Unpivot）以及JSON函数，能显著提升你在面试中的竞争力，并在实际工作中更加高效地处理复杂数据。本文将深入探讨这些技巧，帮助你更好地准备面试，同时增强你的技能树。

行转列（Pivot）

行转列，即Pivot操作，是一种将数据集中的一列或多列转换为多行数据的方法。这在汇总数据、进行透视分析时非常有用。例如，你可能有一个包含销售数据的表格，其中每个产品在每行都有销售记录，但你想要按产品分类汇总销售量，这就需要用到Pivot。

示例代码

假设我们有以下表结构：

CREATE TABLE Sales (
    Product VARCHAR(50),
    Year INT,
    Sales INT
);

表中包含如下数据：

使用Pivot语句，我们可以将“Year”列的值转换为列名，得到每年的产品销售总和：

SELECT * FROM (
    SELECT Product, Year, Sales
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Year IN (2020, 2021)
) AS PivotTable;

输出

列转行（Unpivot）

列转行，即Unpivot操作，与Pivot相反，它将列转换成行。在数据清洗和预处理阶段，这特别有用，尤其是当你需要将宽表转换为长表格式时。

示例代码

假设我们有以下宽表结构：

CREATE TABLE SalesSummary (
    Product VARCHAR(50),
    Year2020 INT,
    Year2021 INT
);

表中包含如下数据：

使用Unpivot语句，我们可以将“Year2020”和“Year2021”两列转换为多行数据：

SELECT Product, Year, Sales
FROM (
    SELECT Product, '2020' AS Year, Year2020 AS Sales
    FROM SalesSummary
    UNION ALL
    SELECT Product, '2021', Year2021
    FROM SalesSummary
) AS UnpivotedTable;

输出

JSON函数

随着JSON数据的普及，许多数据库系统都增加了对JSON的支持，包括存储、检索和操作JSON数据的功能。熟悉这些JSON函数可以让你更灵活地处理非结构化数据。

示例代码

假设我们有以下包含JSON字段的表结构：

CREATE TABLE Products (
    ProductID INT,
    Details JSON
);

表中包含如下数据：

使用JSON函数，我们可以提取JSON字段中的数据：

SELECT ProductID, JSON_EXTRACT(Details, '$.Price') AS Price
FROM Products;

输出

常见的JSON处理功能：

1. JSON字符串转换

   • JSON_STRINGIFY: 将结构化数据转换为JSON格式的字符串。
   • TO_JSON: 类似于JSON_STRINGIFY，但具体名称和语法可能因数据库而异。

2. JSON解析

   • JSON_EXTRACT: 提取JSON文档中的特定值。
   • JSON_QUERY: 返回JSON路径查询的结果。
   • JSON_VALUE: 提取JSON文档中的值并转换为SQL数据类型。
   • JSON_UNQUOTE: 去除字符串类型的JSON值周围的引号。

3. JSON修改

   • JSON_INSERT: 向现有JSON文档插入新的键/值对。
   • JSON_SET: 设置或更新JSON文档中的键/值对。
   • JSON_REMOVE: 从JSON文档中删除一个或多个键。
   • JSON_REPLACE: 替换JSON文档中的一个或多个键的值。
   • JSON_MERGE_PATCH: 合并两个或多个JSON文档，使用RFC 6902的Patch标准。

4. JSON生成

   • JSON_OBJECT: 创建一个新的JSON对象。
   • JSON_ARRAY: 创建一个新的JSON数组。
   • JSON_ARRAYAGG: 将多行数据聚合为一个JSON数组。

5. JSON验证

   • JSON_VALID: 检查一个字符串是否为有效的JSON文档。
   • JSON_TYPE: 返回JSON值的类型。

6. JSON结构操作

   • JSON_LENGTH: 返回JSON数组或对象的长度。
   • JSON_DEPTH: 返回JSON文档的深度。
   • JSON_PRETTY: 格式化JSON字符串以提高可读性。

7. JSON搜索

   • JSON_SEARCH: 在JSON文档中搜索指定的值或模式。

请记得根据你所使用的具体数据库系统调整上述示例中的SQL语法，因为不同数据库（如MySQL、PostgreSQL、SQL Server等）可能会有不同的实现方式。

点赞关注收藏，获取更多干货知识~