HiveSQL 工作实战总结

已于 2023-02-13 13:49:48 修改
阅读量860 收藏 4
点赞数 3
分类专栏： 大数据开发文档 文章标签： hive 大数据 sql
于 2023-02-13 11:18:51 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-NC-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/PirateRaccoon/article/details/129005815
版权

记录一些工作中有意思的统计指标，做过一些简化方便大家阅读，记录如有错误，欢迎在评论区提问讨论~

问题类型

  1. 连续问题
    • 两种思路
    • 第一种：日期减去一列数字得出日期相同，主要是通过row_number窗口函数
    • 第二种：后一个日期减去前一个日期差值相等，用的较少，可以用lag/lead窗口函数解决
  2. 分组问题
    • 主要使用lag(col,1,0)分组将每行移到下一行，再按既定规则分组排序即可
    • 后面抽空试一下
  3. 间隔连续问题，比如每断一天也算连续
    • 两种思路：
    • 第一种：连续使用两次连续问题的求法即可，差了几次可以连续row_number几次，这种无限套娃不推荐使用
    • 第二种：连续差值小于要求数即可，比如断一天也可，只要每行间隔小于2天即可
  4. 打折日期交叉问题，两段活动期重复日期去除
  5. 同时在线问题

一、统计每个设备的累计告警次数

原始数据格式

deviceIdalarmDatealarmCount
设备ID告警日期告警次数
u012022/1/85
u022022/1/87
u032022/1/83
u012022/1/122
u022022/1/121
u012022/1/149
………

统计之后格式

设备ID告警月份告警次数小计告警次数累计
u012022-021111
u022022-031223
…………

工作思路

  1. 先根据设备ID和告警日期分组
  2. 按月份统计可以用substr函数或者日期格式化函数
  3. 再统计即可得出小计告警次数
  4. 接着使用聚合窗口函数计算累计告警次数

工作语句

  1. 第一种方案，使用substr截取字符串函数
SELECT *,
	   SUM(sumPart) OVER (PARTITION BY deviceId ORDER BY alarmMonth) AS sumAll
FROM
(SELECT deviceId,
	   SUBSTR(alarmDate,1,7) AS alarmMonth,
	   SUM(alarmCount) AS sumPart
FROM test_00
GROUP BY deviceId,
		 alarmMonth) t;
  1. 第二种方案，使用日期格式化函数，使用date_format函数的字符串必须满足yyyy-MM-dd格式，所以必须先用regexp_replace替换/为-
SELECT *,
	   SUM(sumPart) OVER (PARTITION BY deviceId ORDER BY alarmMonth) AS sumAll
FROM
(SELECT deviceId,
	    DATE_FORMAT(REGEXP_REPLACE(alarmDate,'/','-'), 'yyyy-MM') AS alarmMonth,
	    SUM(alarmCount) AS sumPart
FROM test_00
GROUP BY deviceId,
		 alarmMonth) t;
开窗函数中的界限说明
  • unbounded：无界限
  • preceding：从分区第一行头开始，则为 unbounded N为：相对当前行向后的偏移量
  • following ：与preceding相反,到该分区结束，则为 unbounded N为：相对当前行向后的偏移量
  • current row：顾名思义，当前行，偏移量为0

二、统计环境设备每天的总污染告警次数，并输出每个设备告警次数排名前三的日期

原始数据格式

deviceIdalarmTime
设备ID告警时间
u012022/1/8/08/04/58
u022022/1/8/12/05/38
u032022/1/8/17/01/12
u012022/1/12/12/04/53
u022022/1/12/13/45/34
u012022/1/14/02/12/51
……

统计之后格式

设备ID告警次数累计
u013
u022
u031
……

工作思路

统计环境设备的总污染告警次数
  1. 由于有设备可能会有同一时间的告警记录，所以需要按告警时间去重后再统计
  2. 如果使用distinct去重，如果表数据过大，且设备ID差异化很大，那么会有性能压力
  3. 所以使用group by子查询代替
  4. mysql中的date_format格式化需要这样写：DATE_FORMAT(alarmTime, '%Y-%c-%d %T')
输出每个设备告警次数排名前三的日期
  1. 使用窗口函数ROW_NUMBER() OVER()进行分组排序即可，MySQL 替换 ROW_NUMBER() OVER (PARTITION ……) 函数
  2. 多个子句查询可以使用视图和WITH语句

工作语句

统计环境设备的总污染告警次数
SELECT deviceId,
		COUNT(alarmTime) AS alarmCount 
FROM
--- http://c.biancheng.net/mysql/date_format.html
(SELECT deviceId,
		DATE_FORMAT(REGEXP_REPLACE(alarmTime,'/','-'), 'yyyy-MM-dd HH:mm:ss') AS alarmTime
FROM test_01
GROUP BY deviceId,alarmTime
ORDER BY alarmTime) t
GROUP BY deviceId;
输出每个设备告警次数排名前三的日期
SELECT * 
FROM 
(SELECT deviceId,
		alarmDate,
		alarmCount,
		ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmCount DESC) AS alarmRank
FROM 
(SELECT deviceId,
		alarmDate,
		COUNT(alarmDate) AS alarmCount
FROM
(SELECT deviceId,
		DATE_FORMAT(alarmTime, 'yyyy-MM-dd') AS alarmDate,
		DATE_FORMAT(alarmTime, 'yyyy-MM-dd HH:mm:ss') AS alarmTime
FROM test_01
GROUP BY deviceId,alarmTime
ORDER BY deviceId,alarmTime) t1
GROUP BY deviceId,alarmDate) t2) t3
WHERE alarmRank<=3;

-- 使用WITH语句优化一下
WITH t1 AS (
SELECT deviceId,
	DATE_FORMAT(alarmTime, 'yyyy-MM-dd') AS alarmDate,
	DATE_FORMAT(alarmTime, 'yyyy-MM-dd HH:mm:ss') AS alarmTime
FROM test_01
GROUP BY deviceId,alarmTime
ORDER BY deviceId,alarmTime),
t2 AS (
	SELECT deviceId,
	alarmDate,
	COUNT(alarmDate) AS alarmCount
FROM t1
GROUP BY deviceId,alarmDate),
t3 AS (
SELECT deviceId,
	alarmDate,
	alarmCount,
	ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmCount DESC) AS alarmRank
FROM t2)
SELECT * FROM t3 WHERE alarmRank<=3;
COUNT(1)和COUNT(*)的区别
  1. 从执行结果来说
    • COUNT(1)和COUNT(*)之间没有区别，因为COUNT(*)和COUNT(1)都不会去过滤空值
    • 但COUNT(列名)就有区别了，因为COUNT(列名)会去过滤空值
  2. 从执行效率来说
    • 他们之间根据不同情况会有些许区别，MySQL会对COUNT(*)做优化
    • 如果列为主键，COUNT(列名)效率优于COUNT(1)
    • 如果列不为主键，COUNT(1)效率优于COUNT(列名)
    • 如果表中存在主键，COUNT(主键列名)效率最优
    • 如果表中只有一列，则COUNT(*)效率最优
    • 如果表有多列，且不存在主键，则COUNT(1)效率优于COUNT(*)

三、统计每个月的总告警次数，总告警设备数，以及能够连续七天数值正常设备数量

留给你思考

四、统计出2022年1月首次出现告警的设备数量

原始数据格式

deviceIdalarmTime
设备ID告警时间
u012022/1/8/08/04/58
u022022/2/8/12/05/38
u032021/9/8/17/01/12
u012022/1/12/12/04/53
u022022/4/12/13/45/34
u012022/5/14/02/12/51
……

统计之后格式

设备ID首次告警时间
xxx2022/1/8/08/04/58
xxx2022/1/8/12/05/38
xxx2022/1/8/17/01/12
……

工作思路

  1. 先用date_format格式化所有设备告警时间为yyyy-MM的日期格式
  2. 运用min函数得出每个设备最早告警日期
  3. 当最早告警日期是2022年1月的时候即为我们所需要知道的设备记录

工作语句

WITH t1 AS (
SELECT *,
		DATE_FORMAT(alarmTime, 'yyyy-MM') AS alarmMonth 
FROM test_01
),
t2 AS (
SELECT deviceId,
		alarmTime,
		MIN(alarmMonth) AS firstAlarmMonth
FROM t1
GROUP BY deviceId
)
SELECT * FROM t2 WHERE firstAlarmMonth='2022-1';

五、根据设备地区编号段对告警次数进行排序

有一个5000万的设备信息表，一个2亿记录的告警记录表

原始数据格式

  1. 设备信息表
deviceIddeviceNamedeviceDistrict
设备ID设备名称设备所属地区
u01xx01210000
u02xx02210010
u03xx03210025
………
  1. 告警记录表
deviceIdalarmTime
设备ID告警时间
u012022/1/8/08/04/58
u022022/2/8/12/05/38
u032021/9/8/17/01/12
u012022/1/12/12/04/53
u022022/4/12/13/45/34
u012022/5/14/02/12/51
……

统计之后格式

设备地区号段告警次数
210000-2100102
210010-2100208
210020-2100304
210040-2100507
……

工作思路

  1. 先根据设备ID分组count统计报警次数
  2. 再使用casewhen条件语句，或者使用concat/floor/ceil函数动态划分，根据分段统计不同设备位于什么地区号段
  3. 最后连接查询，并根据地区号段，使用sum函数统计总告警次数即可

工作语句

-- 第一种方案
WITH t1 AS(
SELECT deviceId,
		COUNT(alarmTime) AS alarmCount
FROM test_01
GROUP BY deviceId
),
t2 AS(
SELECT deviceId,
		deviceDistrict,
		-- 如果地区编号是字符串可以先转换再比较，不然会触发隐式转换，导致全表扫描无法使用索引
		-- CONVERT(deviceDistrict, UNSIGNED)>=210000
		CASE WHEN deviceDistrict>=210000 AND deviceDistrict<210010 THEN '210000-210010'
		 	WHEN deviceDistrict>=210010 AND deviceDistrict<210020 THEN '210010-210020'
			WHEN deviceDistrict>=210020 AND deviceDistrict<210030 THEN '210020-210030'
		 	WHEN deviceDistrict>=210030 AND deviceDistrict<210040 THEN '210030-210040'
		 	WHEN deviceDistrict>=210040 AND deviceDistrict<210050 THEN '210040-210050'
		 	WHEN deviceDistrict>=210050 AND deviceDistrict<210060 THEN '210050-210060'
		 	WHEN deviceDistrict>=210060 AND deviceDistrict<210070 THEN '210060-210070'
		END deviceDistrictSection
FROM test_02
),
t3 AS (
	SELECT t2.deviceDistrictSection AS deviceDistrictSection,
			SUM(t1.alarmCount) AS alarmCount
	FROM t1 LEFT JOIN t2 
	ON t1.deviceId = t2.deviceId
	GROUP BY deviceDistrictSection
	ORDER BY deviceDistrictSection
)
SELECT * FROM t3;

-- 第二种方案
WITH t1 AS(
SELECT deviceId,
		COUNT(alarmTime) AS alarmCount
FROM test_01
GROUP BY deviceId
),
t2 AS(
SELECT deviceId,
		deviceDistrict,
		CONCAT(FLOOR(deviceDistrict/10)*10, '-', (FLOOR(deviceDistrict/10)+1)*10) AS deviceDistrictSection
FROM test_02
),
t3 AS (
	SELECT t2.deviceDistrictSection AS deviceDistrictSection,
			SUM(t1.alarmCount) AS alarmCount
	FROM t1 LEFT JOIN t2 
	ON t1.deviceId = t2.deviceId
	GROUP BY deviceDistrictSection
	ORDER BY deviceDistrictSection
)
SELECT * FROM t3;

-- 第二种方案的函数测试
SELECT FLOOR(210015/10)*10 AS x;  -- 210015
SELECT CEIL(210015/10)*10 AS y;  -- 210020
SELECT CONCAT(FLOOR(210015/10)*10, '-', CEIL(210015/10)*10);  -- 210010-210020
SELECT CONCAT(FLOOR(210020/10)*10, '-', CEIL(210020/10)*10);  -- 210020-210020
SELECT CONCAT(FLOOR(210020/10)*10, '-', (FLOOR(210020/10)+1)*10);  -- 210020-210030
拼接函数concat/concat_ws/group_concat的区别说明
  1. concat
    • 将多个字符串连接成一个字符串
    • concat(str1, str2,...)
    • 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null
  2. concat_ws
    • 和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符
    • 第一个参数指定分隔符，concat_ws(separator, str1, str2, ...)
    • 返回结果为连接参数产生的字符串。需要注意的是分隔符不能为null，如果为null，则返回结果为null
  3. group_concat
    • 将group by产生的同一个分组中的值连接起来，返回一个字符串结果
    • group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
    • *说明：*通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号
拼接函数floor/ceil/round的区别说明
  1. floor
    • 在英文中，是地面，地板的意思，有下面的意思；所以此函数是向下取整，它返回的是小于或等于函数参数，并且与之最接近的整数
    • 向下取整的时候，正数，则取其整数部位，抹除小数部位
    • 负数，则取其整数加一
    • 整数，则不变
  2. ceil
    • 在英文中，是天花板的意思，有向上的意思；所以此函数是向上取整，它返回的是大于或等于函数参数，并且与之最接近的整数
    • 向上取整的时候，正数，则直接将当前整数加一
    • 负数，则将整数后面的数据抹除
    • 整数，则不变
  3. round
    • 在英文中是有大约，环绕，在某某四周，附近的意思，所以，可以取其大约的意思，在函数中是四舍五入
    • 四舍五入的时候，正数，小数位大于5，则整数位加一，小数位小于5，则整数位不变，抹除小数位
    • 负数，小数位小于5，则整数位不变，抹除小数位，小数位大于5，则整数位加一
    • 整数，则不变
MySQL中保留两位小数
  1. round(x,d) 四舍五入保留小数
    • round(x)其实就是round(x,0)，也就是默认d为0，默认不保留小数，d为保留几位小数
    • d可以是负数，这时是指定小数点左边的d位整数位为0，同时小数位均为0，例如：round(114.6, -1) -> 110
  2. truncate(x,d) 函数返回被舍去至小数点后d位的数字x，和round函数类似，但是没有四舍五入
  3. format(x,d) 强制保留d位小数，整数部分超过三位的时候以逗号分割，并且返回的结果是string类型的
  4. convert(value,type) 类型转换，相当于截取，例如：
    • convert(100.3465, decimal(10,2)) -> 100.35
    • convert(100, decimal(10,2)) -> 100
    • convert(100.4, decimal(10,2)) -> 100.4
Hive中保留两位小数
  1. round(column_name,2) 四舍五入截取 这种方法慎用，有时候结果不是你想要的
  2. regexp_extract(column_name,'([0-9]*.[0-9][0-9])',1) 正则匹配截取，不做四舍五入，只是单纯的当作字符串截取
  3. cast(column_name as decimal(10,2)) cast函数截取 推荐使用

六、统计所有告警设备和所有活跃告警设备（连续三天都有告警的设备）的总数，以及平均监测值

活跃告警设备是指连续三天都有告警的设备

连续N天登录等类似题目的解题思路
  1. 日期减去一列数字得到的日期相等
  2. 后一个日期减去前一个日期的差值相等

原始数据格式

deviceIdalarmDatealarmValueAvgDaily
设备ID告警日期当日平均监测值
u012022-1-827
u022022-4-512
u032022-3-245
u012022-2-1066
u022022-1-1898
u012022-1-2853
……

统计之后格式

类型总数总均值
所有告警设备1839834
活跃告警设备321387

工作思路

  1. 首先使用group by去除重复日期的重复数据，用max函数取最大值
  2. 然后使用group by去除重复设备数，分别查询设备总数和总平均值，再用左连接将查询结果拼接，保存结果查询
  3. 接着处理统计活跃告警设备，先用row_number函数查询分组编号，再使用date_sub函数用告警日期减去分组编号，得出一组临时告警日期用于判定是否是活跃告警设备
  4. 如果有连续相同日期说明是活跃告警设备，所以接着使用count函数和having条件统计过滤有大于等于三天的连续相同日期的设备与告警日期，注意同时要计算均值
  5. 左后统计活跃告警设备总数和平均值，并和第二步中的结果union all即可

工作语句

WITH 
-- 首先去除重复日期的重复数据，这里取最大值
t1 AS(
SELECT deviceId,
		alarmDate,
		MAX(alarmValueAvgDaily) AS alarmValueAvgDaily
FROM test_03
GROUP BY deviceId, alarmDate
),
-- 去除重复设备数
t2 AS(
SELECT *
FROM t1
GROUP BY deviceId
),
-- 查询设备总数
t3 AS(
SELECT '告警设备总数与均值' AS type,
		COUNT(deviceId) AS allDeviceCount
FROM t2
),
-- 查询总均值
t4 AS(
SELECT ROUND(AVG(alarmValueAvgDaily)) AS alarmValueAvgAll
FROM t1
),
-- 查询分组后的排序编号
t5 AS(
SELECT *,
		ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmDate) AS alarmDateRank
FROM t1
),
-- 查询告警日期减去分组后排序编号之后的日期，如果有连续相同的说明是连续的天数
t6 AS(
SELECT *,
		DATE_SUB(alarmDate, INTERVAL alarmDateRank DAY) AS alarmDateSub
FROM t5
),
-- 查询连续天数大于3天的设备，以及这些活跃设备的平均值
t7 AS(
SELECT deviceId,
		ROUND(AVG(alarmValueAvgDaily))  AS alarmValueAvgActive,
		alarmDateSub,
		COUNT(*) AS  alarmDateSubCount
FROM t6
GROUP BY deviceId, alarmDateSub
HAVING alarmDateSubCount>=3  
),
t8 AS(
SELECT '活跃告警设备总数与均值' AS type,
		COUNT(deviceId) AS allDeviceCount,
		ROUND(AVG(alarmValueAvgActive)) AS alarmValueAvgActiveAll
FROM t7
)
-- 统计完成所有告警设备以及平均监测值
SELECT * FROM t3 LEFT JOIN t4 ON t4.alarmValueAvgAll IS NOT NULL
UNION ALL
-- 统计完成活跃告警设备以及平均监测值
SELECT * FROM t8;
合并操作符union和union all之间的区别
  1. 相同之处
    • 都是用于合并两个或多个select语句的结果组合成单个结果集
    • 操作符内部的每个select语句必须拥有相同数量的，列也必须拥有相似的数据类型，同时每个select语句中的列的顺序必须相同
  2. 不同之处
    • 对重复结果的处理：union在进行表连接后会筛选掉重复的记录，union all不会去除重复记录
    • 对排序的处理：union将会按照字段的顺序进行排序，union all只是简单的将两个结果合并后就返回
    • 从效率上说，union all要比 union快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用union all
Hive和MySQL中的日期函数
  1. MySQL Date 函数、MySQL 日期函数
  2. 【hive 日期函数】Hive常用日期函数整理
  3. 后期切记整理链接资料，若忘记请读者提醒！！！感谢！！！

七、统计2022年1月8日下午16点-17点，每个接口调用量top10的ip地址

原始数据格式

timeinterfaceip
时间接口访问IP
2021/1/8 15:01:28/api/user/login110.25.3.56
2021/1/8 15:21:12/api/device/alarm23.21.33.87
2021/1/8 15:51:34/api/device/record45.76.21.543
……

统计之后格式

接口访问IP访问次数排名
/api/user/login110.25.3.56891
/api/device/alarm23.21.33.871231
/api/device/record45.76.21.543231
…………
此题作为开放题供大家查阅，后面有空再继续写

附录资料

Hive和MySQL中部分函数的区别

  1. date_format()
    • Hive date_format(date date / timestamp time / string 'xxxx-xx-xx', format 'yyyy-MM-dd')，只能识别用-连接的日期字符串
    • MySQL date_format(date, format)，具体的format规则请查询参考资料
  2. date_sub()
    • Hive date_sub(date date / timestamp time, int days)
    • MySQL date_sub(date, interval 时间间隔 type)，具体的type规则请查询参考资料

Hive和MySQL常用日期函数

  1. date_add() 向日期添加指定的时间间隔
  2. date_sub() 从日期减去指定的时间间隔
  3. datediff() 返回两个日期之间的天数

Hive中order by/distribute by/sort by/group by/partition by之间的区别说明

  1. order by
    • order by会对数据进行全局排序，和oracle、mysql等数据库中的order by效果一样
    • 需要注意的是，hive执行过程中它只在一个reduce中进行，所以数据量特别大的时候效率非常低
    • group by分组之后是会组内聚合的，而distribute by和partition by仅仅是分组了，并未有聚合操作
  2. distribute by
    • distribute by是控制在map端如何拆分数据给reduce端的
    • hive会根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法
  3. sort by
    • sort by为每个reduce产生一个排序文件
    • 在有些情况下，你需要控制某个特定行应该到哪个reducer，这通常是为了进行后续的聚集操作distribute by刚好可以做这件事
    • 因此，distribute by经常和sort by配合使用
  4. group by
    • 和distribute by类似 都是按key值划分数据 都使用reduce操作
    • 唯一不同的是，distribute by只是单纯的分散数据，distribute by col 是按照col列把数据分散到不同的reduce
    • 而group by把相同key值的数据聚集到一起，后续必须是聚合操作
  5. cluster by
    • 按列分桶建表使用
    • distribute by 和 sort by 合用就相当于cluster by，但是cluster by不能指定排序为asc(升序)或desc(倒序)的规则，只能是升序排列
  6. partition by
    • 按所分区名分区建表使用
    • 通常查询时会对整个数据库查询，而这带来了大量的开销，因此引入了partition的概念
    • 在建表的时候通过设置partition的字段，会根据该字段对数据分区存放，更具体的说是存放在不同的文件夹
    • 这样通过指定设置partition的字段条件查询时可以减少大量的开销
    • 区内排序用order by

MySQL多表查询时如何将NULL置为0

使用IFNULL("字段", 0)函数即可

Hive中如何处理NULL值和空字符串

  1. Hive表中默认将NULL存为\N，可查看表的源文件（hadoop fs -cat或者hadoop fs -text），文件中存储大量\N，这样造成浪费大量空间
  2. 但Hive的NULL有时候是必须的
    • Hive中insert语句必须列数匹配，不支持不写入，没有值的列必须使用NULL占位
    • Hive表的数据文件中按分隔符区分各个列，空列会保存NULL（\n）来保留列位置，
      但外部表加载某些数据时如果列不够，如表13列，文件数据只有2列，则在表查询时表中的末尾剩余列无数据对应，自动显示为NULL
  3. 所以，NULL转化为空字符串，可以节省磁盘空间
    • 建表时直接指定
    # 第一种方式
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  
WITH SERDEPROPERTIES ('serialization.null.format' = '')  
# 第二种方式
 ROW FORMAT DELIMITED NULL DEFINED AS ''
    • 修改已存在的表
    ALTER TABLE hive_tb SET SERDEPROPERTIES('serialization.null.format' = '');
  4. 使用函数处理NULL值
    • NVL(expr1,expr2) 如果第一个参数为NULL那么显示第二个参数的值，如果第一个参数的值不为NULL，则显示第一个参数本来的值
    • Coalesce(expr1, expr2, expr3….. exprn) 返回表达式中第一个非空表达式，如果所有自变量均为NULL，则 COALESCE 返回NULL
    SELECT COALESCE(NULL,null,3,4,5); 　　-- 结果为：3
SELECT COALESCE(NULL,null,'',3,4,5);   -- 结果为：''
SELECT COALESCE(NULL,null,null,NULL);  -- 结果为：null

我是 fx67ll.com，如果您发现本文有什么错误，欢迎在评论区讨论指正，感谢您的阅读！
如果您喜欢这篇文章，欢迎访问我的 本文github仓库地址，为我点一颗Star，Thanks~ 😃
转发请注明参考文章地址，非常感谢！！！

Hive SQL数据分析实战（一）
恋恋风尘的博客
05-09 1812
有以下几张数据表，请写出Hive SQL语句，实现以下需求。 注：分区字段为dt，代表日期。 1、某次经营活动中，商家发起了"异性拼团购"，试着针对某个地区的用户进行推广，找出匹配用户。 参考实现：选出城市在北京，性别为男的10个用户名 select user_name from user_info where city='beijing' and sex='male' limit 1...
SQL基础：HQL工作中常用函数总结及案例实战
石榴姐yyds
03-29 2154
本文针对hive进行数据分析时总结了工作中常用的查询函数及分析函数，并对每种函数的用法进行总结赋予案例，每种案例都具有详细的解释。文章主要以实践为主，紧紧围绕工作中常用的一些函数进行总结，更注重对函数的使用方法进行分析，其后的案例读者可自行尝试学习，具有借鉴意义。
一文学完所有的Hive Sql（两万字最全详解）
热门推荐
yuan_more的博客
04-01 4万+
Hive Sql 大全 本文基本涵盖了Hive日常使用的所有SQL，因为SQL太多，所以将SQL进行了如下分类： 一、DDL语句（数据定义语句）： 对数据库的操作：包含创建、修改数据库 对数据表的操作：分为内部表及外部表，分区表和分桶表 二、DQL语句（数据查询语句）： 单表查询、关联查询 hive函数：包含聚合函数，条件函数，日期函数，字符串函数等 行转列及列转行：lateral view 与 explode 以及 reflect 窗口函数与分析函数 其他一些窗口函数 文章首发于公众...
hive的SQL总结
wzcwmhp的博客
11-25 1680
进入hive shell hive或者hive –service cli Hive 的启动方式: hive 命令行模式，直接输入/hive/bin/hive的执行程序，或者输入 hive –service cli hive web界面的启动方式，hive –service hwi hive 远程服务 (端口号10000) 启动方式，hive –service hiveserver
hive sql 的总结分析
weixin_40809627的博客
06-16 312
1、旨在对hive sql整体总结记录 todo refer https://my.oschina.net/leejun2005/blog/75722
hive sql系列（总结）
luo981695830的博客
04-17 2889
每天分享一个sql，帮助大家找到sql的快乐 hive sql系列（总结）介绍 hive sql系列主打sql，通过案例，从实现到分析，帮助大家找到写sql的快乐 hive sql系列目录 1. hive sql系列（一）：找出所有科目成绩都大于某一学科平均成绩的学生 2. hive sql系列（二）：统计每个用户每个月访问量和累计月访问量 3. hive sql系列（三）：求所有用户和活跃用户的总数及平均年龄 4. hive sql系列（四）：请用sql写出所有用户中在今年10月份第一次购.
最强最全面的Hive SQL开发指南，超四万字全面解析！
大数据星球-浪尖
01-28 1327
本文整体分为两部分，第一部分是简写，如果能看懂会用，就直接从此部分查，方便快捷，如果不是很理解此SQl的用法，则查看第二部分，是详细说明，当然第二部分语句也会更全一些！第一部分：hive模...
最强HiveSQL开发指南.pdf
11-30
总结起来，《最强HiveSQL开发指南》全面涵盖了Hive的基本操作、查询语法、性能优化策略及实战经验，是学习和提升Hive技能的宝贵资源。通过深入学习和实践，读者可以更好地驾驭Hive，应对大数据环境下的各种挑战。
大数据 hive 实战数据
04-07
总结来说，这个实战数据集为我们提供了研究大数据分析和数据仓库应用的宝贵资源。通过对`video`和`user`数据的Hive操作，我们可以深入了解用户行为、视频流行趋势，以及如何利用Hadoop生态系统进行大规模数据处理。...
"HiveSQL实战：基础题目、面试题及数据示例
本文将对提供的HiveSQL实战题目进行分析和解答，并对Hive面试题进行总结。总体而言，本文将涵盖50个HiveSQL基础题目，以及一些相关的原始数据和表结构。 首先，根据题目提供的原始数据，我们可以看到有三个表：...
hive数据保留小数点两位小数
雾岛与鲸的博客
07-12 1万+
方法一 round(column_name,2) 四舍五入截取(这种方法慎用，有时候结果不是你想要的) select round(645342.875645342,2); +------------+--+ | _c0 | +------------+--+ | 645342.88 | +------------+--+ 方法二 cast(column_name as decimal(10,2)) cast函数截取（推荐使用） select cast(645342.8756
hive 里的向上取整、向下取整、四舍五入取整的实例！
oracle8090的博客
08-23 3万+
hive 里的向上取整、向下取整、四舍五入取整的实例.以及精确保留小数位数。 ==================================================== 【四舍五入取整截取】 select round(54.56,0) round至少保留一位小数。 55.0 ==================================================== 【...
hive中小数点处理函数
qq_37189286的博客
03-16 7898
hivesql 中对于小数点的处理 ceil 向上取整 SELECT ceil('10.25') 11 SELECT ceil('10.75') 11 floor 向下取整 SELECT floor('10.75') 10 SELECT floor('10.25') 10 round 四舍五入 SELECT round('10.25') 10 SELECT round('10.75') 11 regexp_extract正则取数 0表示把整个正则表达式对应的...
Hive 字符串截取前两位和截取后两位
三妹的博客
04-09 3万+
一、需求 截取 ID 字段前两位和去掉后两位 二、实现 使用 Hive 中 substr() 函数来实现。 1、截取前两位： substr(ID,0,2) substr() 第一个参数表示待截取的字段名称，第二个参数表示截取的起始位置，第三个字段表示截取的长度。 2、截取后两位： substr(ID,-2,2) 表示从倒数第二个位置开始截取两位，即截取后两位。 以上，问题解决...
hive函数总结
li2007211的博客
11-22 941
hive函数的分类 hive的客户端 显示当前会话有多少函数可用  SHOW FUNCTIONS; 如： hive> show functions ; OK ! != %   显示函数的描述信息  DESC FUNCTION concat; 如： hive> DESC FUNCTION concat; OK concat(str1, str2, ...
数据处理奇技巧——hive篇
livan1234的博客
01-04 3439
1、pmod(int a, int b)：返回a除以b的余数的绝对值； cast(aaa as int):将string转化成int； cast(aaa as decimal(10, 2)):将string转化成float,保留两位小数； 2、trim(String A)：去除A两侧的空格； ltrim(String A)：去除左边空格； rtrim(String A)：去除右边空格 ...
Hive函数大全
weixin_30570101的博客
03-28 196
一、关系运算： 1.等值比较: = 语法：A=B 操作类型：所有基本类型 描述:如果表达式A与表达式B相等，则为TRUE；否则为FALSE 举例： hive>select 1 from lxw_dual where 1=1; 1 2.不等值比较: <...
Hadoop Hive sql语法详解
weixin_30355437的博客
02-14 7063
Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，可以将结构 化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需 要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户...
hive中round、floor、ceil区别及用法
很努力学习知识的博客
08-19 1万+
浮点数四舍五入： round(1.4) : 1 round(1.5) : 2 浮点数取两位小数： round(1.23454,2) : 1.23 round(1.04234,2) : 1.04 round(1.04534,2) : 1.05 小数点位数是负数，比如-2，那么小数点前2位为0，且进行四舍五入计算。 round(1213232,-2) : 1213200 round(1213252...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值