Sql 语言常用函数

时间函数

date_format (date,format)

1. date_format 函数用于以不同的格式显示日期/时间数据，即用来转化时间格式
2. 可以提取对应的日期/时间的参数来进行统计
3. date 参数是合法的日期或者日期时间，format规定日期/时间的输出格式

• 一般格式∶date_format (payDate,'%Y-%m-%d')

1. 显示订单创建时间到 day
2. 提取对应的日期/时间的参数来进行统计

-- 提取订单创建时间,只显示年月日
select date_format(create_time,'%y-%m-%d') as dday
from db_order.sn_order_20210412;
-- 对比
select create_time
from db_order.sn_order_20210412;

-- 以小时为时间维度，统计2017年销售金额在不同时间段的分布情况 %H/%h
select date_format(create_time,'%H') as hour1
      ,date_format(create_time,'%h') as hour2
      ,sum(order_money)
from db_order.sn_order_20210412
group by hour1
        ,hour2;
-- 统计每天的销售金额 %j:显示一年中的第几天
select date_format(create_time,'%j') as dday
      ,sum(order_money) 
from db_order.sn_order_20210412
group by dday;
-- 以星期为时间维度，统计2017年销售金额周一到周日的分布情况 %W/%w 
select date_format(create_time,'%W') as week1
      ,date_format(create_time,'%w') as week2
      ,sum(order_money) 
from db_order.sn_order_20210412
group by week1
        ,week2;
-- 统计每个月的销售金额 %M/%m
select date_format(create_time,'%M') as month1
      ,date_format(create_time,'%m') as month2
      ,sum(order_money) 
from db_order.sn_order_20210412
group by month1
        ,month2;

时间函数的常用时间格式（自查用）：

常用时间函数

注意事项： 

• 注意dayofweek和weekday的区别：

1. 开始时间不一样，两个函数计算的数字各不一样
2. weekday:0-6   0是周一，6是周日
3. dayofweek:1-7 1是周日，7是周六

select weekday('20210508') as day1
      ,dayofweek('20210508') as day2; 

时间的加减

date_add:

1.  用于日期的增减，可以用任意的粒度去描述
2. 从年到秒，如果要相减的常量有小数，会自动进行四舍五入，然后再相减 

datediff() ：返回相差的天数；   

timediff() ：返回时、分、秒格式

注意：大的日期/时间需要在第一个位置位置，则返回正数，否则返回负数

str_to_date: 需要固定格式 0000-00-00,%Y-%m-%d 否则返回为空

select create_time  
      ,date_add(create_time,interval 3 day) as day3
      ,date_add(create_time,interval 3 hour) as hour3
      ,date_add(create_time,interval -3 hour) as hour_3
      ,date_add(create_time,interval -3.2 hour) as hour__3
      ,date_add(create_time,interval -3.6 hour) as hour_4 
from db_order.sn_order_20210412;

select datediff('20210501','20210508') as date1;
select timediff('14:05:30','17:28:50') as time1;

select ('2021-05-08') as day0
      ,str_to_date('2021-05-08','%y-%m-%d') as day1
      ,str_to_date('2021-05-08','%Y-%m-%d') as day2
      ,str_to_date('2021-05-08','%Y-%M-%D') as day3
      ,str_to_date('21-05-08','%Y-%M-%D') as day4;

字符函数

-- length 读取字符长度
select id 
      ,length(id) as l_id
from db_order.sn_order_20210412;
-- left 截取字符长度,从左边开始，截取id的前三个字符
select id
      ,left(id,3) 
from db_order.sn_order_20210412;
-- substring:返回的是，从你指定位置开始的字符串
 /* 第二个参数：是指开始截取的位置
 * 第三个参数（可选）：如果第三个参数没有指定，则说明从第二个参数开始到最后，如果指定，则就是指定相应的长度
 */
select id 
      ,substring(id,2,1) as id_1
      ,substring(id,2,3) as id_3 
from db_order.sn_order_20210412;
-- concat 拼接函数,SQL没有%形式，如果数据需要带%可以使用拼接函数
select concat(3,'%');
-- position('N' in  ) 位置函数 /查找第一次出现2的位置
select id
      ,position('2' in id) as 2_frist
from db_order.sn_order_20210412;
-- 从2开始，往后截取3位
select id 
      ,substring(id,position('2' in id),3) 
from db_order.sn_order_20210412;

数字函数

• truncate:必须带小数的参数，要不然会报错
• ceiling:向上取整，返回不小于自身的整数 

1. 运用函数取值，比较函数之间的差异
2. 如果订单金额大于50则满足抽奖条件，随机抽取2名作为得奖   rand

-- 取值
select order_money 
      ,round(order_money) as r_1         /*会进行四舍五入*/
      ,round(order_money,1) as r_11      /*只截取数据，不进行四舍五入，但一定要有Y*/
      ,truncate(order_money,0)as r_2
      ,truncate(order_money,1) as r_21
      ,ceiling(order_money) as c_1       /*向上取整*/
from db_order.sn_order_20210412;

/*
 * 如果订单金额大于50则满足抽奖条件，随机抽取2名作为得奖
 *1、用随机函数 rand 构建得奖名单
 *2、rand(N) 加入种子，可以让每次运行的结果保持不变，N为任意值
 * */
select id 
      ,order_money 
      ,member_id 
      ,rand() as r1
      ,rand(200) as r2
from db_order.sn_order_20210412
where order_money > 50
order by rand(200) desc
limit 2;

控制函数

 控制函数 case when then end /  if

•  case when

1.  case 开始，end 结束;
2.  when 可以有多条，根据你的条件判断内容
3.  else 不是必须的，可选—when 中各种情况 下一条件 之外概括 （除此之外）

•  if

1. if 如果某个条件成立，则选择第二个参数；可以嵌套使用
2. 用 if 嵌套实现 case when 的内容，分成高、中、低

-- 对客单价进行划分，分成高中低三个档次
-- case 
select id 
      ,order_money 
      ,case when order_money <= 50 then '低'
            when order_money >50 and order_money <=100 then '中'
            else '高'
       end as type_1
      ,case when order_money <= 50 then '低'
            when order_money >50 and order_money <=100 then '中'
            when order_money >100 then '高'
            else '其他'
       end as type_2
from db_order.sn_order_20210412;
-- 统计高、中、低(按客单价进行划分)三个档次中的 订单量 销售金额 （去除空值）
select case when order_money <= 50 then '低'
            when order_money >50 and order_money <=100 then '中'
            else '高'
       end as type_1
      ,count(distinct member_id)
      ,sum(order_money) 
from db_order.sn_order_20210412
where order_money is not null                   /*去除空值*/
group by type_1;
-- if 
select id 
      ,order_money 
      ,if(order_money>200,'high','low') as type1
from db_order.sn_order_20210412;
-- 用 if 嵌套实现 case when 的内容，分成高、中、低 Tip:需要从处于中间的数值入手
select id 
      ,order_money 
      ,case when order_money <= 50 then '低'
            when order_money >50 and order_money <=100 then '中'
            else '高'
       end as type_1
      ,if (order_money<=100,if(order_money>50,'中','低'),'高') as type_2
from db_order.sn_order_20210412;

传送门：SQL基础语句—增删查改