Pure SQL implementation of small algorithm (auxiliary decision) _ Calculate product score, timely replenishment

respectively, the calculation of their respective 1, click/Click Average value 2, sales/sales mean the sum of the two, you can get a simple score and problems, pork should not score more than pork. So we have to add a simple weight, such as a click score of 30%. Sales score was 70%

select p_type,p_name,  (P_VIEW/VIEW_AVG) *0.3+ (sales/sales_avg) *0.7 from  (select a.*, b.sales_avg,c. view_avg  from  (select   p_type,a.p_name,a.p_view,  Ifnull (b.p_sales,0)  as sales from products a     LEFT  Join products_sales bon a.p_id=b.p_idgroup by  a.p_type,a.p_name order  by a.p_type desc, a.p_view desc )  a , ( select p_type,round ( SUM (Sales)/count (*), 0)  as sales_avg from  ( select  p_type, a.p_name, A.p_view, ifnull (b.p_sales,0)  as sales from products a      left join products_sales bon a.p_id=b.p_idgroup by  a.p_type,a.p_name  order by a.p_type desc, a.p_view desc )  cwhere c.sales>0group  by p_type) b, (SELect p_type,round (SUM (p_view)/count (*), 0)  as view_avg from products GROUP  by p_type ) cwhere a.p_type=b.p_type  and a.p_type=c.p_type )  aa

Pure SQL implementation of small algorithm (auxiliary decision) _ Calculate product score, timely replenishment