需求:算出商品的平均点击率、平均销售、商品受欢迎度

1.使用inner join查出每件商品的点击率和销售额度

select a.ptype, a.pname, a.pview, ifnull(b.sales, 0) as selas

      from test a

               left join test_sell b on a.pid = b.id

      order by a.ptype desc, a.pview DESC

结果:

2.查出每个商品类的平均点击率

 select ptype, sum(pview) / count(*) as avg_pview from test group by ptype

3.查出每个商品类的平均销售额度

     select ptype,round(sum(selas) / count(*), 0) as avg_sales

      from (select a.ptype, a.pname, a.pview, ifnull(b.sales, 0) as selas

            from test a

                     left join test_sell b on a.pid = b.id

            order by a.ptype desc, a.pview DESC) a

      where a.selas > 0

      group by ptype

总sql:

select a.ptype,pname,pview,avg_pview,(pview/avg_pview)*0.3,selas,avg_sales,(selas/avg_sales)*0.7,(pview/avg_pview)*0.3+(selas/avg_sales)*0.7

from (select a.ptype, a.pname, a.pview, ifnull(b.sales, 0) as selas

      from test a

               left join test_sell b on a.pid = b.id

      order by a.ptype desc, a.pview DESC) a,

     (

         select ptype, sum(pview) / count(*) as avg_pview from test group by ptype) b,

     (select ptype,round(sum(selas) / count(*), 1) as avg_sales

      from (select a.ptype, a.pname, a.pview, ifnull(b.sales, 0) as selas

            from test a

                     left join test_sell b on a.pid = b.id

            order by a.ptype desc, a.pview DESC) a

      where a.selas > 0

      group by ptype) c

where a.ptype = b.ptype

  and b.ptype = c.ptype order by a.ptype;

总结:通过点击率和销售量的权重,得出商品的欢迎度,

巴特西

sql--测试商品的重要度,是否需要及时补货

需求:算出商品的平均点击率、平均销售、商品受欢迎度

最新文章

热门文章