『壹』 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中還在研究，抱歉。
---
以上，希望對你有所幫助。