sql语句

查询

• 查询user_id前100名并按grades排序（降序）
  select * from users where user_id <= 100 order by grades desc/asc(升序)

• 查询姓‘李’的
  select * from users where user_name like “李%”（“李_ _”是查询姓李的三个字的）

• 查询人数去重
  select count distinct user_id from user1

• 查询分数最高的学号
  select max(grade) from user

• 连表查询
  select user_name,grade form user,user1 where user1.user_id=user.user_id

• 筛选
  select * from user where grade in (“90”,“100”,“80”)
  select * from user where grade between 90 and 100

修改表内容

update user set user_name=“alice” where user_id=100

删除表内容

delete from user where user_id = 10

增加

• insert into user(user_name,grade) values(“ashi”,“100”)
• insert into user1 select * from user 把user的数据添加到user1表中

cast

将一个数据类型转换为另一个数据类型

cast（mygoals as string）将mygoals转为string类型

from_unixtime

将时间格式化

mysql>SELECT FROM_UNIXTIME( 1249488000, '%Y年%m月%d' )
->2007年11月20
mysql> Select FROM_UNIXTIME(875996580);
->'1997-10-04 22:23:00'

unix_timestamp

将日期转换为时间戳

mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580

行转列 （case when）

# case when 只返回第一个有效值，其他会被自动忽略，加上“max,sum”等可以过滤null值   
select name,max(case when course = '语文' then grades end) as "语文"，
       max(case when course = '数学' then grades end) as "数学"，
       max(case when course = '英语' then grades end) as "英语" 
from score group by name;   

计算retention

select a.date,count(distinct a.guid_key) new_user,
count(distinct case when date_diff('day',cast(a.date as date),cast(b.date as date))=1 then a.guid_key end) first_user,
count(distinct case when date_diff('day',cast(a.date as date),cast(b.date as date))=3 then a.guid_key end) three_user,
count(distinct case when date_diff('day',cast(a.date as date),cast(b.date as date))=7 then a.guid_key end) seven_user
from
(select distinct guid_key,DATE from usage where bundle_id = 'jp.co.yahoo.ipn.appli' and date between '2018-08-01' and '2018-08-07' and range_type='DAY') a
LEFT JOIN
(select distinct guid_key,DATE from usage where bundle_id = 'jp.co.yahoo.ipn.appli' and date between '2018-08-01' and '2018-08-07' and range_type='DAY') b
on a.guid_key = b.guid_key
group by a.date

将带有“，”的字符串分割成单个字段（explode）

 select count(distinct a.username),level from
(select distinct username,level from user_level where 
dt = '2019-03-09' ) a
join
(select explode(split(username,",")) as username
from wirelessdata.ods_etl_service_cross_user
where logday>='20180101'
and logday<='20180101'
and service_marks != 'bg_service') b
on a.username=b.username
group by level
【tips:explode 外面不能加函数，distinct也不能加，且只能select一个字段，否则会报错。
一般搭配 lateral view使用】