mysql 计算逻辑

Davina_yu

已于 2022-11-08 15:16:05 修改

阅读量276

点赞数

分类专栏： mysql 文章标签： mysql 数据库 etl

于 2022-06-22 09:36:25 首次发布

本文链接：https://blog.csdn.net/Davina_yu/article/details/125402512

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、按月，计算当月月均值，上月月均值

背景

每天的id是唯一的

1、在明细表中筛选出需要的字段

CREATE TABLE industry_day (
	SELECT
		* 
	FROM
		comp_index 
	WHERE
		id IN ( 70, 80,  134 ) 
)

2、按照公式，算出每天的价格，公式中，哪个字段为空，就不算

create table industry_day_tj as (
select 
DISTINCT
w.date,
(ifnull(a.id,null)*0.5 + ifnull(b.id,null)*0.8 +  (ifnull(c.id,null)*0.65)*0.6) as daily_price
FROM
	industry_day w
	LEFT JOIN ( SELECT * FROM industry_day w WHERE custom_id = 70 ) a ON a.date= w.date 
	LEFT JOIN ( SELECT * FROM industry_day w WHERE custom_id = 80 ) b ON b.date= w.date 
	LEFT JOIN ( SELECT * FROM industry_day w WHERE custom_id = 134 ) c ON c.date= w.date 
 )

3、计算每月数据

CREATE TABLE industry_mon (
SELECT
	DATE_FORMAT( a.date, '%Y.%m' ) AS this_month,
	DATE_FORMAT( DATE_SUB( a.date, INTERVAL 1 MONTH ), '%Y.%m' ) AS last_month,
	max( a.daily_price ) AS max_price_mon,
	min( a.daily_price ) AS min_price_mon,
	sum( a.daily_price ) AS sum_mon,
	count( a.daily_price ) AS count_mon,
	sum( a.daily_price )/ count( a.daily_price ) AS mon_avg 
FROM
	industry_day_tj a 
GROUP BY
	this_month,
	last_month
)

4、关联出上月数据

CREATE TABLE industry_mon_last (
	SELECT
	DISTINCT
		a.last_month,
		(select b.mon_avg from 	industry_mon  b where  b.this_month = a.last_month) as last_avg			
	FROM
	 industry_mon  a
);

5、合并相关数据

CREATE TABLE industry_monthly (
	SELECT
		a.*,
		b.last_avg
	FROM
		industry_mon a
	LEFT JOIN industry_mon_last b ON  a.last_month = b.last_month
)

二、按周，计算周均值，上周均值（每天需要公式算出的价格）

1、在明细表中筛选出需要的字段

CREATE TABLE industry_day (
	SELECT
		* 
	FROM
		comp_index 
	WHERE
		id IN ( 70, 80,  134 ) 
)

2、按照公式，算出每天的价格，公式中，哪个字段为空，就不算

create table industry_day_tj as (
select 
DISTINCT
w.date,
(ifnull(a.id,null)*0.5 + ifnull(b.id,null)*0.8 +  (ifnull(c.id,null)*0.65)*0.6) as daily_price
FROM
	industry_day w
	LEFT JOIN ( SELECT * FROM industry_day w WHERE custom_id = 70 ) a ON a.date= w.date 
	LEFT JOIN ( SELECT * FROM industry_day w WHERE custom_id = 80 ) b ON b.date= w.date 
	LEFT JOIN ( SELECT * FROM industry_day w WHERE custom_id = 134 ) c ON c.date= w.date 
 )

3、算出每周数据

CREATE TABLE industry_week (
	SELECT
		a.ts_week,
		a.last_week,
		sum( a.daily_price )/ count( a.daily_price ) week_price 
	FROM
		(
		-- 每天数据，对应 周
		SELECT
			YEARWEEK( d.date, 1 ) AS ts_week,-- 这周
			YEARWEEK(( DATE_FORMAT( DATE_SUB( d.date, INTERVAL 1 WEEK ), '%Y-%m-%d' ) ), 1 ) AS last_week,-- 上周
			daily_price 
		FROM
			industry_day_tj d 
		ORDER BY
			ts_week 
		) a 
	GROUP BY
		a.ts_week,
		a.last_week 
	)

4、关联出上周数据

CREATE TABLE industry_week_last (
	SELECT
	DISTINCT
		a.last_week,
		(select b.week_price from 	industry_week  b where  b.ts_week = a.last_week ) as last_week_price			
	FROM
	 industry_week  a
);

5、合并相关数据

CREATE TABLE industry_weekly (
	SELECT
		a.ts_week,
		a.last_week,
		a.week_price,
		b.last_week_price 
	FROM
		industry_week a
	LEFT JOIN industry_week_last b ON a.last_week = b.last_week
)

三、按周，计算周均值，上周均值（直接取每天价格）

1、在明细表中筛选出需要的字段

CREATE TABLE industry_day  (
SELECT
	* 
FROM
	comp_index 
WHERE
	id IN ( 10,18))

2、列出每天对应价格

CREATE TABLE industry_day_tj (
	SELECT
	DISTINCT
	c.date,
	(SELECT d.daily_price from industry_day d where c.date = d.date and d.id = 10) p1,
	 (SELECT d.daily_price from industry_day d where c.date = d.date and d.id = 18) p2
FROM
	industry_day c
)

3、计算每周价格

CREATE TABLE industry_day_week (
	SELECT
		a.ts_week,
		a.last_week,
		sum( a.day_p1 )/ count( a.day_p1 ) week_p1,
		sum( a.day_p2 )/ count( a.day_p2 ) week_p2 
	FROM
			industry_day_tj  
	GROUP BY
		a.ts_week,
		a.last_week 
	ORDER BY
		ts_week 
	)

4、关联出上周价格

CREATE TABLE industry_day_week_last (
	SELECT
	DISTINCT
		a.last_week,
		(select b.week_p1 from 	industry_day_week  b where  b.ts_week = a.last_week ) as last_week_p1,
		(select b.week_p2 from 	industry_day_week  b where  b.ts_week = a.last_week ) as last_week_p2				
	FROM 
		industry_day_week  a
);

5、合并相关字段

CREATE TABLE industry_weekly (
	SELECT
		a.ts_week,
		a.last_week,
		a.week_p1,
		a.week_p2,
		b.last_week_p1,
		b.last_week_p2 
	FROM
		industry_day_week a
	LEFT JOIN industry_day_week_last b ON a.last_week = b.last_week)

四、给id对应产品赋值，赋中文名

CREATE TABLE price_produce (
	SELECT
		c.*,
	CASE
			c.id 
			WHEN 11 THEN
			"金" 
			WHEN 12 THEN
			"木" 
			WHEN 13 THEN
			"水" 
			WHEN 14 THEN
			"火" ELSE "土" 
		END AS produce 
	FROM
		price_index c 
	WHERE
	c.id IN ( 11, 12, 13, 14, 15 ) 
	)

五、取每周第一天

SELECT Date, DATE_ADD(Date,INTERVAL -WEEKDAY(Date) DAY) FROM test;

六、周转回日期

202051 -- 转换成 每周的第一天（假设周日为第一天） 
SELECT '202051',str_to_date(concat('202051', " ", 'SUNDAY'), '%X%V %W') as day

在这里插入图片描述

七、varchar转日期

SELECT
-- CONCAT(LEFT(a.month,4),RIGHT(a.month,2),'01',' ','00:00'),
-- str_to_date(CONCAT(LEFT(a.month,4),RIGHT(a.month,2),'01',' ','00:00'),'%Y%m%d %H:%i:%s') as  month 

	DATE_FORMAT( str_to_date(CONCAT(LEFT(a.month,4),RIGHT(a.month,2),'01',' ','00:00'),'%Y%m%d %H:%i:%s'), '%Y.%m' ) AS this_month,
	DATE_FORMAT( DATE_SUB( str_to_date(CONCAT(LEFT(a.month,4),RIGHT(a.month,2),'01',' ','00:00'),'%Y%m%d %H:%i:%s'), INTERVAL 2 MONTH ), '%Y.%m' ) AS last_2_month