我需要得到一个包含每天销售的最受欢迎产品的表。所有数据都存储在Magento中,我使用MySQL编写查询。我只需要Sales_flat_order_item
表。
最终表应该有3列:Date
, Product SKU
和当天最受欢迎的产品MaxQty
的销售单位数。
我提出了对我有效的查询,但我想知道它如何改进,因为我在代码中使用了两次相同的子查询:
1从sales_flat_order_item
- Subquery1
Date
、Product Id
、Sku
、Quantity
2从Subquery1
- Subquery2中选择Date
和Maximum Quantity
3将它们连接在一起,知道日期应该相同,并且Subquery1的Quantity应该等于Subquery2的Maximum Quantity
SELECT DATE( sq2.created_at ) AS CreatedAt, sq0.sku AS SKU, sq2.MaxQty
FROM (
SELECT created_at, product_id, sku, SUM( qty_ordered ) AS qty
FROM `sales_flat_order_item`
GROUP BY DATE( created_at ) , product_id
) AS sq0
JOIN (
SELECT sq.created_at, MAX( sq.qty ) AS MaxQty
FROM (
SELECT created_at, product_id, SUM( qty_ordered ) AS qty
FROM `sales_flat_order_item`
GROUP BY DATE( created_at ) , product_id
) AS sq
GROUP BY DATE( sq.created_at )
) AS sq2 ON DATE( sq2.created_at ) = DATE( sq0.created_at )
AND sq2.MaxQty = sq0.qty
GROUP BY DATE( CreatedAt )
我相信这应该是你想要的。我添加了一个WHERE
子句,只在这个月运行它,以防你有一个巨大的数据库,所以它应该不会花太多时间。
SELECT day, sku, MAX(qty_total) AS qty FROM (
SELECT DATE(created_at) AS day, sku, SUM(qty_ordered) AS qty_total
FROM `sales_flat_order_item`
WHERE created_at > '2015-07%'
GROUP BY sku, day
ORDER BY qty_total DESC
) AS item_count
GROUP BY day