SQL和MySQL以及DAX、Power Query的日期表生成？数字型日期？将生成的日期表插入到临时表或者实体表中

原创已于 2025-11-21 14:32:53 修改 · 959 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql #数据库 #powerbi

于 2025-01-17 17:41:36 首次发布

SQL 同时被 3 个专栏收录

9 篇文章

订阅专栏

PowerBI

6 篇文章

订阅专栏

PowerPivot

4 篇文章

订阅专栏

几种生成日期表的方法
如何用SQL语句生成日期表呢？
如何用MySQL语句生成日期表呢？
如何用DAX语句生成日期表呢？

1. MySQL生成日期表

1.1 日期格式：yyyy-MM-dd 字符型 2024-01-02

-- 生成日期表
WITH RECURSIVE temp_dateTable AS (
  SELECT '2023-01-01' AS datekey
  UNION ALL
  SELECT DATE_ADD(datekey, INTERVAL 1 DAY)
  FROM temp_dateTable
  WHERE datekey < '2023-12-31'
)
SELECT datekey FROM temp_dateTable
;
/*
datekey
2023-01-01
2023-01-02
2023-01-03
2023-01-04
*/

1.2 日期格式：yyyyMMdd 数字型 20240102

-- 生成日期表
WITH RECURSIVE temp_dateTable AS (
  SELECT 20240101 AS datekey
  UNION ALL
  SELECT date_format(date_add(CONVERT(datekey, CHAR),interval 1 day), "%Y%m%d")+0
  FROM temp_dateTable
  WHERE datekey < 20240117
)
select
	dt.datekey
from temp_dateTable dt
;
/*
datekey
20240101
20240102
20240103
20240104
*/

1.3 MySQL生成的日期表插入到实体表（临时表）

-- 删除表
DROP TABLE IF EXISTS create_dateTable;

-- 创建表
CREATE TABLE create_dateTable (
    datekey DATE PRIMARY KEY
);

-- 将查找出来的临时表插入到新创建的实体表
INSERT INTO create_dateTable
WITH RECURSIVE temp_dateTable AS (
  SELECT '2023-01-01' AS datekey
  UNION ALL
  SELECT DATE_ADD(datekey, INTERVAL 1 DAY)
  FROM temp_dateTable
  WHERE datekey < '2023-12-31'
)
SELECT datekey FROM temp_dateTable;
-- 查找日期
SELECT * FROM create_dateTable;

2. SQL生成日期表

2.1 日期格式：yyyy-MM-dd 字符型 2024-01-02

DECLARE @BeginDate DATE, @EndDate DATE;
SET @BeginDate = '2023-09-01'
SET @EndDate = '2023-12-01'
;
WITH cteDate AS (
         SELECT @BeginDate AS CalendarDate
         UNION ALL
         SELECT DATEADD(DAY,1,CalendarDate)
         FROM cteDate
         WHERE CalendarDate <= @EndDate
)
SELECT
        CalendarDate DateKey,
        (DATEPART(YEAR,CalendarDate) * 10000) + (DATEPART(MONTH,CalendarDate) * 100) +
        DATEPART(DAY,CalendarDate) CalendarDate,
        DATEPART(YEAR,CalendarDate) AS Year,
        DATEPART(MONTH,CalendarDate) AS MonthNumber,
        DATENAME(MONTH,CalendarDate) AS Month,
        DATENAME(QUARTER,CalendarDate) AS Quarter
        , format(CalendarDate ,'yyyyMM') YeamMonth
FROM cteDate
OPTION (MAXRECURSION 0)

2.2 日期格式：yyyyMMdd 数字型 20240102

DECLARE @BeginDate INT, @EndDate INT;
SET @BeginDate = 20240101; 
SET @EndDate = 20240117;   
WITH cteDate AS (
         SELECT @BeginDate AS CalendarDate
         UNION ALL
         SELECT format(dateadd(day, 1, cast(CalendarDate as varchar(8))), 'yyyyMMdd')+0
         FROM cteDate
         WHERE CalendarDate < @EndDate
)
select
	CalendarDate
from cteDate
/*
CalendarDate
20240101
20240102
20240103
20240104
*/

2.3 SQL生成的日期表插入到实体表（临时表）

DECLARE @BeginDate DATE, @EndDate DATE;
SET @BeginDate = '2023-09-01'
SET @EndDate = '2023-12-01'
;
WITH cteDate AS (
         SELECT @BeginDate AS CalendarDate
         UNION ALL
         SELECT DATEADD(DAY,1,CalendarDate)
         FROM cteDate
         WHERE CalendarDate <= @EndDate
)
SELECT
        CalendarDate DateKey,
        (DATEPART(YEAR,CalendarDate) * 10000) + (DATEPART(MONTH,CalendarDate) * 100) +
        DATEPART(DAY,CalendarDate) CalendarDate,
        DATEPART(YEAR,CalendarDate) AS Year,
        DATEPART(MONTH,CalendarDate) AS MonthNumber,
        DATENAME(MONTH,CalendarDate) AS Month,
        DATENAME(QUARTER,CalendarDate) AS Quarter
        , format(CalendarDate ,'yyyyMM') YeamMonth
        INTO #temp_dateTable
FROM cteDate
OPTION (MAXRECURSION 0);
SELECT * FROM #temp_dateTable

3. DAX日期表生成自动日期表

EVALUATE
ADDCOLUMNS (
     CALENDARAUTO(),
     "Year", YEAR ( [Date] ),
     "Quarter No", QUARTER ( [Date] ),
     "Quarter", "Q" & QUARTER ( [Date] ),
     "Month No", MONTH ( [Date] ),
     "Month", FORMAT ( [Date], "MM" ),
     "Day", DAY ( [Date] )
)

3.2 DAX日期表生成指定日期表

EVALUATE
ADDCOLUMNS (
     CALENDAR ( DATE ( 2023, 11, 01 ), DATE ( 2023, 12, 31 ) ),
     "Year", YEAR ( [Date] ),
     "Quarter No", QUARTER ( [Date] ),
     "Quarter", "Q" & QUARTER ( [Date] ),
     "Month No", MONTH ( [Date] ),
     "Month", FORMAT ( [Date], "MM" ),
     "Day", DAY ( [Date] )
)

日期表生成

4.Power Query生成日期表

let
    // 获取当前日期
    当前日期 = DateTime.Date(DateTime.LocalNow()),
    //  整年
    // 开始日期 = Date.StartOfYear(Date.AddYears(当前日期, -1)),
    // 自动计算开始日期（当前日期前一年）和结束日期（当前日期）
    开始日期 = Date.AddYears(当前日期, -1),
    结束日期 = 当前日期,
    
    // 生成日期序列
    日期列表 = List.Dates(开始日期, Duration.Days(结束日期 - 开始日期) + 1, #duration(1,0,0,0)),
    
    // 转换为表格
    初始表 = Table.FromList(日期列表, Splitter.SplitByNothing(), {"日期"}, null, ExtraValues.Error),
    
    // 添加日期相关列
    添加年份 = Table.AddColumn(初始表, "年份", each Date.Year([日期])),
    添加季度 = Table.AddColumn(添加年份, "季度", each "Q" & Number.ToText(Date.QuarterOfYear([日期]))),
    添加月份 = Table.AddColumn(添加季度, "月份", each Date.Month([日期])),
    添加月份名称 = Table.AddColumn(添加月份, "月份名称", each Date.MonthName([日期])),
    
    // 年月列（格式：202511）
    添加年月 = Table.AddColumn(添加月份名称, "年月", each 
        Text.From(Date.Year([日期])) & 
        Text.PadStart(Text.From(Date.Month([日期])), 2, "0")),
    
    // 年周列（格式：202540）
    添加年周 = Table.AddColumn(添加年月, "年周", each 
        Text.From(Date.Year([日期])) & 
        Text.PadStart(Text.From(Date.WeekOfYear([日期], Day.Monday)), 2, "0")),
    
    // 添加排序序号列
    // 添加年月序号 = Table.AddColumn(添加年周, "年月序号", each Date.Year([日期]) * 100 + Date.Month([日期])),
    // 添加年周序号 = Table.AddColumn(添加年月序号, "年周序号", each Date.Year([日期]) * 100 + Date.WeekOfYear([日期], Day.Monday)),
    
    添加星期几 = Table.AddColumn(添加年周, "星期几", each Date.DayOfWeek([日期], Day.Monday) + 1),
    添加星期名称 = Table.AddColumn(添加星期几, "星期名称", each Date.DayOfWeekName([日期])),
    添加是否周末 = Table.AddColumn(添加星期名称, "是否周末", each if Date.DayOfWeek([日期], Day.Monday) >= 5 then "是" else "否"),
    
    // 设置数据类型
    最终表 = Table.TransformColumnTypes(添加是否周末,{{"年份", Int64.Type}, {"月份", Int64.Type}, {"星期几", Int64.Type}, {"年月", Int64.Type}, {"年周", Int64.Type}})
in
    最终表