笛卡尔积妙用（跨月的数据处理）

本文链接：https://blog.csdn.net/weixin_44813544/article/details/125248379

场景数据比较多，主要摘出实现的条件字段做个例子记录：测试环境MYSQL8.0

数据表：

业务场景：有这样的申请单，计算延迟单比率，按年月展示延迟单比率。

现在需要按月统计单子当月的情况：在单子时长>=80个小时，则当月延迟单+1，计算每个月的延迟单率。

分析：看表我们可以看到数据有三中状态

1、当月开始，当月结束；

2、当月开始，下月（=1）结束；

3、当月开始，跨月（>1）结束。

如何判断是否属于延迟单：开始时间以及结束时间之差>=80h；

计算比率：当月延迟单数/当月总数。

结论：此时我们需要明确的最简易的算法就是，得到每个月每一条数据的开始时间与结束时间，这样可以直接用函数返回两个时间之差，在判断是否>=80h即可，当月总数也能直接计算。

数据分类计算：

状态1的数据：因为都在当月，可以直接计算；

状态2的数据：我们需要注意的是分成两部分，第一个是开始日期+开始日期当月月末，第二个是结束时间+结束日期月初；

状态3的数据：需要分成三个部分，第一个是开始日期+开始日期当月月末，第二个是中间月月初+中间月月末，第三个是结束时间+结束日期月初。

整体解析完成，知道思路后，实现过程：

以年月为维度进行统计，那么我们需要一个时间维度表作为主表（统计展示的话，避免当月数据为空时月份不展示，所以以时间维度表作为主表去连接数据表）。使用WITH语法，可以写多个临时表然后集合使用，第一个临时表写时间维度表（维度表中暂时只有21年以及22年，所以只有去重后只有24条数据）。我们取维度表中的年，年月，以及每个月的月初以及月末（月初月末后边有用）。

时间维度表设计好后，第二个临时表取数据表的数据。

接着利用表连接的机制去产生笛卡尔积，然后借助主表的时间给每条数据分配合适的开始日期与结束日期。以主表的年月为ON筛选器的条件，取 >=数据表的开始日期 AND <= 数据表的结束日期。以其中一条数据看，可以看到在开始日期与结束日期中按月生成了笛卡尔积。这样我们就能够得到所有数据的按月扩散的明细表。这时候就可以开始筛选每一条数据的开始日期与结束日期了。

每一条数据的开始日期与结束日期的选择：使用CASE WHEN 语法，

开始日期：

1、当主表年月=数据表开始日期年月时，输出数据表开始日期作为开始日期；

2、当主表年月>数据表开始日期年月 AND 主表年月<=数据表结束日期年月取主表FIRSTDAY作为开始日期

结束日期：

1、主表年月<结束日期年月，输出主表LASTDAY；

2、主表年月=结束日期年月，输出数据表结束日期。

从结果上可以看到一条数据原本从2021-10月开始到2022-04结束，已经通过笛卡尔积扩散到每个月，并且选定了合适且合理的开始日期以及结束日期，这样就可以以这个扩散表为明细表统计每个月的总数，以及延迟单的总数了。这样指标就可以计算出来了。

总结：简单复现一下，主要说明如何通过笛卡尔积在合适的区间内扩散，生成我们需要的明细数据，实际上会更复杂一点，因为还有计划月的统计，具体到哪个小时划分，以及外来的时间参数（外来时间参数第一个首先影响到我们统计的基表数据--需要开始日期都<外来日期参数；第二个时在选择结束日期时需要加入外来日期参数的考虑）。

整体实现SQL（时间维度表看上一章：利用存储过程生成时间维度表）：

#主表日期，选择带上当月的月初以及月末。外来参数可控，具体计划月的划分，具体小时的划分，也可以在这里完成。这里的思想：得出我们需要的时间维度，然后通过年月与数据表年月进行区间内的笛卡尔积扩散，得到准确的数据扩散表，然后再选择判断扩散数据的分类，分别选择合适的开始日期以及结束日期。
WITH MONTHTABLE AS (
SELECT  DISTINCT
	T.`YEAR`,
	T.YEARMONTH,
	CAST(DATE_ADD(T.DATE, interval - day(T.DATE) + 1 day) AS DATETIME)FIRSTDAY,
	CAST(LAST_DAY(T.DATE) AS DATETIME)LASTDAY
FROM
	test_dim.dim_Time T
	WHERE 1=1 
	),  
	
	#数据表查询，原表样不需要处理，直接查询，这个位置看具体的表结构，以及数据格式
	DATATABLE AS (
	SELECT
		T.ORDERNUM,
		T.BEGINDATE,
		LEFT(T.BEGINDATE,7)BEGINDATEAA,
		T.ENDDATE,
	  LEFT(	T.ENDDATE,7)ENDDATEBB
	FROM
		test_dim.month_periodic_statistics T
	)
	



#左连接查询，利用笛卡尔积在年月区间内扩散，得到数据源表的扩散，然后再结合主表时间维度选择合适的开始与结束日期。
	SELECT 
	TT.YEARMONTH,
	TT.ORDERNUM,
	(CASE WHEN TT.YEARMONTH=TT.BEGINDATEAA THEN TT.BEGINDATE
	      WHEN TT.YEARMONTH>TT.BEGINDATEAA AND TT.YEARMONTH<=TT.ENDDATEBB THEN TT.FIRSTDAY
				ELSE NULL END
	)BEGINDATE,   -- 判断取开始日期
		(CASE WHEN TT.YEARMONTH<TT.ENDDATEBB THEN TT.LASTDAY
	        WHEN TT.YEARMONTH=TT.ENDDATEBB THEN TT.ENDDATE	
				ELSE NULL END	
	)ENDDATE  -- 判断取结束日期
	
	FROM
	(
	
	SELECT 
	A.YEARMONTH,A.FIRSTDAY,A.LASTDAY,B.ORDERNUM,B.BEGINDATE,B.BEGINDATEAA,B.ENDDATE,B.ENDDATEBB
	FROM MONTHTABLE A
	LEFT JOIN  DATATABLE B ON  A.YEARMONTH>=B.BEGINDATEAA   AND A.YEARMONTH<=ENDDATEBB
	WHERE 1=1 
	AND B.ORDERNUM='BLNO004'
	)TT
-- 	ORDER BY

测试数据表：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for month_periodic_statistics
-- ----------------------------
DROP TABLE IF EXISTS `month_periodic_statistics`;
CREATE TABLE `month_periodic_statistics`  (
  `ID` int(0) NOT NULL AUTO_INCREMENT,
  `ORDERNUM` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `BEGINDATE` datetime(0) NULL DEFAULT NULL,
  `ENDDATE` datetime(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_periodic_statistics
-- ----------------------------
INSERT INTO `month_periodic_statistics` VALUES (1, 'BLNO001', '2021-12-01 00:00:00', '2021-12-03 22:59:05');
INSERT INTO `month_periodic_statistics` VALUES (2, 'BLNO002', '2022-02-03 10:59:38', '2022-03-01 14:59:52');
INSERT INTO `month_periodic_statistics` VALUES (3, 'BLNO003', '2022-04-06 23:00:24', '2022-07-19 15:00:32');
INSERT INTO `month_periodic_statistics` VALUES (4, 'BLNO004', '2021-10-06 23:00:55', '2022-04-20 17:01:06');
INSERT INTO `month_periodic_statistics` VALUES (5, 'BLNO005', '2022-05-10 23:01:28', '2022-05-11 19:01:35');

SET FOREIGN_KEY_CHECKS = 1;