你的位置:首页 > 数据库

[数据库]数据库简单的实际运用 ②


文章讲述了对于数据量较少的处理。包括新建数据库、使用SQL语言建表、向数据库中导入数据、简单的数据查询操作等。下面介绍一个相对上一个较为复杂的数据库。其实也就是数据量增大了点,数据处理要求多了些,运用基础的数据库知识以及网上的各种讲解都可以掌握这些处理方法。

例2

 下面列举某宝一些数据处理。

Table 1: Online user behavior before Dec. 2015. (ijcai2016_taobao)

Field

Description

User_id

unique user id

Seller_id

unique online seller id

Item_id

unique item id

Category_id

unique category id

Online_Action_id

“0” denotes “click” while “1” for “buy”

Time_Stamp

date of the format “yyyymmdd”

 

统计内容:

  • 用户计数特征
  1. 用户总交互次数,用户点击次数,用户购买次数
  2. 用户交互、点击、购买的不同商品种类数
  3. 用户交互、点击、购买的不同商品数
  • 用户时间层级(预测时间为20151201)
  1. 最近1天/3天/10天用户对商品的交互/点击/购买数
  2. 最近1天/3天/10天用交互的不同商品种类总数
  3. 用户周末(周五六日)对商品的交互、点击、购买次数
  • 用户比值特征
  1. 用户点击到购买商品的转化率
  2. 用户周末点击、购买占总点击、购买的比值
  • 商品计数特征
  1. 被点击、被购买次数最多的前5商品
  2. 商品被点击、购买的平均时间间隔

提交结果:

  1. 用户表

1

user_id

2

用户交互次数

3

点击次数

4

购买次数

5

交互不同商品数

6

点击不同商品数

7

购买不同商品数

8

交互不同商品种类数

9

点击不同商品种类数

10

购买不同商品种类数

11

最近1天用户对商品的交互数

12

最近1天用户对商品的点击数

13

最近1天用户对商品的购买数

14

最近3天用户对商品的交互数

15

最近3天用户对商品的点击数

16

最近3天用户对商品的购买数

17

最近10天用户对商品的交互数

18

最近10天用户对商品的点击数

19

最近10天用户对商品的购买数

20

周末用户对商品的交互数

21

周末用户对商品的点击数

22

周末用户对商品的购买数

23

用户点击到购买商品的转化率

24

用户周末点击、购买占总点击、购买的比值

 

  1. 商品表

 

1

item_id

2

商品被点击平均时间间隔

3

商品被购买平均时间间隔

 

实现工具:SQL Server 2014 、基本的数据

1.这里我们可以和例1不同,可以先将淘宝的数据导入到数据库中,但是你也许会问:表都没有建立数据导入到哪里呢?其实如果在例1中的导入数据时候认真观察的话,可以发现当我们将excel导入时候,会有一个默认的表,并且这个表的名字和我们导入的excel的表名字相同(通常情况下,为Sheet),所以我们这里就直接导入数据了哦。

 

2.修改表的列名

3.检验数据是否成功导入

 

use 淘宝SELECT *FROM dbo.taobao

 

4.完成前面的4项要求(注意Online_Action_id=1 或 0 代表不同含义

  1. 用户表

1

user_id

2

用户交互次数

3

点击次数

4

购买次数

use 淘宝select A.User_id, click as 点击次数,buy as 购买次数, click+buy as 用户交互次数from(  select user_id,count(Online_Action_id) click  from taobao  where Online_Action_id=0  group by user_id) as Ajoin  (select user_id,count(Online_Action_id) buy  from taobao  where Online_Action_id=1  group by user_id)as Bon A.user_id=B.user_id

5.完成要求5~7

5

交互不同商品数

6

点击不同商品数

7

购买不同商品数

use 淘宝select A.user_id,click as 点击不同商品数 ,buy as 购买不同商品数,click+buy as 交互不同商品数from(  select user_id,count(Item_id) click  from taobao  where Online_Action_id=0  group by user_id) as Ajoin  (select user_id,count(Item_id) buy  from taobao  where Online_Action_id=1  group by user_id)as Bon A.user_id=B.user_id

 

6.完成要求8~10(注意关键字DISTINCT 的使用)

8

交互不同商品种类数

9

点击不同商品种类数

10

购买不同商品种类数

 

use 淘宝select A.user_id,click as 点击不同商品种类数 ,buy as 购买不同商品种类数,click+buy as 交互不同商品种类数from(  select user_id,count(DISTINCT Item_id) click  from taobao  where Online_Action_id=0  group by user_id) as Ajoin  (select user_id,count(DISTINCT Item_id) buy  from taobao  where Online_Action_id=1  group by user_id)as Bon A.user_id=B.user_id

 7.完成要求11~19(只是天数的改变因此相当于只有三项任务 这里用<2 或<4或<11 是因为<的操作效率比<=要高!)

至于详细讲解见博客http://blog.chinaunix.net/uid-20586655-id-3406139.html

 DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<2 

至于这个函数的作用:详情见http://www.w3school.com.cn/sql/func_datediff.asp

10

购买不同商品种类数

11

最近1天用户对商品的交互数

12

最近1天用户对商品的点击数

13

最近1天用户对商品的购买数

14

最近3天用户对商品的交互数

15

最近3天用户对商品的点击数

16

最近3天用户对商品的购买数

17

最近10天用户对商品的交互数

18

最近10天用户对商品的点击数

19

最近10天用户对商品的购买数

use 淘宝SELECT A.user_id ,click as 最近1天用户对商品的点击数 ,buy as 最近1天用户对商品的购买数,click+buy as 最近1天用户对商品的交互数FROM   (SELECT User_id ,count(Online_Action_id) click  fROM dbo.taobao  WHERE Online_Action_id=0 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<2  GROUP BY User_id)Ajoin  (SELECT User_id ,count(Online_Action_id) buy  FROM dbo.taobao  WHERE Online_Action_id=1 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<2  GROUP BY User_id)BON A.user_id=B.user_id

给出完整的11~19查询语句

use 淘宝SELECT A.user_id ,click as 最近1天用户对商品的点击数 ,buy as 最近1天用户对商品的购买数,click+buy as 最近1天用户对商品的交互数from (SELECT User_id ,count(Online_Action_id) clickfrom dbo.taobaoWhere Online_Action_id=0 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<2Group By User_id)AJoin(SELECT User_id ,count(Online_Action_id) buyfrom dbo.taobaoWhere Online_Action_id=1 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<2Group By User_id)Bon A.user_id=B.user_idSELECT A.User_id ,click as 最近3天用户对商品的点击数 ,buy as 最近3天用户对商品的购买数,click+buy as 最近3天用户对商品的交互数from (SELECT User_id ,count(Online_Action_id) clickfrom dbo.taobaoWhere Online_Action_id=0 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<4Group By User_id)AJoin(SELECT User_id ,count(Online_Action_id) buyfrom dbo.taobaoWhere Online_Action_id=1 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<4Group By User_id)Bon A.user_id=B.user_idSELECT A.User_id ,click as 最近10天用户对商品的点击数 ,buy as 最近10天用户对商品的购买数,click+buy as 最近10天用户对商品的交互数from (SELECT User_id ,count(Online_Action_id) clickfrom dbo.taobaoWhere Online_Action_id=0 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<11Group By User_id)AJoin(SELECT User_id ,count(Online_Action_id) buyfrom dbo.taobaoWhere Online_Action_id=1 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<11Group By User_id)Bon A.user_id=B.user_id

8.完成要求20~22(注意我们的每周第一天和美国的每周第一天是不同的,

所以开始要使用语句SET DATEFIRST 1设置第一天为周一)

在这里的DATEPART(dw,CONVERT(date,time_stamp,110))

参看http://www.w3school.com.cn/sql/func_datepart.asp讲解

 

20

周末用户对商品的交互数

21

周末用户对商品的点击数

22

周末用户对商品的购买数

use 淘宝set DATEFIRST 1SELECT A.User_id ,click as 周末用户对商品的点击数 ,buy as 周末用户对商品的购买数,click+buy as 周末用户对商品的交互数FROM  (SELECT USER_id,Count(online_action_id) click  FROM taobao  WHERE online_action_id =0  AND DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)  GROUP BY User_id)Ajoin  (SELECT USER_id,Count(online_action_id) buy  FROM taobao  WHERE online_action_id =1  AND DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)  GROUP BY User_id)BON A.user_id = B.user_id

 9.完成要求23

函数ltrim(Convert(numeric(9,2),buy*100.0/(click+buy)))+'%'as 用户点击到购买商品的转化率 

详情见https://msdn.microsoft.com/zh-cn/library/ms177827.aspx

23

用户点击到购买商品的转化率

 

use 淘宝SELECT A.user_id,buy,click ,ltrim(Convert(numeric(9,2),buy*100.0/(click+buy)))+'%' as 用户点击到购买商品的转化率FROM   (SELECT USER_id,Count(online_action_id) click  FROM taobao  WHERE online_action_id =0  AND DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)  GROUP BY User_id)Ajoin  (SELECT USER_id,Count(online_action_id) buy  FROM taobao  WHERE online_action_id =1  AND DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)  GROUP BY User_id)BON A.user_id = B.user_id

 

 

10.完成要求24(对于这个要求可以利用前面的周末点击以及周末购买除以总点击、总购买)

24

用户周末点击、购买占总点击、购买的比值

 

那么关键问题是我们该怎么将之前分开的查询操作统一在一起完成一个查询操作呢。

现在放出我们的大招

使用with 可以将我们的查询语句写在一起,并且可以将一堆的查询语句进行重命名,之后只需要使用这个重命名就可以了。具体操作见下面的例子

use 淘宝;set datefirst 1;//加上分号with cnt_action as(select a.User_id, click as 点击次数,buy as 购买次数, click+buy as 用户交互次数from(select user_id,count(Online_Action_id) clickfrom taobaowhere Online_Action_id=0group by user_id)Ajoin(select user_id,count(Online_Action_id) buyfrom taobaowhere Online_Action_id=1group by user_id)Bon A.user_id=B.user_id),//注意加上逗号cnt_click_wk as(SELECT A.User_id ,click as 周末用户对商品的点击数 ,buy as 周末用户对商品的购买数,click+buy as 周末用户对商品的交互数from (select USER_id,Count(online_action_id) clickfrom taobaowhere online_action_id =0and DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)group by User_id)Ajoin(select USER_id,Count(online_action_id) buyfrom taobaowhere online_action_id =1and DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)group by User_id)Bon A.user_id = B.user_id)//这里不加逗号Select cnt_action.user_id,cnt_action.用户交互次数,cnt_action.点击次数,cnt_action.购买次数,ltrim(Convert(numeric(9,2),cnt_click_wk.周末用户对商品的点击数*100.0/(cnt_action.点击次数)))+'%' as 用户周末点击占总点击的比值,ltrim(Convert(numeric(9,2),cnt_click_wk.周末用户对商品的购买数*100.0/(cnt_action.购买次数)))+'%' as 用户周末购买占总购买的比值from cnt_action join cnt_click_wk on cnt_action.user_id=cnt_click_wk.user_id //直接使用重命名就可以

在这里需要注意以下几点:

1.每个重命名都需要不同;

2.每个查询之后的右括号上逗号(,) 最后一个不加;

3.对于ltrim(Convert(numeric(9,2),cnt_click_wk.周末用户对商品的点击数*100.0/(cnt_action.点击次数)))+'%'as 用户周末点击占总点击的比值,这些函数的使用需要通过网上的查找来学会使用;

4.还有就是在with之前的那个语句末尾要加上分号(;)

 

11.上面的with使用给了我们新的技能,那么我们可以用with放出一个特大的招数。这一招可以让我们把上面所有查询得到的表全部融合成一张表,这将是一个巨大无比的表,在没有完全掌握with的时候,千万不要尝试这项操作。因为实在是太大了,而且这个招数会令程序员十分难受,有太多的重复代码。

所以在with大招之前,我们还是把每一个查询都亲自写一遍,这样在把它们组合在一起的时候就不会感到陌生。

现在见证大招的威力:

use 淘宝;set datefirst 1;with cnt_action as(select a.User_id, click as 点击次数,buy as 购买次数, click+buy as 用户交互次数from(select user_id,count(Online_Action_id) clickfrom taobaowhere Online_Action_id=0group by user_id)Ajoin(select user_id,count(Online_Action_id) buyfrom taobaowhere Online_Action_id=1group by user_id)Bon A.user_id=B.user_id),cnt_item as(select A.user_id,click as 点击不同商品数 ,buy as 购买不同商品数,click+buy as 交互不同商品数from(select USER_id,Count(item_id) clickfrom dbo.taobaowhere Online_Action_id = 0 group by user_id) Ajoin(select USER_id,Count(item_id) buyfrom dbo.taobaowhere Online_Action_id = 1 group by user_id) Bon A.user_id=B.user_id),cnt_item_type as(select A.user_id,click as 点击不同商品种类数 ,buy as 购买不同商品种类数,click+buy as 交互不同商品种类数from(select USER_id,Count(DISTINCT item_id) clickfrom dbo.taobaowhere Online_Action_id = 0 group by user_id) Ajoin(select USER_id,Count(DISTINCT item_id) buyfrom dbo.taobaowhere Online_Action_id = 1 group by user_id) Bon A.user_id=B.user_id),cnt_oneday as(SELECT A.user_id ,click as 最近1天用户对商品的点击数 ,buy as 最近1天用户对商品的购买数,click+buy as 最近1天用户对商品的交互数from (SELECT User_id ,count(Online_Action_id) clickfrom dbo.taobaoWhere Online_Action_id=0 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<2Group By User_id)AJoin(SELECT User_id ,count(Online_Action_id) buyfrom dbo.taobaoWhere Online_Action_id=1 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<2Group By User_id)Bon A.user_id=B.user_id),cnt_threeday as(SELECT A.User_id ,click as 最近3天用户对商品的点击数 ,buy as 最近3天用户对商品的购买数,click+buy as 最近3天用户对商品的交互数from (SELECT User_id ,count(Online_Action_id) clickfrom dbo.taobaoWhere Online_Action_id=0 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<4Group By User_id)AJoin(SELECT User_id ,count(Online_Action_id) buyfrom dbo.taobaoWhere Online_Action_id=1 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<4Group By User_id)Bon A.user_id=B.user_id),cnt_tenday as(SELECT A.User_id ,click as 最近10天用户对商品的点击数 ,buy as 最近10天用户对商品的购买数,click+buy as 最近10天用户对商品的交互数from (SELECT User_id ,count(Online_Action_id) clickfrom dbo.taobaoWhere Online_Action_id=0 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<11Group By User_id)AJoin(SELECT User_id ,count(Online_Action_id) buyfrom dbo.taobaoWhere Online_Action_id=1 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<11Group By User_id)Bon A.user_id=B.user_id),cnt_click_wk as(SELECT A.User_id ,click as 周末用户对商品的点击数 ,buy as 周末用户对商品的购买数,click+buy as 周末用户对商品的交互数from (select USER_id,Count(online_action_id) clickfrom taobaowhere online_action_id =0and DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)group by User_id)Ajoin(select USER_id,Count(online_action_id) buyfrom taobaowhere online_action_id =1and DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)group by User_id)Bon A.user_id = B.user_id),cnt_percent as(SELECT A.user_id,buy,click ,ltrim(Convert(numeric(9,2),buy*100.0/(click+buy)))+'%' as 用户点击到购买商品的转化率from (select USER_id,Count(online_action_id) clickfrom taobaowhere online_action_id =0and DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)group by User_id)Ajoin(select USER_id,Count(online_action_id) buyfrom taobaowhere online_action_id =1and DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)group by User_id)Bon A.user_id = B.user_id)Select cnt_item.user_id,cnt_action.用户交互次数,cnt_action.点击次数,cnt_action.购买次数,cnt_item.交互不同商品数,cnt_item.点击不同商品数,cnt_item.购买不同商品数,cnt_item_type.交互不同商品种类数,cnt_item_type.点击不同商品种类数,cnt_item_type.购买不同商品种类数,cnt_oneday.最近1天用户对商品的交互数,cnt_oneday.最近1天用户对商品的点击数,cnt_oneday.最近1天用户对商品的购买数,cnt_threeday.最近3天用户对商品的交互数,cnt_threeday.最近3天用户对商品的点击数,cnt_threeday.最近3天用户对商品的购买数,cnt_tenday.最近10天用户对商品的交互数,cnt_tenday.最近10天用户对商品的点击数,cnt_tenday.最近10天用户对商品的购买数,cnt_click_wk.周末用户对商品的交互数,cnt_click_wk.周末用户对商品的点击数,cnt_click_wk.周末用户对商品的购买数,cnt_percent.用户点击到购买商品的转化率,ltrim(Convert(numeric(9,2),cnt_click_wk.周末用户对商品的点击数*100.0/(cnt_action.点击次数)))+'%' as 用户周末点击占总点击的比值,ltrim(Convert(numeric(9,2),cnt_click_wk.周末用户对商品的购买数*100.0/(cnt_action.购买次数)))+'%' as 用户周末购买占总购买的比值from cnt_action join cnt_item on cnt_action.user_id=cnt_item.user_id left join cnt_item_type on cnt_action.user_id =cnt_item_type.user_id left join cnt_oneday on cnt_item.user_id=cnt_oneday.user_idleft join cnt_threeday on cnt_item.user_id=cnt_threeday.user_idleft join cnt_tenday on cnt_item.user_id=cnt_tenday.user_idleft join cnt_click_wk on cnt_item.user_id=cnt_click_wk.user_idleft join cnt_percent on cnt_item.user_id=cnt_percent.user_id

 12.经过前面的练习,后面的商品表只是一些函数的使用

商品表

1

item_id

2

商品被点击平均时间间隔

3

商品被购买平均时间间隔

use 淘宝;Select A.item_id,click_time as 商品被点击平均时间间隔,buy_time as 商品被购买平均时间间隔from(select A.item_id, item_rank, max_date, min_date, cnt_click, 0 - cnt_click/DATEDIFF(D, max_date, min_date) as click_timefrom (select item_id, dense_rank() over(order by count(online_action_id) desc) item_rank from taobao where online_action_id = 0 group by item_id) A inner join (select item_id, max(CONVERT(date,time_stamp,101)) max_date,min(CONVERT(date,time_stamp,101)) min_date,count(online_action_id)cnt_click from taobao where online_action_id = 0 group by item_id )B on A.item_id = B.item_id where item_rank < 6)Aleft join(select A.item_id, item_rank, max_date, min_date, cnt_click, 0 - cnt_click/DATEDIFF(D, max_date, min_date) as buy_timefrom (select item_id, dense_rank() over(order by count(online_action_id) desc) item_rank from taobao where online_action_id = 1 group by item_id) A join (select item_id, max(CONVERT(date,time_stamp,101)) max_date,min(CONVERT(date,time_stamp,101)) min_date,count(online_action_id)cnt_click from taobao where online_action_id = 1 group by item_id )B on A.item_id = B.item_id where item_rank < 6)Bon A.item_id=B.item_id

 




跟团韩国旅游需要多少钱几月份去韩国旅游最好什么时候去韩国旅游最便宜去韩国旅游跟团要多少钱去韩国旅游需要多少钱这一天收到你从无锡寄来的明信片 长江黄金邮轮独揽2015中国邮轮年度大奖 是吃货的看过来 大理美食攻略 卸妆后的成都 你还爱吗? 深圳世界之窗中高考生半价游时间?深圳世界之窗暑期优惠? 2015高考生到深圳世界之窗半价吗?深圳世界之窗高考生优惠活动? 深圳世界之窗七夕夜场票价?深圳世界之窗七夕节怎么玩? 深圳2015七夕活动?深圳七夕节有什么好玩的活动? 株洲火车站到方特欢乐世界怎么走?株洲火车站到方特多少公里? 2015五一德清月潭温泉门票价格?德清月牙泉温泉度假村五一泡温泉多少钱? 跑马岭五一优惠活动?五一跑马岭门票价格? 株洲汽车南站到方特有车吗?株洲汽车南站到方特欢乐世界坐什么车? 暑假带小孩去哪里旅游最好? 博鳌东屿岛温泉在哪?博鳌东屿岛温泉水质如何? 七月份厦门适合旅游吗? 博鳌莲花馆在哪里?怎么样? 5-22I-WC36-30 Datasheet 5-22I-WC36-30 Datasheet 5-22I-WC36-4 Datasheet 5-22I-WC36-4 Datasheet 5-22I-WC36-8 Datasheet 5-22I-WC36-8 Datasheet 深圳五台山旅游价格 深圳五台山旅游价格 深圳五台山旅游价格 山西旅游 线路 山西旅游 线路 山西旅游 线路 深圳山西旅游价格 深圳山西旅游价格 深圳山西旅游价格