题解 | 电商平台需要对商品的销售和评价情况进行综合分析

WITH t1 AS(
    SELECT product_id,sum(quantity) total_quantity
    FROM sales_underline
    GROUP BY product_id
),
t2 AS(    
    SELECT product_id,round(avg(rating),2) average_rating
    FROM reviews_underline 
    GROUP BY product_id
)

SELECT a.product_id,a.product_name,t1.total_quantity,t2.average_rating
FROM products_underline AS a
INNER JOIN t1 ON a.product_id = t1.product_id
INNER JOIN t2 ON a.product_id = t2.product_id
WHERE t2.average_rating < 4
ORDER BY average_rating ASC,product_id ASC

我的方法可能比较繁琐，我是先将下面两个表的数据聚合计算出平均分和售出总数，然后再将三个表连结选出符合条件的列