SQL学习笔记之日期函数

1. date_format(date, format) 函数：根据format字符串格式化date值

   format的值可由下组合

    %M 月名字(January……December) 
   %W 星期名字(Sunday……Saturday) 
   %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
   %Y 年, 数字, 4 位 
   %y 年, 数字, 2 位 
   %a 缩写的星期名字(Sun……Sat) 
   %d 月份中的天数, 数字(00……31) 
   %e 月份中的天数, 数字(0……31) 
   %m 月, 数字(01……12) 
   %c 月, 数字(1……12) 
   %b 缩写的月份名字(Jan……Dec) 
   %j 一年中的天数(001……366) 
   %H 小时(00……23) 
   %k 小时(0……23) 
   %h 小时(01……12) 
   %I 小时(01……12) 
   %l 小时(1……12) 
   %i 分钟, 数字(00……59) 
   %r 时间,12 小时(hh:mm:ss [AP]M) 
   %T 时间,24 小时(hh:mm:ss) 
   %S 秒(00……59) 
   %s 秒(00……59) 
   %p AM或PM 
   %w 一个星期中的天数(0=Sunday ……6=Saturday ） 
   %U 星期(0……52), 这里星期天是星期的第一天 
   %u 星期(0……52), 这里星期一是星期的第一天 
   %% 一个文字“%”

   代码实例：题目来源于牛客网SQL169

   SELECT product_id,
   ROUND(SUM(if_click)/COUNT(*),3) AS ctr,
   ROUND(IF(SUM(if_click)>0,SUM(if_cart)/SUM(if_click),0),3) AS cart_rate,
   ROUND(IF(SUM(if_cart)>0,SUM(if_payment)/SUM(if_cart),0),3) AS payment_rate,
   ROUND(IF(SUM(if_payment)>0,SUM(if_refund)/SUM(if_payment),0),3) AS refund_rate
   FROM tb_user_event
   WHERE DATE_FORMAT(event_time,'%Y-%m') = '2021-10'
   GROUP BY product_id
   HAVING refund_rate <= 0.5
   ORDER BY product_id

   犯错点：函数名为DATE_FORMAT而不是DATEFORMAT；format参数的格式要与输出格式相同，本题都为字符串格式，都要加单引号

2. DATE_SUB(date,INTERVAL expr type)：返回从指定日期date减去指定时间间隔expr的日期

   type的值可以如下：

   MICROSECOND

   SECOND

   MINUTE

   HOUR

   DAY

   WEEK

   MONTH

   QUARTER

   YEAR

   SECOND_MICROSECOND

   MINUTE_MICROSECOND

   MINUTE_SECOND

   HOUR_MICROSECOND

   HOUR_SECOND

   HOUR_MINUTE

   DAY_MICROSECOND

   DAY_SECOND

   DAY_MINUTE

   DAY_HOUR

   YEAR_MONTH

   代码实例：题目来源于牛客网SQL171

   SELECT product_id,
       ROUND(SUM(repurchase) / COUNT(repurchase), 3) as repurchase_rate
   FROM (
       SELECT uid, product_id, IF(COUNT(event_time)>1, 1, 0) as repurchase
       FROM tb_order_detail
       JOIN tb_order_overall USING(order_id)
       JOIN tb_product_info USING(product_id)
       WHERE tag="零食" AND event_time >= (
           SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
           FROM tb_order_overall
       )
       GROUP BY uid, product_id
   ) as t_uid_product_info
   GROUP BY product_id
   ORDER BY repurchase_rate DESC, product_id
   LIMIT 3;
   

3.  last_day(date) ：返回月份中的最后一天

4. DAYOFMONTH(date)：返回日期在本月的第几天，范围为1到31。

date可以是指定的具体日期，也可以是日期表达式。可与last_day（）函数联用计算出某日期所在月份的天数。

代码实例：题目来源于牛客网SQL127

select ifnull(y_m, '2021汇总') as submit_month,
    count(1) as month_q_cnt,
    round(count(1) / max(days_of_month), 3) as avg_day_q_cnt
from (
    select question_id,
        DAYOFMONTH(LAST_DAY(submit_time)) as days_of_month,
        DATE_FORMAT(submit_time, "%Y%m") as y_m
    from practice_record
    where year(submit_time)='2021'
) as t_month_stat
group by y_m
with rollup