SQL执行原理

语法顺序： select—from—where—group by—having—order by
执行顺序： from—where—group by—having—select—order by

第一步 准备表格

复制源数据进行连接、筛选，分组，分组后过滤等操作

  • from
    • 执行顺序为从后往前、从右到左
      • 从后往前意味着，最后面的那个表名为驱动表，执行顺序为从后往前, 所以数据量较少的表尽量放后
    • 指定从哪些数据表读取数据
    • 从数据库中找到from后指定的表格，复制一张完全一样的表格用于查询处理
  • join
    • 将多个表格进行连接
  • where
    • 执行顺序为自下而上、从右到左
      • 根据这个原理，表之间的连接必须写在其他Where 条件之前, 可以过滤掉最大数量记录的条件必须写在Where 子句的末尾
    • 指定源数据的筛选条件
  • group by
    • 执行顺序从左往右分组
    • 对数据进行分组
    • sql进行分组的本质是去重合并
    • group by指定了聚合函数的计算依据，具体的聚合运算还是要由聚合函数进行
    • 因为group by先运行，并且运行后，表格中的非聚合字段已经形成了，所以select后的非聚合字段一定要与group by后指定的字段一致。也就是说：group by后没有的非聚合字段select无法显示，但group by后有的非聚合字段select可以选择不显示

  • having

    • 很耗资源，尽量少用
      • 可以通过将不需要的记录在group by之前过滤掉。即在group by前使用where来过滤，而尽量避免group by后再having过滤
      • where 可以使用rushmore 技术，而having 就不能，在速度上后者要慢
    • 在分组后的数据中进行筛选过滤
    • 数据分组后，对作为分组依据的非聚合字段和分组后聚合运算的结果进行筛选
    • having无法对非聚合运算结果和非group by后的字段进行筛选，这些筛选需要在源数据处理前通过where实现，这样的效率才是最高的

      第二步 查询字段

      对准备好的表格进行排序和行数限制 查询表格中的字段，并基于已有的字段进行计算

  • order by

    • 根据以下字段的升/降序进行排序
    • 执行顺序为从左到右排序，很耗资源
  • limit
    • 基于排序好的表格，指定行数的数据
  • select
    • 少用*号，尽量取字段名称
    • 基于from、where、group by、having处理好的数据表格，按照select后的计算规则，计算并复制指定的字段

      语法说明

      全局规则

  1. 在阅读SQL代码和书写时，可以先从FROM和JOIN语句看起，明确用了哪些表，然后看SELECT查询和创建了哪些字段，接下来细致研究代码细节，最后看WHERE的筛选逻辑
  2. SQL语句不区分大小写，因此SELECT与select甚至是SeLect的效果是相同的，但是要对命令和变量进行区分，所以默认命令需要大写，其他内容如变量等则需要小写；但是在高强度编程时，如果编程工具能够识别SQL语法自动对命令和变量进行区分（基本上都有这个功能），则也会使用小写命令
  3. 命令和变量名称冲突时需要在变量两侧加``（在左上角~那）做区分，’’（单引号在区分字符串时使用），””（已有单引号时用双引号包含单引号）
  4. 表和变量名中不要出现空格，可使用下划线_替代
  5. 为提高代码的可移植性，请在查询语句结尾添加一个分号;
  6. 对于定义模糊或关键变量和关键代码要加注释
  7. 命令和变量使用单一空格隔开
  8. 能用自链接则尽量不要使用子查询
  9. 在各个库中，表和字段别名尽量统一，方便代码移植，不要使用a,b来别名，别名要有含义

    代码注释

    ```sql 单行注释 使用两个连字符-，添加注释

SELECT col_name — 这是一条注释 FROM table_name;

多行注释 多行注释以/起始，以/结尾 /SELECT col_name FROM table_name;/ SELECT col_2 FROM table_name;

  1. <a name="EODfj"></a>
  2. ## FROM
  4. - 表和字段一样尽量加上别名，并且同一个表用一个别名
  5. - sql server 把子查询作为数据源时，如果没有表别名，将会提示错误。所以一定要别名
  6. <a name="rT5ey"></a>
  7. ## SELECT
  9. - 检索某列中不同的值使用**DISTINCT**
  10.    - 本质上是删除重复值
  11. - 可以使用**AS**给查询字段起别名
  12. - 在检索中支持进行数学计算
  13.    - 适用于计算的操作符有+（加），-（减）和/（除）
  14. - 支持字符类型的拼接
  15.    - SELECT RTRIM(col_name) + '('+RTRIM(col_country)+')' AS col_title FROM table_name ORDER BY col_name;
  16.    - 输出
  17.       - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/1862552/1635945212248-ee8b1b76-fdeb-44a1-991e-3426bc39194e.png#clientId=u5526def8-6dda-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=u33389df5&margin=%5Bobject%20Object%5D&name=image.png&originHeight=120&originWidth=684&originalType=url&ratio=1&rotation=0&showTitle=false&size=5385&status=done&style=none&taskId=uba360993-649f-4798-9fcb-50972f9a368&title=)
  18.    - 这里实现的就是将col_name列与col_country列进行了拼接，新列的名字叫做col_title
  19.    - RTRIM()函数是去掉右边的所有空格，LTRIM()是去掉左边的所有空格，TRIM()是去掉两边的所有空格
  20. <a name="m7xeh"></a>
  21. ## WHERE
  23. - WHERE子句应该写在表名（即FROM子句）之后，**在ORDER BY子句之前**
  24. - 可以使用的运算符
  25.    - = 等于
  26.    - <> 不等于
  27.    - > 大于
  28.    - < 小于
  29.    - >= 大于等于
  30.    - <= 小于等于
  31.    - BETWEEN…AND…    在指定的两值之间，并且包括边界值
  32.    - IS NULL 为NULL值
  33.    - AND 逻辑运算符：与
  34.    - OR 逻辑运算符：或
  35.    - IN 条件范围筛选
  36.    - NOT 逻辑运算符：非
  37.    - SQL的版本不同，可能导致某些运算符不同（如不等于可以用！=表示），具体要查阅数据库文档。
  38. - 用**通配符LIKE**进行过滤
  39.    - % 
  40.       - 表示任何字符出现任意次数
  41.    - _ 
  42.       - 表示任何字符出现一次
  43.    - []
  44.       - 指定一个字符集，它必须匹配该位置的一个字符
  45.    - ^ 
  46.       - 在[]中使用，表示否定
  47.    - 例子：筛选出，第二个字符为非J且非M的数据
  48.       - SELECT col_1 FROM table_1 WHERE col_1 LIKE '_[^JM]%' ORDER BY col_1;
  49. <a name="uphkm"></a>
  50. ## GROUP BY
  52. - GROUP BY子句必须出现在WHERE子句之后，ORDER BY之前
  53. - GROUP BY子句可以包含任意数量的列，因而可以对分组进行多重嵌套
  54.    - 如按照班级和性别进行分组的话，结果中班级A包含男生组和女生组，班级B也包含男生组和女生组
  55. <a name="LJkYY"></a>
  56. ## HAVING
  57. ```sql
  58. SELECT col_1,COUNT(*) AS num_col
  59. FROM table_1
  60. GROUP BY col_1
  61. HAVING COUNT(*) >= 2;
  62. --这里我们就筛选出了具有两个以上类别的分组

ORDER BY

  • 默认按照升序进行排序（从小到大，从a到z），使用DESC关键字可以改为降序
  • 可以是单列也可以是多列排序
  • 可以使用非检索的列进行排序

  • DESC关键字的用法

    • 只对跟在语句前面的变量有效。所以，想要对多列进行降序排序时，需要对每一列都指定DESC关键字

      LIMIT

  • 限制检索的结果

  • 返回前10行（即第1-第10行，下标0 - 9）
    • SELECT col_1 FROM table_name LIMIT 10;
  • OFFSET语句，设置返回数据的起始行
    • 从第五行之后，返回十行数据（即第6-第15行，下标5-14）
      • SELECT col_1 FROM table_name LIMIT 10 OFFSET 5;

        子查询

        构建子查询十分简单，只需将被查询的语句放在小括号里，进行嵌套即可，但在使用时一定要注意格式要清晰
        1. SELECT *
        2. FROM (SELECT day,channel, COUNT(*) AS events
        3. FROM web_events
        4. GROUP BY 1,2 -- 按照第一列(day)和第二列(channel)进行分组
        5. ORDER BY 3 DESC) sub -- 小括号内的查询语句即为子查询
        6. GROUP BY channel
        7. ORDER BY 2 DESC;
        如上，我们创建了一个子查询，放在小括号里，并将其命名为sub。在子查询中也注意到了各个子句上下对齐，这样条例更清晰。

        临时表格（WITH）

        这种方法，就是使用WITH将子查询的部分创建为一个临时表格，然后再进行查询即可。 ```sql WITH sub AS( SELECT day,channel, COUNT(*) AS events FROM web_events GROUP BY 1,2 ORDER BY 3 DESC) — 创建临时表格

SELECT * FROM sub — 对临时表格进行检索 GROUP BY channel ORDER BY 2 DESC; — 这里是根据临时表格的第二列(channel)进行排序

如上，我们将被嵌套的子查询单独拎出来，用WITH创建了一个临时表格，再之后又使用SELECT根据该表格进行查询。
<a name="l0jdc"></a>
## 链接表
<a name="b7rBg"></a>
### 基本链接（JOIN）
内链接（等值链接）
```sql
SELECT col_1,col_2,col_3
FROM table_1,table_2
WHERE table_1.id = table2.id;

--另外更直观常见的写法
SELECT col_1,col_2,col_3
FROM table_1 INNER JOIN table_2
ON table_1.id = table2.id;

--使用别名，简化输入，并且标明各列与表的隶属关系
SELECT t1.col_1,t1.col_2,t2.col_3
FROM table_1 t1 INNER JOIN table_2 t2
ON t1.id = t2.id;

如上代码同样适用于左链接、右链接和外链接

  • LEFT JOIN : 获取FROM语句后的表格中的所有行，对于那些不存在于 JOIN 语句后的表格中的数据填充None;
  • RIGHT JOIN : 获取JOIN语句后的表格中的所有行，对于那些不存在于 FROM语句后的表格中的数据填充None;
  • FULL JOIN: 只要其中一个表中存在匹配，就返回数据，结果是两表的并集。

    自链接

    自链接经常用于对子查询的简化，如下示例：
    假如要获取与Allen同一公司的所有顾客信息，那就需要你先筛选出Allen所在的公司，然后再根据该公司筛选出所有的顾客。使用子查询的方式如下：
    SELECT id,customer_name,company_name,phone_number
FROM customers
WHERE company_name = (SELECT company_name
FROM customers
WHERE customer_name = 'Allen')
    如果改为自链接的方式如下：
    SELECT c1.id, c1.customer_name, c1.company_name, c1.phone_number
FROM customers c1,customers c2
WHERE c1.company_name = c2.company_name
AND c2.customer_name = 'Allen';
    结果是一样的，但是使用自链接的处理速度比子查询要快得多。

    组合查询（UNION）

    UNION用于合并两个或多个SELECT 语句的结果集，使用方法也很简单，只要在多条SELECT语句中添加UNION关键字即可。
    ⚠️UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。而且UNION返回的结果只会选取列中不同的值（即唯一值）。
    使用UNION的场合情况：在一个查询中从不同的表返回结果；对一个表执行多个查询返回结果。
    多数情况下，组合相同表的多个查询所完成的任务与具有多个WHERE子句的一个查询是一样的。
    如下三种查询的结果是一致的。 ```sql — 语句1:原始语句 — 查询一 SELECT customer_name,phone_number FROM customers WHERE customer_state IN (‘str1’,’str2’);

—查询二 SELECT customer_name,phone_number FROM customers WHERE customer_state = ‘str3’;

— 语句2:使用UNION链接 SELECT customer_name,phone_number FROM customers WHERE customer_state IN (‘str1’,’str2’) UNION SELECT customer_name,phone_number FROM customers WHERE customer_state= ‘str3’ ORDER BY customer_name; — 在最后添加了ORDER BY对所有SELECT语句进行排序，这里只是为了示例在使用UNION时如何进行排序。

— 语句3:使用WHERE SELECT customer_name,phone_number FROM customers WHERE customer_state IN (‘str1’,’str2’) OR customer_state = ‘str3’;

虽然这里看起来使用UNION比WHERE更复杂，但对于较复杂的筛选条件，或者从多个表中检索数据时，使用UNION更简单一些。<br />🙋‍♂️如果想要获取筛选列的所有值，可以使用UNION ALL代替UNION,他们的使用方式是一样的。
<a name="OyaNU"></a>
## SQL聚合
有时候我们只是需要获取数据的汇总信息，比如说行数啊、平均值啊这种，并不需要吧所有数据都检索出来，为此，SQL提供了专门的函数，这也是SQL最强大功能之一。

| **聚合函数** |  |
| --- | --- |
| **函数** | **说明** |
| AVG() | 返回某列的均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列的和 |

⚠️聚合函数都会忽略列中的NULL值，但是COUNT(*)也就是统计全部数据的行数时，不会忽略NULL值。<br />当添加DISTINCT参数时，就可以只对不同值（也就是某列中的唯一值）进行函数操作。示例：
```sql
SELECT AVG(DISTINCT col_1) AS avg_dist_col_1
FROM table_1;

条件判断（CASE WHEN）

CASE语句是用来做条件判断的，如果满足条件A，那么就xxx，如果满足条件B，那么就xx。
注意事项：

  • CASE 语句始终位于 SELECT 条件中
  • CASE 必须包含以下几个部分：WHEN、THEN和 END。ELSE 是可选组成部分，用来包含不符合上述任一 CASE 条件的情况。
  • 可以在 WHEN 和 THEN之间使用任何条件运算符编写任何条件语句（例如 WHERE），包括使用 AND 和 OR 连接多个条件语句。

使用示例：

SELECT account_id, unit_name,
CASE WHEN standard_qty = 0 OR standard_qty IS NULL THEN 0
         ELSE standard_amt_usd/standard_qty
         END AS unit_price
FROM orders
LIMIT 10;

如上，我们使用CASE WHEN(条件一) THEN(条件一的结果),ELSE(其他不符合条件一的结果),END语句设立了两个条件，即当standard_qty为0或者不存在时我们返回0，当standard_qty不为0时进行计算，并储存为新列unit_price。

时间序列的处理

在SQL中有一套专门的内置函数，用来处理时间序列，那就是DATE函数。

SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：
DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
YEAR - 格式：YYYY 或 YY
SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：
DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：唯一的数字

DATE_TRUNC 函数

DATE_TRUNC使你能够将日期截取到特定部分。常见的截取依据包括日期、月份 和 年份。
语法：DATE_TRUNC(‘datepart’, timestamp)
其中datepart即为截取依据，后面的timestamp类型可以参考上面的Date数据类型
使用示例：

SELECT DATE_TRUNC('y',col_date) col_year
FROM table_1
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;
--如上，我们将col_date列按照年（’y’）进行了分组，并按由大至小的顺序排序，取前10组数据。

DATE_PART 函数

DATE_PART 可以用来获取日期的特定部分，如获取日期2018-10-6的月份，只会获得一个结果10，这是它与DATE_TRUNC的最大区别。
语法：DATE_PART (‘datepart’, date或timestamp)

DATEDIFF 函数

DATEDIFF(datepart,startdate,enddate)impala是前面减后面，可以为负值，impala里不需要第一个参数，默认为天
例子中，reg_time= 2019-03-28
image.png

TO_DATE 函数

TO_DATE函数可以将某列转为DATE格式，主要是将单独的月份或者年份等等转换为SQL可以读懂的DATE类型数据,这样做的目的是为了后续可以方便地使用时间筛选函数。
语法：TO_DATE(col_name,’datepart’)

TO_DATE('02 Oct 2001', 'DD Mon YYYY');
--这里是将col_name这列按照datepart转化为DATE类型的数据。

datepart对应表

日期部分或时间部分 缩写
世纪 c、cent、cents
十年 dec、decs
年 y、yr、yrs
季度 qtr、qtrs
月 mon、mons
周 w，与 DATE_TRUNC一起使用时将返回离时间戳最近的一个星期一的日期
一周中的日 （ DATE_PART支持） dayofweek、dow、dw、weekday 返回 0–6 的整数（星期日是0，星期六是6）
一年中的日 （ DATE_PART支持） dayofyear、doy、dy、yearday
日 d
小时 h、hr、hrs
分钟 m、min、mins
秒 s、sec、secs
毫秒 ms、msec、msecs、msecond、mseconds、millisec、millisecs、millisecon

SQL数据清理

这一部分主要针对数据清理讲解了几个SQL中的常用函数，一般来说，也都是用在筛选阶段，更详尽的数据清理还是建议放在python中去进行。

LEFT、RIGHT、LENGTH

LEFT和RIGHT相当于是字符串截取，LEFT 是从左侧起点开始，从特定列中的每行获取一定数量的字符，而RIGHT是从右侧。
LENGTH就是获取字符串的长度，比如说字符串AIGROUP的长度为7。

LEFT(phone_number, 3) -- 返回从左侧数，前3个字符
RIGHT(phone_number, 8) -- 返回从右侧数，前8个字符
LENGTH(phone_number) -- 返回phone_number的长度

POSITION、STRPOS、SUBSTR

这三个函数都是与位置相关的函数。
POSITION 和STRPOS 可以获取某一字符在字符串中的位置，这个位置是从左开始计数，最左侧第一个字符起始位置为1，但他俩的语法稍有不同。
SUBSTR可以筛选出指定位置后指定数量的字符。

POSITION(',' IN city_state) -- 返回‘,’在city_state中的位置
STRPOS(city_state, ‘,’) -- 跟上面的语句等价
SUBSTR(city_state,4,5) -- 返回city_state字符串中，以第4个字符为起始的5个字符。

CONCAT

顾名思义，就是将两个字符串进行拼接。

CONCAT(first_name, ' ', last_name) -- 结果为：first_name last_name
--或者你也可以使用双竖线来实现上述任务
first_name || ' ' || last_name

CAST

SQL中进行数据类型转换的函数，但经常用于将字符串类型转换为时间类型

CAST(date_column AS DATE)
-- 你也可以写成这样
date_column::DATE

--这里是将date_column转换为DATE格式的数据，其他时间相关的数据类型与样式
--对照可以参考上面写过的SQL Date数据类型，确保你想转换的数据样式与数据类型对应。

COALESCE

这个函数可以把NULL值进行替换
COALESCE函数，会返回其参数中第一个不为NULL的参数，其参数必须是相同的类型

COALESCE(col_1,0) -- 将col_1中的NULL值替换为0
COALESCE(col_2,'no DATA') -- 将col_2中的NULL值替换为no DATA

unix_timestamp

1、UNIX_TIMESTAMP() ：若无参数调用，则返回一个 Unix timestamp (‘1970-01-01 00:00:00’ GMT 之后的秒数) 作为无符号整数，得到当前时间戳
2、UNIX_TIMESTAMP(date) ：若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以’1970-01-01 00:00:00’ GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。

SELECT UNIX_TIMESTAMP(); --（执行时的时间:2009-08-06 10:10:40）
--1249524739

SELECT UNIX_TIMESTAMP('2009-08-06');
--1249488000

from_unixtime

语法：from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)
其中t1是10位的时间戳值，即1970-1-1至今的秒，而13位的所谓毫秒的是不可以的。
对于13位时间戳，需要截取，然后转换成bigint类型，因为from_unixtime类第一个参数只接受bigint类型。例如： 

select from_unixtime(cast(substring(tistmp,1,10) as bigint),’yyyy-MM-dd HH’) tim 
,count(*) cn from ttengine_hour_data where …

可以结合使用unix_timestamp来规范时间格式

from_unixtime(unix_timestamp(date_created),'yyyy-MM-dd HH:mm:ss')

如何在不同编程语言中获取现在的Unix时间戳(Unix timestamp)

MySQL SELECT unix_timestamp(now())
Java time
JavaScript Math.round(new Date().getTime()/1000)
getTime()返回数值的单位是毫秒
Python 先 import time 然后 time.time()

如何在不同编程语言中实现Unix时间戳(Unix timestamp) → 普通时间？

MySQL from_unixtime(Unix timestamp)

substring_index

字符串截取函数
语法：substring_index(str,delim,count)
str：要处理的字符串
delim：分隔符
count：计数

str=www.baidu.com
substring_index(str,'.',1)
--结果是：www
substring_index(str,'.',2)
--结果是：www.baidu

如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容
相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：

substring_index(str,'.',-2)
--结果为：baidu.com

窗口函数

基础介绍

对准备好的表格，在任意分组内部进行排序和计算（为了区分，我们称之为分区）
在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名 topN问题：找出每个部门排名前N的员工进行奖励

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。
窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：
1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2） 聚合函数，如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

专用窗口函数rank

例如下图，是班级表中的内容
image.png
如果我们想在每个班级内按成绩排名，得到下面的结果。
image.png
以班级“1”为例，这个班级的成绩“95”排在第1位，这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内，按成绩排名了。
得到上面结果的sql语句代码如下：

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：
1）每个班级内：按班级分组
partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）
2）按成绩排名
order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。
通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用了。
image.png
简单来说，窗口函数有以下功能：
1）同时具有分组和排序的功能
2）不减少原表的行数

其他专业窗口函数

专用窗口函数rank, dense_rank, row_number有什么区别呢？
下面举个例子

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

得到结果：
image.png
这三个函数的区别如下：

  • rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。
  • dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。
  • row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

image.png
最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

聚合函数作为窗口函数

聚合窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

我们来看一下窗口函数是聚合函数时，会出来什么结果：

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

image.png
单独用sum举个例子：

如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算，现在再结合刚才得到的结果（下图），是不是理解起来容易多了？

如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

作用：聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

Hive SQL

regexp/rlike

匹配符合条件的数据，若要做排除，前面加not即可。

select roomname
  from aa
 where roomname rlike '\\d{1}'
   and roomname = '特价1.5米大床房(无窗)'

 --返回的结果为：特价1.5米大床房(无窗)

若在前面加个not

select roomname
  from aa
 where roomname not regexp'\\d{1}'
   and roomname = '特价1.5米大床房(无窗)'

 --返回的结果为空

regexp_extract

语法: regexp_extract(string subject, string pattern, int index)
返回值: string
说明： 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
第一参数： 要处理的字段
第二参数: 需要匹配的正则表达式
第三个参数:
0是显示与之匹配的整个字符串
1 是显示第一个括号里面的
2 是显示第二个括号里面的字段…

注意，在有些情况下要使用转义字符（双斜杠了‘\’）。

select
regexp_extract( 'x=a3&x=18abc&x=2&y=3&x=4' , 'x=([0-9]+)([a-z]+)' ,0),   -- x=18abc
regexp_extract( 'x=a3&x=18abc&x=2&y=3&x=4' , '^x=([a-z]+)([0-9]+)' ,0),  -- x=a3

regexp_extract( 'https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive' , 'id=([0-9]+)' ,0),     -- id=522228774076
regexp_extract( 'https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive' , 'id=([0-9]+)' ,1),     -- 522228774076

regexp_extract( 'http://a.m.taobao.com/i41915173660.htm' , 'i([0-9]+)' ,0),             -- i41915173660
regexp_extract( 'http://a.m.taobao.com/i41915173660.htm' , 'i([0-9]+)' ,1)              -- 41915173660

regexp_extract('hitdecisiondlist','(i)(.*?)(e)',0)   --itde
regexp_extract('hitdecisiondlist','(i)(.*?)(e)',1)   --i
regexp_extract('hitdecisiondlist','(i)(.*?)(e)',2)     --td

regexp_extract('特价1.5米大床房(无窗)','特价(\\d\.\\d)(米大床房)(\\(\\D\+\\))',1)  --1.5



from  test.dual;

regexp_replace

语法: regexp_replace(string A, string B, string C)
返回值: string
说明：将字符串A中的符合java正则表达式B的部分替换为C。

select regexp_replace(roomname,'\\(无窗\\)','\\[无窗\\]') as roomname
  from aa
 where roomname= '特价1.5米大床房(无窗)'

 --结果为：特价1.5米大床房[无窗]


 select regexp_replace(cname,'^(无窗)_(哈哈)_(小明)_(\\d+)_','\\1;2;\\3;\\4;') as newname
  from aa
 where cname= '无窗_哈哈_小明_232_其他'

 --结果为：无窗;2;小明;232;其他

关于正则表达式的符号及意义

符号 意义
\ 做为转意，即通常在”\“后面的字符不按原来意义解释，如\b\匹配字符”b”，当b前面加了反斜杆后\\b\，转意为匹配一个单词的边界。
或者
对正则表达式功能字符的还原，如”“匹配它前面元字符0次或多次，\a\将匹配a,aa,aaa，加了”\“后，\a\\将只匹配”a“。
^ 匹配一个输入或一行的开头，\^a\匹配”an A”，而不匹配”An a”
$ 匹配一个输入或一行的结尾，\a$\匹配”An a”，而不匹配”an A”
* 匹配前面元字符0次或多次，\ba*\将匹配b,ba,baa,baaa
+ 匹配前面元字符1次或多次，\ba+\将匹配ba,baa,baaa
? 匹配前面元字符0次或1次，\ba?\将匹配b,ba
. 表示匹配除换行符以外的任意字符
(x) 匹配x保存x在名为$1…$9的变量中
x|y 匹配x或y
{n} 精确匹配n次
{n,} 匹配n次以上
{n,m} 匹配n-m次
[xyz] 字符集(character set)，匹配这个集合中的任一一个字符(或元字符)
[^xyz] 不匹配这个集合中的任何一个字符
[\b] 匹配一个退格符
\b 匹配一个单词的边界
\B 匹配一个单词的非边界
\cX 这儿，X是一个控制符，\\cM\匹配Ctrl-M
\d 匹配一个字数字符，\\d\ = \[0-9]\
\D 匹配一个非字数字符，\\D\ = \[^0-9]\
\n 匹配一个换行符
\r 匹配一个回车符
\s 匹配一个空白字符，包括\n,\r,\f,\t,\v等
\S 匹配一个非空白字符，等于\[^\n\f\r\t\v]\
\t 匹配一个制表符
\v 匹配一个重直制表符
\w 匹配一个可以组成单词的字符(alphanumeric，这是我的意译，含数字)，包括下划线，如[\w]匹配”$5.98”中的5，等于[a-zA-Z0-9]
\W 匹配一个不可以组成单词的字符，如[\W]匹配”$5.98”中的$，等于[^a-zA-Z0-9]。
  • ‘( )’ 标记一个子表达式的开始和结束位置。
  • ‘[]’ 标记一个中括号表达式。
  • \num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
  • 字符簇：
    • [[:alpha:]] 任何字母。
    • [[:digit:]] 任何数字。
    • [[:alnum:]] 任何字母和数字。
    • [[:space:]] 任何白字符。
    • [[:upper:]] 任何大写字母。
    • [[:lower:]] 任何小写字母。
    • [[:punct:]] 任何标点符号。
    • [[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]
    • [[:<:]],[[:>:]] 标记表示word边界。它们分别与word的开始和结束匹配。word是一系列字字符，其前面和后面均没有字字符。字字符是alnum类中的字母数字字符或下划线(_)

各种操作符的运算优先级：

  1. \ 转义符
  2. (), (?:), (?=), [] 圆括号和方括号
  3. *, +, ?, {n}, {n,}, {n,m} 限定符
  4. ^, $, anymetacharacter 位置和顺序

[

](https://blog.csdn.net/jv_rookie/article/details/55211955)

使用经验

  1. max()可以直接比较字符串形式的时间
  2. isNULL(字段，0)可以填充null值
  3. CAST(2 / 60 AS BIGINT) 在MYSQL中会报错，在HIVE里可以正常执行