sql小技巧积累

- 日理万妓 2022-05-12 13:14 199阅读 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函数][IF]。

3、扩展   **isnull,ifnull,nullif的用法如下：(转自：**[https://blog.csdn.net/sunwukong\_hadoop/article/details/53692986][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；  
在这个例子中，[测试][Link 1]列的类型为   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';

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

[IF]: http://www.yiibai.com/mysql/if-function.html
[https_blog.csdn.net_sunwukong_hadoop_article_details_53692986]: https://blog.csdn.net/sunwukong_hadoop/article/details/53692986
[Link 1]: http://lib.csdn.net/base/softwaretest