🎉 黑五感恩回馈!VIP、蓝莓大促销,抢购开始!🦃 点击查看详情
查看: 547|回复: 1
收起左侧

MySQL 面试准备

|只看干货
nicecoco | 显示全部楼层 |阅读模式
本楼: 👍   100% (2)
 
 
0% (0)   👎
全局: 👍   99% (168)
 
 
0% (1)    👎

注册一亩三分地论坛,查看更多干货!

您需要 登录 才可以下载或查看附件。没有帐号?注册账号

x
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:

  • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
  • 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.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.


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

CTE可用于:

  • 创建递归查询。有关更多信息,请参阅使用公用表表达式的递归查询。
  • 在不需要一般使用视图时替换视图;也就是说,您不必将定义存储在元数据中。
  • 通过从标量子选择派生的列或不具有确定性或具有外部访问权限的函数启用分组。
  • 在同一语句中多次引用结果表。. From 1point 3acres bbs


临时表Temp tables

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

Window Functions
window functions真的很强大而且也是面试高危问题,以下内容整理自MySQL的官方Reference Manual https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

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行的值。. From 1point 3acres bbs

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


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大米 +10 收起 理由
bryanjhy + 10 欢迎分享你知道的情况,会给更多积分奖励!

查看全部评分


上一篇:DS/DA New Grad 2021.12 简历求狠批,加米!!
下一篇:入行6年,我的数据分析之路
QingZhang2050 2021-9-26 13:20:52 | 显示全部楼层
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   73% (28)
 
 
26% (10)    👎
不错不错不错不错不错不错不错不错不错
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册账号
隐私提醒:
  • ☑ 禁止发布广告,拉群,贴个人联系方式:找人请去🔗同学同事飞友,拉群请去🔗拉群结伴,广告请去🔗跳蚤市场,和 🔗租房广告|找室友
  • ☑ 论坛内容在发帖 30 分钟内可以编辑,过后则不能删帖。为防止被骚扰甚至人肉,不要公开留微信等联系方式,如有需求请以论坛私信方式发送。
  • ☑ 干货版块可免费使用 🔗超级匿名:面经(美国面经、中国面经、数科面经、PM面经),抖包袱(美国、中国)和录取汇报、定位选校版
  • ☑ 查阅全站 🔗各种匿名方法

本版积分规则

>
快速回复 返回顶部 返回列表