数据库上机实验答案2-3

数据库上机实验答案2-31描述性统计对卖家信誉、商品价格、期内销量等三个属性进行描述性统计。要求：统计样本容量，最大值，最小值，平均值；绘制分布图。例：卖家信誉selectcount(*)as样本容量,max(rp_as_seller)as最大值,min(rp_as_seller)as最小值,avg(rp_as_seller)as平均值fromsellersselectround(rp_as_seller,-3)asrp_group,count(*)asfreqfromsellersgroupbyround(rp_as_seller,-3)orderbyrp_groupasc商品价格selectcount(*)as样本容量,max(good_price)as最大值,min(good_price)as最小值,avg(good_price)as平均值fromgoodsselectround(good_price,-2)asprice_group,count(*)asfreqfromgoodsgroupbyround(good_price,-2)orderbyprice_groupascselectcount(*)as样本容量,max(good_soldinterm)as最大值,min(good_soldinterm)as最小值,avg(good_soldinterm*1.0)as平均值fromgoods_extent注：这里avg(good_soldinterm)会得到0，原因是good_soldinterm是整数，且大部分为0，对整数取avg会自动取整，就变成0了。这里good_soldinterm*1.0的目的，就是把这个整数变成小数。selectgood_soldinterm,count(*)asfreqfromgoods_extentgroupbygood_soldintermorderbygood_soldintermasc2变量相关性研究（1）好名字与好销量的关系？首先要定义什么是好名字。比如，定义越长的名字越好。selectlen(good_name)as名字长度,count(*)as商品数量,round(avg((good_soldbefore+good_soldinterm)*1.0),2)as平均销量fromgoods,goods_extent,sellerswheregoods.good_id=goods_extent.good_idandgoods.seller_id=sellers.seller_idgroupbylen(good_name)orderby名字长度asc（2）是否包邮与销量的关系？select'包邮'as是否包邮,count(*)as商品数量,round(avg((good_soldbefore+good_soldinterm)*1.0),2)as平均销量fromgoods,goods_extent,sellerswheregoods.good_id=goods_extent.good_idandgoods.seller_id=sellers.seller_idandgood_shipping=0unionallselect'不包邮'as是否包邮,count(*)as商品数量,round(avg((good_soldbefore+good_soldinterm)*1.0),2)as平均销量fromgoods,goods_extent,sellerswheregoods.good_id=goods_extent.good_idandgoods.seller_id=sellers.seller_idandgood_shipping>0（3）卖家信誉与销量的关系？selectround(log(rp_as_seller+1),0)as卖家信誉,count(*)as商品数量,round(avg((good_soldbefore+good_soldinterm)*1.0),2)as平均销量fromgoods,goods_extent,sellerswheregoods.good_id=goods_extent.good_idandgoods.seller_id=sellers.seller_idgroupbyround(log(rp_as_seller+1),0)orderby卖家信誉asc注：这里的“卖家信誉”不能直接做分组统计，用了一个小技巧，log(rp_as_seller+1)转换为有限的分组个数。这是计量经济学中，对于变量服从对数正态分布、幂率分布时，常用的转换手段。（4）商品价格与销量的关系？selectround(good_price/100,0)as价格,count(*)as商品数量,round(avg((good_soldbefore+good_soldinterm)*1.0),2)as平均销量fromgoods,goods_extent,sellerswheregoods.good_id=goods_extent.good_idandgoods.seller_id=sellers.seller_idgroupbyround(good_price/100,0)orderby价格asc注：这里的价格也做了简单的转换。只用了价格的千位数和百位数做分组依据。（5）卖家信誉与价格的关系？selectround(log(rp_as_seller+1),0)as卖家信誉,count(*)as商品数量,round(avg(good_price*1.0),2)as平均价格fromgoods,goods_extent,sellerswheregoods.good_id=goods_extent.good_idandgoods.seller_id