一、SQL
1.union和union all的区别
二者都是SQL中用于合并两个或多个SELECT语句的关键字,区别在于如何处理重复行
1️⃣union去除重复行,返回唯一的行
2️⃣union all不去除重复的行,所有行都保留
2.几种窗口函数及区别
1️⃣ROW_NUMBER()
为结果集中的每一行分配一个唯一的整数值,按照指定的排序顺序进行排序
若有两个或多个行排序条件相同,则每行都会分配一个不同的序号
返回唯一的行号
2️⃣RANK()
为结果集中的每一行分配一个排名值,按照指定的排序顺序进行排序
若有两个或者多个行具有相同的排序条件,则会被分配相同的排名值,并且下一个行将跳过相应数量的排名值
返回相同排序值的相同排名
3️⃣DENSE_RANK()
与RANK()类似,也为结果集中的每一行分配一个排名值,胆不会跳过排名值
eg:3个行有相同排序值,则分配为1,1,1,然后下一行被分配排名值为2若是rank函数则为4
返回相同排序值的不同排名
4️⃣NTILE()
将结果集划分为指定数量的桶,并为每个桶分配一个编号,从1开始
如果结果集中的行数不能被桶的数量整除,最后一个桶将包含剩余的行
将结果集划分为指定数量的桶
3.CASE WHEN THEN ELSE END
CASE WHEN
是 SQL 中的一个条件表达式,可以用来实现条件逻辑。它允许根据条件来返回不同的值。您可以使用 CASE WHEN
语句来实现类似 IF-THEN-ELSE
的逻辑。具体分为两种情况:
简单形式:这种形式适用于在 expression
的值等于某个特定值时返回对应的结果。
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END
搜索形式:这种形式适用于根据条件来返回结果。
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
4.计算中位数
(1)使用变量的方法:
这是一种传统的方法,适用于MySQL 5.7及以下版本。
示例表结构和数据:
CREATE TABLE tb_video_info (
video_id INT,
tag VARCHAR(255),
like_cnt INT,
start_time DATETIME
);
INSERT INTO tb_video_info (video_id, tag, like_cnt, start_time) VALUES
(1, 'tag1', 10, '2021-10-01 10:00:00'),
(2, 'tag1', 20, '2021-10-01 10:10:00'),
(3, 'tag1', 30, '2021-10-01 10:20:00'),
(4, 'tag2', 40, '2021-10-01 10:30:00'),
(5, 'tag2', 50, '2021-10-01 10:40:00');
查询中位数代码如下:
SET @row_number := 0;#初始化一个变量用于记录行号。
SELECT
AVG(like_cnt) AS median_val
FROM (
SELECT
@row_number := @row_number + 1 AS row_number,
like_cnt
FROM
tb_video_info
ORDER BY
like_cnt
) AS ranked_likes
WHERE
row_number IN (FLOOR((@row_number + 1) / 2), CEIL((@row_number + 1) / 2));
#子查询中,使用变量 @row_number 对 like_cnt 进行排序,并为每行分配一个行号
#在外层查询中,选择行号在中间位置的记录并计算其平均值,这就是中位数
FLOOR
函数返回小于或等于给定数值的最大整数。CEIL
函数(或CEILING
函数)返回大于或等于给定数值的最小整
(2)使用窗口函数的方法:
适用于MySQL 8.0及以上版本
WITH ordered_likes AS (
SELECT
like_cnt,
ROW_NUMBER() OVER (ORDER BY like_cnt) AS row_number,
COUNT(*) OVER () AS total_rows
FROM tb_video_info
)
SELECT
AVG(like_cnt) AS median_val
FROM ordered_likes
WHERE
row_number IN (FLOOR((total_rows + 1) / 2), CEIL((total_rows + 1) / 2));
#使用窗口函数:ROW_NUMBER() OVER (ORDER BY like_cnt) AS row_number:为每行分配一个排序后的行号。
#COUNT(*) OVER () AS total_rows:计算总行数。
#在外层查询中,