商务服务
hiveSQL/SQL常用句式整理(不定时更新)
2024-12-26 23:41

业务常用整理

  1. 移动数据表的部分字段到另一个数据表
 

此种方式会覆盖原有字段值。 如果只是更新 使用下方方式 更新部分字段值与另一张表的一致

 
  1. 数据库构建用户并给予权限
 
  1. sql语句修改 数据库表字段长度
 

基础
子查询的语法
(SELECt [ALL|DISTINCT]<select item list>]
FROM <table list>
[WHERe<search condition>]
[GROUP BY <group item list>]
[HAVINg <group by search condition>])
注意:1.子查询的SELECt查询必须使用圆括号括起来
2.任何可以使用表达式的地方都可以使用子查询,只要它返回的是单个值
子查询常用的语法格式
WHERe 查询表达式 [NOT] IN(子查询)
WHERe 查询表达式 比较运算符 [ANY|ALL] (子查询
WHERe [NOT] EXISTS (子查询)

1.新数据插入到表格
业务常用
1.IF 条件语句的用法
if(bool , expr1, expr2)
如果bool为True,则取expr1,否则取expr2

2.①字符串替换函数。
regexp_replace(orig_str, ‘pattern’, ‘replacement’)
第一个参数为原始字符串orig_str. 第二个参数为要搜索的模式,第三个参数为要换为的字符串。
注意 特殊字符如‘|’需要用转义字符 ‘\|’
②字符串截取函数
substr(string A, int start, int len)

3.列转行数据
concat_ws(‘,’, collect_list(col))
第一个参数为连接的符号,第二个参数为需要列转行的列名。

4.联结操作
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

5.空值字段赋值 nvl
nvl(string,replace_col),如果字段string为null,则返回replace_col的值

6.分组求topN
row_number()函数基于over对象分组、排序的记过,为每一行分组记录返回一个序号,该序号从1开始,依次递增,遇到新组则重新从1开始。

 

7.行转列

 

8.按照字段区间对进行数据进行分组

 

9.时间戳转换为日期/ 日期转换为时间戳
from_unixtime(bigint unixtime,string format)
unix_timestamp(string date)

10.把数据插入到指定分区。
insert overwrite table t PARTITION(idate=20200728)

注意
***group by 子句进行分组查询 select查询的列必须包含在group by子句中或者包含在聚合函数中。

建表操作
CREATE TABLE ‘Student’(
‘s_id’ VARCHAr(20),
‘s_name’ VARCHAr(20) NOT NULL DEFAULT ‘’,
PRIMARY KEY(‘s_id’)
)

插入数据
insert into Student values[‘01’, ‘阿花’, ‘1990-01-01’, ‘男’]

第1题.查询课程编号为“”01“”的课程比02课程成绩高的所有学生的学号

 

第2题 查询平均成绩大于60分的学生学号和平均成绩

 

第3题 查询所有学生的学号,姓名,选课数,总成绩

 

4.查询姓“张”的老师的个数

 

5.查询没学过‘张三’老师课的学生的学号,姓名。

 

6.查询学过“张三”老师所教的所有课的同学的学号,姓名

 

7.查询学过编号为01的课程并且编号为02的课程学生的学号,姓名。

 

8.查询课程编号为“02”的总成绩

 

9.查询所有课程成绩小于60分的学生,姓名

 

10.查询没有学全所有课的学生的学号,姓名。

 

11.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
select s_id from score
where c_id in (
select c_id from score
where s_id=‘01’
) and s_id!=‘01’

12.查询和01号同学所学课程完全相同的其他同学的学号
select * from student
where s_id in (
select s_id FROM score
where s_id!=‘01’
GROUP BY s_id having count(distinct c_id)=(select count(distinct c_id) from score where s_id=‘01’)
) 条件1:选课数目必须相同,同时保证s_id不一样
and s_id not in (
select distinct s_id FROM score
where c_id not in(
select c_id from score
where s_id=‘01’
)
) 条件2:课程号 不在 01同学所学课程当中的 直接排除

15.查询两门及以上不及格课程的同学的学号,姓名及其平均成绩
select a.s_id, a.s_name, avg(s_score) from student as a
INNER JOIN score as b
on a,s_id=b.s_id
where s.id in (
select s_id from score
where s_score<60
group by s_id having count(distinct c_id)>=2
) 选择有两门不及格课程同学的学号
group by s_id,s_name

16.检索01课程分数小于60,按分数降序排列的学生信息
select t.*, s.c_id, s.s_score FROM student as t
inner join score as s ON t.s_id=s.s_id
where s.c_id=‘01’ and s.s_score<60
ORDER BY s.s_score DESC 降序排列

17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
s_id “学号”
,MAX(case when c_id=‘01’ THEN s_score ELSE NULL END)“语文”
,MAX(case when c_id=‘02’ THEN s_score ELSE NULL END)“数学”
,MAX(case when c_id=‘03’ THEN s_score ELSE NULL END)“英语”
,avg(s_score)
from score
group by s_id
ORDER BY avg(s_score) DESC

18.查询各科成绩最高分,最低分和平均分:以如下形式显示
课程ip,课程name,最高分,最低分,平均分,及格率,中等率,优秀率
select s.c_id
,c.cname,
,max(s.s_score)
,min(s.score)
,avg(s.score)
,sum(case when s.s_score>=60 then 1 else 0 END)/count(s_id)
,sum(case when s.s_score>=70 and s.s_core<80 then 1 else 0 END)/count(s_id)
from score as s
inner join course as c ON s.c_id=c,c_id
GROUP by c_id

19.按各科成绩进行排序,并显示排名
over (order by 列)
窗口函数
row_number() 不会重复
dense_rank() 会重复 2 2 3
disrank() 会重复,2 2 4

rank跳跃式的排序

20.查询学生的总成绩并进行排名
select s_id"学号", sum(s_score)“总成绩”
from score
group by s_id
order by sum(s_score) DESC

21.查询不同老师所教不同课程平均分从高到低显示
select c.c_id, c.name, avg(sc.s_score) as avg_score
from score as sc
inner join course as c on sc.c_id=c.c_id
group by sc.c_id
order by avg_score DESC

相关知识
1.什么是hive
解决海量结构化日志的数据统计
数据仓库工具 提供类似SQL查询功能。 将HQL转化成MapReduce 传输–存储–计算–

操作用mapreduce写成很多模板-》模板封装在hive中-》
1)数据存储在hdfs
2)分析数据底层默认实现是mapreduce
3)执行程序运行在Yam上

hive的优缺点
优点:执行延迟高,实时性要求不高 ,支持自定义函数
缺点:迭代算法无法表达,效率较低

hive架构
meta store 元数据
SQL Parser 解析器
Physical Plan编译器
Query Optimizer优化器
Exeution 执行器

二.hive安装
1.官网:http://hive.apache.org/
3.下载地址:http://archive.apache.org/dist/hive/

解压:unzip .zip
bin conf examples hcatalog lib LICENSE NOTICE README.txt scripts

bin/hive (启动 多了derby. , )
show database
create table student(id int, name string); row format delimited fields terminated by ‘t’
insert into table student values(1, ‘’)
导入数据 load data local inpath ’ ’ into tabel student: 本地数据

hadoop fs -put stu.txt / put文件到某目录下
查询

    以上就是本篇文章【hiveSQL/SQL常用句式整理(不定时更新)】的全部内容了,欢迎阅览 ! 文章地址:http://www78564.xrbh.cn/news/30586.html 
     文章      相关文章      动态      同类文章      热门文章      栏目首页      网站地图      返回首页 迅博思语移动站 http://www78564.xrbh.cn/mobile/ , 查看更多   
最新文章
闲置手机怎么处理?除了以旧换新外,闲置手机有了新去处
随着手机更新换代,不少人家中保存了一些闲置手机,如何处理成为大家头疼的问题,一旦处理不好可能会涉及手机里的隐私泄露。除了
大涨后大跌!“金条还没到手已亏4万多” 金店老板称20年没见过这样的行情
潮新闻客户端 记者 吴恩慧美国大打“关税战”,正引发全球金融市场巨震。“没想到黄金也被拖下水了,本来以为它作为避险资产,应
泰拉瑞亚手机合成树泰拉瑞亚手机合成表「泰拉瑞亚手机合成树」
      从泰拉瑞亚Wiki上扒的手机合成树,对萌新应该有用的,毕竟我刚玩几十个小时的时候都不知道合成东西需要查wiki。以下是
湘潭公积金使用政策优化 代际互助、首付降低等6大利好来了
为更好地满足缴存人刚性和改善性住房需求,加大住房公积金购房支持力度,4月7日,湘潭市住房公积金管理委员会发布《湘潭市住房公
小米云服务64位2.5.3小米手机云服务「小米云服务64位2.5.3」
小米云服务是一款小米集团推出的个人数据存储服务,软件内置在小米品牌手机、平板、电视、IoT等设备中,同时提供Windows及Mac的
明日首班车起,北京15条轨道交通地面高架线路区段停运
新京报讯(记者裴剑飞)记者从北京市交通委获悉,为全力应对大风橙色预警,保障市民安全出行,北京市交通部门已全面进入应急状态
生态环保业推动绿色转型动力强劲
经济日报记者 刘瑾 潘卓然在“双碳”战略引领下,生态环保产业迎来前所未有的发展机遇。在近日举办的第二十三届中国国际环保展览
发烧第二步——JBL 4312E和Melody Super 34的故事(多图,手机流量慎入)手机故事「发烧第二步——JBL 4312E和Melody Super 34的故事(多图,手机流量慎入)」
很久没上SMZDM晒单了,不是不想,只是因为——本DIAO在憋!大!招!………………………………哥这次晒单是冲着分(yi)享(qian
手机、手环、计步器 都是如何知道我们每天走了多少步的?谜底揭晓手机自动计步器「手机、手环、计步器 都是如何知道我们每天走了多少步的?谜底揭晓」
如今,每个人都非常关注健康。不管是出门佩戴手环、计步器,还是拿手机记录行走步数,已经成为很多人的生活习惯。可是,计步器到
极客修已恢复营业 3亿元的案子背后:价格低、质量差、扩张快互联网手机「极客修已恢复营业 3亿元的案子背后:价格低、质量差、扩张快」
《科创板日报》(上海,记者 戚夜云)讯,手机屏幕碎了,电池掉电太快,走官方维修,价格过高,不少用户选择线上下单、线下上门