数据字段含义:
item_id: 商品ID;
shop_id: 店铺ID;
insert_time: 更新时间;
volume: 月销量;
cprice: 实际价格
任务:(分别使用SQL和python代码完成)
1、找出一天内有多次更新的商品ID
2、对于一天内有多次更新的商品,只保留当天最后一条记录,去掉其他记录
3、分析商品的最后更新时间分布,看能否得出有价值结论
alter table taobao_data add 日期 date;
update taobao_data set 日期=cast(insert_time as date);
alter table taobao_data add 精准日期 datetime;
update taobao_data set 精准日期=cast(insert_time as datetime);
当我将cvs导入MySQL的时候发现日期他是varchar形式的,所以要用cast函数进行格式转换。因为2,3题需要计算当天的指标,所以我们日期格式化的时候要加上以天为单位的日期。
select item_id,日期,count(item_id) as 修改量
from
taobao_data
GROUP BY 日期,item_id
having 修改量>=2
ORDER BY 修改量 desc
一天内更新多次的记录也就是数据是>1 或者是>=2 两种写法都可以,顺序是先按照每天日期分组,再按照item_id,也就是商品分组。比如说1月8号为一组,在这组里面再以某个商品为一组,就可以count出他一天的交易量。
第二题:
delete from taobao_data
where 精准日期=
(select 精准日期
from
(select item_id,row_number() over (partition by item_id,日期 order by 精准日期 desc) as 排序,精准日期
from
taobao_data
where item_id in
(select item_id
from taobao_data
GROUP BY 日期,item_id
having count(item_id)>=2
)
)a
where 排序 >1
)
这题写的复杂,应该有效率更高的写法。
思路:顺延第一题,找出一天内修改多次的数据,因为只有这些数据需要删除。之后按照窗口函数,先对item分组再对日期分组,之后按照精准日期就是具体哪一秒的时间进行排序,下图查询结果可以看到,按照降序后的结果最新的日期会排在第一位,所以我们直接进行where 排序>1 筛选即可,然后删除。
导出到Excel进行透视表操作:
得出结论:发现在24点和22点是更新商品的高峰期。
直接用groupby,计算count,在筛选>1即可
第二题:
用rank方法可以实现SQL的 窗口函数,对day和itemid分组,在进行insert_time排序,然后row_number<2 也就是等于1的保留,因为这条数据是当天最新数据。
第三题:
转换成小时,导入matplotlib 进行绘图,结论同上。