微网站价格推广网络广告

mysql 8.0 时间维度表生成实例

时间维度表的作用

dim_times（时间维度表）在数据仓库（Data Warehouse）中的作用至关重要。作为维度表，dim_times 主要提供与时间相关的详细信息，帮助用户按照时间维度对事实数据进行查询、分析和聚合。以下是时间维度表在数据仓库中的主要作用：

• 提供一致的时间表示
  dim_times 表提供一致且标准化的时间表示。时间维度表通常包括从秒、分钟、小时、天、星期、月、季度、年份等不同的时间层级信息，确保数据分析中所有与时间相关的操作都使用同一套时间标准，避免时间计算中的不一致。

时间维度表生成

• 创建时间维度表

CREATE TABLE `dim_time` (`time_key` int NOT NULL COMMENT '唯一的时间键，表示一天中的秒数 (0 - 86399)',`time_value` time DEFAULT NULL COMMENT '一天中的具体时间值，格式为HH:MM:SS',`hour24` tinyint DEFAULT NULL COMMENT '24小时制的小时数 (0 - 23)',`hour12` tinyint DEFAULT NULL COMMENT '12小时制的小时数 (1 - 12)',`minutes` tinyint DEFAULT NULL COMMENT '分钟 (0 - 59)',`seconds` tinyint DEFAULT NULL COMMENT '秒数 (0 - 59)',`am_pm` char(2) DEFAULT NULL COMMENT '时间的上午/下午标识 (AM/PM)',PRIMARY KEY (`time_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='时间维度表，记录一天中每秒的时间信息';

• 创建生成时间维度过程

```sql
CREATE PROCEDURE generate_dim_time()
begin-- SET SESSION cte_max_recursion_depth = 100000;-- 清空表，确保数据为全新的DELETE FROM dim_time;-- 使用递归CTE生成一天中的秒数(0 到 86399)INSERT INTO dim_time (time_key, time_value, hour24, hour12, minutes, seconds, am_pm)select time_key, time_value, hour24, hour12, minutes, seconds, am_pm from  (
WITH RECURSIVE time_cte AS (SELECT 0 AS seconds_of_dayUNION ALLSELECT seconds_of_day + 1FROM time_cteWHERE seconds_of_day < 86399  -- 一天 24 小时有 86400 秒（0-86399）
)
SELECT seconds_of_day AS time_key,                     -- time_key为秒数SEC_TO_TIME(seconds_of_day) AS time_value,      -- 使用SEC_TO_TIME函数将秒数转为时间HOUR(SEC_TO_TIME(seconds_of_day)) AS hour24,    -- 24小时制的小时数IF(HOUR(SEC_TO_TIME(seconds_of_day)) = 0 OR HOUR(SEC_TO_TIME(seconds_of_day)) = 12, 12, HOUR(SEC_TO_TIME(seconds_of_day)) % 12) AS hour12,  -- 12小时制的小时数MINUTE(SEC_TO_TIME(seconds_of_day)) AS minutes, -- 分钟SECOND(SEC_TO_TIME(seconds_of_day)) AS seconds, -- 秒IF(HOUR(SEC_TO_TIME(seconds_of_day)) < 12, 'AM', 'PM') AS am_pm  -- AM/PM
FROM time_cte ) time_cte;END

执行以后得截图

技术细节

• 技术点1

由于 默认 mysql 8.0 递归有限制1000层， 需要修改

SET SESSION cte_max_recursion_depth = 100000;

注意事项
调整递归深度限制时，务必谨慎，因为递归层数过多可能会消耗大量的内存和 CPU，影响数据库性能。
在大多数情况下，默认的 1000 层递归深度已经足够，如果不需要非常复杂的递归操作，尽量避免大幅提升这个限制。

• 技术点2

1天=24小时 ，1小时 =60分钟 。1分钟=60秒
所以 是 246060=86400秒

• 技术点3
  我这里是从 0开始 递归 ，根据每个公司的需求也可以从 1开始

//如果从1开始 
WITH RECURSIVE time_cte AS (SELECT 1 AS seconds_of_dayUNION ALLSELECT seconds_of_day + 1FROM time_cteWHERE seconds_of_day < 86400  -- 一天 24 小时有 86400 秒（0-86399）
)

• 技术点4
  维度表引擎建议用 MyISAM ，因为生成一次后，一般不会在修改

ENGINE=MyISAM

使用时间维度表的好处

• 时间维度表的定义

dim_time 表是数据仓库中的一个重要维度表，用来存储一天中每秒的时间信息，并提供标准化的时间表示。该表通过 time_key 唯一标识每一秒，并为其提供多层级的时间信息，如 24 小时制、12 小时制、分钟、秒和 AM/PM 标识等。

• 表结构概述

time_key：以秒为单位的唯一标识，范围从 0 到 86399，表示一天中的每一秒。
time_value：秒数对应的具体时间（HH:MM:SS 格式）。
hour24：24 小时制的小时数，用于与时间相关的精确分析。
hour12：12 小时制的小时数，配合 AM/PM 标识支持更常见的时间展示。
minutes 和 seconds：分别表示分钟和秒数，提供精确的时间粒度。
am_pm：表示当前时间为上午（AM）或下午（PM），方便时间分段分析。

• 时间维度表的作用

标准化时间表示：dim_time 提供了一致的时间表示，避免在数据分析中因时间格式不统一而导致的混淆。
支持多层级时间聚合：能够在不同时间粒度上进行聚合分析，例如按小时、天、月、季度或年等进行业务汇总。
简化时间查询：提供与时间相关的字段，支持复杂的时间计算，如按 AM/PM、工作日、周末、节假日等分类进行分析。
提高查询效率：通过关联事实表中的 time_key，大幅提高与时间相关的数据查询性能，避免实时计算时间字段。
历史趋势分析：时间维度表是执行历史数据分析、同比、环比等时间比较的基础，帮助用户进行数据趋势洞察和预测。

• 性能优化

时间维度表中使用了整数型的 time_key 作为主键，便于事实表高效地与时间维度表进行关联查询。这种方式减少了复杂时间字段的存储和计算压力，同时提高了查询响应速度。

• 数据仓库建模中的关键角色

在星型或雪花型数据仓库模型中，时间维度表是所有与时间相关的分析、汇总和计算的基础。它为事实表提供了完整的时间维度支持，使得业务分析能够在不同的时间层级和时间段上展开。

• 应用场景

销售分析：按小时、日、月、季度、年等时间维度聚合销售数据，分析销售趋势。
用户行为分析：分析用户的访问时间分布，比如按小时、工作日与周末、节假日进行比较。
财务报表：生成按时间维度汇总的财务报表，支持时间段对比，如去年同期或上季度的财务表现。