![](https://img-blog.csdnimg.cn/20201014180756928.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
SQL
努力成为一个帅气数据风控科学家
不信没有女粉
展开
-
hive 使用regexp筛选和替代特殊字符
```sql---替换regexp_replace(lower(name),'[^0-9a-zA-Z\\u4e00-\\u9fa5]','') 除中文字母数字以外的字符全部替换掉“\u4e00”和“\u9fa5”是unicode编码,并且正好是中文编码的开始和结束的两个值,所以这个正则表达式可以用来判断字符串中是否包含中文例如:select '李中*华',--李中*华regexp_replace(lower('李中*华' ),'[^0-9a-zA-Z\\u4e00-\\u9fa5]','.原创 2021-06-27 14:51:34 · 4730 阅读 · 4 评论 -
【精选必读】navicat批量读取excel导入到数据库中操作详解
1.打开navicat比如在thousand这个连接下,批量将excel数据导入到该数据库下的表格中1.1右键thousand 打开命名为thousand的这个连接,其中含有建好的数据库pku1.2右键pku,打开pku这个数据库1.3右键“表”,打开导入向导,或者先将其中一个excel导入到数据库,比如显示的“review1”选择我们想导入的文件格式,比如excel file,点击下一步1.4点击下一步,点击… 从路径中选择需要导入的文件,选择所想导入的多个文件,文件的表头必须一致原创 2020-12-20 19:59:52 · 4704 阅读 · 0 评论 -
identifiers must not start with a digit; surround the identifier with double quotes
identifiers must not start with a digit; surround the identifier with double quotes或许你尝试了很多遍也没有解决这个问题,甚至没有看懂这句话,没关系,现在你懂了因为你在命名的时候 用的是“7days“这样数字开头 英文结尾的,sql里面可以别名里含有下划线和数字,但一定不能用数字开头取别名哦~改个名字就好啦...原创 2020-11-21 16:26:06 · 6936 阅读 · 4 评论 -
presto hive like用法总结,使用| 和 or like多个怎么写
–like与regexp_like的用法prestowhere (address like ‘%广州%’ OR address like ‘%北京%’ OR address like ‘%上海%’ OR address like ‘%深圳%’)或者presto:case when (regexp_like (industry,‘土石方|挖机|矿业|装修|建筑|采油|钢构|钢材|油田|煤矿’)) then 1 else 0 end as ZX_B5HIVE LIKEcount(distinct原创 2020-11-21 16:14:08 · 7154 阅读 · 0 评论 -
使用hive alter进行改删等操作
—改列名和列型ALTER table dm.table2 change column txn_num txn_num3 string;—增列 为空值ALTER table dm.table2 ADD columns (txn_num2 int);–或者ALTER table dm.table2 replace columns (txn_pas_num int,txn_pas_num2 STRING);—表改名ALTER table dm.table2 rename to dm.table3;原创 2020-11-21 15:17:42 · 875 阅读 · 0 评论 -
SQL union的用法
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。SQL UNION 语法SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。SQL原创 2020-10-25 17:51:14 · 654 阅读 · 0 评论 -
SQL函数汇总(超全!!!)
abs(X)The abs(X) function returns the absolutevalue of the numeric argument X. Abs(X) returns NULL if X is NULL. Abs(X) returns 0.0 if X is a string orblob that cannot be converted to anumeric value. If X is the 原创 2020-10-03 16:58:09 · 617 阅读 · 0 评论 -
窗口函数之 lead() over(partition by ) 和 lag() over(partition by )
lag() over() 与 lead() over() 函数是跟偏移量相关的两个分析函数,通过这两个函数可以在一次查询中取出同一字段的前 N 行的数据 (lag) 和后 N 行的数据 (lead) 作为独立的列, 从而更方便地进行进行数据过滤。这种操作可以代替表的自联接,并且 LAG 和 LEAD 有更高的效率。 over() 表示 lag() 与 lead() 操作的数据都在 over() 的范围内,他里面可以使用 partition by 语句(原创 2020-10-03 15:36:40 · 13434 阅读 · 0 评论 -
数据库里两个数写错了怎么通过查询的方式纠正
同事不小心将某一天的两个产品的几百条渠道号,14 15记反了,数据库是不可能改了,怎么样能查到正确的结果呢SELECTID,CASE WHEN DATE='2020-07-01‘ and channel = 14 then 15else channel end as channel原创 2020-09-10 20:43:50 · 139 阅读 · 0 评论 -
SQL row_number函数详解,row_number多个partition怎么写
selectrow_number()over(partition by id order by date asc ) as rn当rn = 1留下的就是最小date下的id了原创 2020-09-10 20:39:31 · 2228 阅读 · 0 评论 -
presto SQL快捷获取每月第一天
SELECTCAST(DATE_ADD(‘day’,1-day(current_date),current_date)as varchar(20))原创 2020-09-10 20:37:41 · 3231 阅读 · 2 评论 -
SQL case when 多个条件怎么写
示例CASE WHEN ID = 101 THEN ‘A1’ WHEN ID = 102 THEN ‘A2’ ESLE NULL END AS ID原创 2020-09-10 20:35:50 · 2034 阅读 · 0 评论 -
灵活使用inner join,on≤,limit计算最近15天的年度累计值
工作中遇到这样一个需求,计算最近15天的 今年20200101来每一天的用信年度总额,年度用信总人数SELECTA2.DATE,SUM(A1.USE_LOAN_CNT) AS USE_LOAN_CNT,SUM(A1.USE_LOAN_AMT) AS USE_LOAN_AMTFROM (SELECT DATE,COUNT(DISTINCT ID) AS USE_LOAN_CNT,SUM(USE_LOAN_AMT) AS USE_LOAN_AMTFROM TABLE1WHERE ETL_原创 2020-08-15 19:58:36 · 367 阅读 · 0 评论 -
left join on多个条件怎么写
有时我们不仅需要用一个字段去关联,还希望两个表的两个字段都是一样的,这时候可以这样写select * from(select id,name,code from table1) aleft join(select id,name,code from table2) bon a.id = b.id and a.code = b.code有时候on出错的话,可以用caston cast(a.id as varchar(40)) = cast(b.id as varchar(40))and原创 2020-08-15 17:49:19 · 8143 阅读 · 1 评论 -
tableau数据可视化心得之一
1.能写一个大一点的sql,就不要用联接,奇慢无比。但tableau查询上限是100,所以往往在hive里建立临时表再查询会提高查询效率2.tableau不能识别汉字的字段,有中文字段要先转换成英文,后续再转化成中文,切忌!3.tableau对于重复出现的字段会报错,一定要换一个名字!4.实时和提取的区别,为什么有时候你使用tableau很卡,可能是因为你的数据没有进行数据提取,页面总在不断地实时刷新。对于想在T+1自动更新在tableau的报表,是必须要将数据提取保存在本地的!5.未完 待后续不断原创 2020-08-09 15:33:06 · 3100 阅读 · 0 评论 -
sql division by zero 分母为0
有时我们想计算两个字段的比例,但有个别字段的数据为0,那么就会存在分母为0而报错。如果,我们想要分母为0 的时候输出比例为0,那么可以写一个case whenselect (case when count(a) = 0 then 0 else count(b)/count(a) end ) as ratio...原创 2020-07-22 12:56:04 · 6018 阅读 · 0 评论 -
sql count(a)/cout(b)得到的小数显示为0
count(a)为180,count(b)为2800,明显count(a)/cout(b)得到的是一个小于1的小数,然而count的结果默认为int那么小于1的小数都会显示为0解决办法之一:select cast(count(a) as double) / cast(count(b) as double) as ratio...原创 2020-07-22 12:53:26 · 820 阅读 · 0 评论 -
REGEXP_REPLACE SQL正则表达式
REGEXP_REPLACE语法: regexp_replace(string A, string B, string C)操作类型: strings返回值: string说明: 将字符串A中的符合java正则表达式B的部分替换为C。REGEXP_REPLACE6个参数第一个是输入的字符串第二个是正则表达式第三个是替换的字符第四个是标识从第几个字符开始正则表达式匹配。(默认为1)第五个是标识第几个匹配组。(默认为全部都替换掉)第六个是取值范围...原创 2020-07-20 18:07:46 · 2189 阅读 · 0 评论 -
Tableau日期函数汇总
Tableau 提供多种日期函数。许多日期函数还使用 date_part,它是一个常量字符串参数。您可以使用的有效 date_part 值为:date_part值’year’四位数年份’quarter’1-4’month’1-12 或 “January”、“February” 等’dayofyear’一年中的第几天;1 月 1 日为 1、2 月 1 日为 32,依此类推’day’1-31’weekday’1-7 或 “Sunday”、“Monday” 等’week’1-52’hour’0-23’minut原创 2020-07-19 18:33:45 · 3819 阅读 · 0 评论 -
牛客 使用含有关键字exists查找未分配具体部门的员工的所有信息
使用含有关键字exists查找未分配具体部门的员工的所有信息。CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_n原创 2020-06-25 20:19:28 · 141 阅读 · 0 评论 -
SQL and or 用法示例
比如我们查询出来的有两列数据,M列和Z列,有的行都有结果,有的行只有一个结果,有的行没有结果,如何统计我们想要的个数count(M),–M列有数据COUNT(Z),COUNT(M>0 AND Z>0),MZ都有数据COUNT(M>0 OR Z > 0) MZ至少有一列有数据...原创 2020-06-16 15:48:52 · 187 阅读 · 0 评论 -
SQL row_numbe用法详解 解决一对多
hive 去掉重复数据,显示第一条例如:name adx tran_id cost tsck 5 125.168.10.0 33.00 1407234660ck 5 187.18.99.00 33.32 1407234661ck 5 125.168.10.0 33.24 14原创 2020-06-16 15:35:09 · 400 阅读 · 0 评论 -
牛客 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
```sql--题目描述:--查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name,你可以不使用order by完成吗CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT.原创 2020-06-16 13:51:32 · 140 阅读 · 0 评论 -
SQL常见函数
SQL常见函数概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名好处:1、隐藏了实现细节 2、提高代码的重用性调用:select 函数名(实参列表) 【from 表】;特点:①叫什么(函数名)②干什么(函数功能)分类:1、单行函数如 concat、length、ifnull等2、分组函数功能:做统计使用,又称为统计函数、聚合函数、组函数常见函数:一、单行函数字符函数:length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)con原创 2020-06-10 17:21:39 · 162 阅读 · 0 评论 -
SQL常见函数汇总
1111111原创 2020-06-10 17:17:03 · 195 阅读 · 0 评论 -
SQL、presto将一对多出现重复的字段新添一列表示
SQL、presto将一对多出现重复的字段新添一列表示例如一个用户(id)在多个渠道都有授信金额(amount),我希望展示出来这个用户在所有渠道的授信金额。但 select id,amount from table 可能出现的结果是,一个用户的信息出现多条、多行,因为他在其他渠道可能有多条授信。select id,max(CASE WHEN product = ‘ABC’ then amount else null end) as ABC,max(CASE WHEN product = ‘XYZ’原创 2020-06-08 17:22:44 · 675 阅读 · 0 评论 -
hive不能用in或者 not in 子查询时怎么办
1111111111111111111111原创 2020-06-08 13:10:42 · 3306 阅读 · 0 评论 -
写SQL就必须【收藏】的时间函数汇总!超详细!
例如 a.apply_time 是2020-05-11 13:54:27b.su_time是2020-03-25 11:20:44怎么计算两个‘年月日时分秒’时间戳的时间间隔呢?SELECT to_unixtime(cast(a.apply_time as timestamp)) - to_unixtime(cast(b.su_time as timestamp)) as intervel即可获得两个时间戳的时间间隔,单位为秒,/60或者/(60*24)换算为天即可。...原创 2020-06-05 13:09:33 · 2043 阅读 · 0 评论 -
hive建表、Partition key设置、insert
drop table if exists ccs_table;create table if not exists ccs_table(id string comment '客户号‘,data_date string comment '数据日期’)partitioned by (data_dt string);插入内容到 ccs_tableset hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nons原创 2020-06-03 13:42:26 · 1052 阅读 · 0 评论 -
hive presto sql从身份证号提取年龄和性别
hive presto sql如何从身份证号提取年龄和性别select 2020-cast(substr(id,7,4)as int))*1.0 as age平均年龄avg(2020-cast(substr(id,7,4)as int))*1.0 as age) as avg_ageselect distinct case when cast(substr(id,17,1)as int) in (1,3,5,7,9) then male end男性比例:count (select distinc原创 2020-06-01 20:18:05 · 960 阅读 · 0 评论 -
leetcode数据库相关题目和答案部分汇总
176.第二高薪思路:小于最高薪的最高薪即为第二高薪select max(salary) as SecondHighestSalaryfrom employeewhere salary < (select max(salary) from employee)177.第N高薪思路:使用order by desc limit n,选出前n个高薪的,再min选择最小的一个即为第N高薪select if (count<N,null,min)from(select min(salary)原创 2020-05-31 17:40:08 · 310 阅读 · 0 评论 -
presto json_extract_scalar的用法和hive get_json_object的用法
select json_extract_scalar (properties,’$.type’)as type原创 2020-05-29 16:15:03 · 11413 阅读 · 2 评论 -
查ID字段内容中长度为11的比例
SELECT COUNT(DISTINCT CASE WHEN LENGTH(ID)=11 THEN ID END),COUNT(DISTINCT ID),COUNT (DISTINCT CASE WHEN LENGTH(ID)=11 THEN ID END)*1.000/COUNT(DISTINCT ID)FROM tableID字段既含有手机号,也含有乱码字符串查看某一字段长度为11,即字段内容为手机号的占比...原创 2020-05-29 11:17:11 · 627 阅读 · 0 评论 -
inner join或者left join后本来有数据的字段都没有数据了
比如我们用表a left join 表b, on a.id=b.id如果id为空的话,空对空,sql也会默认为能够匹配会自动跑出来数据。联系几个left的话,id字段可能就都为空了。所以在我们连接的时候,往往需要就一句。where id <> ’ ’and id <>’ ’and id is not null之类的。总之牢记一句,空对空也是能够识别出来的,连续inner join或者left join后本来有数据的字段就也为空了...原创 2020-05-29 09:50:18 · 4170 阅读 · 0 评论 -
inner join出现重复值
比如表A inner join Bon A.ID = B.ID假如B存在一个ID对应多条信息,那么即使我们在前面select用了distinct ID,这种连接下还是会出现重复的id1.首先B中含有ID的后续信息如果被我们select了,即使distinct ID也没有用。那么,需要思考和判断那些后续信息是否对我们的工作有用,无用可以直接删掉不要select,这样只留下distinct ID的数据,明显不会有重复ID2.如果一定要去除指定的重复ID并留下想要的后续信息,可以使用row_number原创 2020-05-28 20:11:52 · 10402 阅读 · 0 评论 -
presto hive sql 常用必会语法汇总
时间转时间戳: cast(to_unixtime(timestamp ‘2016-09-01 01:00:00’) as bigint)int 转 varchar :cast(1 as varchar)varchar 转int:cast(‘1’ as bigint)map数据查询:element_at(url_parsed,‘do’)数据包含 contains(x, element)时间戳转格式化时间:select format_datetime(from_unixtime(cast(view原创 2020-05-27 11:12:21 · 1559 阅读 · 0 评论 -
cannot be applied to timestamp,varchar
select count (distinct if(a.t_time>a.s_time and a.t_time <date_add(to_date(a.s_time),7),a.id,null))as a_user这算代码是同事帮忙写的,在presto上并不能查询'>'cannot be applied to timestamp,varchar显然,时间戳是不能比较大小的做出如下修改:select count(distinct if(cast(a.t_time as var原创 2020-05-27 09:32:57 · 8782 阅读 · 0 评论 -
presto、sql用not exists选择表A中的所有id但剔除掉表B中的id
SELECT student_id,numFROMtable_A AS a----别名,注意哦 这里没有括号WHEREchannel = 3AND NOT EXISTS -----别只会做加法,presto、SQL做减法查询,凡是另一个表出现的都不要都剔除( SELECT 1 FROM table_B AS b -----别名,as可以省略WHEREa.student_id = b.student_id)order by student_id desc...原创 2020-05-22 14:54:51 · 2030 阅读 · 0 评论 -
presto、hive、sql工作中犯的一些错误总结
1.日期错误etl_dt = '20200520’写成了‘202005200’,多一个1得出错误结果,不细心。2.用错表格,虽然都有student_id这个标签,但表a left join 表b后,表b的很多字段都是空的。说明两个表格没有交集,一定要用对表格,熟悉业务逻辑和流程。3.刚刚开始工作的时候,没有筛选distinct 的id,导致一个id对应多条信息。4.inner join,left join 不熟悉未完待补充...原创 2020-05-22 13:46:57 · 406 阅读 · 0 评论 -
使用count(1)确认presto、SQL、HIVE查询没有重复数据
例如我们锚定student_idselect count(1),count(distinct student_id) ----注意distinct 的重要使用fromabc.table_student;不一致那么就有重复值,会出现一对多,一个id多条信息或者:select student_id,count(1) from abc.table_studentgroup by student_idhaving count(1)>=2;-----注意having的用法-----为什么用原创 2020-05-20 11:15:17 · 1720 阅读 · 0 评论