hivesqlsubstr

1. hive中count和sum的区别

首先，sum是对一个字段进行求和，hive中字段的类型一般是string或者int，如果是int当然没问题，如果是string类型但是全部是数字也没问题，如果包含一个字母sum出来将会是0.
假如有这样一张简单的表
user_id stringshop_id string
1234hello
12341234
那
么sum(user_id)的结果是2468，sum(shop_id)的结果是0.如果没有符合条件的记录，sum的返回值将是null，如
sum(case when user_id<1000 then shop_id
end)返回值将是null。但某些时候我没希望sum的结果如果没有符合条件的记录就返回0怎么办呢，可以用coalesce(shop_id,0)解
决这个问题。
count是对数据记录的条数进行统计，有一条符合的记录就是1，没有就是0.

前几天遇到这样一条sql语句，sum(case when
substr(gmt_receive_pay,0,13)='$cur_date $env.last_hour' then t2.total_fee end)
as hour_alipay_fee,
我知道这条语句当没有符合条件的记录时计算出来结果是null，但是我没有意识到这是一个bug，我们要的结果应该是0。检讨一下。

2. sql substring()从右边开始截取字符串

1、创建测试表，create table test_substr(value varchar2(200));

3. sql 截取某个字符之前的数据

CREATE PROCEDURE pim_Utility_ReadCRMSaleLead
AS

select A.ProjectName as '项目名称', A.SaleDistrict as '项目所在地',A.Address as '客户地址', SUBSTRING（1，CHARINDEX（‘+’，A.RelateLead1）- 1,LEN(A.RelateLead1)) as '设计院',
A.BudgetNumber as '预计容量',A.Finish_On as '订货时间' ,
A.WorkStatus as '状态', B.FolderId
INTO #Temp0 from pimCRMSaleLead A ,pimCRMLead B
where A.CustomerId *= B.LeadID

update #Temp0 set #Temp0.项目所在地=C.Name FROM pimDataClassFolder C WHERE #Temp0.FolderId=C.FolderId

SELECT * FROM #Temp0
GO

4. sql中如何在where字句里截取某个字段的前几位字符

sql中在where字句里截取字符方法如下：

1、如果是sqlserver：where left（p.end＿time，4） ＝ ＇2012＇。

2、如果是Oracle：where substr（p.end＿time，0，4） ＝ ＇2012＇。

举例：

1、oracle： ＇where substr（字段名，1，2）＝＇＇＇123＇＇＇＇

2、sqlserver： ＇where substring（字段名，1，2）＝＇＇＇123＇＇＇＇

(4)hivesqlsubstr扩展阅读：

sql中，常用函数介绍：

1、AVG()：返回平均值

2、COUNT()：返回行数

3、FIRST()：返回第一个记录的值

4、LAST()：返回最后一个记录的值

5、MAX()：返回最大值

6、MIN()：返回最小值

7、SUM()：返回总和

8、UCASE()：将某个字段转换为大写

9、LCASE()：将某个字段转换为小写

10、MID()：从某个文本字段提取字符

11、LEN()：返回某个文本字段的长度

12、ROUND()：对某个数值字段进行指定小数位数的四舍五入

13、NOW()：返回当前的系统日期和时间

14、FORMAT()：格式化某个字段的显示方式

15、INSTR()：返回在某个文本域中指定字符的数值位置

16、LEFT()：返回某个被请求的文本域的左侧部分

17、RIGHT()：返回某个被请求的文本域的右侧部分

5. oracleSQL语句转换为hive识别的SQL语句

把like的部分改为
like concat(p_org_no,'%')

6. 在SQL语句中如何用正则取出一个字符串的前几位数字

1. SQL 取字符串的前几位数字，SQL 关键字 substring

2. substring 使用方法，参考下列SQL：

3. declare @T nvarchar(10)

4. set @T='12345abcde'

5. select substring(@T,1,5)

6. 结果如下：12345

7. 如果是SQL 写正则表达式判断，只能通过存储过程或函数来处理

8. SQL 如下：

9. CREATE FUNCTION dbo.find_regular_expression
   (
   @source varchar(5000), --需要匹配的源字符串
   @regexp varchar(1000),--正则表达式
   @ignorecase bit = 0--是否区分大小写，默认为false
   )
   RETURNS bit--返回结果0-false，1-true
   AS
   BEGIN
   --0（成功）或非零数字（失败），是由 OLE 自动化对象返回的 HRESULT 的整数值。
   DECLARE @hr integer
   --用于保存返回的对象令牌，以便之后对该对象进行操作
   DECLARE @objRegExp integer DECLARE @objMatches integer
   --保存结果
   DECLARE @results bit
   
   /*
   创建 OLE 对象实例,只有 sysadmin 固定服务器角色的成员才能执行 sp_OACreate,

   并确定机器中有VBScript.RegExp类库
   */
   EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
   IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
   END
   /*
   以下三个分别是设置新建对象的三个属性。下面是'VBScript.RegExp'中常用的属性举例：
   Dim regEx,Match,Matches '建立变量。
   Set regEx = New RegExp '建立一般表达式。
   regEx.Pattern= patrn '设置模式。
   regEx.IgnoreCase = True '设置是否区分大小写。
   regEx.Global=True '设置全局可用性。
   set Matches=regEx.Execute(string) '重复匹配集合
   RegExpTest = regEx.Execute(strng) '执行搜索。
   for each match in matches '重复匹配集合
   RetStr=RetStr &"Match found at position "
   RetStr=RetStr&Match.FirstIndex&".Match Value is '"
   RetStr=RetStr&Match.Value&"'."&vbCRLF Next
   RegExpTest=RetStr
   */
   EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
   IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
   END
   EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
   IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
   END
   EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
   IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
   END
   --调用对象方法
   EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
   IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
   END
   --释放已创建的 OLE 对象
   EXEC @hr = sp_OADestroy @objRegExp
   IF @hr <> 0 BEGIN
   SET @results = 0
   RETURN @results
   END
   RETURN @results
   END

7. hivesql怎么获取上一个月月份

hivesql sql — 获取指定hive表或指定文件所hive表DDL按区则默认执行近7区DDL同table支持符合sql语则表达式表匹配则提示用户选择(使用file则自关闭该交互功能)。

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语句转换为MapRece任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapRece统计，不必开发专门的MapRece应用，十分适合数据仓库的统计分析。另外一个是Windows注册表文件。

8. hive中如何求两个时间点之间相差月份数，我只知道datediff函数可以求天数

可以用datediff函数。
创建表及插入数据：

create table test
(begindate datetime,
enddate datetime);

insert into test values ('2015-01-01','2015-07-13')
执行：

select datediff(day,begindate,enddate) from test;

结果：