mysql 计算逻辑

背景

每天的id是唯一的

1. 算出当月月均值，当月对应的上月日期（表名 a）

SELECt
	c.id AS "编号",
	DATE_FORMAT( c.gmt_create, '%Y.%m' ) AS "当月",
	sum( c.daily_value )/ count( c.id) AS "当月月均值",
	sum( c.daily_value ) AS "当月总和",
	count( c.id ) AS "当月个数",
	max(c.daily_value) as "当月最高值",
	min(c.daily_value) as "当月最低值",
	DATE_FORMAT( DATE_SUB( c.gmt_create, INTERVAL 1 MONTH ), '%Y.%m' ) AS '上月' 
FROM
	comp_index c 
GROUP BY
	DATE_FORMAT( c.gmt_create, '%Y.%m' ),
	DATE_FORMAT( DATE_SUB( c.gmt_create, INTERVAL 1 MONTH ), '%Y.%m' ),
	id

2. 关联出上月月均值（表名b）

SELECt
	b.*,
	a.`当月月均值` AS "上月月均值" 
FROM
	(
	SELECt
		c.id AS "编号",
		DATE_FORMAT( DATE_SUB( c.gmt_create, INTERVAL 1 MONTH ), '%Y.%m' ) AS '上月',
		count( id ) 
	FROM
		comp_index c 
	GROUP BY
		DATE_FORMAT( DATE_SUB( c.gmt_create, INTERVAL 1 MONTH ), '%Y.%m' ),
		id 
	) b
	LEFT JOIN (
	SELECt
		c.id AS "编号",
		DATE_FORMAT( c.gmt_create, '%Y.%m' ) AS "当月",
		sum( c.daily_value )/ count( c.id ) AS "当月月均值",
		DATE_FORMAT( DATE_SUB( c.gmt_create, INTERVAL 1 MONTH ), '%Y.%m' ) AS '上月' 
	FROM
		comp_index c 
	GROUP BY
		DATE_FORMAT( c.gmt_create, '%Y.%m' ),
		DATE_FORMAT( DATE_SUB( c.gmt_create, INTERVAL 1 MONTH ), '%Y.%m' ),
		id 
	) a ON a.`编号` = b.`编号` 
	AND a.`当月` = b.`上月`

3、关联上月和当月（表名c）

SELECt
	a.*,
	b.`上月月均值` 
FROM
	a
	LEFT JOIN b ON a.`上月` = b.`上月` 
	AND a.`编号` = b.`编号`

1、取出每周数据,上周日期,按周计算

create table week_price (
SELECt
	a.id,
	a.ts_WEEK,
	a.last_week,
	a.`year`,
	a.`month`,
	sum( a.daily_price ) week_sum,
	count( a.id ) cout,
	sum( a.daily_price )/ count( a.id ) week_avg,
	max( a.daily_price ) AS week_max,
	min( a.daily_price ) AS week_min 
FROM
	(
	SELECt
		c.id,
		YEARWEEK( c.gmt_create, 1 ) AS ts_WEEK,  -- 这周
		YEARWEEK(( DATE_FORMAT( DATE_SUB( c.gmt_create, INTERVAL 1 WEEK ), '%Y-%m-%d' ) ), 1 ) AS last_week, -- 上周
		c.daily_price daily_price,
		YEAR ( gmt_create ) YEAR,
		MONTH ( gmt_create ) MONTH 
	FROM
		comp_index c 
	ORDER BY
		ts_WEEK 
	) a 
GROUP BY
	a.id,
	a.ts_WEEK,
	a.last_week,
	a.`year`,
	a.`month`
)

2、关联出上周平均价

CREATE TABLE week_price_a (
	SELECt
		b.*,
		a.week_avg AS last_avg 
	FROM
	( SELECt 
			w.custom_id, 
			w.last_week,
			w.`MONTH` MONTH,
			w.`YEAR` YEAR 
		FROM week_price w ) b
	LEFT JOIN
	( SELECt 
			w.custom_id, 
			w.ts_WEEK, 
			w.week_avg, 
			w.`MONTH` MONTH,
			w.`YEAR` YEAR 
		FROM week_price w ) a 
	ON a.custom_id = b.custom_id 
	AND a.ts_WEEK = b.last_week 
	AND a.MONTH = b.MONTH 
	AND a.YEAR = b.YEAR 
);

3、合并相关字段

CREATE TABLE week_price_fn AS (
	SELECt
		w.*,
		a.last_avg 
	FROM
		week_price w
		LEFT JOIN week_price_a a ON a.id = w.id 
		AND w.last_week = w.last_week 
		AND a.MONTH = w.MONTH 
	AND a.YEAR = w.YEAR 
	)