SQL实例系列 之二

有产品表,产品多属性表,订单表,订单详情表。产品多属性表记录产品的多个属性,比如颜色为红色,尺寸为5寸的产品组成一个新的SKU插入产品多属性表,那么这条记录必定就有它的主SKU,所以产品表中通过一个多属性字段记录该产品是否是多属性产品。产品表中有记录该产品最早上线时间。订单详情表中记录了订单具体的产品,每个订单详情中的产品记录有主SKU字段和是否多属性字段和多属性SKU,它通过主SKU和多属性SKU和产品表以及产品多属性表产品联系。每个订单都有创建时间。

现在要查找一批产品从它的最早上线时间开始,顺延一段时间(比如30天)的这一段时间内产生的销售总额(分货币显示)和有销售的产品和涉及到的SKU数以及涉及到的所有订单并且在每个订单后列出这些产品贡献的销售额(分货币显示)。

主要使用如下SQL获取所有符合的记录:

SELECT p.product_id, p.product_name, p.product_sku, p.product_is_more_var, p.product_is_list, if(p.product_is_more_var,pm.product_more_var_sku,p.product_sku) as display_sku, if(p.product_create_time,from_unixtime('%Y%M%D',p.product_create_time),0) as product_create_time, if(p.product_first_list_time,from_unixtime('%Y%M%D',p.product_first_list_time),0) as product_first_list_time,
o.order_number, o.order_currency_code,
od.order_product_qty, od.order_price
FROM 
(product p LEFT JOIN product_more_vars pm ON p.produc_id = pm.product_id) 
JOIN 
(order o JOIN order_detail ON o.order_id = od.order_id) 
ON od.order_product_sku = if(p.product_is_more_var,pm.product_more_var_sku,p.product_sku)
WHERE ((o.order_create_time > product_first_list_time) AND (o.order_create_time < product_first_list_time+30*24*2600))
AND product_id in(......)

可以很容易的在后面添加GROUP BY语句进行各类汇总,如果添加多次GROUP BY语句发送SQL,看起来效率不高(这里是大数据集)。所以最终决定循环一遍这个结果集,然后顺便做各种汇总,这样这里就使用了一个多重嵌套的数组。

关于使用JOIN还是LEFT JOIN,需要紧记,当需要一个集合全部记录时,LEFT JOIN就很合适,以上提到的产品与产品多属性表就必须使用LEFT JOIN来获取完整的产品集合,这个搞法奇葩地方在于,当使用SKU定位产品信息时,你到产品表中可能无法匹配,因为它可能是多属性的子SKU,同样,你到多属性表中定位这个SKU时,可能也无法定位,因为这个SKU的产品是非多属性的,产品表中SKU就是它的实际SKU。

然后把这个汇总数组传递到视图,在视图中调用JS显示详情(比如涉及到的所有订单),如果一页显示不下还要做翻页处理(JS端)。