问题场景:
前端:前端提供报表录入数据,导入年度计划类的数据,但是不确定导入以及修改的间隔,需要达到的效果,希望能实现,计算年度完成率时(按月度计算),比如在3月份导入一版年初计划,在没有修改前,希望今年整个的计划基数就是3月份这版数据,假设在5月份对今年的计划做出修改,那么正确的应该是前4个月使用1、2、3、4月份使用3月份这版数据作为计算基数,5月份及之后使用5月份的数据,假设6月份又做了一次修改,那么对应的应该是:3月份的版本对应1、2、3、4月;5月份版本对应5月;6、7、8以及之后到12月使用6月份版本。以此类推,修改间隔不定,但是要保持历史数据正确性。数据主要包含以下:
数据表:主键,更新日期,版本日期,数量。(演示数据,比较少字段,数据也比较少,主要演示实现思路以供借鉴。)
解决思路:
前端数据导入或者修改时,加上版本日期的标识(年月),以及数据更新日期(天)。这样可以取到每个月最大一个更新日期的版本数据,保证当前数据对于当月来说是最新的。然后在考虑如何补充间隔月份的版本数据选择问题。
实现步骤(简单演示如何根据数据的变化自动补充版本月份到一年中对应的12个月中),演示环境(MYSQL8.0):
1、时间维度表作为主表
2、数据表的处理:获取每个月最大的版本日期的数据,且按照月汇总数量。(实际用到的字段:版本日期+最大日期+汇总数量)。
3、数据版本的自动选择,根据版本日期自动选择扩展间隔月。
先扩展补充<最小版本月的
扩展补充>最大补充月的
连接两个补充表:我们可以得到最小版本月补充的列,最大版本月补充的月,然后依据最大版本月,使用IFNULL,用最小版本月进行补充NULL值,就能够得到DATEFULL的补充列了。可以看到这是符合要求中的补充规则的。数据表中有3、4、6、8四个月份的版本,那么我们得到的DATEFULL应该是3版本对应1、2、3月,4版本对应4、5月,6版本对应6、7月,8版本对应8、9、10、11、12月。达到效果。
4、最终数据查询,字段依次是:展示维度月份,日期版本,日期版本对应的数据汇总。
完整SQL脚本:
#时间维度作为主表
WITH MONTHTABLE AS (
SELECT DISTINCT
T.`YEAR`,
T.YEARMONTH
FROM
test_dim.dim_Time T
WHERE 1=1
AND T.YEAR='2022'
),
#数据表处理:连接临时表A与B,这样我们就可以按月取到当月最新的一个更新日期的版本明细数据,然后就能统计当月最新变化的总数
DATATEST AS (
SELECT A.EDITION_DATE,A.MAXDATE,B.CREATE_DATE,SUM(B.TOTALNUM)TONUM
FROM
(SELECT EDITION_DATE,MAX(CREATE_DATE)MAXDATE FROM month_full_testdata
WHERE 1=1
GROUP BY EDITION_DATE)A -- 临时表A:查出每个月最新更新一个版本日期
LEFT JOIN
(SELECT * FROM month_full_testdata )B ON A.EDITION_DATE=B.EDITION_DATE AND B.CREATE_DATE=A.MAXDATE -- 临时表B:查询数据表明细
WHERE 1=1 GROUP BY A.EDITION_DATE,A.MAXDATE,B.CREATE_DATE
)
SELECT
TT.YEARMONTH,
TT.DATEFULL,
GG.TONUM
FROM
(
/*最终从DD与CC连接中,进行版本月的补充,以MAXDATE日期作为主要选择,MINDATE作为NULL时的补充,得到版本选择日期DATEFULL,最后再连接数据表的计算,这样就可以得到使用历史版本月自动补充其他月份的数据,做为计算的基数了。*/
SELECT
DD.YEARMONTH,
DD.MINDATE,
CC.MAXDATE,
IFNULL(CC.MAXDATE,DD.MINDATE)DATEFULL
FROM
#临时表DD:通过连接查询,补充出小于数据表版本日期的月份
(SELECT T.YEARMONTH,MIN(T.EDITION_DATE)MINDATE
FROM
(SELECT
A.YEARMONTH,B.EDITION_DATE
FROM MONTHTABLE A
LEFT JOIN DATATEST B ON A.YEARMONTH<=B.EDITION_DATE)T
WHERE 1=1 GROUP BY T.YEARMONTH )DD
LEFT JOIN
-- #临时表CC:通过连接补充出大于数据表版本日期的部分
(SELECT T.YEARMONTH,MAX(T.EDITION_DATE)MAXDATE
FROM
(SELECT
A.YEARMONTH,B.EDITION_DATE
FROM MONTHTABLE A
LEFT JOIN DATATEST B ON A.YEARMONTH>=B.EDITION_DATE)T
WHERE 1=1 GROUP BY T.YEARMONTH)CC ON CC.YEARMONTH=DD.YEARMONTH
)TT
LEFT JOIN DATATEST GG ON GG.EDITION_DATE=TT.DATEFULL -- 使用版本日期作为ON条件,补充出符合的数据
数据表脚本:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for month_full_testdata
-- ----------------------------
DROP TABLE IF EXISTS `month_full_testdata`;
CREATE TABLE `month_full_testdata` (
`ID` int(0) NOT NULL AUTO_INCREMENT,
`CREATE_DATE` date NULL DEFAULT NULL,
`EDITION_DATE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`TOTALNUM` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of month_full_testdata
-- ----------------------------
INSERT INTO `month_full_testdata` VALUES (1, '2022-04-02', '2022-04', 23);
INSERT INTO `month_full_testdata` VALUES (2, '2022-04-13', '2022-04', 34);
INSERT INTO `month_full_testdata` VALUES (3, '2022-03-01', '2022-03', 56);
INSERT INTO `month_full_testdata` VALUES (4, '2022-06-14', '2022-06', 5);
INSERT INTO `month_full_testdata` VALUES (5, '2022-08-22', '2022-08', 67);
INSERT INTO `month_full_testdata` VALUES (6, '2022-06-24', '2022-06', 14);
INSERT INTO `month_full_testdata` VALUES (7, '2022-04-29', '2022-04', 33);
INSERT INTO `month_full_testdata` VALUES (8, '2022-04-29', '2022-04', 52);
INSERT INTO `month_full_testdata` VALUES (9, '2022-06-14', '2022-06', 24);
INSERT INTO `month_full_testdata` VALUES (10, '2022-08-22', '2022-08', 45);
SET FOREIGN_KEY_CHECKS = 1;