同环比设计一二三

最新推荐文章于 2022-06-15 19:09:23 发布

那一年天空很高风很清澈

最新推荐文章于 2022-06-15 19:09:23 发布

阅读量417

点赞数

分类专栏：数据处理文章标签：窗口函数同环比 LAG LEAD

本文链接：https://blog.csdn.net/u011572265/article/details/86690287

版权

数据处理专栏收录该内容

3 篇文章 0 订阅

订阅专栏

思路一：表自连接

这种方案能搜到很多类似的回答，点击这个链接。这种方案可以用户一般的业务系统，但是数据量一旦提升还是有一定的查询压力的。这里不多阐述。

思路二：采用窗口函数

oracle有lag和lead窗口函数，配合over(partition by order by)使用。示例文章点击这里。

这里大家都会碰到一个问题就是，时间节点补全的问题。由于数据原因，数据不是每天连续的数据，就会导致在行偏移进行计算的时候，会出现偏移错位的情形，这样计算出来的数据就会出现错误。比如数据库只存在2019-01-27和2019-01-29的数据，在计算偏移的时候就会出现29号和27号进行环比计算的bug，针对这个问题，我们可以采取自主补全时间节点的方案。

建立时间段临时时间表。例如从1900-01-01到2099-12-31号的数据提前录入数据库，然后通过join或者union进行关联查询。这样就会模拟做出每一天的数据，从而避免上面的问题，这里会出现的问题有两个，数据量加大会使查询变慢，第二个问题是两个月份天数不等会出现行偏移错误，所以需要把每个月全部补全，比如就会出现2019-02-31这种数据。然后通过判断最终再把数据给清除掉。这种方案的弊端就是有点凑的感觉，而且维度出现除时间之外的另一个维度的时候，会出现只有时间维度补全了，但是其他维度的内容并没有补全，所以这种方案只适用于单时间维度查询。

SELECT
	*
FROM
	(
		SELECT
			tdate,
      sex,
			v,
			lag (v, 1, NULL) over (PARTITION by sex ORDER BY tdate) prev,
			(
				v - lag (v, 1, NULL) over (PARTITION by sex ORDER BY tdate)
			) xx
		FROM
			(
				SELECT
					tdate,
          sex,
					v
				FROM
					(
						SELECT
							DATE_FORMAT(create_date, '%Y-%m-%d') AS tdate,
							sex as sex,
							count(customer_id) AS v
						FROM
							tf_f_customer t
						GROUP BY
							DATE_FORMAT(create_date, '%Y-%m-%d'),
							sex
						UNION ALL
							SELECT
								temp_date AS tdate,
                temp_value as sex,
								count(temp_value) AS v
							FROM
								temp_date temp_date
							GROUP BY
								temp_date,
                temp_value
					) AS temp
				ORDER BY
					tdate
			) td
		ORDER BY
			td.tdate
	) result
WHERE
	v != 0
AND prev != 0
AND v IS NOT NULL
AND prev IS NOT NULL

思路三：窗口函数变种。

这种思路是受一些开源OLAP引擎的启发。先附上sql。

SELECT
			CONCAT(
				td. YEAR,
				'年',
				td. MONTH,
				'月',
				td. DAY,
				'日'
			) AS field02676,
			IFNULL(td.field11917, 0.00) field11917,
			IFNULL(td.field85970, 0.00) field85970
		FROM
			(
				SELECT
					result. YEAR,
					result. MONTH,
					result. DAY,
					result.WEEKTH,
					result. WEEK,

				IF (
					DATEDIFF(
						CONCAT(
							result. YEAR,
							'-',
							result. MONTH,
							'-',
							result. DAY
						),
						lag (
							CONCAT(
								result. YEAR,
								'-',
								result. MONTH,
								'-',
								result. DAY
							),
							1,
							NULL
						) over (

							ORDER BY
								result. YEAR,
								result. MONTH,
								result. DAY
						)
					) = 1,
					(
						field11917 - lag (result.field11917, 1, NULL) over (

							ORDER BY
								result. YEAR,
								result. MONTH,
								result. DAY
						)
					),
					NULL
				) field11917,
				field85970
			FROM
				(
					SELECT
						tmp. YEAR,
						tmp. MONTH,
						tmp. DAY,
						tmp.WEEKTH,
						tmp. WEEK,
						SUM(`skjq4td7dv`) AS field11917,
						SUM(`skjq4td7dv`) AS field85970
					FROM
						(
							SELECT
								DATE_FORMAT(skhl8envgz, '%Y') AS YEAR,
								DATE_FORMAT(skhl8envgz, '%m') AS MONTH,
								DATE_FORMAT(skhl8envgz, '%d') AS DAY,
								DATE_FORMAT(skhl8envgz, '%u') AS WEEKTH,
								DATE_FORMAT(skhl8envgz, '%w') AS WEEK,
								t.*
							FROM
								skuyjsemhf t
						) tmp
					GROUP BY
						tmp. YEAR,
						tmp. MONTH,
						tmp. DAY,
						tmp.WEEKTH,
						tmp. WEEK
					ORDER BY
						tmp. YEAR,
						tmp. MONTH,
						tmp. DAY,
						tmp.WEEKTH,
						tmp. WEEK
				) result
			GROUP BY
				result. YEAR,
				result. MONTH,
				result. DAY,
				result.WEEKTH,
				result. WEEK
			) td
		WHERE
			(
				field11917 IS NOT NULL
				OR field85970 IS NOT NULL
			)