大数据智能综合训练编程题（Spark、RDD、Hadoop）

大数据智能综合训练编程题（Spark、RDD、Hadoop）

做题准备

环境准备

做题前需要安装好spark环境，以及python环境，当然有个Hadoop环境更好了，

环境搭建参考链接：spark环境，hadoop集群搭建

知识储备

• 熟练掌握python基础语法及编程训练
• 熟悉spark rdd编程
• 了解Scala基础知识

题目一：全校2018级学生名单分析

1、使用RDD编程的方式，找到自己的那一行信息

编写spark脚本

from pyspark import SparkConf, SparkContext
import sys

cf = SparkConf()  # 创建SparkConf对象

cf.setMaster("local")
cf.setAppName("my-book-tongji")

sc = SparkContext(conf = cf)
rdd1 = sc.textFile("file://"+sys.argv[1]) #将传递给脚本的第一个参数作为路径
rdd2 = rdd1.map(lambda x:x.split(",")) # 分割数据
rdd3 = rdd2.filter(lambda x:("哇" in x[6])) # 通过filter筛选出满足函数的元素，并返回一个新的RDD（数据集）

names = rdd3.collect()
print("===================================================")
print("输出结果：")
print(names)
print("===================================================\n")

通过以下命令使用spark运行脚本

./spark-submit /home/user/tasks/one.py /caifl/spark/data/stuinfo/

最终输出结果：

2、按照学院分组，统计每个学院的学生数量，并排序。

编写spark脚本

from pyspark import SparkConf, SparkContext
import sys
from operator import add

conf = SparkConf().setMaster('local').setAppName('Grouping')  # 创建SparkConf对象
sc = SparkContext(conf=conf)  # 创建SparkContext对象
rdd1 = sc.textFile("file:///caifl/spark/data/stuinfo")  # 读取数据
rdd2 = rdd1.map(lambda x: x.split(","))  # 分割数据
rdd3 = rdd2.map(lambda x: [x[2]])  # map提取学院，因为给的数据已经对学院进行分类了。
rdd3.foreach(print)  # 输出测试
rdd4 = rdd3.map(lambda x: [x[0], 1])  # 新建个两个元素列表的RDD
rdd5 = rdd4.reduceByKey(lambda a, b: a + b)  # 对rdd4的键值对进行聚合及相加
list = rdd5.sortBy(lambda a: a[1], False).collect()  # 根据大小进行排序
print("=====================================================")
print("输出结果：")
print(list)
print("=====================================================")
sc.stop()

结果输出显示

3、按照学员分组，再按照性别分组统计每个学院的男女生占比。（不包含不在校的学生）

注：排除了“在校”学生以外的所有学生，只对"在校"学生进行筛选

编写脚本：

from pyspark import SparkConf, SparkContext

conf = SparkConf().setMaster('local').setAppName('Grouping')  # 创建SparkConf对象
sc = SparkContext(conf=conf)  # 创建SparkContext对象
rdd1 = sc.textFile("file:///caifl/spark/data/stuinfo")  # 读取数据
rdd2 = rdd1.map(lambda x: x.split(","))  # 分割数据

rdd_xueyuan = rdd2.groupBy(lambda t: t[2])  # 根据学院对数据进行分类
print("================输出学院分类测试===================")
rdd_xueyuan.foreach(print)  # 输出学院分类测试
list1 = rdd_xueyuan.collect()
print(list1)


# rdd_xueyuan_total = rdd_xueyuan.map(lambda t: (t[0], len(list(t[1]))))
# print("================输出学院人数统计===================")
# rdd_xueyuan_total.foreach(print)  # 输出学院人数统计

def foo(it):
    s = ''
    ls = tuple(it)
    ls_nan = []
    ls_nv = []
    for item in ls:
        if item[8] == '在校':  # 筛选在校学生，排除不在校学生
            if item[7] == '男':
                ls_nan.append(item)  # 女生添加
            elif item[7] == '女':
                ls_nv.append(item)   # 男生添加
    if len(ls_nan) == 0:  # 被除数不能为0，否则报错
        s = "0%"
    elif ls_nan != 0:
        s = str(round(len(ls_nv) / len(ls_nan) * 100, 2)) + "%"  # 计算百分比并保留两位小数
    return [len(ls_nan), len(ls_nv), s]  # 返回在校男女生人数及男女比率：女生/男女生*100%


rdd3 = rdd_xueyuan.mapValues(foo)  # 对rddtuple值执行foo函数并返回男女人数及男女百分比：女生数量/男生数量*100%(保留两位小数)

print("================输出结果：================:")
list2 = rdd3.collect()
for t in list2:
    print(t)
sc.stop()

通过命令运行

/caifl/spark/spark-2.4.7/bin/spark-submit /home/user/tasks/three.py 

运行脚本，输出结果：

4、找出学号最小值，最大值。

分析及推测所有学生的学号排列规律，包括但不限于：

​ 是否存在学号区段与专业班级的关系，是否存在学号区段与学院名称的关系，

​ 是否存在空闲的学号区段未被使用

编写脚本

from pyspark import SparkConf, SparkContext
import sys

cf = SparkConf()  # 创建SparkConf对象
cf.setMaster("local")
cf.setAppName("my-book-tongji")
sc = SparkContext(conf=cf)
rdd1 = sc.textFile("file:///caifl/spark/data/stuinfo")  # 读取数据
rdd2 = rdd1.map(lambda x: x.split(","))  # 分割数据

# ===================方法一：=====================
rdd_num = rdd2.map(lambda x: x[5])

r3 = rdd_num.reduce(lambda a, b: max(a, b))  # 找到最大值学号
r4 = rdd_num.reduce(lambda a, b: min(a, b))  # 找到最小值学号

rdd4 = rdd2.filter(lambda x: str(x[5]) == str(r3))  # 根据学号筛选最大值信息
rdd5 = rdd2.filter(lambda x: str(x[5]) == str(r4))  # 根据学号筛选最小值信息

print("输出信息：")
t3 = rdd4.collect()
t4 = rdd5.collect()

print("分别输出最大值与最小值：")
print("最大值：", t3)
print("最小是", t4)

# ===================方法二：======================
# t3 = rdd2.max(lambda x: x[5])  # 筛选学号中的最大值
# t4 = rdd2.min(lambda x: x[5])  # 筛选学号中的最小值

# print("分别输出最大值与最小值：")
# print("最大值：", t3)
# print("最小是", t4)

# print("分别输出最大值与最小值：")
# print("最大值：", t3)
# print("最小是", t4)

sc.stop()

输出结果：

5、按学生姓氏统计排名TOP5的姓氏及人数（只统计在校的非留学生，注意复姓）

编写程序如下：

from pyspark import SparkConf, SparkContext

conf = SparkConf().setMaster('local').setAppName('Grouping')  # 创建SparkConf对象
sc = SparkContext(conf=conf)  # 创建SparkContext对象
rdd1 = sc.textFile("file:///caifl/spark/data/stuinfo")  # 读取数据
rdd2 = rdd1.map(lambda x: x.split(","))  # 分割数据

#  =====================方法一：=======================
list1 = rdd2.collect()
# rdd_xueyuan = rdd2.groupBy(lambda t: t[2])  # 根据学院对数据进行分类
# rdd_xueyuan = sc.parallelize(rdd2.collect())
# rdd_xueyuan = rdd2.groupBy(lambda x: x)

lastname = ['欧阳', '上官', '皇甫', '令狐', '诸葛', '司徒', '司马', '申屠', '夏侯', '贺兰', '完颜', '慕容', '尉迟', '长孙']


def dotit(it):
    last_name = {}
    for item in it:
        if item[4] != "留学本科生":
            name = item[6]  # 提取学生姓名
            length = len(name)  # 提取名字长度
            if length == 2:  # 如果名字是两个字，必然不是复姓！
                s1 = name[0]
                if s1 in last_name:  # 判断姓是否已经存在
                    last_name[s1] = last_name[s1] + 1  # 存在则数量加1
                else:
                    last_name[s1] = 1  # 不在则赋值为1
            elif length == 3:
                s1 = name[0]
                s2 = name[0] + name[1]
                if s2 in lastname:  # 判断姓名3个字名字的姓氏是否是复姓
                    if s2 in last_name:  # 判断姓是否已经存在
                        last_name[s1] = last_name[s1] + 1  # 存在则数量加1
                    else:
                        last_name[s1] = 1  # 不在则赋值为1
                else:
                    if s1 in last_name:  # 判断姓是否已经存在
                        last_name[s1] = last_name[s1] + 1  # 存在则数量加1
                    else:
                        last_name[s1] = 1  # 不在则赋值为1

            elif length >= 4:
                s1 = name[0] + name[1]
                if s1 in last_name:  # 判断姓是否已经存在
                    last_name[s1] = last_name[s1] + 1  # 存在则数量加1
                else:
                    last_name[s1] = 1  # 不在则赋值为1
    a1 = sorted(last_name.items(), key=lambda x: x[1], reverse=True)  # 对数据进行排序
    return a1  # 返回排序处理好的出具


# rdd3 = rdd_xueyuan.mapValues(dotit)
last_name = dotit(list1)
rdd3 = sc.parallelize(last_name)  # 装载到rdd中
ouput = rdd3.collect()

print("==============结果输出（姓氏人数从大到小排序）：============")
print(ouput)
print("================输出人数排名top5的姓氏：===============")
for i in range(0, 5):
    print(ouput[i], end=" ")
    print()

结果输出：

题目二：红楼梦所用到的汉字统计分析

1、统计小说全文用到的汉字及其出现次数，并排序得到最常用的500个汉字。

编写程序如下：

import re
from pyspark import SparkConf, SparkContext
from collections import Counter
conf = SparkConf().setMaster('local').setAppName('WordCount')  # 创建SparkConf对象
sc = SparkContext(conf=conf)  # 创建SparkContext对象

lines = sc.textFile("file:///caifl/spark/data/红楼梦txt")  # 读取数据
punc = '~`!#$%^&*()_+-=|‘’\'．”;":/.,?><~·！@#￥%……&*（）——+-=“：’；、。，？》《{}\u3000, '  # 添加标点符号


def clean(w):  # 清洗数据
    w = w.replace(' ', '')
    w = w.replace('，', '')
    w = w.replace("'", "")
    w = re.sub('[a-zA-Z]', '', w)  # 消除英文字符
    w = re.sub(r"[%s]+" % punc, "", w)  # 消除标点符号
    return w


def tongji(w):  # 统计汉字字符出现次数
    hanzi = {}
    for s in w:
        if s in hanzi:  # 判断字典是否已存在汉字
            hanzi[s] = hanzi[s] + 1
        else:
            hanzi[s] = 1
    return hanzi


def jian_add(a, b):  # 合并字典
    A, B = Counter(a), Counter(b)
    result = dict(A + B)
    return result


#  ====================方法一：====================
rdd1 = lines.map(clean)  # 清洗数据
rdd2 = rdd1.map(tongji)  # .reduceByKey(lambda x, y:x+y)
list1 = rdd2.reduce(jian_add)   # 合并多个元素
list2 = sorted(list1.items(), key=lambda x: x[1], reverse=True)  # 对数据进行排序
print(list2)

print("=========================最常用的500个汉字===================")
for i in range(0, 501):
    print(list2[i], end=" ")

输出结果：

题目三：物联卡流量/短信活跃度分析

使用sparkSQL进行数据分析的步骤

• 1）读取文本文件创建RDD
• 2）RDD进行预处理，以便创建DataFrame 包括将每行数据分隔成多个字段，删除掉表头行
• 3）将RDD的每一个数据转换为Row类型 对象
• 4）创建DataFrame 代码如示： df1 = spark.createDataFrame(rdd) #要求入参的rdd的每一个数据都是Row类型的对象
• 5）用DataFram创建临时表 df1.createOrReplaceTempView(“tb_name??”)
• 6）使用SQL语句进行查询分析 spark.sql(“select … from tb_??? …”)
• 7）saprk.sql() 函数返回的是一个新的DataFrame(), 可以保存中间变量 df8=saprk.sql(“select …”) 。 df8.printSchema()
• 8）DataFrame 可以转换为RDD 代码： rdd_df8 =df8.rdd

小题

1、按照EC来统计每个EC所属的卡在有流量的和没有流量的占比（有活跃的卡的占比）学号尾号123

2、按照EC来统计每个EC所属的卡在有短信的和没有短信的占比（活跃的卡的占比） 456

3、按照省份来统计各个省分的卡有流量和没有流量的活跃度占比 78

4、按照省份来统计各个省分的卡有短信和没有短信的活跃度占比 90

题目解析：

1、2、3、4题相差不大，通过做一道题可以对其它题目的思路进行分析。

下面给出第4小题解法：

第4小题：

table分析

数据关系图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AMUyUPSk-1625197469333)(D:\Resources\md\大数据智能综合训练编程题（Spark、RDD、Hadoop）.assets\image-20210627232945410.png)]

card_info表：卡号定义表， 一个OID对应一张卡且独一无二

prod_sublist：产品订购表， 一个OID与一个prod_pkg、一个prod_id决定一个prod_instid，prod_instid 在该表独一无二，表示某个卡订购某些产品的编号

provice_del：省份信息表

ec_info：公司信息表

prod_info：产品信息表， 有发送短信的必然同时选择了I90011与I90012这两个服务，有流量的必然选中了I90032与剩余两个（I90036、I90037）之中的其中一个。

flow_daily：流量日活跃表，活跃的必然是有流量的

sms_daily：短信日活跃表，活跃的必然是有短信的

活跃度指的是在某个统计周期内有流量的卡号，就是活跃的。这个周期内没有流量消耗的卡号，就是不活跃的。

要求大家求的是活跃度占比。就是某个分组内(按ECID或者按省份)的总卡数量为分母，活跃的卡数量为分子，求百分比

编写pyspark脚本程序：

from pyspark import SparkConf, SparkContext
from pyspark.sql import SparkSession
from pyspark.sql import Row
import sys
import re

conf = SparkConf().setMaster('local').setAppName('SQLName')
sc = SparkContext(conf=conf)
spark = SparkSession(sc)

'''导入文件'''
card_info = sc.textFile("file:///caifl/spark/data/iot_card/card_info.csv")
province_def = sc.textFile("file:///caifl/spark/data/iot_card/provice_def.csv")
sms_daily = sc.textFile("file:///caifl/spark/data/iot_card/sms_daily.csv")
prod_sublist = sc.textFile("file:///caifl/spark/data/iot_card/prod_sublist.csv")
ec_info = sc.textFile("file:///caifl/spark/data/iot_card/ec_info.csv")
prod_info = sc.textFile("file:///caifl/spark/data/iot_card/prod_info.csv")

# 创建DataFrame
rdd_card_info = card_info.map(lambda x: x.split(","))
rdd_province_def = province_def.map(lambda x: x.split(","))
rdd_sms_daily = sms_daily.map(lambda x: x.split(","))
rdd_prod_sublist = prod_sublist.map(lambda x: x.split(","))
rdd_ec_info = ec_info.map(lambda x: x.split(","))
rdd_prod_info = prod_info.map(lambda x: x.split(","))

# 清除第一行
rdd1_card_info = rdd_card_info.filter(lambda x: "status" not in x)
rdd1_province_def = rdd_province_def.filter(lambda x: "province_name" not in x)
rdd1_sms_daily = rdd_sms_daily.filter(lambda x: "sms_used" not in x)
rdd1_prod_sublist = rdd_prod_sublist.filter(lambda x: "status" not in x)
rdd1_ec_info = rdd_ec_info.filter(lambda x: "ecname" not in x)
rdd1_prod_info = rdd_prod_info.filter(lambda x: "prod_pkg" not in x)
# print("===============输出测试==============")
# for i in rdd1_province_def.collect():
#     print(i)
# 将每一行转换为Row类型的对象
rdd2_card_info = rdd1_card_info.map(
    lambda x: Row(OID=x[0], province=x[1], msisdn=x[2], active_day=x[3], active_time=x[4], status=int(x[5]), ecid=x[6]))
rdd2_sms_daily = rdd1_sms_daily.map(lambda x: Row(prod_instid=x[0], total=x[1], sms_used=x[2], stats_day=x[3]))
rdd2_prod_sublist = rdd1_prod_sublist.map((lambda x: Row(OID=x[0], prod_pkg=x[1], prod_id=x[2], prod_instid=x[3],
                                                         APN=x[4], status=int(x[5]), sub_day=x[6], sub_time=x[7])))
rdd2_province_def = rdd1_province_def.map(lambda x: Row(province_id=x[0], province_name=x[1]))
rdd2_ec_info = rdd1_ec_info.map(lambda x: Row(ecid=x[0], ecname=x[1], level=x[2]))
rdd2_prod_info = rdd1_prod_info.map(lambda x: Row(prod_pkg=x[0], prod_id=x[1], prod_name=x[2]))
print("===============输出测试==============")
print(rdd_prod_sublist.first())
'''转为为DataFrame类型的数据对象'''
rdd3_sms_daily = spark.createDataFrame(rdd2_sms_daily)
rdd3_card_info = spark.createDataFrame(rdd2_card_info)
rdd3_prod_sublist = spark.createDataFrame(rdd2_prod_sublist)
rdd3_province_def = spark.createDataFrame(rdd2_province_def)
rdd3_ec_info = spark.createDataFrame(rdd2_ec_info)
rdd3_prod_info = spark.createDataFrame(rdd2_prod_info)

'''输出表中信息'''
print("============输出表测试==========")
rdd3_prod_sublist.printSchema()
# 转变视图
rdd3_card_info.createTempView('card_info')
rdd3_prod_sublist.createTempView('prod_sublist')
rdd3_sms_daily.createTempView('sms_daily')
rdd3_province_def.createTempView('province_def')
rdd3_ec_info.createTempView('ec_info')
rdd3_prod_info.createTempView('prod_info')

'''测试'''
rdd4 = spark.sql("select * from card_info where province=100")
rdd4 = rdd4.rdd
r5 = rdd4.collect()
print("===============输出查询结果==========")
for i in r5:
    print(i)

rdd4_1 = spark.sql("select * from prod_info")
r4_1 = rdd4_1.rdd
r5_1 = r4_1.collect()
print("===============输出prod_info查询结果==========")
for i in r5_1:
    print(i)

'''开始查询'''
# 通过sql语句去重查找到活跃且有短信的OID
rdd4_sms_active_oid = spark.sql("select distinct OID from prod_sublist where prod_instid in (select distinct prod_instid from sms_daily)")
rdd4_sms_active_oid = rdd4_sms_active_oid.rdd
# 将活跃的active_instid变成table
rdd4_sms_active_oid_temp = rdd4_sms_active_oid.map(lambda x: Row(OID=x[0]))
active_oid = spark.createDataFrame(rdd4_sms_active_oid_temp)
active_oid.createTempView("active_oid")

# 输出
t1 = rdd4_sms_active_oid.collect()
# print("===============输出有短信且活跃的OID:==========")
# print(t1)
# print("==============================有短信且活跃的OID数量：", len(t1),"==============================")


# 查找有短信的所有OID
rdd4_sms_oid = spark.sql("select distinct OID from prod_sublist where OID in (select OID from prod_sublist where prod_id='I90011')"
                         "and OID in (select OID from prod_sublist where prod_id='I90012')")
rdd4_sms_oid = rdd4_sms_oid.rdd
# 将有短信的rdd转换成table
rdd4_sms_oid_temp = rdd4_sms_oid.map(lambda x: Row(OID=x[0]))
sms_oid = spark.createDataFrame(rdd4_sms_oid)
sms_oid.createTempView("sms_oid")

t2 = rdd4_sms_oid.collect()
print("===============输出有短信的OID:==========")
print(t2)
print("==============================有短信且活跃的OID数量：", len(t1),"==============================")
print("==============================有短信的OID数量：", len(t2), "==============================")


# 同时查找活跃的与全部的OID数量（语句尚有错误，提示IN使用错误）
# select count(OID in (select OID from active_oid) or NULL) as active, count(OID in (select OID from sms_oid) or NULL) as sms, province from card_info group by province
# 查找各省份有短信且活跃的数量
province_active_oid = spark.sql("select count(*), province from card_info where OID in (select OID from active_oid) group by province")
province_active_oid = province_active_oid.rdd
# province_active_oid.sortBy(lambda x: x[1], False)
t3 = province_active_oid.collect()

print("===============输出有短信且活跃的各省份的OID:==========")
print(t3)
print("==============================输出有短信且活跃的各省份的OID数量：", len(t3), "==============================")
#
#
# # 查找各省份有短信的数量

输出结果：

题目四：某电商平台用户行为日志统计分析

题目要求：

点击量排名前10的品类，是根据所有用户所有会话的行为统计得到。既然是用户/session的点击行为，就有可能会重复点击这一品类，所以再统计哪些user是多次点击了这前10的品类

最后要得出10*10=100行数据

2021-06-10 22:00:45 这个字段的数据，要将字符串转换成日期时间类型，需要引入两个函数
from pyspark.sql.functions import to_date, to_timestamp
并且这两个函数只能在DateFrame或者SQL语句中运用

1)用户session时长的计算，找到TOP10 session 的用户ID

编写脚本：

from pyspark import SparkConf, SparkContext
from pyspark.sql import SparkSession
from pyspark.sql import Row
import sys
import re

conf = SparkConf().setMaster('local').setAppName('Session')
sc = SparkContext(conf=conf)
spark = SparkSession(sc)
sc.setLogLevel("ERROR")

info_lines = sc.textFile("file:///caifl/spark/data/info.txt")
rdd1 = info_lines.map(lambda x: x.split(","))

print(rdd1.collect())
rdd2 = rdd1.map(lambda x: Row(date=x[0], user_id=x[1], session_id=x[2], page_id=x[3], action_time=x[4], search_keyword=x[5],
                       click_category_id=x[6], click_product_id=x[7], order_category_ids=x[8], order_product_ids =x[9],
                       pay_category_ids=x[10], pay_product_ids=x[11]))

rdd3 = spark.createDataFrame(rdd2)
# 转变视图
rdd3.printSchema()
rdd3.createTempView("session")
'''
select * from (select b.user_id, qd.session_id,qd.duration from(select a.session_id,cast(a.max_time as '
          'long)-cast(a.min_time as long) as duration from(select session_id,max(to_timestamp(action_time)) as '
          'max_time,min(to_timestamp(action_time)) as min_time from action_session group by '
          'action_session.session_id) as a) as qd, (select distinct user_id,session_id from action_session) as b '
          'where qd.session_id=b.session_id order by qd.duration DESC) limit 10
'''
rdd4 = spark.sql("select  D.user_id, C.session_id, C.timeLength from (select A.session_id, cast(A.maxTime as long) - cast(A.minTime as long) as timeLength from (select session_id, max(to_timestamp(action_time)) as maxTime, min(to_timestamp(action_time)) as minTime from session group by session_id) as A)  as C, (select distinct user_id,session_id from session) as D where C.session_id = D.session_id order by C.timeLength DESC")
rdd4 = rdd4.rdd
list = rdd4.collect()

print("=============输出查询结果：============")
for i in range(0, 10):
    print("用户ID：", list[i]['user_id'], "SessionID：", list[i]['session_id'], "session时长：", list[i]['timeLength'])

SQL脚本编写思路：

‘’’

date：日期，代表这个用户点击行为是在哪一天发生的

user_id：代表这个点击行为是哪一个用户执行的

session_id ：唯一标识了某个用户的一个访问session

page_id ：点击了某些商品/品类，也可能是搜索了某个关键词，然后进入了某个页面，页面的id

action_time ：这个点击行为发生的时间点

search_keyword ：如果用户执行的是一个搜索行为，比如说在网站/app中，搜索了某个关键词，然后会跳转到商品列表页面；搜索的关键词

click_category_id ：可能是在网站首页，点击了某个品类（美食、电子设备、电脑）

click_product_id ：可能是在网站首页，或者是在商品列表页，点击了某个商品（比如呷哺呷哺火锅XX路店3人套餐、iphone 6s）

order_category_ids ：代表了可能将某些商品加入了购物车，然后一次性对购物车中的商品下了一个订单，这就代表了某次下单的行为中，有哪些

商品品类，可能有6个商品，但是就对应了2个品类，比如有3根火腿肠（食品品类），3个电池（日用品品类）

order_product_ids ：某次下单，具体对哪些商品下的订单

pay_category_ids ：代表的是，对某个订单，或者某几个订单，进行了一次支付的行为，对应了哪些品类

pay_product_ids：代表的，支付行为下，对应的哪些具体的商品

‘’’

# cast将一种数据转换为指定类型的数据

# to_timestamp() 将日期转换为时间戳

# max()函数找到最大值

# min()函数找到最小值

# 查找

# sql思路

# 查找每个session的最大时间与最小时间（也可以理解为最晚时间与最短时间）转化为时间戳

select session_id, max(to_timestamp(action_time)) as maxTime, min(to_timestamp(action_time)) as minTime from session group by session_id

# 将上面的表作为临时表A

# cast(A.maxTime as long) 将timestamp类型转换为long类型

# timeLength 每个session的最大时间与最小时间间隔时间转换而成的long型数据，即间隔时间长度timeLength， 求到了每个session的最大间隔时长

select A.session_id, cast(A.maxTime as long) - cast(A.minTime as long) as timeLength

from (select session_id, max(to_timestamp(action_time)) as maxTime, min(to_timestamp(action_time)) as minTime from session group by session_id) as A

#将上面的表作为临时表C,

(select A.session_id, cast(A.maxTime as long) - cast(A.minTime as long) as timeLength

from (select session_id, max(to_timestamp(action_time)) as maxTime, min(to_timestamp(action_time)) as minTime from session group by session_id) as A) as C

# 去重统计user_id 与session_id

select distinct user_id,session_id from action_session

#将上面的表作为临时表D

(select distinct user_id,session_id from action_session) as D

# C表和D表都有相同的字段session_id

(select A.session_id, cast(A.maxTime as long) - cast(A.minTime as long) as timeLength

from (select session_id, max(to_timestamp(action_time)) as maxTime, min(to_timestamp(action_time)) as minTime from session group by session_id) as A) as C

# 最终合并为一个完整的SQL语句 排序时使用order by timeLength DESC

# top输出前10个，没有效果，采用rdd输出前十个数据， 根据session时长降序从大到小排序

select * from (select D.user_id, C.session_id, C.timeLength from (select A.session_id, cast(A.maxTime as long) - cast(A.minTime as long) as timeLength from (select session_id, max(to_timestamp(action_time)) as maxTime, min(to_timestamp(action_time)) as minTime from session group by session_id) as A) as C, (select distinct user_id,session_id from session) as D where C.session_id = D.session_id order by C.timeLength DESC) limit 10

输出截图：

2)获取点击操作排名(点击量)前10的品类、并获取top10品类的点击量(有些session/用户可能会多次点击这一个品类)排名前10的user

编写脚本代码：

from pyspark import SparkConf, SparkContext
from pyspark.sql import SparkSession
from pyspark.sql import Row
import sys
import re

conf = SparkConf().setMaster('local').setAppName('Session')
sc = SparkContext(conf=conf)
spark = SparkSession(sc)
sc.setLogLevel("ERROR")

info_lines = sc.textFile("file:///caifl/spark/data/info.txt")
rdd1 = info_lines.map(lambda x: x.split(","))  # 处理数据

print(rdd1.collect())
rdd2 = rdd1.filter(lambda x: x[5] == 'null')  # 筛选点击操作
rdd2 = rdd2.filter(lambda x: x[6] != 'null' or x[7] != 'null')  # 筛选点击操作
rdd3 = rdd2.map(
    lambda x: Row(date=x[0], user_id=x[1], session_id=x[2], page_id=x[3], action_time=x[4], search_keyword=x[5],
                  click_category_id=x[6], click_product_id=x[7], order_category_ids=x[8], order_product_ids=x[9],
                  pay_category_ids=x[10], pay_product_ids=x[11]))

rdd4 = spark.createDataFrame(rdd3)  # 创建dataframe
# 转变视图
rdd4.printSchema()
rdd4.createTempView("user_action")  # 转换视图
rdd5 = spark.sql(
    "select page_id, num, user_id, num_all, user_num from (select page_id, num, user_id, num_all, ROW_NUMBER() over(PARTITION by page_id ORDER BY num_all desc) as user_num from (select  B.page_id as page_id, B.num as num, user_id as user_id, A.num_all as num_all from (select page_id, count(page_id) as num from user_action   group by  page_id  order by num desc ) as B,(select page_id ,user_id,COUNT(*) as num_all from user_action group by page_id ,user_id ) as A where B.page_id=A.page_id order by B.num DESC, A.num_all DESC) as C) as D  where user_num<11 ORDER BY num desc")
list = rdd5.collect()
print("=============================输出最终结果==============================")
for item in list:
    print("page_id:", item['page_id'], " 点击总数： ", item['num'], " user_id:", item['user_id'], "user_id点击page_id总数：", item['num_all'],
          "user_id排序：", item['user_num'])

sql思路：

# SQL思路

# 统计page_id出现的数量并按降序排序
select page_id, count(page_id) as num from user_action   group by  page_id  order by num desc


#统计user_id, page_id出现的次数，即每个user_id点击同一个page_id的数量（多次点击）
select page_id ,user_id,COUNT(*) as num_all from user_action group by page_id ,user_id


-- 统计计算user_id数量，并将之前统计的数据提取到新的表中
select page_id, num, user_id, num_all, ROW_NUMBER() over(PARTITION by page_id ORDER BY num_all desc) as user_num from (select  B.page_id as page_id, B.num as num, user_id as user_id, A.num_all as num_all from (select page_id, count(page_id) as num from user_action   group by  page_id  order by num desc ) as B,(select page_id ,user_id,COUNT(*) as num_all from user_action group by page_id ,user_id ) as A where B.page_id=A.page_id order by B.num DESC, A.num_all DESC) as C



-- 将数据提取到外面的表，然后根据page_id点击总数进行排序
-- ROW_NUMBER() over(PARTITION by page_id ORDER BY page_id) as user_num 通过row_number()函数根据page_id分组并以num_all（user_id 点击page_id次数）排序
-- 最后在外面根据num（page_id点击总数）进行降序排序

'''
最终sql脚本
'''
select page_id, num, user_id, num_all, user_num from (select page_id, num, user_id, num_all, ROW_NUMBER() over(PARTITION by page_id ORDER BY num_all desc) as user_num from (select  B.page_id as page_id, B.num as num, user_id as user_id, A.num_all as num_all from (select page_id, count(page_id) as num from user_action   group by  page_id  order by num desc ) as B,(select page_id ,user_id,COUNT(*) as num_all from user_action group by page_id ,user_id ) as A where B.page_id=A.page_id order by B.num DESC, A.num_all DESC) as C) as D  where user_num<11 ORDER BY num desc

输出：

代码及数据资源

https://download.csdn.net/download/EngineerofAI/19999795

Reference

环境搭建https://blog.csdn.net/EngineerofAI/article/details/118399384