SQLBoy之LC刷题函数篇

SQLBoy之LC刷题函数篇

时间篇

 1.时间差值函数
 datediff (date1,date2): res = days of from date1 to date2 
						if date1 > date2 IS positive number
						else IS negative number
Attention : return Interger Not Boolean
e.g. :  datediff("2023-05-02","2023-05-01")  # 1

2.日期 +N / -N函数
addDate(date,INTERVAL expr unit) #expr （time interval）：time to add positive number +  /  negative number -
								 #unit  : day、minute、hour、second  default：day
detail from https://blog.csdn.net/weixin_46082526/article/details/108044313

3.计算相差日期
timeStampDiff（unit，date1，date2）Calculate the difference based on units
e.g. ：TIMESTAMPDIFF(HOUR, '2023-03-22 07:00:00', '2023-03-22 18:00:00'); # 11

String篇

1、Contact函数的使用
contact(str1,str2,...) multiple strings to one string
e.g. : contact("hello","、","world")   # hello、world

2、group_contact函数的使用
group_concat([distinct] 要连接的字段 [order by 排序字段] [separator '分隔符'])
e.g. : 
INPUT：
Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+
OUTPUT：
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
Answer:
select sell_date,count(distinct product) as num_sold,GROUP_CONCAT(distinct product) products
from Activities 
group by sell_date;

3、left，right函数的使用
left：
format:left(expression,length) 
return leftmost characters of a expression
e.g. ： left("hello",2)   # he

right：
format:right(expression,length) 
return rightmost characters of a expression
e.g. ： right("hello",3)   # llo

4、lower，upper函数的使用
lower：
format：lower(expression)
Convert all characters to lowercase
e.g. ： lower("HeLLo")   # hello
upper:
format：upper(expression)
Convert all characters to uppercase
e.g. ： lower("HeLLo")   # HELLO

Over窗口函数

over（ [ PARTITION BY column ] [ ORDER BY culumn ] ）

The OVER window function must be used together with the aggregate function or sorting function. The aggregate function generally refers to common functions such as SUM(), MAX(), MIN, COUNT(), AVG(), etc. Sort functions generally refer to RANK(), ROW_ NUMBER(),DENSE_ RANK(), NTILE(), etc

row_number()

ROW_NUMBER()
Sort the data found by SELECT, adding a serial number to each piece of data. It cannot be used as a ranking for student grades, as there are duplicates in student grades. Rank() is best for ranking grades

rank()

RANK()
The RANK() function, as the name suggests, can rank a certain field. And row_ The difference between number() is that it will be ranked side by side

dense_rank()

It is also a ranking function, similar to the RANK() function, but it will continue to sort by this sequence number after it ranks side by side, while RANK() will default to skipping those sequence numbers
For example, there are three places in the first place, using dense_rank() of the next person in rank () is 2. And Rank()'s ranking is 4

ratio_to_report()

ratio_to_report() over(partition by … order by …) 求百分率 配合round函数进行优化结果百分比
Ratio_to_report() 括号中就是分子，over() 括号中就是分母。

其他函数

1、空值赋值函数
IFNULL(expression, alt_value) 
if expression is null change to alt_value
e.g. ：ifnull(referee_id,0)<>2;  if referee_id is null change referee_id to 0 

2、if函数的使用
if(expr,res1,res2) expr is boolean type. if true return res1 else res2 
expr have exprs use && or || 
e.g. ：Can a triangle be formed 
select x,y,z,if(x+y>z && x-y<z && x-z<y && y-z<x,"Yes","No") as triangle  
from Triangle ;