超级会员免费看
数据时间分析与复杂输出技巧
在数据处理和分析过程中，时间分析和数据输出的呈现方式至关重要。下面将介绍一些关于按季度小时聚合数据、处理字符串日期以及创建复杂数据输出的技巧。
按15分钟聚合数据
CEO想了解每小时中哪个时间段最适合销售，为此可以使用以下代码按15分钟时段展示2017年的销售情况：
SELECT QuarterOfHour
,SUM(SalePrice) AS SalesByQuarterHourBand
FROM
(
SELECT SalePrice
,FLOOR((MINUTE(SaleDate) / 15) + 1) AS QuarterOfHour
FROM salesbycountry
WHERE YEAR(SaleDate) = 2017
) A
GROUP BY QuarterOfHour
ORDER BY QuarterOfHour;
这段SQL代码由两个查询组成：
-
内部查询
：查看2017年的每个销售日期和时间，使用
MINUTE()
函数提取每次销售发生的分钟数，将分钟数除以15得到每15分钟的时段，然后加1（避免时段从0开始），最后使用
FLOOR()
函数向下取整。
-
外部查询
：对内部查询的销售金额进行聚合，并按每15分钟时段分组。
处理存储为字符串的日期
在源数据中，日期并不总是以标准化格式存在，Databricks可能无法将其识别为可用日期，而是将其视为文本。可以使用
TO_DATE()
函数将不规则的日期字段转换为正确的日期。示例代码如下：
SELECT TO_DATE('12/31/2024', 'MM/dd/yyyy') AS NewYears
TO_DATE()
函数接受两个参数：
1.
日期
：可以解释为日期的字符串。
2.
日期格式
：告诉Databricks如何解释日期的日期代码。
在使用
TO_DATE()
函数时，需要注意以下几点：
- 如果日期无法被正确解释，将输出
NULL
。
- 日期中的天和月可以设置为单个字符或两个字符，Databricks仍能正确解释。
- 如果字符串无法解释为日期或时间戳，记录将返回
NULL
。
- 日期格式可以使用单引号或双引号，且可以包含时间格式代码。
核心时间分析函数
以下是一些在时间分析中常用的函数：
| 函数名 | 描述 |
| ---- | ---- |
|
LAST_DAY()
| 返回月份的最后一天的日期 |
|
DAYOFWEEK()
| 从日期返回星期几的数字 |
|
DATEDIFF()
| 计算两个给定日期之间的小时、分钟和秒数 |
|
DATE()
| 将日期时间数据类型转换为日期 |
|
YEAR()
| 提取日期的年份部分 |
|
MONTH()
| 提取日期的月份部分 |
|
DAY()
| 提取日期的日部分 |
|
MINUTE()
| 从日期时间字段中提取分钟数 |
|
HOUR()
| 从日期时间字段中提取小时数 |
|
SECOND()
| 从日期时间字段中提取秒数 |
|
INTERVAL
| 指定时间间隔，用于时间跨度计算 |
|
TO_DATE()
| 结合日期格式，将字符串转换为可用日期 |
|
TIMESTAMPDIFF()
| 计算两个日期和时间在选定单位中的差异 |
创建数据透视表
在数据展示方面，有时候我们需要将数据以更直观的方式呈现，数据透视表就是一种很好的选择。
简单数据透视表
财务总监要求提供一个简单、紧凑的按颜色统计的各年销售表，并且要以交叉表或数据透视表的形式呈现。可以使用以下SQL代码实现：
SELECT Color, y2015, y2016, y2017, y2018
FROM
(
SELECT Color, SD.SalePrice,
YEAR(SA.SaleDate) AS YearOfSale
FROM Stock ST
INNER JOIN SalesDetails SD
ON StockCode = SD.StockID
INNER JOIN Sales SA
ON SA.SalesID = SD.SalesID
) SQ
PIVOT (
SUM(SalePrice) FOR YearOfSale
IN (2015 y2015, 2016 y2016, 2017 y2017,
2018 y2018)
);
使用
PIVOT
运算符创建数据透视表时，需要以下三个要素：
-
行标题
：出现在输出表左列的数据元素。
-
列标题
：每列的数据元素，注意这不是字段名，而是数据库中的数据。
-
表数据
：最终透视输出表内的聚合数值。
创建数据透视表的过程如下：
1.
初始查询
：定义内部查询，输出最终透视表所需的数据元素，如颜色、销售年份和销售价格。
2.
应用
PIVOT
运算符
：对内部查询应用
PIVOT
运算符，隔离要聚合的数据和列规范。
3.
添加
SELECT
子句
：提供输出表的行数据和列标题。
在创建数据透视表时，需要注意以下几点：
- 选择构成列标题的数据是编写SQL的难点，可以先分离出这些数据元素列表。例如，使用
SELECT DISTINCT YEAR(SaleDate) FROM Sales
获取年份列表。
- 用作列标题的数据元素必须有别名，且列名必须遵循Databricks的命名规则，以字母开头。
显示多个行分组的数据透视表
财务总监对第一个数据透视表很满意，并要求创建另一个数据透视表，展示按品牌和型号销售的颜色数量。可以使用以下代码实现：
WITH MakeAndModelCostByYear_CTE (MakeName,
ModelName, Color, CostPrice)
AS
(
SELECT MakeName, ModelName, Color, Cost
FROM allsales
)
SELECT MakeName, ModelName, Black, Blue, 'British Racing
Green',
'Canary Yellow', 'Dark Purple', Green,
'Night Blue', Pink, Red, Silver
FROM MakeAndModelCostByYear_CTE
PIVOT (
COUNT(CostPrice) FOR Color IN
(
'Black' Black, 'Blue' Blue, 'British
Racing Green' 'British Racing Green',
'Canary Yellow' 'Canary
Yellow', 'Dark Purple' 'Dark Purple', 'Green'
Green,
'Night Blue' 'Night
Blue', 'Pink' Pink, 'Red' Red, 'Silver' Silver
)
)
ORDER BY MakeName, ModelName;
该查询使用了公共表表达式（CTE）来简化SQL和解决问题的方式。查询分为四个部分：
1.
SELECT
子句
：定义输出字段，包括行标题和列标题。
2.
FROM
子句
：使用CTE作为数据源。
3.
PIVOT
子句
：指定聚合函数（如
COUNT()
）、要聚合的字段和列标题。
4.
ORDER BY
子句
：对输出进行排序，保证结果易于理解。
在使用这种方式进行数据透视时，需要注意：
- 透视列表中每个数据点的别名不能包含空格，只能包含字母、数字和下划线，且必须以字母开头。
- 透视列表中的文本输出必须用引号括起来，可以使用单引号或双引号。
添加汇总到聚合查询
财务总监希望输出更像电子表格，包含小计和总计。可以使用以下SQL代码实现：
SELECT MakeName, Color, SUM(Cost) AS Cost
FROM allsales
GROUP BY GROUPING SETS ((MakeName, Color), ())
ORDER BY MakeName, Color;
该查询通过
GROUPING SETS
子句扩展了
GROUP BY
子句，允许指定最终输出中返回哪些小计和总计：
1.
正常聚合查询
：连接相关表，输出品牌名称、颜色字段和车辆购买的总成本。
2.
添加
GROUPING SETS
子句
：在
GROUP BY
子句中添加
GROUPING SETS
子句，指定要聚合的字段集合。空括号表示返回数据集的总计。
最终结果集中，每个品牌和颜色组合的总计很容易理解，而初始没有品牌或颜色的记录是总计，由
GROUPING SETS
子句中的空括号产生，在输出的品牌名称和颜色字段中用
NULL
表示。
这些技巧可以帮助我们更有效地分析和呈现数据，在实际应用中根据具体需求选择合适的方法。
数据时间分析与复杂输出技巧（续）
创建带小计和总计的清晰表格
在某些情况下，我们需要在聚合查询中同时创建小计和总计，以呈现更清晰的数据结构。下面通过一个示例来展示如何实现。
假设我们有一个销售数据，包含产品类别、地区和销售额等信息。我们希望在一个查询中同时得到每个产品类别在每个地区的销售额小计，以及所有产品类别的总销售额。以下是实现该功能的 SQL 代码：
SELECT
CASE
WHEN GROUPING(ProductCategory) = 1 THEN 'Total'
ELSE ProductCategory
END AS ProductCategory,
CASE
WHEN GROUPING(Region) = 1 AND GROUPING(ProductCategory) = 0 THEN 'Subtotal'
WHEN GROUPING(Region) = 1 AND GROUPING(ProductCategory) = 1 THEN 'Grand Total'
ELSE Region
END AS Region,
SUM(SalesAmount) AS SalesAmount
FROM
SalesData
GROUP BY
GROUPING SETS ((ProductCategory, Region), (ProductCategory), ())
ORDER BY
ProductCategory, Region;
上述代码的执行步骤如下：
1.
SELECT
子句
：使用
CASE
语句根据
GROUPING
函数的结果来判断当前行是小计、总计还是普通数据行。
GROUPING
函数用于判断某个字段是否参与了分组，如果该字段在当前分组中被聚合，则返回 1，否则返回 0。
2.
FROM
子句
：指定数据源为
SalesData
表。
3.
GROUP BY
子句
：使用
GROUPING SETS
子句指定多个分组集合。
(ProductCategory, Region)
表示按产品类别和地区分组，计算每个组合的销售额；
(ProductCategory)
表示按产品类别分组，计算每个产品类别的小计；
()
表示不分组，计算所有数据的总销售额。
4.
ORDER BY
子句
：按产品类别和地区排序，使结果更易于查看。
下面是这个过程的 mermaid 流程图：
graph TD;
A[开始] --> B[从 SalesData 表获取数据];
B --> C[按 GROUPING SETS 分组];
C --> D{判断分组情况};
D -->|ProductCategory 和 Region| E[计算每个组合销售额];
D -->|ProductCategory| F[计算每个产品类别小计];
D -->|无分组| G[计算总销售额];
E --> H[使用 CASE 语句标记行类型];
F --> H;
G --> H;
H --> I[按 ProductCategory 和 Region 排序];
I --> J[输出结果];
J --> K[结束];
替换最终输出中的缩写词
在数据输出中，有时会包含一些缩写词，为了使输出更易于理解，我们可以将这些缩写词替换为完整的文本。以下是一个简单的示例，假设我们有一个包含产品代码和销售数量的表，产品代码是缩写形式，我们希望将其替换为完整的产品名称。
SELECT
CASE
WHEN ProductCode = 'PC' THEN 'Personal Computer'
WHEN ProductCode = 'LAP' THEN 'Laptop'
WHEN ProductCode = 'TAB' THEN 'Tablet'
ELSE ProductCode
END AS ProductName,
SalesQuantity
FROM
SalesTable;
上述代码的操作步骤如下：
1.
SELECT
子句
：使用
CASE
语句根据产品代码进行判断，将缩写代码替换为完整的产品名称。如果代码不在预设列表中，则保持原样。
2.
FROM
子句
：指定数据源为
SalesTable
表。
总结
本文介绍了一系列数据时间分析和复杂数据输出的技巧，包括：
1.
时间分析
：
- 按 15 分钟聚合数据，使用内部查询提取关键信息，外部查询进行聚合分组。
- 处理存储为字符串的日期，使用
TO_DATE()
函数将其转换为可用日期，并注意相关参数和使用陷阱。
- 掌握了一系列时间分析函数，如
LAST_DAY()
、
DAYOFWEEK()
等。
2.
复杂数据输出
：
- 创建数据透视表，包括简单数据透视表和显示多个行分组的数据透视表，理解
PIVOT
运算符的使用和相关注意事项。
- 在聚合查询中添加小计和总计，使用
GROUPING SETS
子句扩展
GROUP BY
子句。
- 创建带小计和总计的清晰表格，通过
GROUPING
函数和
CASE
语句区分不同类型的行。
- 替换最终输出中的缩写词，使用
CASE
语句进行文本替换。
这些技巧可以帮助我们更深入地分析数据，以更直观、清晰的方式呈现分析结果。在实际应用中，我们可以根据具体需求灵活运用这些技巧，提高数据处理和分析的效率。
以下是本文涉及的主要技巧总结表格：
| 技巧类型 | 具体技巧 | 关键函数/语句 |
| ---- | ---- | ---- |
| 时间分析 | 按 15 分钟聚合数据 |
FLOOR
、
MINUTE
、
SUM
、
GROUP BY
|
| 时间分析 | 处理字符串日期 |
TO_DATE
|
| 时间分析 | 常用时间函数 |
LAST_DAY
、
DAYOFWEEK
、
DATEDIFF
等 |
| 复杂输出 | 创建简单数据透视表 |
PIVOT
|
| 复杂输出 | 创建多分组数据透视表 |
WITH
、
PIVOT
|
| 复杂输出 | 添加小计和总计 |
GROUPING SETS
|
| 复杂输出 | 创建带标记的表格 |
GROUPING
、
CASE
|
| 复杂输出 | 替换缩写词 |
CASE
|
通过掌握这些技巧，我们可以更好地应对各种数据处理和分析场景，挖掘数据中的有价值信息。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
