『壹』 mysql json類型數據 怎麼統計指定value值的重復數量
1、mysql專門對json數據提供了相應的訪問方式，比如你要的其實就是sum函數；
2、sql代碼例如：
1
select sum(data->"$.height") from table_name;
其中的table_name是假設你的表名叫table_name。
『貳』 mysql json類型的一些說明
以前最討厭英語了，然而沒有想到有一天居然要看英文文檔學習東西的時候，只想說MMP。下面就是一些學習的東西的記錄啦。
之前聽小夥伴說他們用的 pgsql ，pgsql 是如何如何牛逼，支持json。然後就去學習了下，但是公司用的是mysql,然後某個項目mysql用了5.7.20版本，這個版本支持json，所以就又學習了一波。吐槽完畢，下面就是正文了。
mysql5.7版本支持json，具體說的是5.7.8開始。這是需要注意的，然後可以的話大家還是看下官方文檔吧。
mysql 5.7 josn官方文檔地址
我這里 json_content 就定義的是json類型，下面就是基本的操作，後續會寫一些json函數。
這算是一個比較復雜的json了，array裡面放的是一個數組，other放了另一個對象。下面我再插入幾條數據。
下面兩條的數據和第一條不一樣，是為了做一些說明用。lisi這條數據所有的欄位都有，但是數據和張三略有不同，而wangwu這條數據就有些欄位沒有。
說明下，我原來用的navicat發現查josn的時候是空 select * 的時候定義的json欄位會不顯示，然後換了新版本就好了。
結果如下：
上面兩條sql都是通過json對象中flag=0的數據，然後上面兩條sql是等價的。JSON_EXTRACT函數的語法為：JSON_EXTRACT(欄位名,『$.key』)，這個函數可以在條件中使用，也可以在查詢的時候使用。如下：
結果如下：
嗯哼，沒錯 JSON_EXTRACT 這個函數是用來獲取json對象中指定key的值的。這是一個很常用的函數。
上面既然提到了time,那麼就用time來展示下吧。json對象中的時間，可以直接拿來比較，sql如下：
結果如下：
我的條件限定的是 2018-06-05 15:49:00 之後，獲取到了2條數據，是不是很神奇呢。這里就要說明下了，這里涉及到了json對象裡面對key對應的value進行比較的時候會有一個優先順序，mysql會按照這個優先順序來轉換，然後進行比較，這里只貼一下優先順序情況，各個類型的比較的規則自己去文檔裡面看吧。
下面再介紹兩個查詢時候會用到的函數JSON_CONTAINS() 和 JSON_CONTAINS_PATH()。
下面就是具體的例子：
結果如下：
結果如下：
結果如下：
結果如下：
用法還是比較簡單的，嗯，可能有小夥伴會問我要查詢不滿足條件的數據呢？很簡單，比如上面的sql，修改成：
查詢暫時就介紹這么多吧，後續可能會補充，現在介紹下修改。
JSON修改的函數比較多，這里介紹一些應該會常用到的。
id=2的數據結果變為：
id=2的數據結果變為：
說明：假設這里key已經存在，那麼本次已經存在的將不會被修改，同時可以添加多個。
id=2的數據結果變為：
id=2的數據結果變為：
說明下，如果指定的key不存在，那麼不存在的修改無效，存在的會被修改。
id=2的數據結果變為：
說明下，JSON_SET已經存在的會被修改，不存在的會被增加。
下面介紹一些其他函數。
暫時就寫這么多吧，後續再進行補充。本人也是剛開始接觸mysql的json，如果有問題請指教~
『叄』 第六章 SQL聚合函數 JSON_ARRAYAGG
創建 JSON 格式值數組的聚合函數。
註：IRIS可用，IRIS之前版本不可用。
JSON_ARRAYAGG 聚合函數返回指定列中值的JSON格式數組。
一個簡單的 JSON_ARRAYAGG (或 JSON_ARRAYAGG ALL )返回一個 JSON 數組，其中包含所選行中 string-expr 的所有值。
字元串 -expr 為空字元串 (") 的行由數組中的( " u0000" )表示。
字元串 -expr 為NULL的行不包含在數組中。
如果只有一個字元串 -expr 值，並且是空字元串( " )， JSON_ARRAYAGG 將返回 JSON 數組 ["u0000"] 。
如果所有的 string-expr 值為 NULL , JSON_ARRAYAGG 返回一個空的 JSON 數組 [] 。
JSON_ARRAYAGG DISTINCT 返回一個 JSON 數組，由所選行中 string-expr 的所有不同(唯一)值組成: JSON_ARRAYAGG(DISTINCT col1) 。
NULL 字元串 -expr 不包含在 JSON 數組中。
JSON_ARRAYAGG(DISTINCT BY(col2) col1) 返回一個 JSON 數組，該數組只包含記錄中 col2 值是不同的(唯一的)的那些 col1 欄位值。
但是請注意，不同的 col2 值可能包含一個單獨的 NULL 值。
JSON_ARRAYAGG string-expr 不能是流欄位。
指定流欄位的結果是 SQLCODE -37 。
默認的 JSON_ARRAYAGG 返回類型是 VARCHAR(8192) 。
這個長度包括 JSON 數組格式化字元以及欄位數據字元。
如果預期返回的值將需要大於 8192 ，可以使用 CAST 函數指定一個更大的返回值。
例如 CAST(JSON_ARRAYAGG(value)) AS VARCHAR(12000)) 。
如果實際返回的JSON數組長於 JSON_ARRAYAGG 返回類型長度，IRIS將在返回類型長度處截斷JSON數組，而不會發出錯誤。
因為截斷JSON數組會刪除其關閉的 ] 字元，這使得返回值無效。
可以使用 %SelectMode 屬性為 JSON 數組中的元素指定數據顯示值: 0=Logical (默認值)， 1=ODBC , 2= display 。
如果 string-expr 包含一個 %List 結構，則元素以ODBC模式表示，用逗號分隔，在邏輯和顯示模式中以 %List 格式字元表示，用 轉義序列表示。
JSON_ARRAYAGG 函數將表中多行列的值組合成一個包含元素值的 JSON 數組。
因為在計算所有聚合欄位之後，查詢結果集中應用了一個 ORDER BY 子句，所以 ORDER BY 不能直接影響這個列表中的值序列。
在某些情況下， JSON_ARRAYAGG 結果可能是按順序出現的，但是不應該依賴於這種順序。
在給定聚合結果值中列出的值不能顯式排序。
下面的嵌入式SQL示例返回一個主機變數，該變數包含示例的 Home_State 列中所有值的 JSON 數組。
以字母 A 開頭的人名表:
注意，這個 JSON 數組包含重復的值。
下面的動態SQL示例返回一個主機變數，該變數包含樣本的 Home_State 列中所有不同(唯一)值的JSON數組。
以字母 A 開頭的人名表:
下面的SQL示例為每個州創建了一個 JSON 數組，其中包含在 Home_City 列中找到的所有值，以及按州列出的這些城市值的計數。
每個 Home_State 行包含該狀態的所有 Home_City 值的 JSON 數組。
這些JSON數組可能包含重復的城市名稱:
更有用的是一個 JSON 數組的所有不同的值，發現在 Home_City 列為每個州，如下所示的動態SQL示例:
注意，這個示例返回每個州的不同城市名稱和總城市名稱的整數計數。
下面的動態SQL示例使用 %SelectMode 屬性為 DOB 日期欄位返回的JSON值數組指定 ODBC 顯示模式:
下面的動態SQL示例使用 %FOREACH 關鍵字。
它為每個不同的 Home_State 返回一行，其中包含該 Home_State 的年齡值的JSON數組。
下面的動態SQL示例使用 %AFTERHAVING 關鍵字。
它為每個 Home_State 返回一行，其中至少包含一個滿足 HAVING 子句條件的 Name 值(以 「M」 開頭的名稱)。
第一個 JSON_ARRAYAGG 函數返回一個包含該狀態所有名稱的 JSON 數組。
第二個 JSON_ARRAYAGG 函數返回的 JSON 數組只包含滿足 HAVING 子句條件的名稱:
『肆』 求一個資料庫函數解析json 如下圖要求
資料庫只是用來存儲數據的，為什麼不把處理數據的部分放在後台？
在這里你需要了解以下幾個知識點。
在sqlserver 中存儲json ，需要用字元串類型進行存儲，一般用nvarchar()或 varchar()進行存儲。
openJson:打開Json字元串
IsJson:判斷一個字元串是不是合法的Json格式。是返回1，否返回0，null返回null。
Json_Value:從Json字元串中提取值。
Json_Query:Json字元串中提取對象或數組。
Json_Modify:更新Json字元串中的屬性值，並返回更新的Json字元串
然後參考圖片的需求，獲取輸入key值中的value欄位可以得到如下sql語句。
從JSON中獲取輸入B的JSON對象
SELECT JSON_QUERY(json, CONCAT('$.',key)) INTO K_VAL;
從B的JSON對象中，獲取value欄位的值
SELECT JSON_VALUE(K_VAL, '$.value') INTO JSONVALUE;
最終我們的聲明的函數大概是：
CREATE FUNCTION PARSEJSON(json VARCHAR(2000), key VARCHAR(200))
RETURNS VARCHAR
BEGIN
declare JSONVALUE VARCHAR;
declare K_VAL VARCHAR;
SELECT JSON_QUERY(json, CONCAT('$.',key)) INTO K_VAL;
SELECT JSON_VALUE(K_VAL, '$.value') INTO JSONVALUE;
return JSONVALUE;
『伍』 sql 處理 json
json的數據json.loads進來以後會變成一個json的對象，你需要自己把python對象中的欄位值取出來，拼成sql語句你可以把這個過程封裝成一個函數importjsondefsave_json(json_str):obj=json.loads(json_str)sql='insertintotblvalues("%s")'%obj['id']#這里注意編碼，要轉成資料庫的編碼格式#blabla
『陸』 sqlserver2008不支持json
sql server2008支持json函數
1。json 轉化成數據集合
1）轉化用函數
CREATE FUNCTION [dbo].[parseJSON]( @JSON NVARCHAR(MAX))
RETURNS @hierarchy TABL
element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
sequenceNo [int] NULL, /* the place in the sequence for the element */
parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
NAME NVARCHAR(2000),/* the name of the object */
StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
)
AS
BEGIN
DECLARE
@FirstObject INT, --the index of the first open bracket found in the JSON string
@OpenDelimiter INT,--the index of the next open bracket found in the JSON string
@NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string
@NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string
@Type NVARCHAR(10),--whether it denotes an object or an array
@NextCloseDelimiterChar CHAR(1),--either a '}' or a ']'
@Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression
@Start INT, --index of the start of the token that you are parsing
@end INT,--index of the end of the token that you are parsing
@param INT,--the parameter at the end of the next Object/Array token
@EndOfName INT,--the index of the start of the parameter at end of Object/Array token
@token NVARCHAR(200),--either a string or object
@value NVARCHAR(MAX), -- the value as a string
@SequenceNo int, -- the sequence number within a list
@name NVARCHAR(200), --the name as a string
@parent_ID INT,--the next parent ID to allocate
@lenJSON INT,--the current length of the JSON String
@characters NCHAR(36),--used to convert hex to decimal
@result BIGINT,--the value of the hex symbol being parsed
@index SMALLINT,--used for parsing the hex value
@Escape INT --the index of the next escape character
DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */
(
String_ID INT IDENTITY(1, 1),
StringValue NVARCHAR(MAX)
)
SELECT--initialise the characters to convert hex to ascii
@characters='',
@SequenceNo=0, --set the sequence no. to something sensible.
/* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */
@parent_ID=0;
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT
@start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin);--next delimited string
IF @start=0 BREAK --no more so drop through the WHILE loop
IF SUBSTRING(@json, @start+1, 1)='"'
BEGIN --Delimited Name
SET @start=@Start+1;
SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start));
END
IF @end=0 --no end delimiter to last string
BREAK --no more
SELECT @token=SUBSTRING(@json, @start+1, @end-1)
--now put in the escaped control characters
SELECT @token=REPLACE(@token, FROMString, TOString)
FROM
(SELECT
'\"' AS FromString, '"' AS ToString
UNION ALL SELECT '\\', '\'
UNION ALL SELECT '\/', '/'
UNION ALL SELECT '\b', CHAR(08)
UNION ALL SELECT '\f', CHAR(12)
UNION ALL SELECT '\n', CHAR(10)
UNION ALL SELECT '\r', CHAR(13)
UNION ALL SELECT '\t', CHAR(09)
) substitutions
SELECT @result=0, @escape=1
--Begin to take out any hex escape codes
WHILE @escape>0
BEGIN
SELECT @index=0,
--find the next hex escape sequence
@escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token)
IF @escape>0 --if there is one
BEGIN
WHILE @index<4 --there are always four digits to a \x sequence
BEGIN
SELECT --determine its value
@result=@result+POWER(16, @index)
*(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),
@characters)-1), @index=@index+1 ;
END
-- and replace the hex sequence by its unicode value
SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result))
END
END
--now store the string away
INSERT INTO @Strings (StringValue) SELECT @token
-- and replace the string with a token
SELECT @JSON=STUFF(@json, @start, @end+1,
'@string'+CONVERT(NVARCHAR(5), @@identity))
END
-- all strings are now removed. Now we find the first leaf.
WHILE 1=1 --forever until there is nothing more to do
BEGIN
SELECT @parent_ID=@parent_ID+1
--find the first object or list by looking for the open bracket
SELECT @FirstObject=PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin)--object or array
IF @FirstObject = 0 BREAK
IF (SUBSTRING(@json, @FirstObject, 1)='{')
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@firstObject
WHILE 1=1 --find the innermost object or list...
BEGIN
SELECT
@lenJSON=LEN(@JSON+'|')-1
--find the matching close-delimiter proceeding after the open-delimiter
SELECT
@NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,
@OpenDelimiter+1)
--is there an intervening open-delimiter of either type
SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%',
RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--object
IF @NextOpenDelimiter=0
BREAK
SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter
IF @NextCloseDelimiter<@NextOpenDelimiter
BREAK
IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{'
SELECT @NextCloseDelimiterChar='}', @type='object'
ELSE
SELECT @NextCloseDelimiterChar=']', @type='array'
SELECT @OpenDelimiter=@NextOpenDelimiter
END
---and parse out the list or name/value pairs
SELECT
@contents=SUBSTRING(@json, @OpenDelimiter+1,
@NextCloseDelimiter-@OpenDelimiter-1)
SELECT
@JSON=STUFF(@json, @OpenDelimiter,
@NextCloseDelimiter-@OpenDelimiter+1,
'@'+@type+CONVERT(NVARCHAR(5), @parent_ID))
WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))<>0
BEGIN
IF @Type='Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null
BEGIN
SELECT
@SequenceNo=0,@end=CHARINDEX(':', ' '+@contents)--if there is anything, it will be a string-based name.
SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents)--AAAAAAAA
SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1),
@endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin),
@param=RIGHT(@token, LEN(@token)-@endofname+1)
SELECT
@token=LEFT(@token, @endofname-1),
@Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1)
SELECT @name=stringvalue FROM @strings
WHERE string_id=@param --fetch the name
END
ELSE
SELECT @Name=null,@SequenceNo=@SequenceNo+1
SELECT
@end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null
IF @end=0
SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ')
+1
SELECT
@start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents)
--select @start,@end, LEN(@contents+'|'), @contents
SELECT
@Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),
@Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end)
IF SUBSTRING(@value, 1, 7)='@object'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5),
SUBSTRING(@value, 8, 5), 'object'
ELSE
IF SUBSTRING(@value, 1, 6)='@array'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5),
SUBSTRING(@value, 7, 5), 'array'
ELSE
IF SUBSTRING(@value, 1, 7)='@string'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, stringvalue, 'string'
FROM @strings
WHERE string_id=SUBSTRING(@value, 8, 5)
ELSE
IF @value IN ('true', 'false')
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'boolean'
ELSE
IF @value='null'
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'null'
ELSE
IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'real'
ELSE
INSERT INTO @hierarchy
(NAME, SequenceNo, parent_ID, StringValue, ValueType)
SELECT @name, @SequenceNo, @parent_ID, @value, 'int'
if @Contents=' ' Select @SequenceNo=0
END
END
INSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)
SELECT '-',1, NULL, '', @parent_id-1, @type
--
RETURN
END
2.舉例
Select * from parseJSON('{
"聯系人":
{
"姓名": "huang",
"網名": "HTL",
"AGE": 05,
"男人":true,
"PhoneNumbers":
{
"mobile":"135123100514",
"phone":"0251-123456789"
}
}
}
')
以上用到函數，轉自：http://blog.csdn.net/ghlfllz/article/details/51649837#
二、sql轉化成json、xml等方法
見鏈接：http://www.cnblogs.com/huangtailang/p/4277809.html
原文鏈接：https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/
三、sqlserver2016支持json
請看：https://msdn.microsoft.com/en-us/library/dn921897.aspx
SELECT Name, Surname,
JSON_VALUE(jsonCol, '$.info.address.PostCode') as PostCode,
JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' ' + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
JSON_QUERY(jsonCol, '$.info.skills') as Skills
FROM PeopleCollection
WHERE ISJSON(jsonCol) > 0
AND JSON_VALUE(jsonCol, '$.info.address.town') = 'Belgrade'
AND Status = 'Active'
ORDER BY JSON_VALUE(@jsonInfo, '$.info.address.PostCode')
文章知識點與官方知識檔案匹配
MySQL入門技能樹內置函數JSON函數
29106 人正在系統學習中
『柒』 mysql5.7以下怎麼解析json
DELIMITER $$
USE `dw`$$
DROP FUNCTION IF EXISTS `fn_Json_getKeyValue`$$
CREATE DEFINER=`data`@`%` FUNCTION `fn_Json_getKeyValue`(
in_JsonArray VARCHAR(4096),#JSON數組字元串
in_Index TINYINT, #JSON對象序號，序號從1開始
in_KeyName VARCHAR(64)#鍵名
) RETURNS VARCHAR(512) CHARSET utf8
BEGIN
DECLARE vs_return VARCHAR(4096);
DECLARE vs_JsonArray, vs_Json, vs_KeyName VARCHAR(4096);
#declare vs_Json varchar(4096);
DECLARE vi_pos1, vi_pos2 SMALLINT UNSIGNED;
#寫監控日誌
#insert into dw.t_etl_log(sp_name, title, description)
#values('dw.fn_Json_getKeyValue', '通過Json鍵名取鍵值', concat('in_JsonArray=', in_JsonArray));
SET vs_JsonArray = TRIM(in_JsonArray);
SET vs_KeyName = TRIM(in_KeyName);
IF vs_JsonArray = '' OR vs_JsonArray IS NULL
OR vs_KeyName = '' OR vs_KeyName IS NULL
OR in_Index <= 0 OR in_Index IS NULL THEN
SET vs_return = NULL;
ELSE
#去掉方括弧
SET vs_JsonArray = REPLACE(REPLACE(vs_JsonArray, '[', ''), ']', '');
#取指定的JSON對象
SET vs_json = SUBSTRING_INDEX(SUBSTRING_INDEX(vs_JsonArray,'}', in_index),'}',-1);
IF vs_json = '' OR vs_json IS NULL THEN
SET vs_return = NULL;
ELSE
SET vs_KeyName = CONCAT('"', vs_KeyName, '":');
SET vi_pos1 = INSTR(vs_json, vs_KeyName);
IF vi_pos1 > 0 THEN
#如果鍵名存在
SET vi_pos1 = vi_pos1 + CHAR_LENGTH(vs_KeyName);
SET vi_pos2 = LOCATE(',', vs_json, vi_pos1);
IF vi_pos2 = 0 THEN
#最後一個元素沒有','分隔符，也沒有結束符'}'
SET vi_pos2 = CHAR_LENGTH(vs_json) + 1;
END IF;
SET vs_return = REPLACE(MID(vs_json, vi_pos1, vi_pos2 - vi_pos1), '"', '');
END IF;
END IF;
END IF;
RETURN(vs_return);
END$$
DELIMITER ;
測試： {"old_current_score":"2","new_current_score":"0","old_grade_id":"1","new_grade_id":"1","grade_time":"2016-04-09 00:43:26","grade_upgrade_time":"2017-04-09 00:43:26"}
select fn_Json_getKeyValue(reason,1,'old_grade_id');
『捌』 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間隔，超過時間間隔則窗口關閉。
『玖』 剛入職的菜鳥，在plsql中怎麼拼接json欄位
Oracle 12.1.0.2版本有一個新功能就是可以存儲、查詢、索引JSON數據格式，而且也實現了使用SQL語句來解析JSON，非常方便。JSON數據在資料庫中以VARCHAR2, CLOB或者BLOB進行存儲。Oracle建議用戶在插入JSON數據之前,使用is_json來驗證輸入JSON數據的正確性。另外，Oracle也提供了相關的函數：
Functions：json_value, json_query, json_table.
Conditions：json_exists, is json, is not json, json_textcontains.
『拾』 如何將JSON，Text，XML，CSV 數據文件導入 MySQL
將外部數據導入(import)資料庫是在資料庫應用中一個很常見的需求。其實這就是在數據的管理和操作中的ETL (Extract, transform, load)的L (Load)部分，也就是說，將特定結構(structure)或者格式(format)的數據導入某個目的地(比如資料庫，這里我們討論MySQL)。
ETL Process
本文要討論的內容，是如何方便地將多種格式(JSON, Text, XML, CSV)的數據導入MySQL之中。
本文大綱：
將Text文件（包括CSV文件）導入MySQL
將XML文件導入MySQL
將JSON文件導入MySQL
使用MySQL workbench的Table Data Export and Import Wizard進行JSON或CSV文件的導入導出
1. 將Text文件（包括CSV文件）導入MySQL
這里我們的討論是基於一個假定，Text file和CSV file是有著比較規范的格式的(properly formatted)，比如說每行的每個數據域(field)之間是由一個共同的分隔符（比如tab: \t）分隔的。
那麼首先，你需要根據你的數據的格式（有哪些域），來設計好資料庫的對應的表 （的Schema）。
舉個例子，要處理的Text文件或者CSV文件是以\t作為分隔符的，每行有id, name, balance這么三個數據域，那麼首先我們需要在資料庫中創建這個表：
CREATE TABLE sometable(id INT, name VARCHAR(255), balance DECIMAL(8,4));
創建成功以後就可以導入了。操作方式很簡單：
LOAD DATA LOCAL INFILE '你的文件路徑（如~/file.csv）' INTO TABLE sometable FIELDS TERMINATED BY '\t' [ENCLOSED BY '"'(可選)] LINES TERMINATED BY '\n' (id, name, balance)
這里要注意的是，我們需要開啟local-infile這個MySQL的配置參數，才能夠成功導入。究其原因，從MySQL的Manual中可以看到這么一段話：
LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 6.1.6, 「Security Issues with LOAD DATA LOCAL」.
這是MySQL出於安全考慮的默認配置。因此，我們需要在配置文件my.cnf中（以Debian發行版的Linux, 如Ubuntu為例， 即是在/etc/my.cnf中），確保：
local-infile=1
抑或是在命令行啟動MySQL時加上--local-infile這一項：
mysql --local-infile -uroot -pyourpwd yourdbname
此外，我們也可以使用MySQL的一個官方導入程序mysqlimport ，這個程序本質上就是為LOAD DATA FILE提供了一個命令行的interface，很容易理解，我們這里就不再詳述。
2. 將XML文件導入MySQL
這件事的完成方式，與我們的XML的形式有著很大的關系。
舉個例子說，當你的XML數據文件有著很非常規范的格式，比如：
<?xml version="1.0"?>
<row>
<field name="id">1</field>
<field name="name">Free</field>
<field name="balance">2333.3333</field>
</row>
<row>
<field name="id">2</field>
<field name="name">Niki</field>
<field name="balance">1289.2333</field>
</row>
或者
<row column1="value1" column2="value2" .../>
我們就可以很方便使用LOAD XML來導入，這里可以參見MySQL的官方手冊--LOAD XML Syntax。
然而我們可能有另外一些需求，比如說，我們可能會想要將XML文件的域映射到不同名字的列(TABLE COLUMN)之中。這里要注意，MySQL v5.0.7以後，MySQL的Stored Procere中不能再運行LOAD XML INFILE 或者LOAD DATA INFILE。所以轉換的程序(procere)的編寫方式與在此之前有所不同。這里，我們需要使用Load_File()和ExtractValue()這兩個函數。
以下是一個示例XML文件和程序：
文件：
<?xml version="1.0"?>
<some_list>
<someone id="1" fname="Rob" lname="Gravelle"/>
<someone id="2" fname="Al" lname="Bundy"/>
<someone id="3" fname="Little" lname="Richard"/>
</some_list>
程序：
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `import_some_xml`(path varchar(255), node varchar(255))
BEGIN
declare xml_content text;
declare v_row_index int unsigned default 0;
declare v_row_count int unsigned;
declare v_xpath_row varchar(255);
set xml_content = load_file(path);
-- calculate the number of row elements.
set v_row_count = extractValue(xml_content, concat('count(', node, ')'));
-- loop through all the row elements
while v_row_index < v_row_count do
set v_row_index = v_row_index + 1;
set v_xpath_row = concat(node, '[', v_row_index, ']/@*');
insert into applicants values (
extractValue(xml_content, concat(v_xpath_row, '[1]')),
extractValue(xml_content, concat(v_xpath_row, '[2]')),
extractValue(xml_content, concat(v_xpath_row, '[3]'))
);
end while;
END
在MySQL中，使用它進行導入：
call import_some_xml('你的XML文件路徑', '/some_list/someone');
程序相當的直白，只要了解一下MySQL的腳本編寫即可。
這里提一下DELIMITER $$。我們知道MySQL的命令分隔符默認為分號，然而腳本中很顯然是有分號的，但是我們並不希望立即執行，所以我們需要臨時更改分隔符。
3. 將JSON文件導入MySQL
如何將JSON文件導入MySQL中，是一個很有趣的話題。JSON是一種現在相當常用的文件結構，所以掌握它的導入具有比較廣泛的意義。
很多時候，我們處理的JSON數據是以如下形式出現的：
{"name":"Julia","gender":"female"}
{"name":"Alice","gender":"female"}
{"name":"Bob","gender":"male"}
{"name":"Julian","gender":"male"}
而並不是規整的[{},{},{},{}]（一些NoSQL資料庫的Export）。
這樣的形勢對於載入有一個好處：因為每一行是一個JSON Object，所以我們便可以按行處理此文件，而不需要因為JSON的嚴格結構將整個文件（比如一個許多G的.json文件）全部載入。
方式一 使用common-schema
common-schema是一個應用很廣泛的MySQL的框架，它有著很豐富的功能和詳細的文檔。我們可以使用它的JSON解析的功能。（它還具有JSON轉換成XML等等方便的功能）
具體說來，將common-schema導入之後，使用它的extract_json_value函數即可。源碼中：
create function extract_json_value(
json_text text charset utf8,
xpath text charset utf8
) returns text charset utf8
該函數接受兩個參數，一個是json_text，表示json文件的內容，另一個是xpath，表示數據的結構（這里可以類比XML文件的處理）。很多讀者應該知道，XPath是用來對XML中的元素進行定位的，這里也可以作一樣的理解。
以本段開始的幾行JSON為例，這里common-schema的使用如下例：
select common_schema.extract_json_value(f.event_data,'/name') as name, common_schema.extract_json_value(f.event_data,'/gender') as gender, sum(f.event_count) as event_count from json_event_fact f group by name, gender;
關於event_data，我們需要先理解LOAD DATA INFILE是一個event，不同的event type對應不同的event data。這部分知識可以參看Event Data for Specific Event Types
如果感興趣，可以參看其源碼。參看一個受到廣泛使用的項目的源碼，對於自身成長是很有益的。
當然了，我們也可以像之前處理XML文件導入一樣，自己編寫程序。這里便不再給出實常式序，有興趣的讀者可以自行編寫或者跟筆者交流。
方式二 使用mysqljsonimport
這是Anders Karlsson的一個完成度很高的作品。這一份程序由C寫成。它依賴於一個JSON Parser，Jansson。他們都有著比較好的維護和文檔，所以使用上體驗很好。
mysqljsonimport的下載在SourceForge上。具體使用參照其文檔即可。
為了方便不熟悉源碼安裝的朋友，筆者在這里提一下安裝流程和注意事項。
安裝命令順序如下：
$ wget http://sourceforge.net/projects/mysqljson/files/myjsonimport_1.6/mysqljsonimport-1.6.tar.gz
$ tar xvfz mysqljsonimport-1.6.tar.gz
$ cd mysqljsonimport-1.6
$ ./configure –-with-mysql=/xxx/mysql
$ make
$ make check
$ sudo make install
--with-mysql這一步不是必要的，只要你安裝的mysql的路徑是系統的默認路徑。很關鍵的，而且很容易被不熟悉的朋友忽略的是，這一個C程序要成功編譯和運行，是需要MySQL的C API的，所以需要安裝的依賴，除了jansson，還有libmysqlclient-dev。
jansson的安裝就是簡單的源碼安裝，libmysqlclient-dev則可以使用包管理工具(比如ubuntu中使用apt-get即可；編譯和安裝前，建議先sudo apt-get update以避免不必要的麻煩)。
導入命令：
$ ./mysqljsonimport –-database test –-table tablename jsonfilename
還有一個parser，作者是Kazuho，感興趣的讀者可以參看一下，他的相關博文是mysql_json - a MySQL UDF for parsing JSON ，github項目是mysql_json。
4. 使用MySQL workbench
Workbench這個工具對於許多不熟悉SQL語言或者命令行的朋友還是很方便和友好的。利用它，可以方便地導入和導出CSV和JSON文件。
具體操作圖例參見MySQL官方手冊即可:Table Data Export and Import Wizard，這里不再贅述。
文／freenik（簡書作者）
原文鏈接：http://www.jianshu.com/p/d330edb61fe2