ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 对一个购物篮需求的思考

对一个购物篮需求的思考

原创 Oracle 作者：Darren_Guo 时间：2014-08-23 21:01:53 0 删除 编辑
      一天一位搞营销的朋友找我帮忙做一个数据分析，就是流行的购物篮分析，需求是这样的，他要根据用户的订单信息，找出哪些产品的组合是最受欢迎的，产品的组合类型为2种产品和3种产品。我根据朋友的要求，设计了两张表，一张basket表，用于存放用户的订单信息，一张item表，用于存放产品信息，代码如下（为简化问题描述，没考虑主外键关系）：
 

点击(此处)折叠或打开

  1. create table basket(order_id varchar2(50),          --订单编号
  2.                     item varchar2(50),              --产品名称
  3.                     item_id varchar2(20),           --产品编号
  4.                     amount number);                 --购买的数量

  6. create table item(name varchar2(50),          --产品名称
  7.                   id varchar(50));            --产品编号

  9. create index ind_item on item(id);
    create index ind_basket on basket(order_id);
    create index ind_basket_it on basket(ITEM);
     解决思路是先根据item表得到2种和3种产品的组合，然后到basket表中匹配组合类型是否出现在订单中，然后对组合出现在订单中出现的次数进行统计，代码如下：

点击(此处)折叠或打开

  1. with x1 as
  2. (select  *
  3.                   from (SELECT ORDER_ID,
  4.                                ltrim(SYS_CONNECT_BY_PATH(ITEM, '+'), '+') xx,
  5.                                LENGTH(SYS_CONNECT_BY_PATH(ITEM, '+')) l1,
  6.                                max(length(SYS_CONNECT_BY_PATH(ITEM, '+'))) over(partition by order_id) l2
  7.                           FROM (select t.*,
  8.                                        row_number() over(PARTITION BY ORDER_ID ORDER BY ITEM) RN
  9.                                   from BASKET t) T
  10.                         CONNECT BY PRIOR T.RN = T.RN - 1
  11.                                AND PRIOR T.ORDER_ID = T.ORDER_ID
  12.                                 )
  13.                  where l1 = l2)
  14. select count(*), str
  15.   from ( select *
  16.           from x1,
  17.                (select name1 || '+' || name2 str, name1, name2
  18.                   from (select i1.name name1,i2.name name2
  19.                           from item i1, item i2
  20.                          where i1.name > i2.name)
  21.                          ) x2
  22.          where instr(x1.xx,x2.name1)>0 and instr(x1.xx,x2.name2)>0 )
  23.  group by str
  24.  order by count(*) desc;
      basket表和item表的记录数如下：

点击(此处)折叠或打开

  1. select count(*) from basket;

  3.   COUNT(*)
  4. ----------
  5.      13618

  7. select count(*) from item;

  9.   COUNT(*)
  10. ----------
  11.        110
    执行计划及执行时间：

点击(此处)折叠或打开

  1. ----------------------------------------------------------------------------------------------------
  2. | Id | Operation             | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
  3. ----------------------------------------------------------------------------------------------------
  4. | 0 | SELECT STATEMENT         |     |     1 | 2082 |     | 329 (2)| 00:00:04 |
  5. | 1 | SORT ORDER BY             |     |     1 | 2082 |     | 329 (2)| 00:00:04 |
  6. | 2 | HASH GROUP BY          |     |     1 | 2082 |     | 329 (2)| 00:00:04 |
  7. | 3 | NESTED LOOPS          |     |     1 | 2082 |     | 327 (1)| 00:00:04 |
  8. | 4 | NESTED LOOPS         |     |     1 |    54 |     |     4 (0)| 00:00:01 |
  9. | 5 | TABLE ACCESS FULL         | ITEM |     1 |    27 |     |     2 (0)| 00:00:01 |
  10. |* 6 | TABLE ACCESS FULL         | ITEM |     1 |    27 |     |     2 (0)| 00:00:01 |
  11. |* 7 | VIEW             |     |    47 | 95316 |     | 323 (1)| 00:00:04 |
  12. | 8 | WINDOW SORT         |     | 18920 | 1237K|     | 323 (1)| 00:00:04 |
  13. |* 9 | CONNECT BY WITHOUT FILTERING|     |     |     |     |        |     |
  14. | 10 | VIEW             |     | 18920 | 1237K|     | 323 (1)| 00:00:04 |
  15. | 11 |     WINDOW SORT         |     | 18920 | 997K| 1200K| 323 (1)| 00:00:04 |
  16. | 12 |      TABLE ACCESS FULL     | BASKET | 18920 | 997K|     |    68 (0)| 00:00:01 |

    Elapsed: 00:13:11.50
   这个记录数，对应这个执行时间，已经到了不能接受的地步，所以必须优化，优化后的代码如下

点击(此处)折叠或打开

  1. with x1 as
  2. (select /*+MATERIALIZE */ *
  3.                   from (SELECT ORDER_ID,
  4.                                ltrim(SYS_CONNECT_BY_PATH(ITEM, '+'), '+') xx,
  5.                                LENGTH(SYS_CONNECT_BY_PATH(ITEM, '+')) l1,
  6.                                max(length(SYS_CONNECT_BY_PATH(ITEM, '+'))) over(partition by order_id) l2
  7.                           FROM (select t.*,
  8.                                        row_number() over(PARTITION BY ORDER_ID ORDER BY ITEM) RN
  9.                                   from BASKET t) T
  10.                         CONNECT BY PRIOR T.RN = T.RN - 1
  11.                                AND PRIOR T.ORDER_ID = T.ORDER_ID
  12.                                 )
  13.                  where l1 = l2)
  14. select count(*), str
  15.   from ( select /*+use_nl(x1,x2) leading(x2) */*
  16.           from x1,
  17.                (select /*+no_merge */name1 || '+' || name2 str, name1, name2
  18.                   from (select i1.name name1,i2.name name2
  19.                           from item i1, item i2
  20.                          where i1.name > i2.name)
  21.                          ) x2
  22.          where instr(x1.xx,x2.name1)>0 and instr(x1.xx,x2.name2)>0 )
  23.  group by str
  24.  order by count(*) desc;
      优化后的执行计划和执行时间：

点击(此处)折叠或打开

  1. ----------------------------------------------------------------------------------------------------------------------
  2. | Id | Operation             | Name          | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  3. ----------------------------------------------------------------------------------------------------------------------
  4. | 0 | SELECT STATEMENT         |             |     47 | 99076 |     |    1760 (1)| 00:00:22 |
  5. | 1 | TEMP TABLE TRANSFORMATION     |             |     |     |     |         |     |
  6. | 2 | LOAD AS SELECT         | SYS_TEMP_0FD9D6609_19CE3B |     |     |     |         |     |
  7. |* 3 | VIEW              |             | 18920 |     37M|     |     323 (1)| 00:00:04 |
  8. | 4 | WINDOW SORT          |             | 18920 |    1237K|     |     323 (1)| 00:00:04 |
  9. |* 5 | CONNECT BY WITHOUT FILTERING|             |     |     |     |         |     |
  10. | 6 | VIEW             |             | 18920 |    1237K|     |     323 (1)| 00:00:04 |
  11. | 7 | WINDOW SORT         |             | 18920 |     997K|    1200K|     323 (1)| 00:00:04 |
  12. | 8 |     TABLE ACCESS FULL     | BASKET         | 18920 |     997K|     |     68 (0)| 00:00:01 |
  13. | 9 | SORT ORDER BY          |             |     47 | 99076 |     |    1438 (1)| 00:00:18 |
  14. | 10 | HASH GROUP BY         |             |     47 | 99076 |     |    1438 (1)| 00:00:18 |
  15. | 11 | NESTED LOOPS         |             |     47 | 99076 |     |    1436 (1)| 00:00:18 |
  16. | 12 | VIEW             |             |     1 |     106 |     |     4 (0)| 00:00:01 |
  17. | 13 | NESTED LOOPS         |             |     1 |     54 |     |     4 (0)| 00:00:01 |
  18. | 14 | TABLE ACCESS FULL     | ITEM          |     1 |     27 |     |     2 (0)| 00:00:01 |
  19. |* 15 | TABLE ACCESS FULL     | ITEM          |     1 |     27 |     |     2 (0)| 00:00:01 |
  20. |* 16 | VIEW             |             |     47 | 94094 |     |    1432 (1)| 00:00:18 |
  21. | 17 | TABLE ACCESS FULL      | SYS_TEMP_0FD9D6609_19CE3B | 18920 |     37M|     |    1432 (1)| 00:00:18 |
  22. ----------------------------------------------------------------------------------------------------------------------

  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------

  27.    3 - filter("L1"="L2")
  28.    5 - access("T"."RN"-1=PRIOR "T"."RN" AND "T"."ORDER_ID\"=PRIOR "T"."ORDER_ID")
  29.   15 - filter("I1"."NAME">"I2"."NAME")
  30.   16 - filter(INSTR("X1"."XX","X2"."NAME1")>0 AND INSTR("X1"."XX","X2"."NAME2")>0)
      Elapsed: 00:00:18.90
      执行时间大有改善，但是，按这个思路，如果要查询3种产品的组合出现的次数就很慢了，代码如下：

点击(此处)折叠或打开

  1. with x1 as
  2. (select /*+MATERIALIZE */ *
  3.                   from (SELECT ORDER_ID,
  4.                                ltrim(SYS_CONNECT_BY_PATH(ITEM, '+'), '+') xx,
  5.                                LENGTH(SYS_CONNECT_BY_PATH(ITEM, '+')) l1,
  6.                                max(length(SYS_CONNECT_BY_PATH(ITEM, '+'))) over(partition by order_id) l2
  7.                           FROM (select t.*,
  8.                                        row_number() over(PARTITION BY ORDER_ID ORDER BY ITEM) RN
  9.                                   from BASKET t) T
  10.                         CONNECT BY PRIOR T.RN = T.RN - 1
  11.                                AND PRIOR T.ORDER_ID = T.ORDER_ID
  12.                                 )
  13.                  where l1 = l2)
  14. select count(*), str
  15.   from ( select /*+use_nl(x1,x2) leading(x2) */*
  16.           from x1,
  17.                (select /*+no_merge */name1 || '+' || name2 || '+' name3 str, name1, name2, name3
  18.                           from item i1, item i2, item i3
  19.                          where i1.name > i2.name and i2.name>i3.name)
  20.                          ) x2
  21.          where instr(x1.xx,x2.name1)>0 and instr(x1.xx,x2.name2)>0 and instr(x1.xx,x2.name3)>0 )
  22.  group by str
  23.  order by count(*) desc;
      到这里，发现如果按此思路，如果产品的类型增加，即item表记录数增加，那这个查询的执行时间会陡增，要想高效的实现这个功能，必须换新的思路。上面的代码其实实现的很复杂，写的时候得花点力气，但效果却不佳。
     回过头想下，其实我们需要的产品组合在basket表中就有，不需要从item表中进行组合。这点很关键，这是对数据的准确理解。所有代码可以改写成如下：

点击(此处)折叠或打开

  1. select item1 || '+' || item2 combination, count(*) amount
  2.                       from (select a.order_id, a.item item1, b.item item2
  3.                               from basket a
  4.                               join basket b
  5.                                 on a.item != b.item
  6.                                and a.order_id = b.order_id
  7.                              where a.item > b.item)
  8.                      group by item1, item2
  9.                      order by combination desc;
      执行计划及执行时间：

点击(此处)折叠或打开

  1. ---------------------------------------------------------------------------------------
  2. | Id | Operation     | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  3. ---------------------------------------------------------------------------------------
  4. | 0 | SELECT STATEMENT |     | 7555 | 796K| | 259    (2)| 00:00:04 |
  5. | 1 | SORT ORDER BY     |     | 7555 | 796K| | 259    (2)| 00:00:04 |
  6. | 2 | HASH GROUP BY |     | 7555 | 796K| | 259    (2)| 00:00:04 |
  7. |* 3 | HASH JOIN     |     | 7555 | 796K| 1224K| 257    (1)| 00:00:04 |
  8. | 4 | TABLE ACCESS FULL| BASKET | 18920 | 997K| | 68    (0)| 00:00:01 |
  9. | 5 | TABLE ACCESS FULL| BASKET | 18920 | 997K| | 68    (0)| 00:00:01 |
  10. ---------------------------------------------------------------------------------------

  12. Predicate Information (identified by operation id):
  13. ---------------------------------------------------

  15.    3 - access("A"."ORDER_ID"="B"."ORDER_ID")
  16.        filter("A"."ITEM\">"B"."ITEM" AND "A"."ITEM"<>"B"."ITEM")
      Elapsed: 00:00:00.29
      此方法中，代码大大简化，sql效率也很高，而且对产品种类数量的增加也能很好的应对。3种产品只需简单修改上述代码即可：

点击(此处)折叠或打开

  1. select item1 || '+' || item2 || '+' || item3 combination, count(*) amount
  2.                       from (select a.item item1, b.item item2 ,c.item item3
  3.                               from basket a,basket b,basket c
  4.                               where a.order_id = b.order_id and b.order_id=c.order_id
  5.                                and a.item > b.item and b.item > c.item)
  6.                      group by item1, item2, item3
  7.                      order by amount desc;
     回顾整个过程，最初的方案没有准确的理解表的数据结构，将整个实现方式复话了，所以，在实现功能的时候，得很好的理解需求的实质和表数据的结构。






来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29821678/viewspace-1256582/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 写在开始
下一篇： 表碎片整理时shrink和move如何选择
请登录后发表评论 登录
全部评论
Darren_Guo

注册时间：2014-08-23

  • 博文量
    29
  • 访问量
    505853

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593