计算每个月的前六个月内的累计值

最新推荐文章于 2024-09-09 11:39:14 发布

原创最新推荐文章于 2024-09-09 11:39:14 发布 · 1.4k 阅读

4 ·

CC 4.0 BY-SA版权

本文介绍了一种方法来计算全量累计数据，通过建立日历表和事实表，利用时间连接统计六个月内的累计值。包括创建日历表、事实表及计算某个月份前六个月累计值的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

业务需求:计算每个月的前六个月内的累计值

增量:比较好求,就是求统计日期在当月之前六个月至当月值的累计
但是对于全量来说,就不好求了,因为大概一思考,这里涉及到循环,也就是说,每个月都需要找前六个月内的累计值.

下面提供一种方法来计算全量累计值.

思路:
1.建立一个日历表
2.建立一个事实表
3.以日历表为主表,通过时间进行不等值连接,统计六个月内的累计值.

--1.日历表 2014年整年
CREATE TABLE tmp_lxq_2 AS
SELECT DATE'2014-01-01' AS statis_dt FROM dual;
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-02-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-03-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-04-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-05-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-06-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-07-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-08-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-09-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-10-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-11-01';
INSERT INTO TMP_LXQ_2
SELECT DATE '2014-12-01';
COMMIT;

SELECT * FROM TMP_LXQ_2;

--结果：

--事实表日期+数量
DROP TABLE tmp_lxq_1;
CREATE TABLE tmp_lxq_1 AS
(SELECT DATE'2014-01-01' AS statis_dt,'100' AS qty ) WITH DATA;

INSERT INTO TMP_LXQ_1
SELECT DATE '2014-02-01' AS STATIS_DT, '200' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-03-01' AS STATIS_DT, '300' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-04-01' AS STATIS_DT, '400' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-05-01' AS STATIS_DT, '500' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-06-01' AS STATIS_DT, '600' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-07-01' AS STATIS_DT, '700' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-08-01' AS STATIS_DT, '800' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-09-01' AS STATIS_DT, '900' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-10-01' AS STATIS_DT, '1000' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-11-01' AS STATIS_DT, '100' AS QTY;
INSERT INTO TMP_LXQ_1
SELECT DATE '2014-12-01' AS STATIS_DT, '100' AS QTY;

SELECT * FROM TMP_LXQ_1;

--结果：

--计算某个月内的前六个月的值分别是什么(此处计算8月份）
SELECT A.*, B.*
FROM TMP_LXQ_1 A
INNER JOIN (SELECT T.STATIS_DT AS CALENDAR_DATE
FROM TMP_LXQ_2 T
WHERE T.STATIS_DT >= DATE '2014-08-01'
AND T.STATIS_DT < DATE '2014-09-01') B
ON A.STATIS_DT >= CAST(ADD_MONTHS(CAST(B.CALENDAR_DATE AS DATE FORMAT 'YYYYMM'), -6) AS DATE FORMAT 'YYYYMMDD')
AND A.STATIS_DT <= CAST(CAST(CAST(B.CALENDAR_DATE AS DATE FORMAT 'yyyymm') AS VARCHAR(6)) || '01' AS DATE FORMAT 'yyyymmdd')

结果：

--在第三步的基础上进行分组求累计值
SELECT B.CALENDAR_DATE, SUM(A.QTY)
FROM TMP_LXQ_1 A
INNER JOIN (SELECT T.STATIS_DT AS CALENDAR_DATE
FROM TMP_LXQ_2 T
WHERE T.STATIS_DT >= DATE '2014-01-01'
AND T.STATIS_DT < DATE '2014-12-01') B
ON A.STATIS_DT >= CAST(ADD_MONTHS(CAST(B.CALENDAR_DATE AS DATE FORMAT 'YYYYMM'), -6) AS DATE FORMAT 'YYYYMMDD')
AND A.STATIS_DT <= CAST(CAST(CAST(B.CALENDAR_DATE AS DATE FORMAT 'yyyymm') AS VARCHAR(6)) || '01' AS DATE FORMAT 'yyyymmdd')
GROUP BY B.CALENDAR_DATE

结果：

至此，结果就正确表达出来了。