『壹』 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