当前位置:   article > 正文

Sql 语言常用函数_sql dateformat

sql dateformat

时间函数

date_format (date,format)

  1. date_format 函数用于以不同的格式显示日期/时间数据,即用来转化时间格式
  2. 可以提取对应的日期/时间的参数来进行统计
  3. date 参数是合法的日期或者日期时间,format规定日期/时间的输出格式
  • 一般格式∶date_format (payDate,'%Y-%m-%d')
  1. 显示订单创建时间到 day
  2. 提取对应的日期/时间的参数来进行统计
  1. -- 提取订单创建时间,只显示年月日
  2. select date_format(create_time,'%y-%m-%d') as dday
  3. from db_order.sn_order_20210412;
  4. -- 对比
  5. select create_time
  6. from db_order.sn_order_20210412;
  1. -- 以小时为时间维度,统计2017年销售金额在不同时间段的分布情况 %H/%h
  2. select date_format(create_time,'%H') as hour1
  3. ,date_format(create_time,'%h') as hour2
  4. ,sum(order_money)
  5. from db_order.sn_order_20210412
  6. group by hour1
  7. ,hour2;
  8. -- 统计每天的销售金额 %j:显示一年中的第几天
  9. select date_format(create_time,'%j') as dday
  10. ,sum(order_money)
  11. from db_order.sn_order_20210412
  12. group by dday;
  13. -- 以星期为时间维度,统计2017年销售金额周一到周日的分布情况 %W/%w
  14. select date_format(create_time,'%W') as week1
  15. ,date_format(create_time,'%w') as week2
  16. ,sum(order_money)
  17. from db_order.sn_order_20210412
  18. group by week1
  19. ,week2;
  20. -- 统计每个月的销售金额 %M/%m
  21. select date_format(create_time,'%M') as month1
  22. ,date_format(create_time,'%m') as month2
  23. ,sum(order_money)
  24. from db_order.sn_order_20210412
  25. group by month1
  26. ,month2;

时间函数的常用时间格式(自查用):

常用时间函数

注意事项: 

  • 注意dayofweek和weekday的区别:
  1. 开始时间不一样,两个函数计算的数字各不一样
  2. weekday:0-6   0是周一,6是周日
  3. dayofweek:1-7 1是周日,7是周六
  1. select weekday('20210508') as day1
  2. ,dayofweek('20210508') as day2;

时间的加减

date_add:

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

datediff() :返回相差的天数;   

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

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

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

  1. select create_time
  2. ,date_add(create_time,interval 3 day) as day3
  3. ,date_add(create_time,interval 3 hour) as hour3
  4. ,date_add(create_time,interval -3 hour) as hour_3
  5. ,date_add(create_time,interval -3.2 hour) as hour__3
  6. ,date_add(create_time,interval -3.6 hour) as hour_4
  7. from db_order.sn_order_20210412;
  8. select datediff('20210501','20210508') as date1;
  9. select timediff('14:05:30','17:28:50') as time1;
  10. select ('2021-05-08') as day0
  11. ,str_to_date('2021-05-08','%y-%m-%d') as day1
  12. ,str_to_date('2021-05-08','%Y-%m-%d') as day2
  13. ,str_to_date('2021-05-08','%Y-%M-%D') as day3
  14. ,str_to_date('21-05-08','%Y-%M-%D') as day4;

字符函数

  1. -- length 读取字符长度
  2. select id
  3. ,length(id) as l_id
  4. from db_order.sn_order_20210412;
  5. -- left 截取字符长度,从左边开始,截取id的前三个字符
  6. select id
  7. ,left(id,3)
  8. from db_order.sn_order_20210412;
  9. -- substring:返回的是,从你指定位置开始的字符串
  10. /* 第二个参数:是指开始截取的位置
  11. * 第三个参数(可选):如果第三个参数没有指定,则说明从第二个参数开始到最后,如果指定,则就是指定相应的长度
  12. */
  13. select id
  14. ,substring(id,2,1) as id_1
  15. ,substring(id,2,3) as id_3
  16. from db_order.sn_order_20210412;
  17. -- concat 拼接函数,SQL没有%形式,如果数据需要带%可以使用拼接函数
  18. select concat(3,'%');
  19. -- position('N' in ) 位置函数 /查找第一次出现2的位置
  20. select id
  21. ,position('2' in id) as 2_frist
  22. from db_order.sn_order_20210412;
  23. -- 从2开始,往后截取3位
  24. select id
  25. ,substring(id,position('2' in id),3)
  26. from db_order.sn_order_20210412;

数字函数

  • truncate:必须带小数的参数,要不然会报错
  • ceiling:向上取整,返回不小于自身的整数 
  1. 运用函数取值,比较函数之间的差异
  2. 如果订单金额大于50则满足抽奖条件,随机抽取2名作为得奖   rand
  1. -- 取值
  2. select order_money
  3. ,round(order_money) as r_1 /*会进行四舍五入*/
  4. ,round(order_money,1) as r_11 /*只截取数据,不进行四舍五入,但一定要有Y*/
  5. ,truncate(order_money,0)as r_2
  6. ,truncate(order_money,1) as r_21
  7. ,ceiling(order_money) as c_1 /*向上取整*/
  8. from db_order.sn_order_20210412;
  9. /*
  10. * 如果订单金额大于50则满足抽奖条件,随机抽取2名作为得奖
  11. *1、用随机函数 rand 构建得奖名单
  12. *2、rand(N) 加入种子,可以让每次运行的结果保持不变,N为任意值
  13. * */
  14. select id
  15. ,order_money
  16. ,member_id
  17. ,rand() as r1
  18. ,rand(200) as r2
  19. from db_order.sn_order_20210412
  20. where order_money > 50
  21. order by rand(200) desc
  22. limit 2;

控制函数

 控制函数 case when then end /  if

  •  case when
  1.  case 开始,end 结束;
  2.  when 可以有多条,根据你的条件判断内容
  3.  else 不是必须的,可选—when 中各种情况 下一条件 之外概括 (除此之外)
  •  if
  1. if 如果某个条件成立,则选择第二个参数;可以嵌套使用
  2. 用 if 嵌套实现 case when 的内容,分成高、中、低
  1. -- 对客单价进行划分,分成高中低三个档次
  2. -- case
  3. select id
  4. ,order_money
  5. ,case when order_money <= 50 then '低'
  6. when order_money >50 and order_money <=100 then '中'
  7. else '高'
  8. end as type_1
  9. ,case when order_money <= 50 then '低'
  10. when order_money >50 and order_money <=100 then '中'
  11. when order_money >100 then '高'
  12. else '其他'
  13. end as type_2
  14. from db_order.sn_order_20210412;
  15. -- 统计高、中、低(按客单价进行划分)三个档次中的 订单量 销售金额 (去除空值)
  16. select case when order_money <= 50 then '低'
  17. when order_money >50 and order_money <=100 then '中'
  18. else '高'
  19. end as type_1
  20. ,count(distinct member_id)
  21. ,sum(order_money)
  22. from db_order.sn_order_20210412
  23. where order_money is not null /*去除空值*/
  24. group by type_1;
  25. -- if
  26. select id
  27. ,order_money
  28. ,if(order_money>200,'high','low') as type1
  29. from db_order.sn_order_20210412;
  30. -- 用 if 嵌套实现 case when 的内容,分成高、中、低 Tip:需要从处于中间的数值入手
  31. select id
  32. ,order_money
  33. ,case when order_money <= 50 then '低'
  34. when order_money >50 and order_money <=100 then '中'
  35. else '高'
  36. end as type_1
  37. ,if (order_money<=100,if(order_money>50,'中','低'),'高') as type_2
  38. from db_order.sn_order_20210412;

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

 

 

 

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/741481
推荐阅读
相关标签
  

闽ICP备14008679号