购物中心商户的租金坪效问题？

-- 加权算法
-- 多重分类销售和租金坪效和贡献率
-- create table vcpark.category_ping
-- insert into vcpark.category_ping 
select 
	-- year,
	-- month,
	-- indy,
	-- floorid,
	rent_type, -- as '收租方式',
	-- shopid,
	-- shop,
	-- position,
	-- if(shopid not in (select distinct shopid from vcpark.sale where month=202206) and shop !='H&M','否','是') as '在租',
	sale_million100 as '销售[亿]',
	rent_million100 as '租金[亿]',
	cnt as '数量',
	loss_quit as '亏损数',
	loss_quit_rate, -- as '亏损数占比',
	-- dc_rent_type as '计租类型变更数',
	-- turnover_rate as '换手次数',
	category_weighted_sale_ping, -- as '销售坪效',
	category_weighted_rent_ping, -- as '租金坪效',
	(sale_contribution_ratio - rent_contribution_ratio * category_weighted_ratio) * 10000  as money_index, -- as '商户赚钱指数', 
	sale_contribution_ratio - rent_contribution_ratio * category_weighted_ratio as break_even_point, -- as '盈亏平衡点',
	sale_contribution_ratio, -- as '销售贡献率',
	rent_contribution_ratio, -- as '租金贡献率',
	ping_ratio as '坪效租售比',
	category_weighted_ratio -- as '租售比'
from
(
	select year,month,indy,floorid,rent_type,shopid,shop,position,
		sum(sale) / 100000000 as sale_million100,
		sum(rent) / 100000000 as rent_million100,
		count(*) as cnt,
		-- 销售贡献率 - 租金贡献率 * 租售比小于0为退租充分条件
		count(if(m_break_even_point < 0,1,null)) as loss_quit, -- as '亏损数',
		-- 大于0.7有退租可能为必要条件
		count(if(m_break_even_point < 0,1,null)) / count(*) as loss_quit_rate,
		count(distinct rent_type) as dc_rent_type,
		count(distinct shop) as turnover_rate,
		sum((sale / area) * (sale / s_sale)) as category_weighted_sale_ping,
		sum((rent / area) * (rent / s_rent)) as category_weighted_rent_ping,
		sum(weighted_sale_contribution_ratio / whole_weighted_sale_contribution_ratio)  as sale_contribution_ratio,
		sum(weighted_rent_contribution_ratio / whole_weighted_rent_contribution_ratio)  as rent_contribution_ratio,
		sum((rent / area) * (rent / s_rent)) / sum((sale / area) * (sale / s_sale)) as ping_ratio,
		sum(ratio * rent / s_rent) as category_weighted_ratio
	from
	(
		select s.*,s_sale,s_rent,whole_weighted_sale_contribution_ratio,whole_weighted_rent_contribution_ratio
		from
		(
			(
				select *,
					weighted_sale_contribution_ratio - weighted_rent_contribution_ratio * ratio  as m_break_even_point
				from vcpark.ping 
				order by month
			) as s,
			(
				select category_group_left_table.*,
					whole_weighted_sale_contribution_ratio,
					whole_weighted_rent_contribution_ratio
				from
				(
					(
						select year as y,month as m,indy as dy,floorid as fid,rent_type as rt,position as p,shopid as id,
							sum(sale) as s_sale,
							sum(rent) as s_rent
						from vcpark.ping
						-- group by year
						-- group by year,indy
						-- group by year,floorid
						-- group by year,rent_type
						-- group by year,shopid
						-- group by month
						-- group by month,indy
						-- group by month,indy,rent_type
						-- group by month,floorid
						-- group by indy
						-- group by indy,floorid
						-- group by floorid
						group by rent_type
						-- group by position
						-- group by indy,rent_type
						-- group by floorid,rent_type
						-- group by indy,floorid,rent_type
						-- group by rent_type,shopid
						-- group by indy,rent_type,position
						-- group by indy,floorid,shopid
						-- group by shopid
					) as category_group_left_table,
					(
						select year as yy,month as mm,indy as ddy,floorid as ffid,rent_type as rrt,position as pp,shopid as iid,
							sum(weighted_sale_contribution_ratio) as whole_weighted_sale_contribution_ratio,
							sum(weighted_rent_contribution_ratio) as whole_weighted_rent_contribution_ratio
						from vcpark.ping
						-- 少1层分类
						-- 2层分类
						-- group by year
						-- 3层分类
						-- group by year,indy
						-- group by year,floorid
						-- group by year,rent_type
						-- group by month
						-- group by month,indy
						-- group by month,floorid
						-- group by indy
						-- group by floorid
						-- group by indy,floorid
						-- group by rent_type
						-- group by indy,floorid,rent_type
						-- group by indy,rent_type
					) as category_group_right_table
				)
				-- where y=yy
				-- where y=yy and dy=ddy
				-- where y=yy and fid=ffid
				-- where y=yy and rt=rrt
				-- where m=mm
				-- where id=iid
				-- where m=mm and dy=ddy
				-- where m=mm and fid=ffid
				-- where dy=ddy
				-- where fid=ffid
				-- where rt=rrt
				-- where dy=ddy and fid=ffid
				-- where dy=ddy and fid=ffid and rt=rrt
				-- where dy=ddy and rt=rrt
			)as category_group_table  -- 外层条件合并
		)
		-- where year=y
		-- where year=y and indy=dy
		-- where year=y and floorid=fid
		-- where year=y and rent_type=rt
		-- where year=y and shopid=id
		-- where month=m
		-- where shopid=id
		-- where month=m and indy=dy
		-- where month=m and indy=dy and rent_type=rt
		-- where month=m and floorid=fid
		-- where indy=dy
		-- where floorid=fid
		where rent_type=rt
		-- where rent_type=rt and shopid=id
		-- where position=p
		-- where indy=dy and floorid=fid
		-- where indy=dy and rent_type=rt
		-- where floorid=fid and rent_type=rt
		-- where indy=dy and floorid=fid and rent_type=rt
		-- where indy=dy and rent_type=rt and `position`=p
		-- where indy=dy and floorid=fid and shopid=id
	)as outer_category_group_table
	-- group by year
	-- group by month
	-- group by indy
	-- group by floorid
	group by rent_type
	-- group by position
	-- group by shopid
	-- group by year,shopid
	-- group by year,indy
	-- group by year,indy,rent_type
	-- group by year,indy,shopid
	-- group by year,floorid
	-- group by year,floorid,rent_type
	-- group by year,rent_type
	-- group by year,rent_type,shopid
	-- group by month,indy
	-- group by month,indy,rent_type
	-- group by month,floorid
	-- group by month,floorid,rent_type
	-- group by month,rent_type
	-- group by indy,floorid
	-- group by indy,floorid,shopid
	-- group by indy,rent_type
	-- group by floorid,rent_type
	-- group by indy,floorid,rent_type
	-- group by indy,rent_type,position
	-- group by rent_type,shopid
	-- group by indy,floorid,shopid
	-- group by indy,floorid,rent_type,shopid
)as whole_combination_table
-- where rent_type='纯扣点' -- shop like '%漫咖啡%' or shop like '%星巴克%' -- indy='CY' -- and shop like '%鼻祖%' -- loss_quit_rate >=0.7 -- and indy='FW' -- and -- floorid=0 and  -- position like '%3-011/012%' -- shopid='0348' or shopid='0356' indy='LS' -- and shopid in (select distinct shopid from vcpark.sale where month=202206)
-- order by year;
-- order by month;
-- order by indy;
-- order by floorid;
order by rent_type;
-- order by turnover_rate desc;
-- order by money_index desc;
-- order by year,money_index desc;
-- order by year,indy;
-- order by year,indy,rent_type;
-- order by year,indy,money_index desc;
-- order by year,floorid;
-- order by year,floorid,rent_type;
-- order by year,rent_type;
-- order by year,rent_type,money_index desc;
-- order by year,money_index desc;
-- order by month,indy;
-- order by month,indy,rent_type;
-- order by indy,rent_type,month;
-- order by month,floorid;
-- order by month,floorid,rent_type;
-- order by month,rent_type;
-- order by indy,floorid;
-- order by indy,floorid,money_index desc;
-- order by indy,rent_type;
-- order by floorid,rent_type;
-- order by indy,floorid,rent_type;
-- order by rent_type,money_index desc;
-- order by money_index desc,indy,rent_type;
-- order by indy,floorid,rent_type,money_index desc;

- 非加权退租算法
-- 与加权算法有差异
select
	-- month,
	shop,
	rent_type as '计租方式',
	-- off as '退租状态',
	-- count(distinct rent_type) as '计租类型数',
	sum(diff_index) as money_index,
	count(month) as '租赁数',
	-- 销售贡献率 - 租金贡献率 * 租售比小于0为退租判断条件
	count(if(diff_index < 0,1,null)) as '亏损数',
	-- 大于0.7有退租可能
	count(if(diff_index < 0,1,null)) / count(month) as lost -- as '亏损数占比'
from
(
	select month,
		shop,
		indy,
		floorid,
		area,
		rent_type,
		sale,
		rent,
		-- weighted_sale_ping,
		-- weighted_rent_ping,
		ratio,
		-- (销售贡献率 - 租金贡献率 * 租售比) * record_count
		(weighted_sale_contribution_ratio - weighted_rent_contribution_ratio * ratio) * 1000 as BEP
		-- if(shopid not in (select distinct shopid from vcpark.sale where month=202206) and shop !='H&M',1,0) as off
	from vcpark.ping
	where shop = '%%' -- indy='CY' and rent_type='纯扣点' -- -- shopid not in (select distinct shopid from vcpark.sale where year=2022) -- and shop like '%%' -- 
	order by shop,month
)as s
group by shop
-- group by month
order by money_index desc,shop;
-- order by month;