淘宝用户购物行为分析

1.从https://tianchi.aliyun.com/dataset/649下载淘宝用户行为数据集

数据说明如下

1.概述
UserBehavior是阿里巴巴提供的一个淘宝用户行为数据集，用于隐式反馈推荐问题的研究。

2.介绍
文件名称 说明 包含特征
UserBehavior.csv 包含所有的用户行为数据 用户ID，商品ID，商品类目ID，行为类型，时间戳
UserBehavior.csv
本数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：

 

2.使用python读取其中得1000W导入到postgres

2.1根据淘宝数据集说明在postgres创建表

CREATE TABLE IF NOT EXISTS public.userbehavior
(
userid integer,
itemid integer,
categoryid integer,
behavior character varying(50) COLLATE pg_catalog."default",
dtime bigint
)

2.2 使用python将数据样本导入数据库

import  pandas as pd

import psycopg2

from io import StringIO

from psycopg2.extras import execute_values

from sqlalchemy import create_engine

# 方法1: 使用pandas.to_sql (便捷但性能一般)

def method1_pandas_to_sql():

    dataparth=r"F:\download\UserBehavior\UserBehavior.csv"

    df=pd.read_csv(dataparth,nrows=10000000, header=None)

    engine = create_engine('postgresql://user:password@localhost:5432/UserBehaviorDB')

    df.to_sql('userbehavior', engine, if_exists='append', index=False)

# 方法2: 使用psycopg2 execute_values (性能较好)

def method2_execute_values():

    dataparth=r"F:\download\UserBehavior\UserBehavior.csv"

    df=pd.read_csv(dataparth,nrows=10000000, header=None)

    conn = psycopg2.connect(

        host="localhost",

        database="UserBehaviorDB",

        user="postgres",

        password="password"

    )

    cursor = conn.cursor()

    sql = "INSERT INTO userbehavior VALUES %s"

    execute_values(cursor, sql, df.values.tolist(), template=None, page_size=1000)

    conn.commit()

    cursor.close()

    conn.close()

 # 方法3: 使用psycopg2 COPY (高性能)

def method_copy_from():

    dataparth=r"F:\download\UserBehavior\UserBehavior.csv"

    df=pd.read_csv(dataparth,nrows=10000000, header=None)

 

    conn = psycopg2.connect(

        host="localhost",

        database="UserBehaviorDB",

        user="postgres",

        password="password"

    )

    cursor = conn.cursor()

    output = StringIO()

    df.to_csv(output, sep='\t', header=False, index=False)

    output.seek(0)

    cursor.copy_from(output, table='userbehavior')

    conn.commit()

    cursor.close()

    conn.close()

method_copy_from()

3.查看导入postgres的数据

 

 3. 数据清洗

首次清理时间异常数据

delete from userbehavior
where dtime<0

根据时间，用户分组统计数据如下：

select dtime,userid,count(1),STRING_AGG(behavior,',') AS behaviorlist,STRING_AGG(cast(itemid as varchar(10)),',') AS itemlist
from userbehavior
group by dtime,userid
having count(1)>1

 

基于同一个用户同一时间点基本不可能对大于1个商品进行两种不同的操作，因此将此类数据作为脏数据处理

select dtime,userid,count(1),STRING_AGG(behavior,',') AS behaviorlist,STRING_AGG(cast(itemid as varchar(10)),',') AS itemlist
from userbehavior
group by dtime,userid
having count(1)>1 and count(distinct behavior)>1 and count(distinct itemid)>1

 对以上脏数据进行删除

CREATE TEMPORARY TABLE temp_filtered_data AS
SELECT dtime, userid, count(1) as cnt,
STRING_AGG(behavior,',') AS behaviorlist,
STRING_AGG(cast(itemid as varchar(10)),',') AS itemlist
FROM userbehavior
GROUP BY dtime, userid
HAVING count(1)>1 AND count(distinct behavior)>1 AND count(distinct itemid)>1;

select sum(cnt) from temp_filtered_data
union all
select count(1) from temp_filtered_data a inner join userbehavior b
on a.dtime=b.dtime and a.userid=b.userid

 数据一致，对异常数据进行删除处理

delete from userbehavior
where (dtime,userid) in (select dtime,userid from temp_filtered_data)

 行为未有异常

 

-- 确认行为类型与文档相符
select distinct behavior from userbehavior
--判断关键数据是否有空值
select * from userbehavior where itemid is null or categoryid is null or dtime is null or userid is null

 

时间格式转换

select *, to_timestamp(dtime),to_timestamp(dtime) AT TIME ZONE 'UTC' AS utc_time
,to_char(to_timestamp(dtime), 'YYYY-MM-DD HH24:MI:SS')
from userbehavior limit 10

alter table public.userbehavior
add formatted_dtime timestamp

update userbehavior set formatted_dtime=to_timestamp(dtime) AT TIME ZONE 'UTC'

update耗时1分钟

PostgreSQL 的 UPDATE 操作本质上是“先删除旧行，再插入新行”，这会占用大量资源并可能导致表膨胀（即产生大量“死元组”）。因此，在处理大量数据时，应尽量避免全表更新或长时间持有锁。

如果需要update的，可根据实际情况新建一张表， 如果对业务不产生影响的可以采用update

 样本为2017年11月25日至2017年12月3日之间，对区间外数据进行删除：

 delete  from userbehavior where formatted_dtime<'2017-11-25' or  formatted_dtime>'2017-12-04'