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

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

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

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

思路:
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

结果：

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