SQL
SQL使用笔记
Asher117
大家好,我是Asher,热衷于研究数据分析、数据挖掘以及网络爬虫等。大家有问题的话,欢迎加我微信,一起学习-分享-成长,MyWechat:liu2536036458
展开
-
【Hive】Both left and right aliases encountered in JOIN
执行Hive语句报错:Both left and right aliases encountered in JOIN报错原因:在join的on条件中添加了不相等条件解决方法:把不相等条件放到where中即可原创 2020-12-21 23:23:19 · 731 阅读 · 0 评论 -
【Hive】unsupport subquery expression
hive报错unsupport subquery expression表示不支持子查询,可以改用left join方式原创 2020-12-21 23:21:03 · 1600 阅读 · 0 评论 -
【MySQL】float数据类型显示数据后几位不准确
解决方法:将数据类型改为bigint类型即可更改数据类型语句:alter table table_name modify column column_name 数据类型;原创 2020-12-21 19:44:47 · 717 阅读 · 0 评论 -
【MySQL】Error 1264: out of range value for column
mysql更改数据类型 或者 插入数据 Error 1264: out of range value for column这是由于mysql数据长度限制原创 2020-12-21 19:33:03 · 1289 阅读 · 0 评论 -
【Hive】向下取整,向上取整,四舍五入
floor函数向下取整select floor(1.2)1ceil函数向上取整select ceil(1.2)2round函数四舍五入select round(1.283,2)1.28原创 2020-12-21 19:25:24 · 1537 阅读 · 0 评论 -
【Hive】distinct on different columns not supported with skew in data
今天运行hive时候报错,如下:distinct on different columns not supported with skew in data由于对多个列执行去重操作导致,如以下代码:select id, count(distinct col1) as cnt1, count(distinct col2) as cnt2 from table_name group by id报这个错误的原因与hive的环境变量hive.groupby.skewindata相关默认情况下原创 2020-10-14 20:49:04 · 3312 阅读 · 0 评论 -
【Hive】missing KW_END at ‘)‘ near ‘<EOF>‘
报错:missing KW_END at ')' near '<EOF>'原因:case when 后面没写end原创 2020-10-09 15:41:00 · 5519 阅读 · 0 评论 -
【Hive】数据倾斜原因及解决方法汇总
(1)数据倾斜根本原因:由于数据分布不均匀,导致map端读取的数据分布不均匀(数据长尾分布),从而使得map处理的数据量差异过大。(2)解决思路:Hive是分阶段执行的,map处理数据量的差异取决于上一个stage的reduce输出,所以解决的根本方法就是如何将数据均匀的分布到各个reduce中(3)出现数据倾斜的主要操作:(a)join:使用join时,一个表较小,但是key值集中,使得数据在分发到各个reduce中的时候,某一个或几个reduce的数据远大于平均值(b)join:大表与大表joi原创 2020-10-09 15:34:02 · 4127 阅读 · 0 评论 -
【Hive】sort by、order by、 distribute by、 group by 、cluster by区别
(1)order by是将所有数据放在一个reduce里面进行排序,当数据量大时,会出现内存溢出的问题,同时数据排序效率低。另外,可以在sort by后面加上limit n来实现top n操作。(2)sort by是将数据放到多个reduce里面进行排序,排序后每一个reduce里面的数据是有序的,但是全部数据不一定有序。如果reduce个数为1,此时全部数据有序,等价于order by操作。当需要对全部数据排序时: (a)直接使用order by进行全局排序,效率低下。 (b)可以先使用so原创 2020-10-09 15:31:54 · 1275 阅读 · 0 评论 -
【Hive】常见优化方法
列裁剪select后面跟要选择的列,由于hive中数据采用列式存储,选择需要的字段可加快字段的读取、减少数据量。(节省了读取开销,中间表存储开销和数据整合开销)分区裁剪where条件第一个为分区字段,多值group by如果group by后面有多个字段时,将更多值的字段放在前面,如group by有user_id和sex两个字段,应该是group by user_id、sexorder byorder by时尽量使用limit,尽量避免使用order by。Order by需要扫描数据到单.原创 2020-10-09 15:29:10 · 225 阅读 · 0 评论 -
【Hive】时间相关函数
(1)获取当前时间在Mysql中获取当前时间,可以直接使用now()函数,输出为yyyy-MM-dd HH:mm:ss格式日期,如下:eg:select now()>2020-09-02 14:19:49在Hive中输出当前日期函数为current_date()函数,如下:eg:select current_date()>2020-09-02在Hive中还可以通过current_timestamp()函数输出时间,如下:eg:select current_timestamp(原创 2020-10-09 15:24:17 · 346 阅读 · 0 评论 -
【Hive】union和union all
union和union all均为拼接查询结果,不同点在于union会对结果去重,而union all不会对结果去重。如果不需要去重时,建议使用union all,因为union会对两个或多个sql查询结果去重,这一过程可能会引发内存问题。必须去重时,建议使用distinct+union all 代替union。eg: select 'a' union select 'a'>output:aeg: select 'a' union all select 'a'>out原创 2020-10-09 15:19:49 · 689 阅读 · 0 评论 -
【Hive】字符串替换函数translate和regexp_replace
Hive中,想要替换字符串中的子字符串,可以使用translate和regexp_replace两个函数,具体如下:translate函数格式:translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)--即将input出现在from中的字符串替换成to中的字符串eg:select translate('Asher117','Asher','Viva')>Viva117原创 2020-10-09 15:17:28 · 27105 阅读 · 0 评论 -
【Hive】if函数用法
Hive的if函数是一个条件判断函数,但判断条件为True时,返回valueTrue,否则返回valueFalseOrNull,如下:格式:if(boolean testCondition, T valueTrue, T valueFalseOrNull)eg:select if(1>2,1,2)>2原创 2020-10-09 15:10:11 · 5695 阅读 · 0 评论 -
【Hive】SemanticException Expression not in group by key
执行Hive语句,报错SemanticException Expression not in group by key。原因是我的select语句中有列,既不在group by子句中,也没有聚合。原创 2020-08-17 13:47:13 · 912 阅读 · 0 评论 -
【Hive】Failed rule:OrderByClause ClusterByClause ditributeByClause sortByClause limitClause can only
执行Hive语句,报错:Failed rule:OrderByClause ClusterByClause ditributeByClause sortByClause limitClause can only be applied to the whole union in statement,如下Failed rule:OrderByClause ClusterByClause ditributeByClause sortByClause limitClause can only be applied原创 2020-08-17 13:43:48 · 828 阅读 · 0 评论 -
【Hive】建表常用语句
--加上external为外部表,没有external为内部表create [external] table tableName( col1 type1, col2 type2, ... coln typen)partition by(colname,datatype) --设置分区字段row format delimited fields terminated by ',' --文件分隔符,这里设置为‘,’stored as [textfile | orc | ...] --设置文件存储原创 2020-08-17 10:33:23 · 330 阅读 · 0 评论 -
【Hive】报错FAILED: SemanticExceptionFailed to breakup Windowing invocations into Groups.
今天使用Hive时候,由于自己的大意,使得代码报错,报错如下:FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies.我的代码如下(这里举个例子,没有用源代码):select id, settle_month, r原创 2020-08-14 16:42:54 · 14195 阅读 · 0 评论 -
【Hive】保留n位小数
使用函数round函数即可,如下:>select round(0.9873,2)输出:>0.99PS:round函数通过四舍五入的方法保留小数原创 2020-08-14 15:21:40 · 2274 阅读 · 0 评论 -
【Hive】json解析函数get_json_object
这篇博文主要分享在Hive里面使用get_json_object函数来解析json格式数据的具体用法。在数据表test中有一列jsonData,其数据如下:jsonData = '{ "ID":121, "message":{ "name":"Asher", "location":[{"county":"浦东","city":"上海"}, {"county":"西直门","city":"北京"}] } }'--PS:jsonData列数据为字符串格式,外面是**单引号**,原创 2020-08-13 18:13:11 · 6746 阅读 · 4 评论 -
【Hive SQL】is not null之后还是存在空值
这是因为数据在数据库中格式可能为‘’,因此可以加一个条件:where length(trim(col)) = 0 另外,Hive SQL中NULL和‘’有所不同:where length(NULL),length('')->NULL,0原创 2020-07-22 15:46:42 · 4168 阅读 · 0 评论 -
【SQL】小数以百分数形式输出
代码:select concat(10.2,'%')->10.2%原创 2020-07-21 17:44:41 · 3536 阅读 · 1 评论 -
【Hive SQL】FAILED: ParseException line 13:4 missing KW_END at ‘,‘ near ‘‘
Hive SQL 代码报错:FAILED: ParseException line 13:4 missing KW_END at ',' near ''原因:case when 后没有加end原创 2020-07-21 14:50:49 · 2030 阅读 · 0 评论 -
【Hive】insert into 和 insert overwrite
insert into :以追加的方式插入数据insert overwrite:以覆盖的方式插入数据(先删除hive表中的数据,再插入数据)原创 2020-07-21 11:03:11 · 922 阅读 · 0 评论 -
【Hive SQL】FAILED: SemanticException [Error 10146]: can not truncate non-managed table
在Hive SQL中,执行truncate table **语句时,报错:FAILED: SemanticException [Error 10146]: can not truncate non-managed table原因是:Hive不能清空外部表(外部表不被hive管理,hive只维护到外部表的引用关系)因此,执行truncate操作,只能对内部表进行。...原创 2020-07-20 15:49:32 · 2139 阅读 · 0 评论 -
【SQL】Attempt to do update or delete using transaction manager that does not support these operations
在Hive SQL中执行update或者delete语句,报错:FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations通过查询资料得知,原因是hive中默认没有开启update和delete操作。如果要执行上述操作,需要自己配置。...原创 2020-07-20 15:46:32 · 3364 阅读 · 0 评论 -
【SQL Server】获取数据库下的数据库名,表名
1.查询所有的数据库名select * from sysdatabases输出:2.获取当前数据库下的所有表名#xtype = 'U'表示所有用户表#xtype = 'S'表示所有系统表select * from sysobjects where xtype = 'U'输出:ps:因为我这里数据库只建了一个用户表,所以显示的只有一个。...原创 2019-12-19 11:54:02 · 1302 阅读 · 0 评论 -
【SQL】数据类型转换
SQL的数据类型转行函数有cast和convert两个函数,用法如下:cast(列名 as 数据类型)eg:cast(tableAge as int)convert(数据类型,列名)eg:convert(int,tableAge)原创 2019-12-18 21:41:34 · 578 阅读 · 0 评论 -
【Python】操作MySQL数据库
摘要本文主要讲解使用Python的MySQLdb库来操作MySQL数据库,包括最常用的数据库的Select、Insert、Delete 、Update操作。如果还没有安装MySQLdb包,可以参考MySQLdb安装。1.首先本文的数据库表为Table1,具有name和id两列,并且目前里面有两条数据。SELECT * from table12.使用Python连接MySQL数据库并获...原创 2018-12-31 16:59:48 · 795 阅读 · 0 评论 -
Windows10下面安装SQL Server2014报错
今天在Windows10下面安装SQL Server2014报错:“需要 .NET Framework 3.5 Service Pack 1”这里就需要先安装.NET Framework 3.5之后再重新安装SQL Server。安装.NET Framework 3.5打开控制面板–程序和功能–启用或关闭Windows功能然后将此项勾选出来即可在线安装完成之后,重启电脑安装SQL S...原创 2019-01-07 19:35:20 · 4203 阅读 · 0 评论 -
导入Excel文件:“未在本地计算机上注册 Microsoft.ACE.OLEDB.12.0 ”
在SQL Server上导入Excel文件时,出现错误提示“未在本地计算机上注册 Microsoft.ACE.OLEDB.12.0 ”,这时只需要下载安装AccessDatabaseEngine.exe即可。下载地址点这里。...原创 2019-01-08 10:42:05 · 2740 阅读 · 3 评论 -
【SQL Server】导入Excel报错:文本截断
将EXCEL数据导入到SQL Server时有时候会因为文本长度原因出现文本截断。错误类型在导入时报错如下:错误0xc020901c:“文本被截断,或者一个或多个字符在目标代码页中没有匹配项”解决方法1将对应字段的长度由nvarchar(250)改为nvarchar(MAX).再重新导入。解决方法2如果字段长度改为nvarchar(MAX)之后依旧报错文本截断,则可以更改Excel文...原创 2019-01-08 11:29:51 · 4592 阅读 · 1 评论 -
Python-pyodbc连接读取SQL Server数据库数据
本文主要利用pyodbc扩展包连接SQL Server数据库,并利用select语句将数据库表中数据取出来存到pandas的DataFrame里面。导入包pandas以及pyodbc连接数据库3.设置编码格式4.使用pandas的read_sql将select查询结果存到DataFrame里面我们下次再见,如果还有下次的话!!!欢迎关注微信公众号:516数据工作室...原创 2018-10-18 21:45:20 · 4304 阅读 · 0 评论 -
【MySQL】LIMIT以及LIMIT OFFSET 使用方法介绍
1.LIMIT使用方法:LIMIT [参数1]--m,参数2--n;#表示从跳过m条数据开始取n行数据#参数1为可选参数,表示跳过m条数据(默认为0),eg:1表示从第二行开始#参数2为必选参数,表示取几行数据eg1:SELECT * FROM table LIMIT 5; //检索前 5 个记录行等价于SELECT * FROM table LIMIT 0,5; ...原创 2019-03-20 11:27:29 · 13090 阅读 · 4 评论 -
【SQL】排名函数ROW_NUMBER、RANK、DENSE_RANK和NTILE讲解
SQL中四大排名为:ROW_NUMBER、RANK、DENSE_RANK和NTILE。其中用法为:SELECT ROW_NUMBER()|RANK()|DENSE_RANK()|NTILE(n) OVER( ORDER BY 列名 [DESC] ) AS NewColumn,* FROM TABLE ......注意:1.你可能会注意到NTILE函数中带有参数n,这个函数用来对...原创 2019-06-06 08:40:58 · 1231 阅读 · 0 评论 -
【SQL】日期加减几天几月几年
#日期加一天DateAdd(Day,1,日期)#日期减一个月DateAdd(Month,-1,日期)#日期加一年DateAdd(Year,1,日期)另外,日期加减函数:DateAdd和DateDiff原创 2019-03-27 13:24:18 · 6162 阅读 · 0 评论 -
【SQL Server】concat、concat_ws、group_concat用法
concat用途:连接一个或多个字符串语法:concat(str1,str2…)eg:>>>select concat('aa','bb','cc')>aabbccconcat_ws用途:使用固定连接符连接一个或多个字符串语法:concat(separator,str1,str2…)eg:>>>select concat_ws('-'...原创 2019-05-17 11:40:10 · 10312 阅读 · 7 评论 -
【SQL Server】substr用法
语法:substr(strings|express,m,[n]) 等价于 substr(string,start,length)参数含义:strings|express :被截取的字符串或字符串表达式m 从第m个字符开始截取n 截取后字符串长度为neg:select substr('abcdefg',3,4) from dual;结果是cdefselect substr('abcde...原创 2019-05-17 11:45:21 · 6230 阅读 · 2 评论 -
【SQL Server】DATE_SUB()函数
作用:DATE_SUB() 函数从日期减去指定的时间间隔。语法:DATE_SUB(date,INTERVAL expr type)date 参数是合法的日期表达式。expr 参数是您希望减去的时间间隔。type 时间间隔类型(包括second、minute、hour、day、week、month等等)实例假设我们有如下的表:OrderIdProductNameOrderD...原创 2019-05-17 15:18:50 · 8566 阅读 · 5 评论 -
【HQL】Hive SQL查询使用中文别名
使用反单引号–Tab键上面的键即可,eg:select max(company_times) as `公司申请次数`from tableA原创 2019-07-22 15:56:17 · 7186 阅读 · 2 评论