背景:获取了主流电商的页面数据,做大盘数据分析
–行业平台
–各平台销额销量以及占比情况
select Platform '平台',sum(SalesVolume) '销量',sum(transprice*SalesVolume) '销额',
sum(SalesVolume)*1.0/(select sum(SalesVolume)*1.0 from [CrawlorData.Global].[dbo].[raw_data1]) '销量占比',
sum(Transaction_Price*SalesVolume)/(select sum(transprice*SalesVolume) from [CrawlorData.Global].[dbo].[raw_data1]) '销额占比'
FROM [CrawlorData.Global].[dbo].[raw_data1]
GROUP BY Platform
–各平台链接数
select Platform '平台',COUNT (Platform) '链接数'
FROM [CrawlorData.Global].[dbo].[raw_data1]
GROUP BY Platform
–各平台评论量以及占比情况
select Platform '平台',sum(isnull(CommentNum,CollectPopularity)) '评论人数',
sum(isnull(CommentNum,CollectPopularity))*1.0/(select sum(isnull(CommentNum,CollectPopularity))*1.0 from [CrawlorData.Global].[dbo].[raw_data1]) '评论量占比'
FROM [CrawlorData.Global].[dbo].[raw_data1]
GROUP BY Platform
–品类
–各平台品类销额销量以及占比情况
select a.Platform '平台',a.category_guifan '品类',a.销量,a.销额,a.销量*1.0/(b.总销量*1.0) '销量占比',a.销额*1.0/(b.总销额*1.0)'销额占比'
FROM (select Platform,category_guifan,sum(SalesVolume) '销量',sum(Transaction_Price*SalesVolume) '销额'
FROM [CrawlorData.Global].[dbo].[raw_data1]
GROUP BY Platform,category_guifan)a,
(select Platform,sum(SalesVolume) '总销量',sum(Transaction_Price*SalesVolume) '总销额'
FROM [CrawlorData.Global].[dbo].[raw_data1]
GROUP BY Platform)b
where a.Platform = b.Platform
–各平台评论量以及占比情况
select a.Platform '平台',a.category_guifan '品类',a.评论人数,a.评论人数*1.0/(b.评论总人数*1.0)'评论占比'
FROM
(select Platform,category_guifan,sum(isnull(CommentNum,CollectPopularity)) '评论人数',
sum(isnull(CommentNum,CollectPopularity))*1.0/(select sum(isnull(CommentNum,CollectPopularity))*1.0
from [CrawlorData.Global].[dbo].[raw_data1]) '评论量占比'
FROM [CrawlorData.Global].[dbo].[raw_data1]
GROUP BY Platform,category_guifan)a,
(select Platform,sum(isnull(CommentNum,CollectPopularity))'评论总人数'
FROM [CrawlorData.Global].[dbo].[raw_data1]
GROUP BY Platform)b
where a.Platform = b.Platform
–行业平台
–各平台销额销量以及占比情况
select Platform '平台',sum(transprice*SalesVolume) '销额',sum(SalesVolume) '销量',
sum(Transaction_Price*SalesVolume)/(select sum(transprice*SalesVolume) from [CrawlorData.Global].[dbo].[raw_data]) '销额占比',
sum(SalesVolume)*1.0/(select sum(SalesVolume)*1.0 from [CrawlorData.Global].[dbo].[raw_data]) '销量占比'
FROM [CrawlorData.Global].[dbo].[raw_data]
GROUP BY Platform
–天猫平台
–各品类销额销量以及占比情况
select a.category_guifan '品类',a.销额,a.销量,a.销额*1.0/(b.总销额*1.0)'销
额占比',a.销量*1.0/(b.总销量*1.0) '销量占比'
FROM (select Platform,category_guifan,sum(SalesVolume) '销量',sum(transprice*SalesVolume) '销额'
FROM [CrawlorData.Global].[dbo].[raw_data]
Where Platform='Tmall'
GROUP BY Platform,category_guifan)a,
(select Platform,sum(SalesVolume) '总销量',sum(transprice*SalesVolume) '总销额'
FROM [CrawlorData.Global].[dbo].[raw_data]
Where Platform='Tmall'
GROUP BY Platform)b
where a.Platform = b.Platform
–各品牌销额销量以及占比情况
select a.Brand '品牌',a.销额,a.销量,a.销额*1.0/(b.总销额*1.0)'销额占比',a.销量*1.0/(b.总销量*1.0) '销量占比'
FROM (select Platform,Brand,sum(SalesVolume) '销量',sum(transprice*SalesVolume) '销额'
FROM [CrawlorData.Global].[dbo].[raw_data]
Where Platform='Tmall'
GROUP BY Platform,Brand)a,
(select Platform,sum(SalesVolume) '总销量',sum(transprice*SalesVolume) '总销额'
FROM [CrawlorData.Global].[dbo].[raw_data]
Where Platform='Tmall'
GROUP BY Platform)b
where a.Platform = b.Platform
ORDER BY a.销额 desc
–各平台品类销额销量以及占比情况
select distinct Platform '平台',category_guifan '品类',shopname '店铺名称',Brand '品牌'
FROM [CrawlorData.Global].[dbo].[raw_data_1]
–价格段占比
select
a.产品所属平台,a.规范类目,a.价格带,
case when b.total_num is not null then a.销量/b.total_num else 0 end num_per,
case when b.total_offtake is not null then a.销售额/b.total_offtake else 0 end offtake_per,
case when b.total_com is not null then a.评论量/b.total_com else a.种草人数/b.total_gra end com_per
from jiagedai100 a
left join(
select
产品所属平台,规范类目,
sum(销量) total_num,sum(销售额)total_offtake,sum(评论量) total_com,sum(种草人数) total_gra
from jiagedai100
group by 产品所属平台,规范类目)b
on a.产品所属平台 = b.产品所属平台 and a.规范类目=b.规范类目
order by a.产品所属平台,a.规范类目,a.价格带
– 需求二:价格带分析,求各平台品类品牌价格带的销量、销额、评论数
select * into jiagedai2 from (
select 产品所属平台,规范类目,品牌信息,价格带,sum(月销量) '月销量',sum(销额) '销额',sum(评论数) '评论数' from
(select case when 规范类目='洁面' and 成交价 between 0 and 100 then '0-100'
when 规范类目='洁面' and 成交价 between 100 and 200 then '100-200'
when 规范类目='洁面' and 成交价 between 200 and 300 then '200-300'
when 规范类目='洁面' and 成交价 between 300 and 400 then '300-400'
when 规范类目='洁面' and 成交价 >400 then '400+'
when 规范类目='面膜' and 成交价 between 0 and 60 then '0-60'
when 规范类目='面膜' and 成交价 between 60 and 120 then '60-120'
when 规范类目='面膜' and 成交价 between 120 and 180 then '120-180'
when 规范类目='面膜' and 成交价 between 180 and 240 then '180-240'
when 规范类目='面膜' and 成交价 between 240 and 300 then '240-300'
when 规范类目='面膜' and 成交价 >300 then '300+'
when 规范类目='化妆水/爽肤水' and 成交价 between 0 and 140 then '0-140'
when 规范类目='化妆水/爽肤水' and 成交价 between 140 and 280 then '140-280'
when 规范类目='化妆水/爽肤水' and 成交价 between 280 and 420 then '280-420'
when 规范类目='化妆水/爽肤水' and 成交价 between 420 and 560 then '420-560'
when 规范类目='化妆水/爽肤水' and 成交价 between 560 and 700 then '560-700'
when 规范类目='化妆水/爽肤水' and 成交价 >700 then '700+'
when 规范类目='眼部护理' and 成交价 between 0 and 160 then '0-160'
when 规范类目='眼部护理' and 成交价 between 160 and 320 then '160-320'
when 规范类目='眼部护理' and 成交价 between 320 and 480 then '320-480'
when 规范类目='眼部护理' and 成交价 between 480 and 640 then '480-640'
when 规范类目='眼部护理' and 成交价 between 640 and 800 then '640-800'
when 规范类目='眼部护理' and 成交价 >800 then '800+'
when 规范类目='面部精华' and 成交价 between 0 and 90 then '0-90'
when 规范类目='面部精华' and 成交价 between 90 and 180 then '90-180'
when 规范类目='面部精华' and 成交价 between 180 and 270 then '180-270'
when 规范类目='面部精华' and 成交价 between 270 and 360 then '270-360'
when 规范类目='面部精华' and 成交价 between 360 and 450 then '360-450'
when 规范类目='面部精华' and 成交价 between 450 and 540 then '450-540'
when 规范类目='面部精华' and 成交价 between 540 and 630 then '540-630'
when 规范类目='面部精华' and 成交价 between 630 and 720 then '630-720'
when 规范类目='面部精华' and 成交价 between 720 and 810 then '720-810'
when 规范类目='面部精华' and 成交价 >810 then '810+'
when 规范类目='乳液/面霜' and 成交价 between 0 and 80 then '0-80'
when 规范类目='乳液/面霜' and 成交价 between 80 and 160 then '80-160'
when 规范类目='乳液/面霜' and 成交价 between 160 and 240 then '160-240'
when 规范类目='乳液/面霜' and 成交价 between 240 and 320 then '240-320'
when 规范类目='乳液/面霜' and 成交价 between 320 and 400 then '320-400'
when 规范类目='乳液/面霜' and 成交价 between 400 and 480 then '400-480'
when 规范类目='乳液/面霜' and 成交价 between 480 and 560 then '480-560'
when 规范类目='乳液/面霜' and 成交价 between 560 and 640 then '560-640'
when 规范类目='乳液/面霜' and 成交价 between 640 and 720 then '640-720'
when 规范类目='乳液/面霜' and 成交价 >720 then '720+'
when 规范类目='防晒' and 成交价 between 0 and 70 then '0-70'
when 规范类目='防晒' and 成交价 between 70 and 140 then '70-140'
when 规范类目='防晒' and 成交价 between 140 and 210 then '140-210'
when 规范类目='防晒' and 成交价 between 210 and 280 then '210-280'
when 规范类目='防晒' and 成交价 between 280 and 350 then '280-350'
when 规范类目='防晒' and 成交价 between 350 and 420 then '350-420'
when 规范类目='防晒' and 成交价 between 420 and 490 then '420-490'
when 规范类目='防晒' and 成交价 >490 then '490+'
when 规范类目 not in ('洁面','面膜','化妆水/爽肤水','眼部护理','面部精华','乳液面霜','防晒') and 成交价 between 0 and 50 then '0-50'
when 规范类目 not in ('洁面','面膜','化妆水/爽肤水','眼部护理','面部精华','乳液面霜','防晒') and 成交价 between 50 and 100 then '50-100'
when 规范类目 not in ('洁面','面膜','化妆水/爽肤水','眼部护理','面部精华','乳液面霜','防晒') and 成交价 between 100 and 150 then '100-150'
when 规范类目 not in ('洁面','面膜','化妆水/爽肤水','眼部护理','面部精华','乳液面霜','防晒') and 成交价 between 150 and 200 then '150-200'
when 规范类目 not in ('洁面','面膜','化妆水/爽肤水','眼部护理','面部精华','乳液面霜','防晒') and 成交价 >=200 then '200+'
end as '价格带',销额,产品所属平台,规范类目,品牌信息,月销量,评论数 from [CrawlorData.Global].[dbo].[Tamll_raw_data]
where 规范类目 is not null ) as t
group by 产品所属平台,规范类目,品牌信息,价格带) T
order by 产品所属平台,品牌信息,规范类目,价格带