‘壹’ sql中DISTINCT的位置
您好誉乱，很高兴为您解答！
1.
distinct
一般是放在select之后；
2.
如果是庆裤档所有查询出来的都要distinct，直接在select后加distinct
关键词就纯毕可以；
3.
如果是单列，可以用distinct(col)函数
希望我的回答对您有用！
‘贰’ sql中的identitycol和rowguidcol是什么意思呢
SELECT子句:
identitycol：返回标识列。有关更多信息，可参见IDENTITY（属性）、ALTER TABLE和CREATE TABLE。如果FROM子句中的多个表内有包含IDENTITY属性的列，则必须用特定的表名（如 T1.identitycol）限定 identitycol。
创建表:
rowguidcol：指定列为全球惟一鉴别行号列（rowguidcol是Row Global UniqueIdentifier Column的缩写）。此列的数据类型必须为UNIQUEIDENTIFIER类型。一个表中数据类型为UNIQUEIDENTIFIER的列中只能有一个列被定义为rowguidcol列。rowguidcol属性不会使列值具有惟一性，也不会自动生成一个新的数据值给插入行。需要在INSERT语句中使用NEWID（）函数或指定列的默认值为NEWID（）函数。
修改表:
{ADD|DROP}rowguidcol}：添加或删除列的rowguidcol属性。rowguidcol属性只能指定给一个UNIQUEIDENTIFIER列。
SELECT子句:
rowguidcol：返回行全局惟一标识列。如果在FROM子句中有多个表具有rowguidcol属性，则必须用特定的表名（如T1.rowguidcol）限定 rowguidcol。
‘叁’ 100分求SQL server 中系统函数精细讲解
系统函数用于获取有关计算机系统用户数据库和数据库对象的信息系统函数可
以让用户在得到信息后使用条件语句根据返回的信息进行不同的操作与其它函数一
样可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数
1APP_NAME
APP_NAME函数语法如下
APP_NAME
APP_NAME函数返回当前执行的应用程序的名称其返回值类型为nvarchar128
例4-77测试当前应用程序是否为SQL Server Query Analyzer
declare @currentApp varchar50
set @currentApp = app_name
if @currentApp 'SQL Query Analyzer'
print 'This process was not started by a SQL Server Query Analyzer query session.'
运行结果如下
-------------------- ------- -----------
The commands completed successfully.
/* 表明当前应用程序是SQL Server Query Analyzer*/
2COALESCE
COALESCE函数语法如下
COALESCE [...n]
COALESCE函数返回众多表达式中第一个非NULL表达式的值如果所有的表
达式均为NULL则COALESCE函数返回NULL值COALESCE函数等效于下
面的CASE语句
CASE
WHEN expression1 IS NOT NULL THEN expression1
…
WHEN expressionN IS NOT NULL THEN expression
ELSE NULL
例4-78
declare @x char @y char10 @d datetime
set @y = 'first'
set @d = getdate
select COALESCE@x @y convertchar20 @d
运行结果如下
-------------------
first
3COL_LENGTH
COL_LENGTH函数语法如下
COL_LENGTH
COL_LENGTH函数返回表中指定字段的长度值其返回值为INT类型
例4-79
use pangu
select col_length'employee' 'e_name' as employee_name_length
运行结果如下
employee_name_length
--------------------
20
4COL_NAME
COL_NAME函数语法如下
COL_NAME
COL_NAME函数返回表中指定字段的名称即列名其返回值为SYSNAME类
型其中table_id和column_id都是INT类型的数据函数用table_id和column_id参数
来生成列名字符串关于如何获得table_id和column_id请参见OBJECT_ID函数
例4-80
use pangu
select col_nameobject_id'employee' ordinal_position
from information_schema.columns
where table_name = 'employee'
运行结果如下
----------------------
emp_id
e_name
birthday
job_level
dept_id
hire_date
e_wage
7 rows affected
5DATALENGTH
DATALENGTH函数语法如下
DATALENGTH
DATALENGTH函数返回数据表达式的数据的实际长度其返回值类型为INT
DATALENGTH函数对
和NTEX等能存储变动长度数据的数据类型特别实用NULL的长度为NULL
例4-81
use pangu
select length = datalengthe_name e_name
from employee
order by length
运行结果如下
length e_name
----------- --------------------
… …
4 张三
4 李四
… …
6 梁山伯
6 祝英台
… …
6DB_ID
DB_ID函数语法如下
DB_ID['database_name']
DB_ID函数返回数据库的编号其返回值为SMALLINT类型如果没有指定
database_name则返回当前数据库的编号
例4-82
use master
select name db_idname as db_id
from sysdatabases
order by dbid
运行结果如下
name database_id
------------------------------------------------------------------------------------
master 1
tempdb 2
model 3
msdb 4
pubs 5
Northwind 6
PANGU 7
7 rows affected
7DB_NAME
DB_NAME函数语法如下
DB_NAMEdatabase_id
DB_NAME函数返回数据库的名称其返回值类型为NVARCHAR128database_id
是SMALLINT类型的数据如果没有指定database_id则返回当前数据库的名称
例4-83
use master
select dbid db_namedbid as db_name
from sysdatabases
order by dbid
运行结果如下
dbid dbname
--------------------------------------
1 master
2 tempdb
3 model
4 msdb
5 pubs
6 Northwind
7 PANGU
7 rows affected
8FORMATMESSAGE
FORMATMESSAGE函数语法如下
FORMATMESSAGE [...n]
FORMATMESSAGE函数用于从系统信息表sysmessages中挑选一条现存的信息
再将其格式转换为用户需要的形式其功能类似于RAISERROR命令不同的是RAISER-
ROR命令立即打印出信息而FORMATMESSAGE函数返回编辑过的信息给调用它
的程序其返回值类型为NVARCHAR
其中message_number是存储在sysmessages系统表中的信息ID号param_value是在
信息中使用的参数最多可使用20个参数
FORMATMESSAGE函数查找使用当前语言平台语言的信息如果没有本地语言
的信息则使用U.S. English版本语言的信息
例4-84假定有一个编号为10001的信息存储在sysmessages表中此信息的内容为
The Length of rows %column_name in %table_name is %column_num.
FORMATMESSAGE函数用值myColumnmyTable和20分别替代上述参数值将结
果存储在变量@test1中程序如下
declare @test1 varchar100
select @test1 = formatmessage10001 'myColumn' 'myTable' 20
9GETANSINULL
GETANSINULL函数语法如下
GETANSINULL['database_name']
GETANSINULL函数返回当前数据库默认的NULL值其返回值类型为INT
GETANSINULL函数对ANSI空值NULL返回1如果没有定义ANSI空值则返回
0
例4-85
select getansinull'pangu'
运行结果如下
------
1
10HOST_ID
HOST_ID函数语法如下
HOST_ID
HOST_ID函数返回服务器端计算机的名称其返回值类型为NCHAR
例4-86
declare @hostID char8
select @hostID = host_id
print @hostID
运行结果如下
-606771
11HOST_NAME
HOST_NAME函数语法如下
HOST_NAME
HOST_NAME函数返回服务器端计算机的名称其返回值类型为CHAR8
例4-87
declare @hostNAME nchar20
select @hostNAME = host_name
print @hostNAME
运行结果如下
XUJIN
12IDENTITY
IDENTITY函数语法如下
IDENTITY[ seed increment] [AS column_name]
IDENTITY函数只在SELECTINTO语句中使用用于插入一个identity column
列到新表中其返回值类型与data_type的类型相同
其中
data_type指定数据类型data type是INTEGER 或DECIMAL类的数据类型Seed
指定identity column的初值即第一个值Increment指定增加一个记录时记录的identity
column值应在前记录的基础上增加的值seed和increment的缺省值为1
column_name是所插入的identity column的列名虽然AS column_name为可选项
但由于函数在表中插入了一个新列所以必须指定列名如果不使用AS column_name选
项可以使用以下两种方式来指定列名
1 select identityint 11 as column_name
into newtable
from oldtable
2 select column_name = identityint 1 1
into newtable
from oldtable
有关identity column的信息请参见管理数据库表中的列属性章节
13IDENT_INCR
IDENT_INCR函数语法如下
IDENT_INCR'table_or_view'
IDENT_INCR函数返回表中标识性字段identity column的增量若无此字段则
返回NULL值其返回值类型为NUMERIC
例4-88
use pangu
select table_name ident_incrtable_name as ident_incr
from information_schema.tables
where ident_incrtable_name is not null
select ident_incr'employee'
运行结果如下
table_name ident_incr
-----------------------------------------------------------
dtproperties 1
----------------------------------------
NULL
14IDENT_SEED
IDENT_SEED函数语法如下
IDENT_SEED
IDENT_SEED函数返回表中标识性字段identity column的初值若无此字段则
返回NULL值其返回值类型为NUMERIC
例4-89
use pangu
select table_name ident_seedtable_name as ident_seed
from information_schema.tables
where ident_seedtable_name is not null
select ident_seed'employee'
运行结果如下
table_name ident_seed
-------------------------------------------------------
dtproperties 1
----------------------------------------
NULL
15INDEX_COL
INDEX_COL函数语法如下
INDEX_COL
INDEX_COL函数返回表内索引识别码为index_id的索引的名称并找出组成该
索引的列组合中第key_id个列名其返回值类型为NCHAR
例4-90
use pangu
declare @table_id int @table_name char10
set @table_name = 'employee'
select @table_id = id --从系统对象表中选取employee表的编号
from sysobjects
where name = @table_name
print 'Index information for the ' + @table_name + ' table'
select index_col@table_name indid 1
from sysindexes
where indid in
select indid --从系统索引表中选取索引编号
from sysindexes
where id = @table_id
运行结果如下
Index information for the employee table
--------------------------
emp_id
e_wage
birthday
dept_id
hire_date
job_level
e_name
16ISDATE
ISDATE函数语法如下
ISDATE
ISDATE函数判断所给定的表达式是否为合理日期如果是则返回1不是则返
回0
例4-91
declare @x char12
set @x = '1/1/2000'
select isdate@x isdate'1999-10-1 12:00am' isdate'Jan 23 2000 21:00pm'
isdate'20001226' isdate'2000101' isdate'16/23/99'
运行结果如下
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 0 0
17ISNULL
ISNULL函数语法如下
ISNULL
ISNULL函数将表达式中的NULL值用指定值替换如果check_expresssion不是
NULL则返回其原来的值否则返回replacement_value的值
! replacement_value的数据类型应与check_expresssion一致
例4-92
use pangu
select avgisnulle_wage $1000.00
from employee
运行结果如下
---------------------
3973.3333
18ISNUMERIC
ISNUMERIC函数语法如下
ISNUMERIC
ISNUMERIC函数判断所给定的表达式是否为合理的数值INTEGERFLOATING
POINT NUMBERMONEY或DECIMAL类型如果是则返回1不是则返回0
! 检查MONEY型数据时应先用CONVERT函数将其转换为数值型或字符型
例4-93
declare @pay money
set @pay = $100
select isnumericconvertint10 @pay
use pangu
select isnumericbirthday isnumericconvertchar10 e_wage
from employee
运行结果如下
-----------
1
1 rows affected
----------- -----------
0 1
0 1
19NEWID
NEWID函数语法如下
NEWID
NEWID函数返回一个UNIQUEIDENTIFIER类型的数值此函数使用计算机的
网卡的Ethernet地址加上经由CPU Clock产生的数字而得到其返回值由于网卡的Ethernet
地址是全球惟一的在未来近一百年内不会生产出相同Ethernet地址的网卡因而函数
所产生的数字也是惟一的
例4-94
declare @xyz uniqueidentifier
set @xyz = newid
print 'Value of @xyz is: '+ convertvarchar255 @xyz
运行结果如下
Value of @xyz is: 8953F4C7-5D4E-11D4-8F18-E9780FD95B7A
20NULLIF
NULLIF函数语法如下
NULLIF
NULLIF函数在expression1与expression2相等时返回NULL值若不相等时则
返回expression1的值其返回值类型与expression1相同expression1与expression2应为
同类的数据类型
例4-95
declare @x varchar20 @y char20 @z char
select @x = 'SQL' @y = 'SQL' @z = 1
select nullif@x @y nullif@x @z
运行结果如下
-------------------- --------------------
NULL SQL
21OBJECT_ID
OBJECT_ID函数语法如下
OBJECT_ID
OBJECT_ID函数返回数据库对象的编号其返回值类型为INT
例4-96
use pangu
select object_id'department'
运行结果如下
-----------
485576768
! 当指定一个临时表的表名时其表名的前面必须加上临时数据库名tempdb如select
object_id'tempdb..#mytemptable'
22OBJECT_NAME
OBJECT_NAME函数语法如下
OBJECT_NAME
OBJECT_NAME函数返回数据库对象的名称其返回值类型为NCHAR
例4-97
use pangu
select table_catalog table_name
from information_schema.tables
where table_name = object_name485576768
运行结果如下
table_catalog table_name
------------------------------------------------------------------
PANGU department
23PARSENAME
PARSENAME函数语法如下
PARSENAME
PARSENAME函数返回一个数据库对象完整名称中的特定部分对象名称或数据
库拥有者名称或数据库名称或服务器名称其返回值类型为NCHAR
其中object_piece为INT类型其取值如下
对象名称Object name
数据库拥有者名称Owner name
数据库名称Database name
服务器名称Server name
如果object_name或object_piece为NULL则返回NULL值
例4-98
use pangu
select parsename'employee' 1 as 'object name'
parsename' employee ' 2 as 'owner name'
parsename' employee ' 3 as 'database name'
parsename' employee ' 4 as 'server name'
运行结果如下
object name owner name database name server name
------------------------------------------------------------------------------------------------
employee NULL PANGU NULL
24PERMISSIONS
PERMISSIONS函数语法如下
PERMISSIONS[object_id [ 'column_name']]
PERMISSIONS函数用于获取当前用户对某一对象的存取权限或对某一命令的执
行权限其返回值为一个32位的bitmap值其中低16位表示目前用户对象的存取权限
设定值高16位表示用户是否可以开放此对象的权限给其它人
如果不指定object_id则函数返回目前用户的命令执行权限的bitmap值使用
column_name选项可以得到表的列权限各权限值见表4-10至4-12
表4-10 命令执行权限的bitmap值
Bit decBit hex命 令 权 限
1 0x1CREATE DATABASE master database only
2 0x2 CREATE TABLE
4 0x4 CREATE PROCEDURE
8 0x8 CREATE VIEW
16 0x10 CREATE RULE
32 0x20 CREATE DEFAULT
64 0x40 BACKUP DATABASE
128 0x80 BACKUP LOG
256 0x100 Reserved
表4-11 对象存取权限的bitmap值
Bit decBit hex对 象 权 限
1 0x1 SELECT ALL
2 0x2 UPDATE ALL
4 0x4 REFERENCES ALL
8 0x8 INSERT
16 0x10 DELETE
32 0x20EXECUTE proceres only
4096 0x1000SELECT ANY at least one column
8192 0x2000 UPDATE ANY
16384 0x4000 REFERENCES ANY
表4-12 列对象存取权限的bitmap值
Bit decBit hex列 权 限
1 0x1 SELECT
2 0x2 UPDATE
4 0x4 REFERENCES
有关PERMISSIONS函数使用方法我们将在以后的章节分别讲述
25STATS_DATE
STATS_DATE函数语法如下
STATS_DATE
STATS_DATE函数返回最新的索引统计日期其返回值类型为DATETIME
例4-99
use pangu
select 'index name' = i.name 'statistics date' = stats_datei.id i.indid
from sysobjects o sysindexes i
where o.name = 'employee' and o.id = i.id
运行结果如下
index name statistics date
------------------------------------------------------------------------------------------
employee NULL
PK_employee NULL
_WA_Sys_e_wage_1B0907CE 2000-07-15 21:41:20.157
_WA_Sys_birthday_1B0907CE 2000-07-16 11:29:59.990
_WA_Sys_dept_id_1B0907CE 2000-07-16 21:19:40.203
_WA_Sys_hire_date_1B0907CE 2000-07-16 21:41:38.470
_WA_Sys_job_level_1B0907CE 2000-07-16 22:05:43.003
_WA_Sys_e_name_1B0907CE 2000-07-16 22:08:17.153
e_name 2000-07-19 09:25:06.890
e_birthday 2000-07-19 09:25:32.180
e_hire_date 2000-07-19 09:25:54.697
11 rows affected
26SUSER_SID
SUSER_ SID函数语法如下
SUSER_SID['login_name']
SUSER_SID函数根据用户登录名返回用户的SIDSecurity Identification Number
安全帐户名号其返回值类型为INT如果不指定login_name则返回当前用户的SID
号
例4-100
use pangu
select suser_sid'sa'
运行结果如下
-----------------------------------
0x01
27SUSER_SNAME
SUSER_SNAME函数语法如下
SUSER_SNAME[server_user_sid]
SUSER_SNAME函数根据SID号返回用户的登录名如果没有指定server_user_sid
则返回当前用户的登录名其返回值类型为NCHAR
例4-101
select suser_sname0x01
运行结果如下
-----------------------------------
sa
28USER_ID
USER_ID函数语法如下
USER_ID['user_name']
USER_ID函数根据用户数据库的用户名返回用户的数据库ID号其返回值类型
为INT如果没有指定user_name则返回当前用户的数据库ID号
例4-102
use pangu
select user_id'dbo'
运行结果如下
------
1
29USER_NAME
USER_NAME函数语法如下
USER_NAME[user_id]
USER_NAME函数根据用户的数据库ID号返回用户的数据库用户名其返回值
类型为NCHAR如果没有指定user_id则返回当前数据库的用户名
例4-103
use pangu
select user_name
运行结果如下
------
dbo
‘肆’ pl/sql中可以用col 命令吗
a10 代表 字符， 长度10
例如：
SQL> col title format a10
SQL> SELECT '饥明test1234567890'烂橘告 AS title FROM al;
TITLE
----------
test123456
7890
SQL> SELECT 1234567890 AS title FROM al;
TITLE
----------
##########
因为 a 只用来处理字符的。
如果你需要 对 数字作额外的格式化处理。
那么 不要用 a
例如：
SQL>伍孙 col title format 9,999,999,999
SQL> SELECT 12345 AS title FROM al;
TITLE
--------------
12,345
SQL> SELECT 1234567890 AS title FROM al;
TITLE
--------------
1,234,567,890
‘伍’ 第十二章 SQL聚合函数 VARIANCE, VAR_SAMP, VAR_POP
返回数据集统计方差的聚合函数。
这些函数返回 NUMERIC 数据类型，除非表达式是数据类型 DOUBLE 。
如果 expression 为 DOUBLE ，则返回 DOUBLE 。
这三个方差聚合函数在丢弃 NULL 值后返回表达式值的统计方差。
也就是说，从数据集的平均值变化的量，表示为一个正数。
返回值越大，值的数据集的变化就越大。
SQL还提供聚合函数来返回对应于每个方差函数的标准偏差。
在这种统计差异的推导过程中有一些细微的差异:
方差计算为:
VAR_POP 的计算是:
这些方差聚合函数可以在引用表或视图的 SELECT 查询或子查询中使用。
它们可以在 SELECT 列表或 HAVING 子句中与普通字段值一起出现。
这些方差聚合函数不能在WHERE子句中使用。
它们不能在 JOIN 的 ON 子句中使用，除非 SELECT 是子查询。
这些方差聚合函数返回数据类型NUMERIC的值，精度为 36 ，刻度为 17 ，除非表达式是数据类型 DOUBLE ，在这种情况下，函数返回数据类型 DOUBLE 。
这些方差聚合函数通常应用于具有数值的字段或表达式。
它们将非数值值(包括空字符串( " ))计算为零( 0 )。
这些方差聚合函数忽略数据字段中的 NULL 值。
如果查询没有返回行，或者返回的所有行的数据字段值为 NULL ，则返回 NULL 。
与所有聚合函数一样，统计方差函数可以采用一个可选的 DISTINCT 子句。
方差( DISTINCT col1 )返回那些不同(唯一)的 col1 字段值的方差。
方差( DISTINCT BY(col2) col1 )返回记录中col1字段值的方差，其中col2值是不同的(唯一的)。
但是请注意，不同的col2值可能包含一个单独的NULL值。
与所有聚合函数一样，方差函数总是返回数据的当前状态，包括未提交的更改，而不管当前事务的隔离级别如何。
下面的示例使用方差返回 sample.employee 中雇员年龄的方差，以及由一个或多个雇员表示的不同年龄的方差:
下面的示例使用 VAR_POP 返回 sample.employee 中雇员年龄的总体差异，以及由一个或多个雇员表示的不同年龄的差异:
‘陆’ sql中有没有类似于oracle中rownum的函数
Oracle
row_number()函数用法
row_number()over(partition
by
col1
order
by
col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。
与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。
row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。
oracle
分析函数
row_number(),返回一个整数值(>=1);
语法格式:
row_number()
over
(order
by
col_1[,col_2
...])
作用:按照col_1[,col_2
...]排序,返回排序后的结果集。
‘柒’ Hive sql及窗口函数
hive函数：
1、根据指定条件返回结果：case when then else end as
2、基本类型转换：CAST（）
3、nvl：处理空字段：三个str时，是否为空可以指定返回不同的值
4、sql通配符： https://www.w3school.com.cn/sql/sql_wildcards.asp
5、count(1)与COUNT(*)：返回行数
如果表没有主键，那么count（1）比count（*）快；
如果有主键，那么count（主键，联合主键）比count（*）快；
count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。
性能问题：
1.任何情况下SELECT COUNT(*) FROM tablename是最优选择,(指没有where的情况）；
2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询；
3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
count(expression)：查询 is_reply=0 的数量： SELECT COUNT(IF(is_reply=0,1,NULL)) count FROM t_iov_help_feedback;
6、distinct与group by
distinct去重所有distinct之后所有的字段，如果有一个字段值不一致就不作为一条
group by是根据某一字段分组，然后查询出该条数据的所需字段，可以搭配 where max(time)或者Row_Number函数使用，求出最大的一条数据
7、使用with 临时表名 as() 的形式,简单的临时表直接嵌套进sql中，复杂的和需要复用的表写到临时表中，关联的时候先找到关联字段，过滤条件最好在临时表中先过滤后关联
处理json的函数：
split(json_array_string(schools), '\\|\\|') AS schools
get_json_object(school, '$.id') AS school_id,
字符串函数：
1、instr（’源字符串’ , ‘目标字符串’ ,’开始位置’,’第几次出现’）
instr(sourceString,destString,start,appearPosition)
1.sourceString代表源字符串； destString代表要从源字符串中查找的子串；
2.start代表查找的开始位置，这个参数可选的，默认为1；
3.appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1
4.如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。
5.返回值为：查找到的字符串的位置。如果没有查找到，返回0。
最简单例子： 在abcd中查找a的位置，从第一个字母开始查，查找第一次出现时的位置
select instr(‘abcd’,’a’,1,1) from al; —1
应用于模糊查询：instr(字段名/列名, ‘查找字段’)
select code,name,dept,occupation from staff where instr(code, ‘001’)> 0;
等同于 select code, name, dept, occupation from staff where code like ‘%001%’ ;
应用于判断包含关系：
select ccn,mas_loc from mas_loc where instr(‘FH,FHH,FHM’,ccn)>0;
等同于 select ccn,mas_loc from mas_loc where ccn in (‘FH’,’FHH’,’FHM’);
2、substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样
substr(time,1,8) 表示将time从第1位开始截取，截取的长度为8位
第一种用法：
substr（string A，int start）和 substring（string A，int start），用法一样
功效：返回字符串A从下标start位置到结尾的字符串
第二种用法：
substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样
功效：返回字符串A从下标start位置开始，长度为len的字符串
3、get_json_object(form_data,'$.学生姓名') as student_name
json_tuple 函数的作用：用来解析json字符串中的多个字段
4、split(full_name, '\\.') [5] AS zq; 取的是数组里的第六个
日期(时间)函数：
1、to_date(event_time) 返回日期部分
2、date_sub：返回当前日期的相对时间
当前日期：select curdate()
当前日期前一天：select date_sub(curdate(),interval 1 day)
当前日期后一天：select date_sub(curdate(),interval -1 day)
date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 14) 将现在的时间总秒数转为标准格式时间，返回14天之前的时间
时间戳>>>>日期：
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') 将现在的时间总秒数转为标准格式时间
from_unixtime(get_json_object(get_json_object(form_data,'$.挽单时间'),'$.$date')/1000) as retain_time
unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss') --1565858400
日期>>>>时间戳：unix_timestamp()
date_format：yyyy-MM-dd HH:mm:ss 时间转格式化时间
select date_format('2019-10-07 13:24:20', 'yyyyMMdd000000')-- 20191007000000select date_format('2019-10-07', 'yyyyMMdd000000')-- 20191007000000
1.日期比较函数: datediff语法: datediff(string enddate,string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
举例： hive> select datediff('2016-12-30','2016-12-29'); 1
2.日期增加函数: date_add语法: date_add(string startdate, intdays)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
举例： hive>select date_add('2016-12-29',10); 2017-01-08
3.日期减少函数: date_sub语法: date_sub (string startdate,int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
举例： hive>select date_sub('2016-12-29',10); 2016-12-19
4.查询近30天的数据
select * from table where datediff(current_timestamp,create_time)<=30；
create_time 为table里的字段，current_timestamp 返回当前时间 2018-06-01 11:00:00
3、trunc()函数的用法：当前日期的各种第一天,或者对数字进行不四舍五入的截取
日期：
1.select trunc(sysdate) from al --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from al --2011-3-1 返回当月第一天.
上月1号 trunc(add_months(current_date(),-1),'MM')
3.select trunc(sysdate,'yy') from al --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from al --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from al --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from al --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from al --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from al --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
数字：TRUNC（number,num_digits） Number 需要截尾取整的数字。Num_digits 的默认值为 0。TRUNC()函数截取时不进行四舍五入
11.select trunc(123.458,1) from al --123.4
12.select trunc(123.458,-1) from al --120
4、round()：四舍五入：
select round(1.455, 2) #结果是：1.46，即四舍五入到十分位，也就是保留两位小数
select round(1.5) #默认四舍五入到个位，结果是：2
select round(255, -1) #结果是：260，即四舍五入到十位，此时个位是5会进位
floor()：地板数
ceil()天花板数
5、
6.日期转年函数: year语法: year(string date)
返回值: int
说明: 返回日期中的年。
举例：
hive> select year('2011-12-08 10:03:01') from al;
2011
hive> select year('2012-12-08') fromal;
2012
7.日期转月函数: month语法: month (string date)
返回值: int
说明: 返回日期中的月份。
举例：
hive> select month('2011-12-08 10:03:01') from al;
12
hive> select month('2011-08-08') fromal;
8
8.日期转天函数: day语法: day (string date)
返回值: int
说明: 返回日期中的天。
举例：
hive> select day('2011-12-08 10:03:01') from al;
8
hive> select day('2011-12-24') fromal;
24
9.日期转小时函数: hour语法: hour (string date)
返回值: int
说明: 返回日期中的小时。
举例：
hive> select hour('2011-12-08 10:03:01') from al;
10
10.日期转分钟函数: minute语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。
举例：
hive> select minute('2011-12-08 10:03:01') from al;
3
11.日期转秒函数: second语法: second (string date)
返回值: int
说明: 返回日期中的秒。
举例：
hive> select second('2011-12-08 10:03:01') from al;
1
12.日期转周函数: weekofyear语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。
举例：
hive> select weekofyear('2011-12-08 10:03:01') from al;
49
查看hive表在hdfs中的位置：show create table 表名;
在hive中hive2hive，hive2hdfs：
HDFS、本地、hive -----> Hive：使用 insert into | overwrite、loaddata local inpath "" into table student;
Hive ----> Hdfs、本地：使用：insert overwrite | local
网站访问量统计：
uv：每用户访问次数
ip：每ip(可能很多人)访问次数
PV:是指页面的浏览次数
VV:是指你访问网站的次数
sql：
基本函数：
count、max、min、sum、avg、like、rlike（'2%'、'_2%'、%2%'、'[2]'）（java正则）
and、or、not、in
where、group by、having、{ join on 、full join} 、order by（desc降序）
sort by需要与distribut by集合结合使用：
hive (default)> set maprece.job.reces=3; //先设置rece的数量
insert overwrite local directory '/opt/mole/datas/distribute-by'
row format delimited fields terminated by '\t'
先按照部门编号分区，再按照员工编号降序排序。
select * from emp distribute by deptno sort by empno desc;
外部表 create external table if not exists dept
分区表：create table dept_partition ( deptno int, dname string, loc string ) partitioned by ( month string )
load data local inpath '/opt/mole/datas/dept.txt' into table default.dept_partition partition(month='201809');
alter table dept_partition add/drop partition(month='201805') ,partition(month='201804');
多分区联合查询：union
select * from dept_partition2 where month='201809' and day='10';
show partitions dept_partition;
desc formatted dept_partition;
二级分区表：create table dept_partition2 ( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';
分桶抽样查询：分区针对的是数据的存储路径；分桶针对的是数据文件
create table stu_buck(id int, name string) clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';
设置开启分桶与rece为1：
set hive.enforce.bucketing=true;
set maprece.job.reces=-1;
分桶抽样：select * from stu_bucktablesample(bucket x out of y on id);
抽取，桶数/y，x是从哪个桶开始抽取，y越大 抽样数越少，y与抽样数成反比，x必须小于y
给空字段赋值：
如果员工的comm为NULL，则用-1代替或用其他字段代替 ：select nvl(comm,-1) from emp;
case when:如何符合记为1，用于统计、分组统计
select dept_id, sum(case sex when '男' then 1 else 0 end) man , sum(case sex when '女' then 1 else 0 end) woman from emp_sex group by dept_id;
用于组合归类汇总(行转列)：UDAF：多转一
concat：拼接查询结果
collect_set(col)：去重汇总，产生array类型字段，类似于distinct
select t.base, concat_ws('|',collect_set(t.name)) from (select concat_ws(',',xingzuo,blood_type) base,name from person_info) t group by t.base;
解释：先第一次查询得到一张没有按照（星座血型）分组的表，然后分组，使用collect_set将名字组合成数组，然后使用concat将数组变成字符串
用于拆分数据：(列转行)：UDTF：一转多
explode(col)：将hive一列中复杂的array或者map结构拆分成多行。
lateral view 侧面显示：用于和UDTF一对多函数搭配使用
用法：lateral view udtf(expression) tablealias as cate
cate：炸开之后的列别名
temptable ：临时表表名
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
开窗函数：
Row_Number,Rank，Dense_Rank over：针对统计查询使用
Row_Number：返回从1开始的序列
Rank:生成分组中的排名序号，会在名词s中留下空位。3 3 5
dense_rank：生成分组中的排名序号，不会在名词中留下空位。3 3 4
over：主要是分组排序，搭配窗口函数使用
结果：
SUM、AVG、MIN、MAX、count
preceding：往前
following：往后
current row：当前行
unbounded：unbounded preceding 从前面的起点， unbounded following：到后面的终点
sum：直接使用sum是总的求和，结合over使用可统计至每一行的结果、总的结果、当前行+之前多少行/之后多少行、当前行到往后所有行的求和。
over(rowsbetween 3/current ) 当前行到往后所有行的求和
ntile：分片，结合over使用，可以给数据分片，返回分片号
使用场景：统计出排名前百分之或n分之一的数据。
lead,lag,FIRST_VALUE,LAST_VALUE
lag与lead函数可以返回上下行的数据
lead(col,n,dafault) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
使用场景：通常用于统计某用户在某个网页上的停留时间
FIRST_VALUE:取分组内排序后，截止到当前行，第一个值
LAST_VALUE:取分组内排序后，截止到当前行,最后一个值
范围内求和: https://blog.csdn.net/happyrocking/article/details/105369558
cume_dist，percent_rank
–CUME_DIST :小于等于当前值的 行数 / 分组内总行数
–比如，统计小于等于当前薪水的人数，占总人数的比例
percent_rank:分组内当前行的RANK值-1/分组内总行数-1
总结：
在Spark中使用spark sql与hql一致，也可以直接使用sparkAPI实现。
HiveSql窗口函数主要应用于求TopN，分组排序TopN、TopN求和，前多少名前百分之几。
与Flink窗口函数不同。
Flink中的窗口是用于将无线数据流切分为有限块处理的手段。
window分类：
CountWindow：按照指定的数据条数生成一个 Window，与时间无关。
TimeWindow：按照时间生成 Window。
1. 滚动窗口（Tumbling Windows）：时间对齐，窗口长度固定，不重叠：：常用于时间段内的聚合计算
2.滑动窗口（Sliding Windows）：时间对齐，窗口长度固定，可以有重叠：：适用于一段时间内的统计（某接口最近 5min 的失败率来报警）
3. 会话窗口（Session Windows）无时间对齐，无长度，不重叠：：设置session间隔，超过时间间隔则窗口关闭。
‘捌’ 在SQL中如何把组合字段
oracle中可以：
select col1,col2,substr(max(sys_connect_by_path(col3,',')),2) col3
(select col1,col2,col3,row_number()over(partition by col1,col2 order by col3) id from 表名)
start with id = 1
connect by prior id = id -1 and prior col1=col1 and prior col2 = col2
group by col1,col2;
***************************
SqlServer中还在研究，抱歉。
---
以上，希望对你有所帮助。