Hive 案例

最新推荐文章于 2024-06-10 10:59:25 发布
最新推荐文章于 2024-06-10 10:59:25 发布
阅读量851 收藏 4
点赞数 2
分类专栏： hive 文章标签： hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ly13607255628/article/details/120798871
版权

1、需求描述和数据说明

针对销售数据，完成统计：

  1. 按年统计销售额
  2. 销售金额在 10W 以上的订单
  3. 每年销售额的差值
  4. 年度订单金额前10位（年度、订单号、订单金额、排名）
  5. 季度订单金额前10位（年度、季度、订单id、订单金额、排名）
  6. 求所有交易日中订单金额最高的前10位
  7. 每年度销售额最大的交易日
  8. 年度最畅销的商品(即每年销售金额最大的商品)

数据如下
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

2、建表并导入数据

vim createtable.hql

-- createtable.hql
drop database sale cascade;
create database if not exists sale;
create table sale.dimdate_ori(
dt date,
yearmonth int,
year smallint,
month tinyint,
day tinyint,
week tinyint,
weeks tinyint,
quat tinyint,
tendays tinyint,
halfmonth tinyint
)
row format delimited
fields terminated by ",";

create table sale.sale_ori(
orderid string,
locationid string,
dt date
)
row format delimited
fields terminated by ",";



create table sale.saledetail_ori(
orderid string,
rownum int,
goods string,
num int,
price double,
amount double
)
row format delimited
fields terminated by ",";


create table sale.dimdate(
dt date,
yearmonth int,
year smallint,
month tinyint,
day tinyint,
week tinyint,
weeks tinyint,
quat tinyint,
tendays tinyint,
halfmonth tinyint
) stored as orc;

create table sale.sale(
orderid string,
locationid string,
dt date
) stored as orc;

create table sale.saledetail(
orderid string,
rownum int,
goods string,
num int,
price double,
amount double
)stored as orc;

hive -f createtable.hql

导入数据

use sale;
load data local inpath "/root/bigdata/test_data/hive/sale/tbDate.dat" overwrite into
table dimdate_ori;
load data local inpath "/root/bigdata/test_data/hive/sale/tbSale.dat" overwrite into
table sale_ori;
load data local inpath "/root/bigdata/test_data/hive/sale/tbSaleDetail.dat" overwrite
into table saledetail_ori;
-- 导入数据
insert into table dimdate select * from dimdate_ori;
insert into table sale select * from sale_ori;
insert into table saledetail select * from saledetail_ori;

hive -f loaddata.hql

查看结果

select * from dimdate limit 5;
+-------------+--------------------+---------------+----------------+--------------+---------------+----------------+---------------+------------------+--------------------+
| dimdate.dt  | dimdate.yearmonth  | dimdate.year  | dimdate.month  | dimdate.day  | dimdate.week  | dimdate.weeks  | dimdate.quat  | dimdate.tendays  | dimdate.halfmonth  |
+-------------+--------------------+---------------+----------------+--------------+---------------+----------------+---------------+------------------+--------------------+
| 2003-01-01  | 200301             | 2003          | 1              | 1            | 3             | 1              | 1             | 1                | 1                  |
| 2003-01-02  | 200301             | 2003          | 1              | 2            | 4             | 1              | 1             | 1                | 1                  |
| 2003-01-03  | 200301             | 2003          | 1              | 3            | 5             | 1              | 1             | 1                | 1                  |
| 2003-01-04  | 200301             | 2003          | 1              | 4            | 6             | 1              | 1             | 1                | 1                  |
| 2003-01-05  | 200301             | 2003          | 1              | 5            | 7             | 1              | 1             | 1                | 1                  |
+-------------+--------------------+---------------+----------------+--------------+---------------+----------------+---------------+------------------+--------------------+



select * from sale limit 5;
+---------------+------------------+-------------+
| sale.orderid  | sale.locationid  |   sale.dt   |
+---------------+------------------+-------------+
| BYSL00000893  | ZHAO             | 2007-08-23  |
| BYSL00000897  | ZHAO             | 2007-08-24  |
| BYSL00000898  | ZHAO             | 2007-08-25  |
| BYSL00000899  | ZHAO             | 2007-08-26  |
| BYSL00000900  | ZHAO             | 2007-08-26  |
+---------------+------------------+-------------+


 select * from saledetail limit 5;
+---------------------+--------------------+-------------------+-----------------+-------------------+--------------------+
| saledetail.orderid  | saledetail.rownum  | saledetail.goods  | saledetail.num  | saledetail.price  | saledetail.amount  |
+---------------------+--------------------+-------------------+-----------------+-------------------+--------------------+
| BYSL00000893        | 0                  | FS527258160501    | -1              | 268.0             | -268.0             |
| BYSL00000893        | 1                  | FS527258169701    | 1               | 268.0             | 268.0              |
| BYSL00000893        | 2                  | FS527230163001    | 1               | 198.0             | 198.0              |
| BYSL00000893        | 3                  | 24627209125406    | 1               | 298.0             | 298.0              |
| BYSL00000893        | 4                  | K9527220210202    | 1               | 120.0             | 120.0              |
+---------------------+--------------------+-------------------+-----------------+-------------------+--------------------+

3、 sql 实现

1、按年统计销售额

SELECT year(B.dt) year, 
       round(sum(A.amount)/10000, 2) amount
FROM saledetail A 
join sale B 
on A.orderid=B.orderid
group by year(B.dt);

+-------+----------+
| year  |  amount  |
+-------+----------+
| 2004  | 326.81   |
| 2005  | 1325.76  |
| 2006  | 1368.1   |
| 2007  | 1671.94  |
| 2008  | 1467.43  |
| 2009  | 632.37   |
| 2010  | 21.09    |
| 3274  | 0.17     |
+-------+----------+

2、销售金额在 10W 以上的订单

SELECT orderid, 
       round(sum(amount), 2) amount
FROM saledetail
group by orderid
having sum(amount) > 100000
+----------------+-----------+
|    orderid     |  amount   |
+----------------+-----------+
| HMJSL00009024  | 119084.8  |
| HMJSL00009958  | 159126.0  |
+----------------+-----------+

3、每年销售额的差值

select
 year,
 amount,
 round( amount - lag(amount) over(sort by year),2 )  df
from 
(
SELECT 
   year(B.dt) year,
   sum(A.amount) amount
FROM saledetail A 
join sale B 
on A.orderid=B.orderid
group by year(B.dt)  
) t

+-------+-----------------------+--------------+
| year  |        amount         |      df      |
+-------+-----------------------+--------------+
| 2004  | 3268115.4991999646    | NULL         |
| 2005  | 1.3257564150000084E7  | 9989448.65   |
| 2006  | 1.3680982899999822E7  | 423418.75    |
| 2007  | 1.6719354560000354E7  | 3038371.66   |
| 2008  | 1.4674295299999664E7  | -2045059.26  |
| 2009  | 6323697.1899998775    | -8350598.11  |
| 2010  | 210949.66000000003    | -6112747.53  |
| 3274  | 1703.0                | -209246.66   |
+-------+-----------------------+--------------+

4、年度订单金额前10位（年度、订单号、订单金额、排名）

with tmp as (
  select  
     year(a.dt) year,
     a.orderid,
     sum(b.amount) as amounts
  from sale a 
  left join saledetail b
  on a.orderid = b.orderid
  group by  year(a.dt),a.orderid
),

tmp2 as (
 select 
     year,
     orderid,
     amounts,
     dense_rank()  over(partition by year order by amounts desc ) as rn 
 from tmp
)
select year,orderid,amounts,rn from tmp2 where rn <=10
+-------+----------------+---------------------+-----+
| year  |    orderid     |       amounts       | rn  |
+-------+----------------+---------------------+-----+
| 2004  | HMJSL00001557  | 23656.79999999997   | 1   |
| 2004  | HMJSL00001556  | 22010.599999999984  | 2   |
| 2004  | HMJSL00001349  | 17147.2             | 3   |
| 2004  | HMJSL00001531  | 16605.6             | 4   |
| 2004  | HMJSL00001567  | 15651.0             | 5   |
| 2004  | HMJSL00001562  | 15429.999999999989  | 6   |
| 2004  | HMJSL00000706  | 15266.0             | 7   |
| 2004  | HMJSL00000656  | 15021.319999999998  | 8   |
| 2004  | HMJSL00001568  | 14444.0             | 9   |
| 2004  | HMJSL00000963  | 14268.800000000001  | 10  |
| 2005  | HMJSL00003263  | 38186.399999999994  | 1   |
| 2005  | HMJSL00005422  | 29442.999999999964  | 2   |
| 2005  | HMJSL00002046  | 27612.399999999983  | 3   |
| 2005  | HMJSL00005147  | 24473.399999999998  | 4   |
| 2005  | HMJSL00004467  | 22908.479999999996  | 5   |
| 2005  | HMJSL00004201  | 22700.159999999996  | 6   |
| 2005  | HMJSL00004084  | 22618.0             | 7   |
| 2005  | HMJSL00004491  | 22570.799999999996  | 8   |
| 2005  | HMJSL00004464  | 22439.76            | 9   |
| 2005  | HMJSL00004547  | 22271.76            | 10  |
| 2006  | HMJSL00006452  | 36124.0             | 1   |
| 2006  | HMJSL00005769  | 31269.799999999977  | 2   |
| 2006  | HMJSL00008345  | 29011.79999999998   | 3   |
| 2006  | HMJSL00006759  | 28610.0             | 4   |
| 2006  | GCSL00000781   | 27158.999999999975  | 5   |
| 2006  | HMJSL00005760  | 26736.49999999998   | 6   |
| 2006  | HMJSL00006758  | 24652.0             | 7   |
| 2006  | HMJSL00006763  | 24368.0             | 8   |
| 2006  | YZSL00000563   | 23696.65000000001   | 9   |
| 2006  | HMJSL00006757  | 23656.0             | 10  |
| 2007  | HMJSL00009958  | 159126.0            | 1   |
| 2007  | HMJSL00009024  | 119084.80000000008  | 2   |
| 2007  | HMJSL00009957  | 52422.0             | 3   |
| 2007  | HMJSL00010216  | 49800.0             | 4   |
| 2007  | HMJSL00009956  | 43018.0             | 5   |
| 2007  | HMJSL00010339  | 42157.59999999999   | 6   |
| 2007  | HMJSL00008593  | 41902.400000000016  | 7   |
| 2007  | HMJSL00010137  | 40339.999999999985  | 8   |
| 2007  | TYSL00001043   | 37940.0             | 9   |
| 2007  | HMJSL00008408  | 37438.2             | 10  |
| 2008  | HMJSL00010598  | 55828.0             | 1   |
| 2008  | YZSL00001404   | 35794.2             | 2   |
| 2008  | YZSL00001405   | 34713.4             | 3   |
| 2008  | GCSL00001414   | 29843.800000000003  | 4   |
| 2008  | YZSL00001235   | 27295.799999999996  | 5   |
| 2008  | RMSL00012270   | 24210.0             | 6   |
| 2008  | YZSL00001246   | 23838.2             | 7   |
| 2008  | GCSL00001255   | 23284.199999999993  | 8   |
| 2008  | SSSL00013486   | 22544.199999999997  | 9   |
| 2008  | YZSL00001283   | 22502.8             | 10  |
| 2009  | TSSL00016101   | 25813.200000000008  | 1   |
| 2009  | TSSL00016110   | 23628.200000000004  | 2   |
| 2009  | TSSL00016082   | 23560.000000000004  | 3   |
| 2009  | SSSL00013554   | 22309.599999999995  | 4   |
| 2009  | TMSL00015434   | 21706.60000000001   | 5   |
| 2009  | SSSL00013535   | 21254.399999999998  | 6   |
| 2009  | SSSL00013540   | 21199.399999999998  | 7   |
| 2009  | TSSL00016087   | 21087.200000000004  | 8   |
| 2009  | TMSL00015426   | 20946.200000000008  | 9   |
| 2009  | TSSL00015110   | 20126.199999999997  | 10  |
| 2010  | SSSL00016272   | 13065.280000000002  | 1   |
| 2010  | SSSL00016265   | 12905.000000000002  | 2   |
| 2010  | SSSL00016279   | 11227.0             | 3   |
| 2010  | SSSL00016338   | 9033.0              | 4   |
| 2010  | TSSL00016266   | 8952.8              | 5   |
| 2010  | TSSL00016340   | 8945.1              | 6   |
| 2010  | TSSL00016281   | 8532.8              | 7   |
| 2010  | SSSL00016327   | 6883.4              | 8   |
| 2010  | TSSL00016274   | 6781.199999999999   | 9   |
| 2010  | GHSL00016289   | 4734.0              | 10  |
| 3274  | BYSL00013309   | 1703.0              | 1   |
+-------+----------------+---------------------+-----+

5、季度订单金额前10位（年度、季度、订单id、订单金额、排名）

with tmp as (
  select  
    year(a.dt) year,
    c.quat,
    a.orderid,
    sum(b.amount) as amounts
  from sale a 
  left join saledetail b
  on a.orderid = b.orderid
  left join  dimdate c 
  on a.dt = c.dt
  group by  year(a.dt),c.quat,a.orderid
),

tmp2 as (
 select 
    year,
    quat,
    orderid,
    amounts,
    dense_rank()  over(partition by year,quat order by amounts desc ) as rn 
 from tmp
)

select year,orderid,amounts,rn from tmp2 where rn <=10

6、求所有交易日中订单金额最高的前10位

with tmp as (
select A.dt, A.orderid, round(sum(B.amount), 2) amount
from sale A join saledetail B on A.orderid=B.orderid
group by A.dt, A.orderid
)
select dt, orderid, amount, rank
from (
select dt, orderid, amount, dense_rank() over(order by
amount desc) rank
from tmp
) tmp1
where rank <= 10;

7、每年度销售额最大的交易日

with tmp as (
select A.dt, round(sum(B.amount), 2) amount
from sale A join saledetail B on A.orderid=B.orderid
group by A.dt
)
select year(dt) year, max(amount) dayamount
from tmp
group by year(dt);

8、年度最畅销的商品(即每年销售金额最大的商品)

with tmp as (
select year(B.dt) year, goods, round(sum(amount),2) amount
from saledetail A join sale B on A.orderid=B.orderid
group by year(B.dt), goods
)
select year, goods, amount
from (select year, goods, amount, dense_rank() over
(partition by year order by amount desc) rank
from tmp) tmp1
where rank = 1;
梵圣
关注
  • 2
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive案例之---------微博数据分析及答案.zip
09-10
在这个“hive案例之---------微博数据分析及答案”的项目中，我们将会探索如何利用Hive进行大规模的微博数据挖掘与分析。 首先，项目说明文档.docx可能会详细阐述了该项目的目标、背景、数据来源以及预期的结果。...
Hive案例数据集.rar
10-27
《Hive案例数据集详解》 Hive是Apache软件基金会开发的一个开源大数据处理框架，它构建在Hadoop之上，提供了一种SQL-like的查询语言（HQL）来处理大规模的数据。这个“Hive案例数据集.rar”文件包含了丰富的示例...
利用Hive进行复杂用户行为大数据分析及优化案例
02-20
利用Hive进行复杂用户行为大数据分析及优化案例（全套视频+课件+代码+讲义+工具软件），具体内容包括： 01_自动批量加载数据到hive 02_Hive表批量加载数据的脚本实现（一） 03_Hive表批量加载数据的脚本实现（二） 04_HIve中的case when、cast及unix_timestamp的使用 05_复杂日志分析-需求分析 06_复杂日志分析-需求字段讲解及过滤 07_复杂日志分析-字段提取及临时表的创建 08_复杂日志分析-指标结果的分析实现 09_Hive中数据文件的存储格式介绍及对比 10_常见的压缩格式及MapReduce的压缩介绍 11_Hadoop中编译配置Snappy压缩 12_Hadoop及Hive配置支持snappy压缩 13_Hive中的常见调优 14_Hive中的数据倾斜及解决方案-三种join方式 15_Hive中的数据倾斜及解决方案-group by 16_Hive中使用正则加载数据 17_Hive中使用Python脚本进行预处理
大数据Hive入门案例
赵广陆
03-27 886
目录1 体验1：Hive使用起来和Mysql差不多吗？1.1 背景1.2 过程1.3 验证1.4 结论2 体验2：如何才能将结构化数据映射成为表？2.1 背景2.2 过程2.3 验证2.4 结论3 体验3：使用hive进行小数据分析如何？3.1 背景3.2 过程3.3 验证3.4 结论 1 体验1：Hive使用起来和Mysql差不多吗？ 1.1 背景 对于初次接触Apache Hive的人来说，最大的疑惑就是：Hive从数据模型看起来和关系型数据库mysql等好像。包括Hive SQL也是一种类SQL语言
大数据开发--基于Hive的航空公司客户价值分析案例
最新发布
m0_70466650的博客
06-10 1148
本文主要以利用Hive对数据进行处理，Hive 是一个适用于处理大规模数据的开源数据仓库工具，提供了 SQL-Like 查询语言和良好的容错性，可以方便地进行数据查询和分析。通过对航空公司客户数据进行清洗分析，并通过K-means聚类将客户划分成几种不同的群体，根据不同群体中的各个特征指标，进一步得出客户价值，企业可以对不同的客户采用相关的策略，实现公司利益最大化。
大数据开发——Hive实战案例
weixin_44606952的博客
11-25 5783
1. 创建表结构 1.1 视频表结构 1.2 用户表结构 2. 准备工作 2.1 创建临时表 2.2 创建最终使用表 2.3 对创建表进行解读 3. 业务分析
6.Hive函数重要应用案例
hutc_Alan的博客
06-30 714
Hive学习笔记6，自用
hive库操作示例
java界的小学生
07-26 3508
1、hive库基本操作； 2、hive库分区表常见操作； 3、关于删除及更新的描述； 4、分区表数据插入、删除； 5、分桶表数据插入、删除； 6、java客户端采用jdbc操作hive库；
hive案例资源
08-21
本资源是hive四中的资源准备，希望对大家有用处，文件不大哦
hive影评案例.zip
04-01
标题中的“hive影评案例.zip”表明这是一个关于使用Hive处理电影评论数据的示例。Hive是基于Hadoop的数据仓库工具，它允许用户通过SQL（HQL，Hive SQL）查询分布式存储的大数据集。这个压缩包可能包含了用于分析用户...
hive优化案例
01-14
作为企业Hadoop应用的核心产品，Hive承载着FaceBook、淘宝等大佬 95%以上的离线统计，很多企业里的离线统计甚至全由Hive完成，如我所在的电商。 Hive在企业云计算平台发挥的作用和影响愈来愈大，如何优化提速已经...
大数据Hive窗口函数应用实例
赵广陆
09-19 1254
目录1 连续登陆用户1.1 需求1.2 分析1.3 建表1.4 方案一：自连接过滤实现1.5 方案二：窗口函数实现2 级联累加求和2.1 需求2.2 分析2.3 建表2.4 方案一：自连接分组聚合2.5 方案二：窗口函数实现3 分组TopN3.1 需求3.2 分析3.3 建表3.4 实现 1 连续登陆用户 1.1 需求 当前有一份用户登录数据如下图所示，数据中有两个字段，分别是userId和loginTime。 userId表示唯一的用户ID，唯一标识一个用户，lo
hive实战案例讲解
张小鱼的博客
06-01 922
本文主要介绍hive的一个案例实战，以及相关hive语句的书写，案例仅供参考
大数据开发之Hive案例篇14：某个节点HDFS块比较多
只是甲的博客
06-30 1585
那么此时可以这么理解，当集群处于空闲时，突然来了一个任务，那么此时因为所有container的优先级相同，优先选择的就是本节点的container，而第一个container 用于启动作业的AM进程，这也就对应了之前的，chd10节点申请AM会比其他节点多很多。从图中可以看出，每个节点的HDFS空间是相同的，大多节点HDFS使用量在40%左右，而出问题的这个节点居然直逼80%，鉴于之前问题出现过多次，且每次都是利用空余时间使用HDFS的rebalance进行解决的，此处需要找出具体问题，规避此类问题。
hive实战例子
十色花的博客
07-04 3856
Hive实战实战案例1——数据ETLü 对web点击流日志基础数据表进行etl（按照仓库模型设计）ü  按各时间维度统计来源域名top10已有数据表 “t_orgin_weblog”：+------------------+------------+----------+--+ |     col_name     | data_type  | comment  | +----------...
hive实例运用
m0_58233168的博客
11-02 594
hive实例运用
Hive SQL综合案例
weixin_42073629的博客
09-24 1329
一 Hive SQL练习之影评案例 案例说明 现有如此三份数据： 1、users.dat 数据格式为： 2::M::56::16::70072， 共有6040条数据 对应字段为：UserID BigInt, Gender String, Age Int, Occupation String, Zipcode String 对应字段中文解释：用户id，性别，年龄，职业，邮政编码 2、movies.dat 数据格式为： 2::Jumanji (1995)::Adventure|Children's|Fa
图解大数据 | Hive搭建与应用@实操案例
热门推荐
ShowMeAI研究中心
03-08 1万+
Hive是大数据离线计算的关键组件，常用于数仓建设。本节ShowMeAI来详细讲解Hive的搭建和配置及使用方法。
hadoop学习---基于Hive的教育平台数据仓库分析案例（一）
weixin_46164667的博客
05-04 3893
基于hive的数据仓库搭建项目，主题是关于在线教育平台数据仓库搭建。
hivesql经典案例
08-22
HiveSQL经典案例有很多，以下是其中的几个： 1. 电商数据分析：通过HiveSQL可以对电商平台的大数据进行分析，如用户购买行为、订单统计、商品销售排名等。可以利用Hive的内置函数和高级操作来进行数据清洗、聚合和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值