58同城深圳商铺出租分析
5 商區均價和占比排名起首把district為深圳的行的district改成對應的行政區名，把district為空的行的district改成‘其他’
update tongchengshoprental set district=case when location in ("龙华新區",
"宝安",
"龙岗區",
"南山",
"罗湖",
"福田",
"布吉",
"坪山新區",
"大鹏新區",
"盐田",
"光亮新區"
) then location when district = ''then '其他' else district end;
然後把district為‘布吉’的行的district改成‘龙岗區’：
update tongchengshoprental set district=case when district = '布吉' then '龙岗區' else district end;
然後把區的名字格局同一為两個字：
update tongchengshoprental set district=case when district like '%區%' then left(district, 2) else district end;
接下来的数据洗濯主如果三步，把每平米天天房錢乘以30获得每平米每個月房錢；從面积中提掏出数字；去掉每平米天天房錢小于0.5的分歧理数据。
先建立一個從字符串中提取数字的函数：
CREATEFUNCTION `GetDeciNum`( Varstring VARCHAR ( 50 ) ) RETURNS varchar(30) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE
v_length INT DEFAULT 0;
DECLARE
v_Tmp VARCHAR ( 50 ) DEFAULT '';
SET v_length = CHAR_LENGTH( Varstring );
WHILE
v_length > 0 DO
IF
(
ASCII( mid( Varstring, v_length, 1 ) ) > 47
AND ASCII( mid( Varstring, v_length, 1 ) ) < 58
OR ASCII( 音波拉皮 ,mid( Varstring, v_length, 1 ) ) = 46
) THEN
SET v_Tmp = concat( v_Tmp, mid( V娛樂城推薦,arstring, v_length, 1 ) );
END IF;
SET v_length = v_length - 1;
END WHILE;
RETURN REVERSE( v_Tmp );
END
然後建立一個知足以上三個前提的新视图：
CREATE VIEW tongchengshoprental_new AS SELECT
title,
url,
true_month_price,
true_day_price,
true_day_price * 30 AS true_price_per_month,
true_area,
type,
STATUS,
district,
location,
address,
tags
FROM
(
SELECT
title,
url,
CONVERT ( month_price, DECIMAL ( 10, 2 ) ) true_month_price,
CONVERT ( day_price, DECIMAL ( 10, 2 ) ) true_day_price,
CONVERT ( GetDeciNum ( area ), DECIMAL ( 10, 2 ) ) true_area,
type,nba即時比分,
STATUS,
瘦身燃脂丸,district,
location,
address,
tags
FROM
tongchengshoprental
) tmp
WHERE
true_day_price > 0.5
新视图tongchengshoprental_new成果以下：
現實举行阐發的表
表布局以下：
1 出租总價、面积、每平米每個月代價的最大最小值和均匀值
select max(true_month_price), min(true_month_price), avg(true_month_price) from tongchengshoprental_new
出租总價的最大最小值和均匀值
出租总價的均匀值為130985，中位数為13000，均匀值比中位数大不少
select max(true_area), min(true_area), avg(true_area) from tongchengshoprental_new
出租面积的最大最小值和均匀值
出租面积的均匀值為338.73平米，中位数為75平米
每平米每個月代價的最大最小值和均匀值
每平米每個月代價的均匀值為527.53，中位数為142.2
2 每一個區的代價均匀值和数目占比是几多
SELECT
district,
concat( round( count( 1 ) / ( SELECT count( 1 ) FROM tongchengshoprental_new ) * 100, 2 ), '%' ) '占比',
round(avg( true_price_per_month ), 2) '均匀房錢(元/月*平方米)'
FROM
tongchengshoprental_new
GROUP BY
1
ORDER BY
除蟎洗面乳,3 DESC;
深圳每一個區的商店出租均匀房錢排行
罗湖和宝安均匀房錢排前两位，坪山和大鹏排最後两位
SELECT
district '行政區',
concat( round( count( 1 ) / ( SELECT count( 1 ) FROM tongchengshoprental_new ) * 100, 2 ), '%' ) '占比',
round(avg( true_price_per_month ), 2) '均匀房錢(元/月*平方米)'
FROM
tongchengshoprental_new
GROUP BY
1
ORDER BY
count( 1 ) DESC;
深圳每一個區的商店出租占比排行
南山和龙岗占比最高，大鹏和盐田占比最低
3 每種商店类型的占比和均價
頁:
[1]