資料庫簡單的實際運用 ②

文章講述了對於數據量較少的處理。包括新建資料庫、使用SQL語言建表、向資料庫中導入數據、簡單的數據查詢操作等。下麵介紹一個相對上一個較為複雜的資料庫。其實也就是數據量增大了點，數據處理要求多了些，運用基礎的資料庫知識以及網上的各種講解都可以掌握這些處理方法。

例2

 下麵列舉某寶一些數據處理。

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

統計內容：

• 用戶計數特征

1. 用戶總交互次數，用戶點擊次數，用戶購買次數
2. 用戶交互、點擊、購買的不同商品種類數
3. 用戶交互、點擊、購買的不同商品數

• 用戶時間層級（預測時間為20151201）

1. 最近1天／3天／10天用戶對商品的交互／點擊／購買數
2. 最近1天／3天／10天用交互的不同商品種類總數
3. 用戶周末（周五六日）對商品的交互、點擊、購買次數

• 用戶比值特征

1. 用戶點擊到購買商品的轉化率
2. 用戶周末點擊、購買占總點擊、購買的比值

• 商品計數特征

1. 被點擊、被購買次數最多的前5商品
2. 商品被點擊、購買的平均時間間隔

提交結果：

1. 用戶表

1. 商品表

實現工具：SQL Server 2014 、基本的數據

1.這裡我們可以和例1不同，可以先將淘寶的數據導入到資料庫中，但是你也許會問：表都沒有建立數據導入到哪裡呢？其實如果在例1中的導入數據時候認真觀察的話，可以發現當我們將excel導入時候，會有一個預設的表，並且這個表的名字和我們導入的excel的表名字相同（通常情況下，為Sheet），所以我們這裡就直接導入數據了哦。

2.修改表的列名

3.檢驗數據是否成功導入

use 淘寶

SELECT *
FROM dbo.taobao

4.完成前面的4項要求（註意Online_Action_id=1 或 0 代表不同含義）

1. 用戶表

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

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 的使用）

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

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講解

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

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（對於這個要求可以利用前面的周末點擊以及周末購買除以總點擊、總購買）

那麼關鍵問題是我們該怎麼將之前分開的查詢操作統一在一起完成一個查詢操作呢。

現在放出我們的大招

使用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