• mysql 日期维度序列生成


    建表语句

    1. CREATE TABLE `shence`.`Untitled` (
    2. `id` int(11) NOT NULL AUTO_INCREMENT,
    3. `date` datetime NULL DEFAULT NULL,
    4. `dayid` int(11) NULL DEFAULT NULL,
    5. `weekid` int(11) NULL DEFAULT NULL,
    6. `monthid` int(11) NULL DEFAULT NULL,
    7. `quartid` int(11) NULL DEFAULT NULL,
    8. `yearid` int(11) NULL DEFAULT NULL,
    9. `y` int(11) NULL DEFAULT NULL,
    10. `m` int(11) NULL DEFAULT NULL,
    11. `d` int(11) NULL DEFAULT NULL,
    12. `yearmon` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    13. `weekstr` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    14. `quar` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    15. `weekday` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    16. `monname` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    17. `dayname` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
    18. PRIMARY KEY (`id`) USING BTREE
    19. ) ENGINE = InnoDB AUTO_INCREMENT = 1

    存储过程

    1. CREATE DEFINER=`root`@`%` PROCEDURE `dataauto`()
    2. BEGIN
    3. SET @d0 = "1970-01-01";
    4. SET @d1 = "2050-12-31";
    5. set @date = date_sub(@d0, interval 1 day);
    6. set @dayid = 0;
    7. set @monthid = 0;
    8. set @weekid = 0;
    9. set @cur_month = 0;
    10. set @cur_week = 0;
    11. INSERT INTO `shence`.`dim_shence` (`date`, `dayid`, `weekid`, `monthid`, `quartid`, `yearid`, `y`, `m`, `d`, `yearmon`, `weekstr`, `quar`, `weekday`, `monname`, `dayname`)
    12. VALUES ('1970-01-01 00:00:00', 0, 0, 0, 0, 0, 1970, 1, 1, '1970', '1', '1', '4', 'January', 'Thursday');
    13. while @date <= @d1 DO
    14. #Routine body goes here...
    15. set @dayid=@dayid+1;
    16. set @date := date_add(@date, interval 1 day);
    17. if @cur_month=12 and month(@date) = 1 then
    18. set @monthid = @monthid+1;
    19. set @cur_month = month(@date);
    20. elseif @cur_month < month(@date) then
    21. set @monthid = @monthid+1;
    22. set @cur_month = month(@date);
    23. END if;
    24. if @cur_week > week(@date, 3) THEN
    25. set @weekid = @weekid+1;
    26. set @cur_week = week(@date, 3);
    27. elseif @cur_week < week(@date, 3) then
    28. set @weekid = @weekid+1;
    29. set @cur_week = week(@date, 3);
    30. END if;
    31. insert into dim_shence(date,y,m,d,yearmon,weekstr,quar,weekday,monname,dayname,dayid,monthid,weekid)
    32. select @date as date ,
    33. year(@date) as y,
    34. month(@date) as m,
    35. day(@date) as d,
    36. date_format(@date, "%x")as yearmon,
    37. week(@date, 3) as weekstr,
    38. quarter(@date) as quar,
    39. weekday(@date)+1 as weekday,
    40. monthname(@date) as monname,
    41. dayname(@date) as dayname,
    42. @dayid as dayid,
    43. @monthid as monthid,
    44. @weekid as weekid
    45. from dim_shence
    46. where @date <= @d1
    47. ORDER BY date desc
    48. limit 1;
    49. END WHILE;
    50. END

  • 相关阅读:
    Redisson之lock()和tryLock()的区别
    谁的孙子最多II
    2023最新群智能优化算法:巨型犰狳优化算法(Giant Armadillo Optimization,GAO)求解23个基准函数(提供MATLAB代码)
    java计算机毕业设计教师招聘考试题库系统源码+mysql数据库+系统+lw文档+部署
    c高级 shell指令
    Netty(2)文件编程(前置了解知识)
    2760. 最长奇偶子数组 : 抽丝剥茧,图解双指针做法正确性
    SEO内链优化的8个终极策略
    【我的渲染技术进阶之旅】让我们一起来了解一下什么是glTF?为什么glTF是3D世界的JPEG?
    nginx学习使用
  • 原文地址:https://blog.csdn.net/jljf_hh/article/details/127119839