登录
  • #机器学习

My‌‌‍‍‌‍‍‌‍‌‍‍‌‍‍‍‌‌‌‌‌‌‌‌‌‍‍‍‌‌‍‍SQL 面试准备

nicecoco
561
1
CTE(Common Table Expression) 和 Temp tables 的区别

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:



  1. Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

  2. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

  3. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

  4. Reference the resulting table multiple times in the same statement.



公用表表达式(CTE)可以被认为是在单个SELECT,INSERT,UPDATE,DELETE或CREATE VIEW语句的执行范围内定义的临时结果集。 CTE类似于派生表,因为它不作为对象存储,并且仅在查询期间持续。与派生表不同,CTE可以是自引用的,并且可以在同一查询中多次引用。

CTE可用于:



  1. 创建递归查询。有关更多信息,请参阅使用公用表表达式的递归查询。

  2. 在不需要一般使用视图时替换视图;也就是说,您不必将定义存储在元数据中。

  3. 通过从标量子选择派生的列或不具有确定性或具有外部访问权限的函数启用分组。

  4. 在同一语句中多次引用结果表。



临时表Temp tables

临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。

Window Functions

window functions真的很强大而且也是面试高危问题,以下内容整理自MySQL的官方Reference Manual dev.mysql.com

Null的处理问题:

Some window functions permit anull_treatmentclause that specifies how to handle NULL values when calculating results. This clause is optional. It is part of the SQL standard, but the MySQL implementation permits onlyRESPECT NULLS(which is also the default). This means thatNULLvalues are considered when calculating results.

Over_clause: 一般包含partition by和order by

Window function最大的特点在于:window operations do not collapse groups of query rows to a single output row. Instead, they produce a result for each row. Like the preceding queries, the following query usesSUM(), but this time as a window function: (也就是说 所有的aggregation function是都可以用于window function的)

window_function的含义:

1) CUME_DIST(): 返回cumulative distribution,range from 0 to 1. 要和order by一起用。分母是总共的行数,分子是排在这个value之前,包括这个value所占的所有行。

2)DENSE_RANK(): 排序without gaps,相同的value有相同的rank,要和order by一起用。RANK(): with gap,相同的value有相同的rank. ROW_NUMBER():就是添加标号。PERCENT_RANK():计算row relative rank,公式为(rank-1)/(rows-1)

3)FIRST_VALUE(expr)/LAST_VALUE(expr)/NTH_VALUE(expr,n) 它是一行一行看,截止到每一行的第一个值/最后一个值/第n个值

4)LAG(expr,N,default) 返回先于当前行N行的expr值,如果不存在那一行,返回default值。N和default在不规定值的时候是1和NULL。一般用于计算differences between rows。另一个放在一起说的是 LEAD(),用法一样,返回后面的第N行的值。

5)NTILE(N) 分箱 分成N个箱子,返回这一行的值在第几个箱子,比如可以取出前后百分之多少的记录。

Frame Specification:是针对current row的,也就是这个frame是可以跟着当前行move,所以我们就可以算各种running和moving!

frame clause的语法是什么样子呢?它分成frame_units和frame_extent两部分。

The frame_units value indicates the type of relationship between the current row and frame rows: (frame从哪到哪 如何定义的)

ROWS: The frame is defined by beginning and ending row positions. Offsets are differences in row numbers from the current row number.

RANGE: The frame is defined by rows within a value range. Offsets are differences in row values from the current row value.

2. Theframe_extentvalue indicates the start and end points of the frame. You can specify just the start of the frame (in which case the current row is implicitly the end) or useBETWEENto specify both frame endpoints:

只用frame_start: 默认结束是current row.

用frame_between: 规定起始 BETWEEN frame_start AND frame_end,有这么一些值可选择:

CURRENT ROW: For ROWS, the bound is the current row. For RANGE, the bound is the peers of the current row.

UNBOUNDED PRECEDING: The bound is the first partition row.

UNBOUNDED FOLLOWING: The bound is the last partition row.

exprPRECEDING: ForROWS, the bound isexprrows before the current row. ForRANGE, the bound is the rows with values equal to the current row value minusexpr; if the current row value isNULL, the bound is the peers of the row.

exprFOLLOWING: ForROWS, the bound isexprrows after the current row. ForRANGE, the bound is the rows with values equal to the current row value plusexpr; if the current row value isNULL, the bound is the peers of the row.

Finally,你可以给你的window命名,也可以嵌套。
1条回复
热度排序

发表回复