sql小技巧积累

最新推荐文章于 2020-11-29 12:03:31 发布

grootblockchain

最新推荐文章于 2020-11-29 12:03:31 发布

阅读量274

点赞数 1

分类专栏： sql

本文链接：https://blog.csdn.net/captainjava/article/details/82754472

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

******************************************

计算日期差

字符串截取

示例1：

SELECT
	d.customer_id,
	d.traffic_id,
	d.activity_id,
	d.visit_start_time AS last_visit_time,
	DATEDIFF ( '2018-04-19', substr( d.visit_start_time, 0, 10 ) ) AS last_visit_interval,
	'2018-04-19 00:00:00' 
FROM
	(
	SELECT
		* 
	FROM
		(
		SELECT
			a.customer_id,
			a.traffic_id,
			a.activity_id,
			a.visit_start_time,
			ROW_NUMBER ( ) OVER ( PARTITION BY a.customer_id, a.activity_id ORDER BY a.visit_start_time DESC ) AS num 
		FROM
			huntor_dw.customer_visit_record_detail_activity a 
		WHERE
			customer_id IS NOT NULL 
		) c 
	WHERE
		c.num = 1 UNION ALL
	SELECT
		* 
	FROM
		(
		SELECT
			b.customer_id,
			b.traffic_id,
			b.activity_id,
			b.visit_start_time,
			ROW_NUMBER ( ) OVER ( PARTITION BY b.traffic_id, b.activity_id ORDER BY b.visit_start_time DESC ) AS num 
		FROM
			huntor_dw.customer_visit_record_detail_activity b 
		WHERE
			customer_id IS NULL 
		) t 
	WHERE
	t.num = 1 
	) d;

1、DATEDIFF ( '2018-04-19', substr( d.visit_start_time, 0, 10 ) )算日期差

2、 substr( d.visit_start_time, 0, 10 )截取字符串

*******************************************************

示例2：

将文件中的数据插入hive表中：

LOAD DATA LOCAL INPATH '/data/groot/groot.txt' INTO TABLE huntor_trans.customer_life_cycle_trans partition (input_date = '2018-04-25');

有分区的加partition 。

groot.txt格式：与建表时分隔符有一定关系

NULL 1 1170505002713481 3 客户 2 高潜 2018-04-25 08:04:10 2018-04-25

NULL 1 1232143165254252 4 新购 3 客户 2018-04-25 08:04:55 2018-04-25

NULL 1 1141252152142341 NULL NULL 4 新购 2018-04-25 08:05:43 2018-04-25

**************************************************

sql查询、统计中 NULL的值替换

如，left join on 右侧null修改为0/修改null值

套上coalesce(a.open_count,0) 即可或者 IFNULL( sum( t.final_amount ), 0.00 )

例：

SELECT
	e.source_id AS activity_id,
	e.NAME,
	e.type,
	e.start_at,
	e.end_at,
	e.STATUS,
	COALESCE ( a.open_count, 0 ) AS open_count,
	COALESCE ( b.share_count, 0 ) AS share_count,
	COALESCE ( c.awarded_count, 0 ) AS awarded_count,
	COALESCE ( d.received_count, 0 ) AS received_count 
FROM
	huntor_dw.dw_v_activities e
	LEFT JOIN ( SELECT activity_id, count( activity_id ) AS open_count FROM huntor_dw.dw_v_app_activity_card_gathering_records WHERE is_new = 1 AND created_at < '2018-03-16' AND created_at >= '2018-03-15' GROUP BY activity_id ) a ON e.source_id = a.activity_id
	LEFT JOIN (
	SELECT
		activity_id,
		count( activity_id ) AS share_count 
	FROM
		huntor_dw.dw_v_app_activity_card_gathering_details 
	WHERE
		is_new = 1 
		AND created_at < '2018-03-16' AND created_at >= '2018-03-15' 
		AND action_type = 2 
	GROUP BY
		activity_id 
	) b ON e.source_id = b.activity_id
	LEFT JOIN ( SELECT activity_id, count( activity_id ) AS awarded_count FROM huntor_dw.dw_v_app_activity_card_gathering_records WHERE is_new = 1 AND created_at < '2018-03-16' AND created_at >= '2018-03-15' GROUP BY activity_id ) c ON e.source_id = c.activity_id
	LEFT JOIN ( SELECT activity_id, count( activity_id ) AS received_count FROM huntor_dw.dw_v_app_activity_card_gathering_records WHERE is_new = 1 AND created_at < '2018-03-16' AND created_at >= '2018-03-15' GROUP BY activity_id ) d ON e.source_id = d.activity_id 
WHERE
	is_new = 1 
	AND e.start_at <= '2018-03-19' AND e.end_at >= '2018-03-19' 
	AND e.STATUS = 1 
	AND e.publish_status = 1;

1、coalesce的用法：coalesce是将返回传入的参数中第一个非null的值，比如

   SELECT COALESCE(NULL, NULL, 1); 

-- Return 1 



SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1); 

-- Return 1

如果传入的参数所有都是null，则返回null，比如

SELECT COALESCE(NULL, NULL, NULL, NULL); 

-- Return NULL

这个参数使用的场合为：假如某个字段默认是null，你想其返回的不是null，而是比如0或其他值，可以使用这个函数

   SELECT COALESCE(field_name,0) as value from table;

2、MySQL IFNULL

MySQL IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。否则，IFNULL函数返回第二个参数。

两个参数可以是文字值或表达式。

以下说明了IFNULL函数的语法：

SQL

如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。

IFNULL函数根据使用的上下文返回字符串或数字。

如果要返回基于TRUE或FALSE条件的值，而不是NULL，则应使用IF函数。

3、扩展 isnull,ifnull,nullif的用法如下：(转自：https://blog.csdn.net/sunwukong_hadoop/article/details/53692986）

isnull(expr) 的用法：
如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。

mysql> select isnull(1+1);
-> 0
mysql> select isnull(1/0);
-> 1

使用= 的null 值对比通常是错误的。

isnull() 函数同 is null比较操作符具有一些相同的特性。请参见有关is null 的说明。

IFNULL(expr1,expr2)的用法：

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1;
否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

mysql>   SELECT   IFNULL(1,0);
->   1
mysql>   SELECT   IFNULL(NULL,10);
->   10
mysql>   SELECT   IFNULL(1/0,10);
->   10
mysql>   SELECT
IFNULL(1/0,'yes');

->   'yes'

IFNULL(expr1,expr2)的默认结果值为两个表达式中更加“通用”的一个，顺序为STRING、   REAL或
INTEGER。假设一个基于表达式的表的情况，     或MySQL必须在内存储器中储存一个临时表中IFNULL()的返回值：
CREATE   TABLE   tmp   SELECT   IFNULL(1,'test')   AS   test；
在这个例子中，测试列的类型为   CHAR(4)。
NULLIF(expr1,expr2)  的用法：
如果expr1
=   expr2     成立，那么返回值为NULL，否则返回值为   expr1。这和CASE   WHEN   expr1   =   expr2
THEN   NULL   ELSE   expr1   END相同。

mysql>   SELECT
NULLIF(1,1);

->   NULL
mysql>   SELECT   NULLIF(1,2);
->   1

如果参数不相等，则 MySQL 两次求得的值为 expr1 。

*********************************************************************

LOCATE(substr,str), LOCATE(substr,str,pos)

字段是否包含字符，字符在字符串中的位置

第一种语法返回字串符substr在字符串str第一个出现的位置。第二个语法返回串substr在字符串str，位置pos处开始第一次出现的位置。返回值为0，substr不在str.

case when条件语句

当满足某一条件时，执行某一result

CASE 
	WHEN CONDITION THEN result  
	WHEN CONDITION THEN result 
	WHEN CONDITION THEN result 
	ELSE result 
END

当colume 与condition 条件相等时结果为result

CASE colume 
	WHEN CONDITION THEN	result
	WHEN CONDITION THEN	result 
	WHEN CONDITION THEN	result
 ELSE result 
END

case when 自定义排序时的使用

根据 case when 新的 sort字段排序

CASE 
		WHEN t2.STATUS = 4 
		AND t2.expire_time > UNIX_TIMESTAMP( ) 
		AND t2.expire_time < UNIX_TIMESTAMP( DATE_ADD( NOW( ), INTERVAL 60 DAY ) ) THEN
			4 
			WHEN `status` = 2 THEN
			3 
			WHEN `status` = 3 THEN
			2 
			WHEN t2.STATUS = 4 
			AND t2.expire_time > UNIX_TIMESTAMP( DATE_ADD( NOW( ), INTERVAL 60 DAY ) ) 
			AND t2.expire_time < UNIX_TIMESTAMP( DATE_ADD( NOW( ), INTERVAL 1 YEAR ) ) THEN   
			1 
        ELSE 0 
	END sort$ QUERY -> orderBy ( 'sort desc ,t2.expire_time desc,t2.created_at desc' );

- 当满足某一条件时，执行某一result,把该结果赋值到new_column_name 字段中

case

when condition then result

else result

end new_column_name //end 后面加as new_colume_name

```

case when 用在select 语句中，新的字段new_column_name可以用来排序，但是不能用在where中

示例：

SELECT
	id,
	tenant_id,
CASE
		
		WHEN locate( '-', customer_id ) > 0 THEN
		NULL ELSE customer_id 
	END,
CASE
		
		WHEN locate( '-', customer_id ) > 0 THEN
		customer_id ELSE NULL 
	END,
	last_life_cycle_id,
	last_life_cycle_name,
	life_cycle_id,
	life_cycle_name,
	'2018-05-17 00:00:00',
	'2018-05-17' 
FROM
	huntor_trans.customer_life_cycle_trans 
WHERE
	input_date = '2018-05-17';

****************************************************************