间隔量和总量的区别

间隔量和总量的区别

测试表结构

CREATE TABLE `test_table` (
  `create_date` date NOT NULL,
  `interval_num` int(11) DEFAULT NULL COMMENT '间隔数量',
  `current_num` int(11) DEFAULT NULL COMMENT '当前数量',
  PRIMARY KEY (`create_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

间隔量算每月总量

select create_date,1000+sum(interval_num) month_num from test_table GROUP BY DATE_FORMAT(create_date,'%Y-%m')

总量算每天差额

SELECT
    t1.create_date,
    t1.current_num,
    IFNULL(t1.current_num - t2.current_num, 0) AS increase_amount
FROM
    test_table t1
LEFT JOIN
    test_table t2 ON t1.create_date = DATE_ADD(t2.create_date, INTERVAL 1 DAY)
ORDER BY
    t1.create_date;

总量算每月差额

SELECT
    t1.ym,
    t1.current_num,
		t2.current_num,
    IFNULL(t1.current_num - t2.current_num, 0) AS increase_amount
FROM
    (select concat(DATE_FORMAT(create_date,'%Y-%m'),'-01') ym,sum(current_num) current_num from test_table GROUP BY DATE_FORMAT(create_date,'%Y-%m')) t1
LEFT JOIN
    (select concat(DATE_FORMAT(create_date,'%Y-%m'),'-01') ym,sum(current_num) current_num from test_table GROUP BY DATE_FORMAT(create_date,'%Y-%m')) t2 ON t1.ym = DATE_ADD(t2.ym, INTERVAL 1 month)
ORDER BY
    t1.ym;