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')