postgresql 模糊搜索 匹配度排序

基础查询

常量和运算

要求:

• 从名为student的数据表中选择出所有学生的姓名（name）和分数（score），并且额外计算出分数的 2 倍（double_score）

select name, score, score * 2 as double_score from student;

条件查询 – 空值 – IS NOT NULL

要求:

• 从名为 student 的数据表中选择出所有学生的姓名（name）、年龄（age）和成绩（score），要求学生年龄不为空值。

select name, age, score from student where age is not null;

条件查询 – 模糊查询 – LIKE / NOT LIKE

要求:

• 从名为 student 的数据表中选择出所有学生的姓名（name）和成绩（score），要求姓名（name）不包含 “李” 这个字。

select name, score from student where name not like '%李%';

去重 – DISTINCT

要求:

• 从名为 student 的数据表中选择出所有不重复的班级 ID（class_id）和考试编号（exam_num）的组合

select distinct class_id, exam_num from student;

时间函数 – DATE()

包含以下字段：name（姓名）、age（年龄）。
请你编写一个 SQL 查询，展示所有学生的姓名（name）和当前日期（列名为 “当前日期”）

select name, date_format(now(), '%Y-%m-%d') 当前日期 from student;

日期格式化参数

条件分支 — CASE WHEN

要求:

• 假设有一个学生表 student，包含以下字段：name（姓名）、age（年龄）。请你编写一个 SQL 查询，将学生按照年龄划分为三个年龄等级（age_level）：60 岁以上为 “老同学”，20 岁以上（不包括 60 岁以上）为 “年轻”，20 岁及以下、以及没有年龄信息为 “小同学”。
  返回结果应包含学生的姓名（name）和年龄等级（age_level），并按年龄等级降序, 姓名升序排序。

select name, case when (age > 60) then '老同学' when (age > 20) then '年轻' else '小同学' end as age_level
from student
order by age_level desc, name;

字符串处理函数 – UPPER、LOWER、LENGTH

筛选出姓名为 ‘热dog’ 的学生，展示其学号（id）、姓名（name）、大写姓名（upper_name）、小写姓名(lower_name)、名字长度(length_name)

select id, name, upper(name) as upper_name, lower(name) as lower_name, length(name) as length_name
from student;

聚合函数 – COUNT、SUM、AVG、MAX、MIN

要求:

• 汇总学生表中所有学生的总成绩（total_score）、平均成绩（avg_score）、最高成绩（max_score）和最低成绩（min_score）, 计算学生数量, 并且去姓名重

select sum(score)           as total_score,
       avg(score)           as avg_score,
       max(score)           as max_score,
       min(score)           as min_score,
       count(distinct name) as count_name
from student;

分组聚合 – 单字段分组

要求:

• 统计学生表中的班级编号（class_id）和每个班级的平均成绩（avg_score）

select class_id, avg(score) as avg_score
from student
group by class_id;

分组聚合 – 多字段分组

要求:

• 请你编写一个 SQL 查询，统计学生表中每个班级每次考试的总学生人数（total_num）

select class_id, exam_num, count(*) as total_num
from student
group by class_id, exam_num;

分组聚合 – having 子句

要求:

• 统计学生表中班级的总成绩超过 150 分的班级编号（class_id）和总成绩（total_score）

select class_id, sum(score) as total_score
from student
group by class_id
having sum(score) > 150;

进阶查询

关联查询 – CROSS JOIN (笛卡尔积)

要求:

• 将学生表和班级表的所有行组合在一起，并返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）以及班级名称（class_name）。

select s.class_id as class_id, c.name as class_name, s.name as stduent_name, s.age as student_age
from student s
         cross join class c;

关联查询 – INNER JOIN – 自查询

要求:

• 根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）、班级名称（class_name）、班级级别（class_level）

select s.class_id as class_id,
       c.name     as class_name,
       s.name     as student_name,
       c.level    as class_level,
       s.age      as student_age
from student s
         inner join class c on s.class_id = c.id;

关联查询 – LEFT OUTER JOIN(等同于LEFT JOIN) / RIGHT OUTER JOIN(等同于RIGHT JOIN)

要求:

• 根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）、班级名称（class_name）、班级级别（class_level），要求必须返回所有学生的信息（即使对应的班级编号不存在）

-- 左连接
select s.name     as student_name,
       s.age      as student_age,
       s.class_id as class_id,
       c.name     as class_name,
       c.level    as class_level
from student s
         left join class c on s.class_id = c.id;

-- 右连接
select s.name     as student_name,
       s.age      as student_age,
       s.class_id as class_id,
       c.name     as class_name,
       c.level    as class_level
from class c
         right outer join student s on s.class_id = c.id;

开窗函数 - SUM / AVG / MAX / MIN / COUNT OVER

语法: SUM(计算字段名) OVER (PARTITION BY 分组字段名)
要求:

• 返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并计算每个班级的学生平均分（class_avg_score）

select *, avg(score) over (partition by class_id) as class_avg_score
from student;

开窗函数 – SUM OVER ORDER BY

语法: SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)
要求:

• 返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数升序的方式累加计算每个班级的学生总分（class_sum_score）

select *, sum(score) over (partition by class_id order by score asc) as class_sum_score
from student;

开窗函数 – RANK() OVER

语法: RANK() OVER (PARTITION BY 分组字段名 ORDER BY 排序字段名 [ASC][DESC])
要求:

• 返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式计算每个班级内的学生的分数排名（ranking）

select *, rank() over (partition by class_id order by score desc) as ranking
from student;

开窗函数 – ROW_NUMBER() OVER

语法: ROW_NUMBER() OVER (PARTITION BY 分组字段名 ORDER BY 排序字段名 [ASC][DESC])
要求:

• 返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式给每个班级内的学生分配一个编号（ranking）

select *, row_number() over (partition by class_id order by score desc) as ranking
from student;

开窗函数 – LAG / LEAD

语法:

• Lag 函数:
  Lag 函数用于获取 当前行之前 的某一列的值。它可以帮助我们查看上一行的数据。
• Lag 函数的语法如下：
• LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
• 参数解释：
• column_name：要获取值的列名。
• offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推。
• default_value：可选参数，用于指定当没有前一行时的默认值。
• PARTITION BY和ORDER BY子句可选，用于分组和排序数据。
• Lead 函数
• Lead 函数用于获取 当前行之后 的某一列的值。它可以帮助我们查看下一行的数据。
• Lead 函数的语法如下：
• LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
• 参数解释：
• column_name：要获取值的列名。
• offset：表示要向下偏移的行数。例如，offset为1表示获取下一行的值，offset为2表示获取下两行的值，以此类推。
• default_value：可选参数，用于指定当没有后一行时的默认值。
• PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

要求:

• 返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式获取每个班级内的学生的前一名学生姓名（prev_name）、后一名学生姓名（next_name）

select *,
       lag(name, 1) over (partition by class_id order by score desc)  as prev_name,
       lead(name, 1) over (partition by class_id order by score desc) as next_name
from student;