sql使用技巧3

数据库环境：mysql,hive,sqlserver

1. group by 1 order by 2

解析：按select 第一列 分组，第二列排序

注意:

1.hive不支持group by 1 这种语法,但是支持order by 1这种语法

2.虽然可以取巧，但是在正式的代码开发中不推荐使用这种语法，因为可读性低，不利于代码维护。

2. SELECT count(null),sum(null),count(0),sum(0),count(1),sum(1),count(2),sum(2)

count(null) =0 ，但是在sqlserver中不支持。

在mysql中sum(null)=null,但是在hive和sqlserver中直接不支持。但可以支持字段case when.

如

select sum(case when id>100 then id else null end) from test.test1 ;

 注意：count(0)=1

------------------------------

3. 在Hql和spark sql中可以使用正则表达式，指定你要查询的列

// HQL使得``内的内容表示正则表达式，不设置则无法使用正则表达式
SET hive.support.quoted.identifiers = none;

//spark sql 使得``内的内容表示正则表达式，不设置则无法使用正则表达式
SET spark.sql.parser.quotedRegexColumnNames=true;

使用举例:

//查询出所有以字母o打头的列
SELECT  `^o.*`  FROM offers;  

//查询temp_table 表中除了oneid、transaction_header_id之外的所有字段
 select `(oneid|transaction_header_id)?+.+` from  dws.temp_table limit 1;
//说明:？是匹配0次或1次，之后的+表示独占模式（尽可能多的匹配，并且匹配失败不发生回溯），
最后的.+表示任意字符一次或多次。

--从非分区表导数据到分区表
--法一:从原始表(原始表的transaction_date并不是最后一列，但其他字段顺序与目标表一致)列出所有字段，并指定最后一个字段为分区字段
insert overwrite table dws.dws_transaction_header
select
oneid,transaction_header_id,ref_transaction_header_id,user_id,member_id,member_phone,transaction_month,
transaction_time,transaction_modify_time,store_id,transaction_channel,transaction_sub_channel,transaction_amount,
transaction_total_qty,transaction_discount_amount,transaction_status,cdp_data_source,create_time,update_time,invoice_no
,transaction_date
from dws.dws_transaction_header_np;

--法二：使用正则表达式
insert overwrite table dws.dws_transaction_header
select `(transaction_date)?+.+`,transaction_date
from dws.dws_transaction_header_np;