mysql 8.0 時間維度表生成實例

時間維度表的作用

dim_times（時間維度表）在數(shù)據(jù)倉庫（Data Warehouse）中的作用至關(guān)重要。作為維度表，dim_times 主要提供與時間相關(guān)的詳細(xì)信息，幫助用戶按照時間維度對事實數(shù)據(jù)進(jìn)行查詢、分析和聚合。以下是時間維度表在數(shù)據(jù)倉庫中的主要作用：

• 提供一致的時間表示
  dim_times 表提供一致且標(biāo)準(zhǔn)化的時間表示。時間維度表通常包括從秒、分鐘、小時、天、星期、月、季度、年份等不同的時間層級信息，確保數(shù)據(jù)分析中所有與時間相關(guān)的操作都使用同一套時間標(biāo)準(zhǔn)，避免時間計算中的不一致。

時間維度表生成

• 創(chuàng)建時間維度表

CREATE TABLE `dim_time` (`time_key` int NOT NULL COMMENT '唯一的時間鍵，表示一天中的秒數(shù) (0 - 86399)',`time_value` time DEFAULT NULL COMMENT '一天中的具體時間值，格式為HH:MM:SS',`hour24` tinyint DEFAULT NULL COMMENT '24小時制的小時數(shù) (0 - 23)',`hour12` tinyint DEFAULT NULL COMMENT '12小時制的小時數(shù) (1 - 12)',`minutes` tinyint DEFAULT NULL COMMENT '分鐘 (0 - 59)',`seconds` tinyint DEFAULT NULL COMMENT '秒數(shù) (0 - 59)',`am_pm` char(2) DEFAULT NULL COMMENT '時間的上午/下午標(biāo)識 (AM/PM)',PRIMARY KEY (`time_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='時間維度表，記錄一天中每秒的時間信息';

• 創(chuàng)建生成時間維度過程

```sql
CREATE PROCEDURE generate_dim_time()
begin-- SET SESSION cte_max_recursion_depth = 100000;-- 清空表，確保數(shù)據(jù)為全新的DELETE FROM dim_time;-- 使用遞歸CTE生成一天中的秒數(shù)(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為秒數(shù)SEC_TO_TIME(seconds_of_day) AS time_value,      -- 使用SEC_TO_TIME函數(shù)將秒數(shù)轉(zhuǎn)為時間HOUR(SEC_TO_TIME(seconds_of_day)) AS hour24,    -- 24小時制的小時數(shù)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小時制的小時數(shù)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

執(zhí)行以后得截圖

技術(shù)細(xì)節(jié)

• 技術(shù)點1

由于 默認(rèn) mysql 8.0 遞歸有限制1000層， 需要修改

SET SESSION cte_max_recursion_depth = 100000;

注意事項
調(diào)整遞歸深度限制時，務(wù)必謹(jǐn)慎，因為遞歸層數(shù)過多可能會消耗大量的內(nèi)存和 CPU，影響數(shù)據(jù)庫性能。
在大多數(shù)情況下，默認(rèn)的 1000 層遞歸深度已經(jīng)足夠，如果不需要非常復(fù)雜的遞歸操作，盡量避免大幅提升這個限制。

• 技術(shù)點2

1天=24小時 ，1小時 =60分鐘 。1分鐘=60秒
所以 是 246060=86400秒

• 技術(shù)點3
  我這里是從 0開始 遞歸 ，根據(jù)每個公司的需求也可以從 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）
)

• 技術(shù)點4
  維度表引擎建議用 MyISAM ，因為生成一次后，一般不會在修改

ENGINE=MyISAM

使用時間維度表的好處

• 時間維度表的定義

dim_time 表是數(shù)據(jù)倉庫中的一個重要維度表，用來存儲一天中每秒的時間信息，并提供標(biāo)準(zhǔn)化的時間表示。該表通過 time_key 唯一標(biāo)識每一秒，并為其提供多層級的時間信息，如 24 小時制、12 小時制、分鐘、秒和 AM/PM 標(biāo)識等。

• 表結(jié)構(gòu)概述

time_key：以秒為單位的唯一標(biāo)識，范圍從 0 到 86399，表示一天中的每一秒。
time_value：秒數(shù)對應(yīng)的具體時間（HH:MM:SS 格式）。
hour24：24 小時制的小時數(shù)，用于與時間相關(guān)的精確分析。
hour12：12 小時制的小時數(shù)，配合 AM/PM 標(biāo)識支持更常見的時間展示。
minutes 和 seconds：分別表示分鐘和秒數(shù)，提供精確的時間粒度。
am_pm：表示當(dāng)前時間為上午（AM）或下午（PM），方便時間分段分析。

• 時間維度表的作用

標(biāo)準(zhǔn)化時間表示：dim_time 提供了一致的時間表示，避免在數(shù)據(jù)分析中因時間格式不統(tǒng)一而導(dǎo)致的混淆。
支持多層級時間聚合：能夠在不同時間粒度上進(jìn)行聚合分析，例如按小時、天、月、季度或年等進(jìn)行業(yè)務(wù)匯總。
簡化時間查詢：提供與時間相關(guān)的字段，支持復(fù)雜的時間計算，如按 AM/PM、工作日、周末、節(jié)假日等分類進(jìn)行分析。
提高查詢效率：通過關(guān)聯(lián)事實表中的 time_key，大幅提高與時間相關(guān)的數(shù)據(jù)查詢性能，避免實時計算時間字段。
歷史趨勢分析：時間維度表是執(zhí)行歷史數(shù)據(jù)分析、同比、環(huán)比等時間比較的基礎(chǔ)，幫助用戶進(jìn)行數(shù)據(jù)趨勢洞察和預(yù)測。

• 性能優(yōu)化

時間維度表中使用了整數(shù)型的 time_key 作為主鍵，便于事實表高效地與時間維度表進(jìn)行關(guān)聯(lián)查詢。這種方式減少了復(fù)雜時間字段的存儲和計算壓力，同時提高了查詢響應(yīng)速度。

• 數(shù)據(jù)倉庫建模中的關(guān)鍵角色

在星型或雪花型數(shù)據(jù)倉庫模型中，時間維度表是所有與時間相關(guān)的分析、匯總和計算的基礎(chǔ)。它為事實表提供了完整的時間維度支持，使得業(yè)務(wù)分析能夠在不同的時間層級和時間段上展開。

• 應(yīng)用場景

銷售分析：按小時、日、月、季度、年等時間維度聚合銷售數(shù)據(jù)，分析銷售趨勢。
用戶行為分析：分析用戶的訪問時間分布，比如按小時、工作日與周末、節(jié)假日進(jìn)行比較。
財務(wù)報表：生成按時間維度匯總的財務(wù)報表，支持時間段對比，如去年同期或上季度的財務(wù)表現(xiàn)。