求各区域热门商品Top3 - HiveSQL

  1. 背景:这是尚硅谷SparkSQL练习题,本文用HiveSQL进行了实现。

  2. 数据集:用户点击表,商品表,城市表
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

  3. 题目:
    ① 求每个地区点击量前三的商品;
    ② 在①的基础上,求出每个地区点击量前三的商品后,求出每个商品中的点击量前三的城市分别占本商品总点击量的百分比。

  4. 建表,导入数据
    没啥说的,建表语句直接抄过来

use atguigu;CREATE TABLE `user_visit_action`(`date` string,`user_id` bigint,`session_id` string,`page_id` bigint,`action_time` string,`search_keyword` string,`click_category_id` bigint,`click_product_id` bigint,`order_category_ids` string,`order_product_ids` string,`pay_category_ids` string,`pay_product_ids` string,`city_id` bigint)
row format delimited fields terminated by '\t';load data local inpath 'datas/user_visit_action.txt' 
into table atguigu.user_visit_action;CREATE TABLE `product_info`(`product_id` bigint,`product_name` string,`extend_info` string)
row format delimited fields terminated by '\t';load data local inpath 'datas/product_info.txt' into table atguigu.product_info;CREATE TABLE `city_info`(`city_id` bigint,`city_name` string,`area` string)
row format delimited fields terminated by '\t';load data local inpath 'datas/city_info.txt' into table atguigu.city_info;
  1. 查询

第一问:求每个地区点击量前三的商品;
分析:按 area,product_name 两个字段分组,求出点击量click_ct2,保留每个地区点击量前三的商品。

selectarea,product_name,click_ct2
from (selectarea,product_name,click_ct2,row_number() over( partition by area order by click_ct2 desc ) as rn2from (selectarea,product_name,count(*) as click_ct2from (selecta.*,p.product_name,c.area,c.city_namefrom user_visit_action ajoin product_info p on a.click_product_id = p.product_idjoin city_info c on a.city_id = c.city_idwhere a.click_product_id > -1) t1 group by area, product_name) t2
) t3 
where rn2 <= 3

在这里插入图片描述

第二问: 在①的基础上,求出每个地区点击量前三的商品后,求每个商品中的点击量前三的城市分别占本商品总点击量的百分比。

分析:
第一问求出了每个地区、每个商品的点击量,这个点击量叫click_ct2,并取了每个地区前三名的商品。
在第二问中,可以先求出每个地区、每个商品、每个城市的点击量,这个点击量叫click_ct3,取每个地区、每个商品点击量的前三名城市。

用click_ct3/click_ct2就是每个地区、每个商品、每个城市点击率,这个点击率叫click_rate3。
click_ct3所在的临时表叫tmp1,click_ct2所在的临时表叫tmp2。
tmp1有三个维度,粒度更细,数据条数会更多,tmp2有两个维度,粒度粗,数据条数少。
无论是tmp1 join tmp2,还是tmp2 join tmp1,两种方式都可以,tmp1中不符合条件的数据会被筛掉。

之后,用concat()将每行的城市名和点击率拼接在一起,
再按地区、商品、总点击量进行分组,用collect_set()收集每组拼接的结果,
将收集的结果拼接成字符串,再转换成map。

--维度:area,city_name,product_name
--度量:点击次数
--限定:前三
with tmp1 as(select area,product_name,city_name,click_ct3from(select area,city_name,product_name,click_ct3,row_number()over(partition by area,product_name order by click_ct3) rn1from(select area,city_name,product_name,count(*) click_ct3from(selecta.*,p.product_name,c.area,c.city_namefrom user_visit_action ajoin product_info p on a.click_product_id = p.product_idjoin city_info c on a.city_id = c.city_idwhere a.click_product_id > -1)t1group by area,city_name,product_name)t2)t3where rn1<=3order by area,product_name,city_name,click_ct3 desc
),
--维度:area,product_name
--度量:点击次数
--限定:前三
tmp2 as(selectarea,product_name,click_ct2from (selectarea,product_name,click_ct2,row_number() over( partition by area order by click_ct2 desc ) as rn2from (selectarea,product_name,count(*) as click_ct2from (selecta.*,p.product_name,c.area,c.city_namefrom user_visit_action ajoin product_info p on a.click_product_id = p.product_idjoin city_info c on a.city_id = c.city_idwhere a.click_product_id > -1) t1 group by area, product_name) t2) t3 where rn2 <= 3
)
select area,product_name,click_ct2,-- 按地区、商品、总点击量进行分组,用collect_set()收集每组拼接的结果,-- 将收集后的结果转换成mapstr_to_map(concat_ws(',',collect_set(city_rate)),',',':') city_rate3
from(select area,product_name,click_ct2,click_rate,-- 将每行的城市名和点击率拼接在一起concat(city_name,':',click_rate,'%') city_ratefrom(select tmp1.area,tmp1.product_name,tmp1.city_name,tmp2.click_ct2,round(tmp1.click_ct3*100/tmp2.click_ct2,2) click_ratefrom tmp2 join tmp1 on tmp2.area=tmp1.area and tmp2.product_name=tmp1.product_name)t1order by area,click_ct2 desc,click_rate desc
)t2
group by area,product_name,click_ct2
order by area,click_ct2 desc

在这里插入图片描述

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/145751.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

【Linux】【网络】传输层协议:TCP

文章目录 TCP 协议1. TCP 协议段格式2. TCP 报头解析3. TCP 的可靠性4. 面向字节流5. 粘包问题6. 连接队列维护 TCP 的 确认应答机制TCP 的 超时重传机制TCP 的 三次握手TCP 的 四次挥手setsockopt 函数&#xff1a;设置套接字选项&#xff0c;解决 TIME_WAIT 状态引起的 bind …

【Python】基于OpenCV人脸追踪、手势识别控制的求实之路FPS游戏操作

【Python】基于OpenCV人脸追踪、手势识别控制的求实之路FPS游戏操作 文章目录 手势识别人脸追踪键盘控制整体代码附录&#xff1a;列表的赋值类型和py打包列表赋值BUG复现代码改进优化总结 py打包 视频&#xff1a; 基于OpenCV人脸追踪、手势识别控制的求实之路FPS游戏操作 手…

小谈设计模式(7)—装饰模式

小谈设计模式&#xff08;7&#xff09;—装饰模式 专栏介绍专栏地址专栏介绍 装饰模式装饰模式角色Component&#xff08;抽象组件&#xff09;ConcreteComponent&#xff08;具体组件&#xff09;Decorator&#xff08;抽象装饰器&#xff09;ConcreteDecorator&#xff08;具…

玩转数据-大数据-Flink SQL 中的时间属性

一、说明 时间属性是大数据中的一个重要方面&#xff0c;像窗口&#xff08;在 Table API 和 SQL &#xff09;这种基于时间的操作&#xff0c;需要有时间信息。我们可以通过时间属性来更加灵活高效地处理数据&#xff0c;下面我们通过处理时间和事件时间来探讨一下Flink SQL …

解决前端二进制流下载的文件(例如:excel)打不开的问题

1. 现在后端请求数据后&#xff0c;返回了一个二进制的数据&#xff0c;我们要把它下载下来。 这是响应的数据&#xff1a; 2. 这是调用接口的地方&#xff1a; uploadOk(){if(this.files.length 0){return this.$Message.warning("请选择上传文件&#xff01;&#xff…

小白vite+vue3搭建项目整个流程

第一步 查看npm 版本npm -v&#xff0c;npm版本是7&#xff0c;创建项目命令&#xff1a; npm create vitelatest threejsVue -- --template vue第二步 // 进入项目名为threejsVue的项目命令 cd threejsVue // 安装路由 npm install vue-router4 // 安装css npm install -D s…

十、空闲任务及其钩子函数

1、空闲任务的介绍 (1)一个良好的程序&#xff0c;它的任务都是事件驱动的&#xff1a;平时大部分时间处于阻塞状态。 (2)有可能我们自己创建的所有任务都无法执行&#xff0c;但是调度器必须能找到一个可以运行的任务。所以&#xff0c;我们要提供空闲任务。 (3)在使用vTas…

Python日期的加减等操作

嗨喽&#xff0c;大家好呀~这里是爱看美女的茜茜呐 日期输出格式化 所有日期、时间的api都在datetime模块内。 &#x1f447; &#x1f447; &#x1f447; 更多精彩机密、教程&#xff0c;尽在下方&#xff0c;赶紧点击了解吧~ python源码、视频教程、插件安装教程、资料我都…

揭秘:机构招生电子传单制作的五个黄金法则

机构招生微传单制作一直都是让很多人在意的事情。一款好的微传单不仅可以吸引更多的学生&#xff0c;还可以省去很多招生工作的时间和精力。但是&#xff0c;很多人却不知道如何制作一款精美的微传单。下面就让我们来学习一下如何制作一款机构招生的微传单吧。 首先&#xff0c…

亲和力的作用,以及提高亲和力的六个办法

亲和力指的是容易使人亲近&#xff0c;当人身处集体中&#xff0c;亲和力也即是影响力和凝聚力的体现。通常是在职场中会明确对亲和力的考评&#xff0c;尤其是某些管理型岗位&#xff0c;所以HR人力资源管理中对亲和力有详细的评级标准。不过这里小猫测试网不详细讨论亲和力的…

敏感性分析一览

敏感性分析 SobolMorrisFourier Amplitude Sensitivity Test (FAST)Random Balance Designs - Fourier Amplitude Sensitivity Test (RBD-FAST)Delta Moment-Independent MeasureDerivative-based Global Sensitivity Measure (DGSM)Fractional Factorial Sensitivity Analysis…

服务器数据恢复-zfs下raidz多块磁盘离线导致服务器崩溃的数据恢复案例

服务器数据恢复环境&#xff1a; 一台服务器共配备32块硬盘&#xff0c;组建了4组RAIDZ&#xff0c;Windows操作系统zfs文件系统。 服务器故障&#xff1a; 服务器在运行过程中突然崩溃&#xff0c;经过初步检测检测没有发现服务器存在物理故障&#xff0c;重启服务器后故障依…

YOLOv7改进:结合CotNet Transformer结构

1.简介 京东AI研究院提出的一种新的注意力结构。将CoT Block代替了ResNet结构中的3x3卷积&#xff0c;在分类检测分割等任务效果都出类拔萃 论文&#xff1a;Contextual Transformer Networks for Visual Recognition 论文地址&#xff1a;https://arxiv.org/abs/2107.12292 有…

技术Leader对下管理的法宝-SMART

SMART方法论 源于国外管理大师的《管理的实践》是管理者能够更加明确员工高效工作的利器&#xff0c;科学、规范的对员工绩效制定考核目标和考核标准5个单词缩写 Specific:目标要具体Measurable:目标成果要可衡量(量化) Attainable:目标要可实现&#xff0c;避免过高/过低Rel…

模块化CSS

1、什么是模块化CSS 模块化CSS是一种将CSS样式表的规则和样式定义封装到模块或组件级别的方法&#xff0c;以便于更好地管理、维护和组织样式代码。这种方法通过将样式与特定的HTML元素或组件相关联&#xff0c;提供了一种更具可维护性、可复用性和隔离性的方式来处理样式。简单…

SpringBoot读取配置的方式

在 Spring Boot 应用中&#xff0c;我们通常需要一些配置信息来指导应用的运行。这些配置信息可以包括如下内容&#xff1a;端口号、数据库连接信息、日志配置、缓存配置、认证配置、等等。Spring Boot 提供了多种方式来读取这些配置信息。读取配置的目的是为了在程序中使用这些…

前端JavaScript入门到精通,javascript核心进阶ES6语法、API、js高级等基础知识和实战 —— Web APIs(三)

思维导图 全选案例 大按钮控制小按钮 小按钮控制大按钮 css伪类选择器checked <!DOCTYPE html> <html lang"en"><head><meta charset"UTF-8"><meta http-equiv"X-UA-Compatible" content"IEedge"><…

Apache Commons Pool2 池化技术

对象池是一种设计模式&#xff0c;用于管理和重用对象&#xff0c;以提高性能和资源利用率。对象池的概念在许多应用程序中都有广泛应用&#xff0c;特别是在需要频繁创建和销毁对象的情况下&#xff0c;例如数据库连接、线程、HTTP连接等 对象池通过预先创建一组对象并将它们存…

B. Comparison String

题目&#xff1a; 样例&#xff1a; 输入 4 4 <<>> 4 >><< 5 >>>>> 7 <><><><输出 3 3 6 2 思路&#xff1a; 由题意&#xff0c;条件是 又因为要使用尽可能少的数字&#xff0c;这是一道贪心题&#xff0c;所以…

最大内切圆算法计算裂缝宽度

本文这里是对CSDN上另一位博主的代码进行了整理&#xff1a; 基于opencv的裂缝宽度检测算法&#xff08;计算轮廓最大内切圆算法&#xff09; 我觉得这位博主应该是上传了一个代码草稿&#xff0c;我对其进行了重新整理&#xff0c;并添加了详细的注释。 import cv2 import …