[Magento SQL] 按天统计产品的浏览量

SELECT t1.added_at,t2.sku,t1.product_id FROM report_viewed_product_index AS t1  
LEFT JOIN catalog_product_entity  AS t2 ON t2.entity_id=t1.product_id  
WHERE t2.sku IN('HW52032+')   
AND t1.added_at>='2016-06-21' 
           

数据表是商品每当浏览一下就记录一条

如果要统计一段时间内的总浏览器SQL这样写：

SELECT t1.added_at,t2.sku,COUNT(t1.product_id) as total_views FROM report_viewed_product_index AS t1  
LEFT JOIN catalog_product_entity  AS t2 ON t2.entity_id=t1.product_id  
WHERE t2.sku IN('HW52032+')   
AND t1.added_at>='2016-06-21' 
GROUP BY t1.product_id;
           

如果要按天统计，需要再安装日期分组。所以这里需要格式化日期字段。

SELECT t1.added_at,t2.sku,COUNT(t1.product_id) as total_views FROM report_viewed_product_index AS t1  
LEFT JOIN catalog_product_entity  AS t2 ON t2.entity_id=t1.product_id  
WHERE t2.sku IN('HW52032+')   
AND t1.added_at>='2016-06-21' 
GROUP BY t1.product_id,DATE_FORMAT(t1.added_at,'%m-%d-%Y')