当前位置：首页 > news >正文

建设银行网站转账建站程序免费下载

news 2025/9/6 6:24:20

建设银行网站转账,建站程序免费下载,网站速成,广州活动网站设计文章目录 mysql 8.0 时间维度表生成实例时间维度表的作用时间维度表生成技术细节使用时间维度表的好处 mysql 8.0 时间维度表生成实例时间维度表的作用 dim_times（时间维度表）在数据仓库（Data Warehouse）中的作用至关重要。作为…

文章目录

mysql 8.0 时间维度表生成实例
- 时间维度表的作用
- 时间维度表生成
- 技术细节
- 使用时间维度表的好处

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，大幅提高与时间相关的数据查询性能，避免实时计算时间字段。
历史趋势分析：时间维度表是执行历史数据分析、同比、环比等时间比较的基础，帮助用户进行数据趋势洞察和预测。