数据库简单的实际运用 ②

文章讲述了对于数据量较少的处理。包括新建数据库、使用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 A
join
    (select user_id,count(Online_Action_id)  buy
    from taobao
    where Online_Action_id=1
    group by user_id)as B
on 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 A
join
    (select user_id,count(Item_id)  buy
    from taobao
    where Online_Action_id=1
    group by user_id)as B
on 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 A
join
    (select user_id,count(DISTINCT Item_id)  buy
    from taobao
    where Online_Action_id=1
    group by user_id)as B
on 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)A
join
    (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)B
ON 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)  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)A
Join
(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)B
on A.user_id=B.user_id

SELECT A.User_id ,click as 最近3天用户对商品的点击数 ,buy as 最近3天用户对商品的购买数,click+buy as 最近3天用户对商品的交互数
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))<4
Group By User_id)A
Join
(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))<4
Group By User_id)B
on A.user_id=B.user_id


SELECT A.User_id ,click as 最近10天用户对商品的点击数 ,buy as 最近10天用户对商品的购买数,click+buy as 最近10天用户对商品的交互数
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))<11
Group By User_id)A
Join
(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))<11
Group By User_id)B
on 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 1
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
    AND DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)
    GROUP BY User_id)A
join
    (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)B
ON 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)A
join
    (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)B
ON 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)  click
from taobao
where Online_Action_id=0
group by user_id)A
join
(select user_id,count(Online_Action_id)  buy
from taobao
where Online_Action_id=1
group by user_id)B
on 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) click
from taobao
where online_action_id =0
and DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)
group by User_id)A
join
(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)B
on 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)  click
from taobao
where Online_Action_id=0
group by user_id)A
join
(select user_id,count(Online_Action_id)  buy
from taobao
where Online_Action_id=1
group by user_id)B
on 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) click
from dbo.taobao
where Online_Action_id = 0 
group by user_id) A
join
(select USER_id,Count(item_id) buy
from dbo.taobao
where Online_Action_id = 1 
group by user_id) B
on 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) click
from dbo.taobao
where Online_Action_id = 0 
group by user_id) A
join
(select USER_id,Count(DISTINCT item_id) buy
from dbo.taobao
where Online_Action_id = 1 
group by user_id) B
on 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)  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)A
Join
(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)B
on 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)  click
from dbo.taobao
Where  Online_Action_id=0 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<4
Group By User_id)A
Join
(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))<4
Group By User_id)B
on 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)  click
from dbo.taobao
Where  Online_Action_id=0 AND DATEDIFF(D,CONVERT(date,time_stamp,110),convert(date,'20151201',110))<11
Group By User_id)A
Join
(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))<11
Group By User_id)B
on 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) click
from taobao
where online_action_id =0
and DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)
group by User_id)A
join
(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)B
on 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) click
from taobao
where online_action_id =0
and DATEPART(dw,CONVERT(date,time_stamp,110))in(5,6,7)
group by User_id)A
join
(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)B
on 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_id
left join cnt_threeday on cnt_item.user_id=cnt_threeday.user_id
left join cnt_tenday on cnt_item.user_id=cnt_tenday.user_id
left join cnt_click_wk on cnt_item.user_id=cnt_click_wk.user_id
left 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_time
from  
(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)A
left join
(select A.item_id, item_rank, max_date, min_date, cnt_click, 0 - cnt_click/DATEDIFF(D, max_date, min_date) as buy_time
from  
(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)B
on A.item_id=B.item_id