分类目录归档:SQL

SQL实例系列 之三

ER图
产品 与 供应商实现多对多关系,但是每个产品有一个默认供应商,这个字段记录在产品-供应商表(正确应该写入产品表);采购员 和 供应商也实现多对多关系,供应商有一个默认的采购员,写入了采购员-供应商(应该写入供应商表才对)。

现在问题是,要根据一个SKU,获取默认的采购员代码(就是要进入采购员表)。流程如下,先根据SKU定位到产品ID,根据这个产品ID到产品-供应商表中获取到供应商代码,用这个代码到采购员-供应商表中获取采购员ID,用这个ID到采购员表中获取采购员代码。

这个过程少说要搞5个查询…..

试着分析一下,产品有默认供应商,供应商有默认采购员,那么不就意味着产品有默认采购员吗?这样的话,就应该把默认供应商和默认采购员写入产品表中,当要获取供应商和采购员时都非常容易(也高效)。但是目前不是这样搞的。最终使用如下SQL实现这个需求:

SELECT u.采购员 FROME 产品表 p LEFT JOIN 产品多属性表 pv ON p.pid=pv.pid JOIN 产品-供应商表 pp ON pp.pid=p.pid AND pp.is_default=1 JOIN
(采购员-供应商 ppu JOIN采购员 u ON u.uid=ppu.uid AND ppu.is_default=1)
ON pp.supplier_code = ppu. supplier_code
WHERE if(p.is_var,pv.sku,p.sku)=SKU

很费劲,有么有?

数据模型设计的重要性在这里充分体现出来。

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端)。

SQL实例系列 之一

有产品表,产品上架表,站点表,产品表和产品上架表的关系是1对n关系,站点表和产品上架表也是1对n的关系,产品表和站点表通过产品上架表实现了n对n的关系。通俗说就是,产品上架表中记录了每个产品、产品上架时间、是否在上架以及上架到了哪个站点。

现在要找出所有产品的最早上架时间以及是否已经上架(注,产品上架表中记录了同一个产品的多条记录,都有一个上架时间,以及这个产品是否在上架,只要有一个在上架就说明这个产品在上架的),那么从字面理解就是找出同一产品的所有记录,按照时间从小到大排序,取第一条记录的时间即为最早上架时间,然后对同一产品所有记录进行循环,判断是否有产品在上架,一旦碰到真就返回。

这个搞法面对数据表里面的上10万条数据时(超过1万产品),实在让人发毛。虽然从程序上来看,实现非常简单,也很好理解,但是它是最低效的。更加高效的做法应该是充分利用数据库的特征:

SELECT count(is_list) as is_list, create_time 
FROM product_site 
GROUP BY product_id ORDER BY create_time ASC

通过数据表索引的帮助,这条语句非常快速的返回了结果,虽然也有上万条记录,但是这个做法比最原始的做法至少提升10倍。10倍是什么概念,如果原始做法要10秒,那么后面的这个搞法只要1秒。

实际工作中遇到的问题,是为总结记录。