数据分析基础问题总结——SQL(2)

1.sql中，如何求字段整体的标准差和均值？

均值使用聚合函数avg计算：

select avg(column) as mean from your_table;

标准差用聚合函数stdev计算：

select stdev(column) as std from your_table;

有些数据库中的标准差函数是stddev，注意区别。

2.sql中，什么是笛卡尔积？笛卡尔积一般出现在什么场景下？

笛卡尔积是指在两个或多个表之间进行连接操作时，将一个表的每一行与另一个表的每一行进行组合，生成一个包含所有可能组合的结果集。这种操作会导致结果集的行数成倍增加，可能会引发性能问题，因此需要谨慎使用。

笛卡尔积一般出现在以下场景中：

①未指定连接条件的情况：如果在进行表连接时没有明确指定连接条件，某些数据库系统可能会默认为进行笛卡尔积。

②交叉连接：有时候可能需要获得所有可能的组合，例如在某些报表或数据分析中。这种情况下可以明确使用'cross join'来获得笛卡尔积。

select * from table1cross join table2;
# 或者
select *from table1, table2;

3.sql中，rank, dense_mark, row_number的区别是什么?

在sql查询中，rank, dense_mark, row_number都是窗口函数，用于在结果集的窗口上进行排序和分配排名。它们在赋予排名时有一些不同之处：

①rank函数赋予相同值的行相同的排名，但会跳过后续排名。

②dense_mark函数赋予相同值的行相同的排名，但不会跳过后续排名。

③row_number函数无论值相同，都会为每一行分配一个唯一的连续整数排名。

4.在sql中，除了rank以外还有哪些窗口函数？

除了rank，sql还提供了许多其他窗口函数，用于在指定的范围内进行计算、排序和分析。以下是一些常见的窗口函数。

①dense_mark：分配连续的、不重复的排名给结果集中的行，相同的值会获得相同的排名，而且不会跳过排名。

②row_number：为结果集中的每一行分配唯一的、连续的整数排名，不考虑相同值的行。

③ntile(n)：将结果集分成n个大小相等的部分，并为每个部分的行分配一个整数值表示部分号（1到n）。

④lead(column , offset)：返回当前行之后的某一行中的列值。

⑤lag(column,offset)：返回当前行之前的某一行中的列值。

⑥first_value：获取分组集合中第一行的某个列值。

⑦last_value：获取分组集合中最后一行的某个列值。

⑧sum/avg/min/max(column) over(partition by ... order by ...)：计算指定窗口中某列的总和/平均值/最小/最大值。

5.简述max()聚合函数和窗口函数max(A) over(partition by B)的区别。

①max聚合函数：用于在查询中对某个列的值进行聚合计算，得出整个结果集的最大值。

②max窗口函数：在查询结果集的某个窗口（或分区）上进行计算的函数。窗口函数可以同时获得每个分区的最大值。

6.窗口函数和where的执行顺序孰先孰后？

在sql中，where子句通常会在窗口函数之前执行。

首先，数据库会根据where子句的条件对表进行筛选，过滤出符合条件的行。然后，在已经筛选出的结果集上，窗口函数开始计算。

这个执行顺序确保了窗口函数在筛选和过滤数据后进行计算，以及在计算窗口函数不会考虑不满足where子句条件的行。

7.sql中正则化函数一般如何使用？

在sql中，正则表达式函数（正则化函数）允许在文本数据中进行模式匹配、查找和替换。不同的数据库系统可能提供不同的正则化函数，但一般来说，它们都遵循类似的语法和用法。以下是一些常见的SQL正则表达式和使用方法：

①regexp 或 rlike：这些函数用于在文本列中进行正则表达式的匹配操作。它们一般用于select语句的where子句中。

select column from your_tablewhere column regexp 'pattern';

②regexp_replace：用于在文本列中使用正则表达式进行替换操作：

select regexp_replace(column,'pattern','replacement') from your_table;

③regexp_substr：用于从文本列中提取匹配正则表达式的子字符串：

select regexp_substr(column,'pattern') from your_table;

④locate：用于匹配子串在文本中出现的位置:

select column,locate('pattern',column) from your_table;

8.sql中如何将int类型的字段转换为string类型？

以下是常见示例：

# mysql
select cast(int_column as char) as string_column from your_table;
# 或者
select convert(int_column,char) as string_column from your_table;
# sql server:
select cast(int_column as varchar) as string_column from your_table;
# 或者
select convert(varchar,int_column) as string_column from your_table;

9.sql中，left join , right join , inner join 有什么差别？

left join , right join 和 inner join 都是用于在sql查询中进行表连接操作的关键字，它们之间的差别如下：

①inner join返回两个表中的匹配行，如果某行一个表中找不到匹配的行，那么这个行不会在结果中显示。

②left join返回左表中的所有行以及右表中与坐标匹配的行。如果在右表中找不到匹配的行，那么对应的右表列将会显示为NULL。

③right join返回右表的所有行，以及左表中与右表匹配的行。如果在左表中找不到匹配的行，那么对应的左表列将会显示为NULL。

join操作的效率通常比单纯使用子查询的效率要高，但应尽量避免多重嵌套join或join多张表，以免影响查询性能。

10.sql如何将类型为float的字段保留两位小数？

可使用格式化函数来将float类型的字段保留指定的小数位数。以下是一些通用的方法：

# mysql 使用format函数
select format(float_column,2) as formatted_float from your_table;
# sql server
select round(float_column,2) as formatted_float from your_table;