spark SQL快速入门 10- 15 调优常用sql UDF慕课网

Bertha 。 2022-10-13 01:51 179阅读 0赞

# 10.Spark调优策略 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70]

## 一：资源设置 ##

core  memory executor-num
    executor  driver

![在这里插入图片描述][20210705193647885.png]

1）--executor-memory MEM    1G        每个executor的内存大小
        Cache
        shuffle
        task
    
    2）--executor-cores NUM     1         每个executor的cpu core数量
        4exe *  2core = 8个
        4exe *  4core = 16个
    
    3）--num-executors          2         executor的数量
        4exe *  2core = 8个
        8exe *  2core = 16个
    
        100task  ？ * 2 ？
    
    4）--queue                  root.用户名   运行的队列

${SPARK_HOME}/bin/spark-submit --class xxxx \
    master yarn \
    --deploy-mode cluster \
    --executor-cores ? \
    --num-executors ? \
    --executor-memory ? \
    application.jar xxx  yyy  zzz

## 二：广播变量在Spark中的使用 ##

50exe 1000task

val params = … // 10M  
val rdd = …  
rdd.foreach(x=>\{…params…\})

1000task \* 10M = 10G  
50exe \* 10M = 500M

作业题： ETL处理中日志 join ip这个功能结合我们讲解的广播变量的原理及实现 重构我们的ETL实现逻辑

import org.apache.spark.broadcast.Broadcast
    import org.apache.spark.rdd.RDD
    import org.apache.spark.sql.SparkSession
    
    object JoinApp02 { 
    
      def main(args: Array[String]): Unit = { 
        System.setProperty("hadoop.home.dir", "D:\\hadoop-2.7.1")
        val spark = SparkSession.builder().master("local[*]").appName("JoinApp02").getOrCreate()
    
    
        val peopleInfo: collection.Map[String, String] = spark.sparkContext
          .parallelize(Array(("100", "pk"), ("101", "jepson"))).collectAsMap()
        // 广播变量是把小表的数据通过sc广播出去
        val peopleBroadcast: Broadcast[collection.Map[String, String]] = spark.sparkContext.broadcast(peopleInfo)
    
        val peopleDetail: RDD[(String, (String, String, String))] = spark.sparkContext
          .parallelize(Array(("100", "ustc", "beijing"), ("103", "xxx", "shanghai")))
          .map(x => (x._1, x))
    
    
        // TODO... 是Spark以及广播变量实现join
        // mapPartitions做的事情： 遍历大表的每一行数据 和 广播变量的数据对比 有就取出来，没有就拉倒
        peopleDetail.mapPartitions(x => { 
          val broadcastPeople: collection.Map[String, String] = peopleBroadcast.value
          for((key,value) <- x if broadcastPeople.contains(key))
            yield (key, broadcastPeople.get(key).getOrElse(""), value._2)
        }).foreach(println)
    
    
        Thread.sleep(2000000)
    
        spark.stop()
      }
    }

## 三：Shuffle调优 ##

map端缓冲区大小 : spark.shuffle.file.buffer 默认32k	
        如果过小 ==> 数据频繁写入磁盘文件
    
    reduce端拉取数据缓冲区大小: spark.reducer.maxSizeInFlight
    
    reduce端拉取数据重试次数：spark.shuffle.io.maxRetries 默认3次
    
    reduce端拉取数据等待间隔：spark.shuffle.io.retryWait   默认5s

**实际生产中这些参数调成可以以乘以2的叠加调整**

## 四：JVM相关 ##

对象==>eden 和其中一个survivor0区  此时另外一个survivor1是空
    
    eden + survivor0 满 ==> minor gc
        ==> survivor1
        8:1:1
    
    old enough or Survivor2 is full ==> old ==> full gc
    
    stop the world
    
    个人建议在生产上优先使用CMS G1     JDK8

young gc/full gc 导致 stop the time 可能会导致出现 file not found/ file lost / timeout 等异常错误

调节连接等待时长 避免gc 导致的超时  
spark.core.connection.ack.wait.timeout

调节executor堆外内存 spark.yarn.executor.memoryOverhead 1~2G之间  
executor lost、oom、shuffle output file cannot find  
300M

spark-submit时通过–conf 把我们讲解的这些参数一一设置进去即可

内存 统一内存管理方式（可以借的） 静态管理方式（固定）

## 其他调优 ##

### 官方saprk的性能调优 ###

官方提供的调优文档：  
http://spark.apache.org/docs/latest/tuning.html

打开页面直接翻译看  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 1]

### 官方Spark Sql 的性能调优 ###

http://spark.apache.org/docs/latest/sql-performance-tuning.html  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 2]

预估对象占用内存

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 3]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 4]

# 12.云平台建设的思考 #

## 大数据项目和平台的差异性对比 ##

Hadoop、Hive、Spark、Flink、Storm、Scala 其实都是大数据开发必备的技术/框架

项目(项目)：以功能为主  中小公司/大公司
    平台：大公司  提供给用户通用/定制化的解决方案
    
        数据采集
        离线计算
        实时计算
        机器学习
        图计算
        ......

## 认知云平台能为我们提供的能力 ##

为什么要构建大数据云平台  
1）统一管控  
数据分散、异构 ==> 信息孤岛  
数据的存储、资源 ==> 统一的资源管控 造成资源浪费  
2）能力  
运维、支撑程度  
性能、规模瓶颈

==>
    数据湖：数据和资源的共享
    云能力：集群、扩容、快速开发

## 大数据云平台功能架构 ##

大数据云平台所涉及到的常见的功能  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 5]

## 数据湖架构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 6]

## 数据存储和计算角度剖析 ##

存储和计算：大数据的思想 ==> Hadoop  
公有云  
多租户 多用户  
==> 数据安全  
==> 不同的租户或者用户登录到系统之后，能访问的数据的权限是不一样  
==> Table： 行 列  
==> 数据权限控制到行和列级别  
==> 使用的资源也是不一样的 queue  
私有云

有多个集群：热 冷 备份
        数据迁移  自动、压缩
        计算能力能否无缝对接  √
    
    隔离：队列隔离、资源隔离

## 资源角度剖析 ##

资源分配如何最优、资源使用率最大化  
Spark/Flink  
spark-submit 多少executor、每个executor多少memory、每个executor多少core？

调度框架：AZ、Oozie、crontab
        一个作业涉及多个Job：workflow
        多个job之间如何做资源隔离
    
    Spark/Flink on YARN：申请资源是需要一定的时间的，调优使得申请资源的时间减少！
        10分钟：2分钟+8分钟    30秒+9分30秒

## 兼容性角度剖析 ##

兼容性角度  
Apache Hadoop  
CDH Cloudera Manager  
HDP  
MapR  
华为：Huawei Manager FusionInsight  
阿里  
腾讯  
百度  
…

假设数据平台是基于CDH/HDP来构建的
        但是：用户现在已有的数据是基于阿里、华为云来开发和运维的
    
    务必要提前做一件事情：熟悉已有的数据平台的一些功能 vs 我们数据平台的功能
        数据存储：CarbonData
        调度：oozie ==>  PK调度

## 执行引擎和运行方式适配角度剖析 ##

引擎的适配  
离线：Spark Flink  
做一个功能：按照我们的产品设计文档，写一份代码，可以同时运行在不同的引擎上  
√Spark  
√Flink  
Apache Beam 支持写一份代码，可以同时运行在不同的引擎上像spark、flink

运行方式的适配  
√YARN  
√K8S(Docker)

## Spark和Flink的选择 ##

Spark和Flink的选择性问题  
Spark到现在社区是比较成熟、提供的功能也是比较完善的  
Flink 实时部分

Spark 离线的性能优于Flink  
Fllink的实时性优于SparkStreaming

# 13 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 7]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 8]

## 动态分区裁剪 ##

执行计划优化  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 9]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 10]

## 自适应查询执行 ##

![在这里插入图片描述][20210707165839112.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 11]  
开启自使用分区配置：

![在这里插入图片描述][20210707171059357.png]  
spark.sql.adaptive.enabled true  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 12]

放开注释即可  
![在这里插入图片描述][20210707171201849.png]  
根据数据量评估出来只需要一个分区  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 13]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 14]

# 14 saprk sql 窗口函数 #

官网关于窗口函数的介绍  
https://databricks.com/blog/2015/07/15/introducing-window-functions-in-spark-sql.html

![在这里插入图片描述][2021070820142532.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 15]  
![在这里插入图片描述][20210708201534369.png]

## mysql中的concat函数,concat\_ws函数，concat\_group函数之间的区别 ##

[mysql中的concat函数,concat\_ws函数，concat\_group函数之间的区别][mysql_concat_concat_ws_concat_group]

## 列转行功能需求分析 ##

源数据

id name  dept  sex 
    
    1,PK,RD,1
    2,XIAOAI,RD,1
    3,XIAOHONG,RD,2
    4,XIAOZHANG,QA,1
    5,XIAOLI,QA,2
    6,XIAOFANG,QA,2

转换要求  
按照部门和性别进行分组，名字之间使用|进行拼接

dept  sex  name  
    
    RD,1 PK|XIAOAI
    RD,2 XIAOHONG
    QA,1 XIAOZHANG
    QA,2 XIAOLI|XIAOFANG

分析：

1.  把dept和sex进行拼接
2.  group by
3.  分组后的名字要采用什么函数使用|拼到一起

create table emp_info(
    id string,
    name string,
    dept string,
    sex string
    ) row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/data/imooc/emp_info.txt' overwrite into table emp_info;

select 
    t.dept_sex, concat_ws("|", collect_list(t.name))
    from 
    (
    select name, concat_ws(",",dept,sex) dept_sex from emp_info
    ) t group by t.dept_sex;

collect\_set  
collect\_list

## 行转列功能需求分析 ##

源数据

teacher      courses
    PK	    MapReduce,Hive,Spark,Flink
    XIAOAI	Hadoop,HBase,Kafka

转换后的数据格式

teacher courses
    PK	    MapReduce
    PK	    Hive
    PK	    Spark
    PK	    Flink
    XIAOAI	Hadoop
    XIAOAI	HBase
    XIAOAI	Kafka

分析：

1.  借助于某一个函数能够把一列的数据按照某个分隔符进行拆分

create table column2row(
    name string,
    courses string
    ) row format delimited fields terminated by '\t';

load data local inpath ‘/home/hadoop/data/imooc/column2row.txt’ overwrite into table column2row;

select \*, split(courses, “,”) from column2row;

UDTF  
UDF: UDF UDAF UDTF

select
    name, course
    from 
    column2row
    lateral view
    explode(split(courses, ",")) course_tmp as course;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 16]

## 窗口函数概述 ##

spark官网对窗口的介绍不多，hive官网对窗口的用法介绍比较全面，也都是可以在spark中使用的

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 17]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 18]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 19]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 20]

## 窗口函数之累计求和操作 ##

原始数据 window01.txt  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 21]  
创建表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 22]  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 23][]

对traffic求和操作  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 24]  
partition by:相同的domain会划分到一起

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 25]

## 窗口函数之窗口划分原则 ##

**规则**

![在这里插入图片描述][20210707220217867.png]

以上两句的效果一样都是统计从第一行（UNBOUNDED PRECEDING） 开始 到 当前行结束（CURRENT ROW）的结果

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 26]

select
    domain, time, traffic,
    sum(traffic) over(partition by domain order by time) pv1,
    sum(traffic) over(partition by domain order by time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) pv2,
    sum(traffic) over(partition by domain order by time ROWS BETWEEN  CURRENT ROW  AND UNBOUNDED FOLLOWING ) pv3
    from 
    w1;

pv3为分组后组内从当前行取到最后一行的和  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 27]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 28]

select
    domain, time, traffic,
    sum(traffic) over(partition by domain order by time) pv1,
    sum(traffic) over(partition by domain order by time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) pv2,
    sum(traffic) over(partition by domain order by time ROWS BETWEEN  3 PRECEDING AND CURRENT ROW ) pv3
    from 
    w1;

pv3 为计算分组后组内数据的当行和当前行的前三行的值  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 29]

select
    domain, time, traffic,
    sum(traffic) over(partition by domain order by time) pv1,
    sum(traffic) over(partition by domain order by time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) pv2,
    sum(traffic) over(partition by domain order by time ROWS BETWEEN  3 PRECEDING AND 1 FOLLOWING ) pv3
    from 
    w1;

pv3为分组后计算当行 + 当前行的前三行 + 当前行的后一行  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 30]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 31]

## 窗口函数之ntile的使用 ##

原始数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 32]

![在这里插入图片描述][20210708194729937.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 33]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 34]

## 非常重要窗口函数之row\_number()&rank()&dense\_rank() ##

### row\_number() ###

row\_number(): 对组内数据根据排序编号  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 35]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 36]

### rank() ###

![在这里插入图片描述][20210708202330511.png]

select
    domain,time,traffic,
    row_number() over(partition by domain order by traffic desc） rn1, 
    rank() over(partition by domain order by traffic desc） rn2, 
    from 
    w1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 37]

### dense\_rank() ###

select
    domain,time,traffic,
    row_number() over(partition by domain order by traffic desc） rn1, 
    rank() over(partition by domain order by traffic desc） rn2, 
    dense_rank() over(partition by domain order by traffic desc） rn2, 
    from 
    w1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 38]

## 窗口函数之lag&lead(取上次&下次的值) ##

[mysql里 lag() 和 lead() 用法][mysql_ lag_ _ lead_]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 39]  
数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 40]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 41]

select
    cookieid,time,
    lag(time, 1,'===') over(partition by cookieidorder by timedesc） lag1, 
    lag(time, 2,'===') over(partition by cookieidorder by timedesc） lag2, 
    from 
    w3;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 42]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 43]

## 窗口函数之first\_value和 last\_value ##

### first\_value 分组后的第一个值 ###

select
    cokieid ,time, first
    first_value() over(partition by cookieid order by time) first
    from w3

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 44]

### first\_value 分组后当前位置的最后一个值 ###

select
    cokieid ,time, first
    last_value() over(partition by cookieid order by time) last
    from w3

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 45]  
cume\_dist percent\_rank

## 窗口函数之cume\_dist&percent\_rank ##

### cume\_dist ###

建表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 46]  
数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 47]

select
    dept,user,sal,
    cume_dist() over(order by sal) ranl,
    cume_dist() over(partition by dept order by sal)ran2
    from w2;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 48]  
四舍五入  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 49]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 50]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 51]

### percent\_rank ###

select
    dept,user,sal,
    round(percent_rank() over(order by sal) ,2) ranl,
    round(percent_rank() over(partition by dept order by sal),2) ran2
    from w2;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 52]

## 窗口函数实战之数据准备 ##

原始数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 53]  
建表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 54]

![在这里插入图片描述][20210715192811676.png]

**需求1： 统计2021年9月份购买过的用户以及总人数**

1.  先过滤出2021-09  
    方法1：使用字符串判断

[MySQL|SUBSTR() 函数用法][MySQL_SUBSTR_]

select * from w_ordrs where substr(time,1,7)='2021-09'

查询结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 55]  
方法二使用时间函数

[MySQL日期时间函数大全（咋个办呢 zgbn）][MySQL_ zgbn]

select * from w_ordrs where year(time)=2021 and month(time)=9;

2.获取用户的名字

select 
    name
    from w_ordrs 
    where year(time)=2021 and month(time)=9
    group by name

3.获取总人数

先将上面两步查到结果放入一个临时表t1

create table t1 as
    select 
    name,
    count(1) over(row between unbounded preceding and unbounded following)
    from w_ordrs 
    where year(time)=2021 and month(time)=9
    group by name

[关于mysql中的count()函数][mysql_count]

[sum()over()和count()over()分析函数][sum_over_count_over]  
![在这里插入图片描述][20210715200107366.png]

**2. 统计用户的购买明细信息以及月份购买总额**

根据名字和年月分组，再计算每组中cost的总和

select
    name,time,cost,
    sum(cost) over(partition by name, substring(time,1,7) order by time) month_cost
    from w_orders;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 56]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 57]

**2. 统计用户的购买明细信息以及按日期份购买总额（每个人的购买总额）**

select
    name,time,cost,
    sum(cost) over(partition by name order by time) costs
    from w_orders;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 58]  
**2. 统计用户的购买明细信息以及改用户上次的购买时间**

select
    name,time,cost,
    lag(time,1,'--') over(partition by name order by time) lag
    from w_orders;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 59]  
**2. 统计用户的购买明细信息以及改用户下次的购买时间**

select
    name,time,cost,
    lead(time,1,'--') over(partition by name order by time) lag
    from w_orders;

# 15 自定义函数 #

**UDF 一进一出 User Defined Function  
UDAF 多进一出(聚合函数)， User Defined Aggregate Function(用户自定义聚合函数)**

# 自定义UDF自定义函数 #

## 示例一 ##

数据  
![在这里插入图片描述][e7b7c5b061378784ad0ba89ed6a6cbed.png]

![在这里插入图片描述][dd702c6b9fa2b83d27ca066d5420528c.png]  
**需求：自定义函数实现，统计每个人所在队的个数**

**SQL UDF 实现 (自定义API)**

object UDFApp { 
      def main(args: Array[String]): Unit = { 
        val spark = SparkSession.builder()
            .master("local[2]")
            .appName("UDFApp")
          .getOrCreate()
    
        udfWithSQL(spark)
    
        spark.stop()
      }
    
      def udfWithSQL(spark: SparkSession): Unit = { 
        import spark.implicits._
        val df: DataFrame = spark.sparkContext.textFile("data/udf.txt")
          .map(_.split(" "))
          .map(x => FootballTeam(x(0), x(1)))
          .toDF()
        df.createOrReplaceTempView("/**//")
    
    
        // 注册我们自定义的Spark SQL的UDF函数
        val teams_length: UserDefinedFunction = spark.udf.register("teams_length", (input: String) => { 
          input.split(",").length
        })
    
        spark.sql(
          """ | |select |name, teams, teams_length(teams) as len |from |teams | """.stripMargin)
          .show()
    
        df.select($"name", $"teams", teams_length($"teams").as("len")).show()
      }
    
    
      case class FootballTeam(name:String, teams: String)
    }

![在这里插入图片描述][084ab9f9b928f02946f411b56cf4c35a.png]

![在这里插入图片描述][0ecac3695651fad482ce001ceae7224a.png]

**dateStream API UDF(自定义API)**  
![在这里插入图片描述][a24b93fd931dd692e72d3dc804d1e996.png]

import org.apache.spark.sql.functions._
    
    object UDFApp { 
      def main(args: Array[String]): Unit = { 
        val spark = SparkSession.builder()
            .master("local[2]")
            .appName("UDFApp")
          .getOrCreate()
    
    
    // udfWithSQL(spark)
       udfWithAPI(spark)
    
    
        spark.stop()
      }
    
      def udfWithAPI(spark: SparkSession): Unit = { 
        import spark.implicits._
        spark.sparkContext.textFile("data/udf.txt")
          .map(_.split(" "))
          .map(x => FootballTeam(x(0), x(1)))
          .toDF()
          .select($"name", $"teams", getTeamsLength($"teams").as("len"))
          .show()
      }
    
    
      def getTeamsLength = udf((input: String) => { 
        input.split(",").length
      })
    
    
    
      case class FootballTeam(name:String, teams: String)
    }

实际开中应该把udf 装在一起方便调用

## 示例二 ##

package com.imooc.bigdata.udf
    
    import org.apache.spark.sql.expressions.UserDefinedFunction
    import org.apache.spark.sql.{ DataFrame, SparkSession}
    import org.apache.spark.sql.functions._
    
    object UDFApp { 
      def main(args: Array[String]): Unit = { 
        val spark = SparkSession.builder()
            .master("local[2]")
            .appName("UDFApp")
          .getOrCreate()
    // udfWithSQL(spark)
      // udfWithAPI(spark)
       test(spark)
        spark.stop()
      }
      def test(spark: SparkSession): Unit = { 
        import spark.implicits._
        val ds = spark.createDataset(List(("广东省","深圳市","南山区"),("北京市","北京市","昌平区"),("浙江省","杭州市","西湖区")))
        val df = ds.toDF("province", "city", "district")
    
        // 使用自定义函数，把每个Tuple中对应的元素拼接(？？？)起来，sql中其实已经提供了拼接函数 concat_ws()
    
        def func = (split:String, p1:String, p2:String, p3:String) => { 
          p1 + split + p2 + split + p3
        }
    
        spark.udf.register("PK_CONCAT_WS", func)
    
    
        df.createOrReplaceTempView("cities")
    //SQL
        spark.sql(
          """ | |select |PK_CONCAT_WS("|", province,city,district) province_city_district_sql |from |cities | """.stripMargin)
          .show(false)
    
    //API
        df.select(expr("PK_CONCAT_WS('|', province,city,district)").as("province_city_district_api")).show()
      }
    
    }

# 自定义UDAF函数 #

## 示例一 分组之后求平均年龄 ##

**需求：分组之后求，每个组的平均年龄**

### Row 弱类型 无约束类型 ###

package com.imooc.bigdata.udf
    
    import org.apache.spark.sql.{ DataFrame, Dataset, Encoder, Encoders, Row, SparkSession, TypedColumn}
    import java.util
    
    import org.apache.spark.sql.expressions.{ Aggregator, MutableAggregationBuffer, UserDefinedAggregateFunction}
    import org.apache.spark.sql.types._
    
    object UDAFApp { 
      def main(args: Array[String]): Unit = { 
        val spark = SparkSession.builder()
            .master("local[2]")
            .appName("UDFApp")
          .getOrCreate()
     // Row 弱类型 无约束类型
      test01(spark)
    
      
        spark.stop()
      }
    
    
      // Row 弱类型 无约束类型
      def test01(spark:SparkSession): Unit = { 
    
        val rows = new util.ArrayList[Row]()
        // 按照性别分组，求平均年龄
        rows.add(Row("PK",30 ,"M"))
        rows.add(Row("PK1",60 ,"M"))
        rows.add(Row("XIAOHONG",18 ,"F"))
        rows.add(Row("XIAOFANG",20 ,"F"))
    
        val schema = StructType(List(
          StructField("name", StringType, false),
          StructField("age", IntegerType, false),
          StructField("sex", StringType, false)
        )
        )
    
        val df: DataFrame = spark.createDataFrame(rows, schema)
        df.createOrReplaceTempView("user")
    // df.show(false)
    
        spark.udf.register("avg_udaf", new AvgUDAF)
    
        spark.sql(
          """ | |select |sex, avg_udaf(age) avg_age |from |user |group by sex | """.stripMargin)
          .show()
      }
    
    }
    
    case class Student(name:String, age:Int, sex:String)
    case class AgeAvg(sum:Int, count:Int) { 
      def avg = sum.toDouble / count
    }
    
    
    
    /** * select sex, avg_udaf(age) from xxx group by sex; * * 平均数 = 总数 / 次数 */
    class AvgUDAF extends UserDefinedAggregateFunction { 
    
      // UDAF输入的数据类型
      override def inputSchema: StructType = { 
        StructType(
          StructField("nums", DoubleType, true) :: Nil
        )
      }
    
      //数据处理中间过程的Schema 求平数需要 总数和次数
      override def bufferSchema: StructType = { 
        StructType(
          StructField("buf1", DoubleType, true)  ::
            StructField("buf2", LongType, true)
            :: Nil
        )
      }
    
      //返回值的据类型，返回是平均数
      override def dataType: DataType = DoubleType
    
      //传入的数据类型和返回的数据类型时候一样
      override def deterministic: Boolean = true
    
      //初始处理中间使用的数
      override def initialize(buffer: MutableAggregationBuffer): Unit = { 
        buffer.update(0, 0.0)
        buffer.update(1, 0L)
      }
    
      //单分区内，新进来的数据和值进来的数据累加
      override def update(buffer: MutableAggregationBuffer, input: Row): Unit = { 
        //数据相加
        buffer.update(0, buffer.getDouble(0) + input.getDouble(0))
        //统计次数
        buffer.update(1, buffer.getLong(1) + 1)
      }
      //计算一般是多分区的，分区之间的数据进行聚合
      override def merge(buffer1: MutableAggregationBuffer, buffer2: Row): Unit = { 
        buffer1.update(0, buffer2.getDouble(0) + buffer1.getDouble(0))
        buffer1.update(1, buffer2.getLong(1) + buffer1.getLong(1))
      }
    
      //统计完数据，数据计算逻辑 这里是求平均数
      override def evaluate(buffer: Row): Any = { 
        buffer.getDouble(0) / buffer.getLong(1)
      }
    }

![在这里插入图片描述][1663942f9ac8e1a8f4b712290b80384a.png]  
数据  
![在这里插入图片描述][ec919d289de43a05aeb352029bcc6e3f.png]

计算结果  
![在这里插入图片描述][e46e0098260b856a91425156c4815370.png]

### 强类型 带约束 ###

import org.apache.spark.sql.expressions.Aggregator
    import org.apache.spark.sql.functions.udaf
    import org.apache.spark.sql.{ Dataset, Encoders, SparkSession, TypedColumn}
    
    object UDAFApp { 
      def main(args: Array[String]): Unit = { 
        val spark = SparkSession.builder()
          .master("local[2]")
          .appName("UDFApp")
          .getOrCreate()
        test02(spark)
        spark.stop()
      }
    
      // 强类型 带约束
      def test02(spark: SparkSession): Unit = { 
        import spark.implicits._
    
        val ds: Dataset[Student] = List(
          Student("PK", 30, "M"), Student("PK1", 60, "M"),
          Student("XIAOHONG", 18, "F"), Student("XIAOFANG", 12, "F")
        ).toDS()
    
        ds.createOrReplaceTempView("user")
        ds.show(false)
        //3.0.0之后版本
        spark.udf.register("avg_udaf",udaf(new AvgUDAF2))
        spark.sql(
          """ | |select |sex, avg_udaf(age) avg_age |from |user |group by sex | """.stripMargin)
          .show()
    
        //3.0.0之前版本
         val avgUDAF = new AvgUDAF2
           val value: TypedColumn[Student, Double] = avgUDAF.toColumn.name("avg_age")
            val result = ds.select(value)
         result.show()
      }
    }
    
      case class Student(name: String, age: Int, sex: String)
    
      case class AgeAvg(sum: Int, count: Int) { 
        def avg = sum.toDouble / count
      }
    
    
    class AvgUDAF2 extends Aggregator[Student, AgeAvg, Double] { 
        override def zero: AgeAvg = AgeAvg(0, 0)
    
        override def reduce(b: AgeAvg, a: Student): AgeAvg = AgeAvg(b.sum + a.age, b.count + 1)
    
        override def merge(b1: AgeAvg, b2: AgeAvg): AgeAvg = AgeAvg(b1.sum + b2.sum, b1.count + b2.count)
    
        override def finish(reduction: AgeAvg): Double = reduction.avg
    
        override def bufferEncoder: Encoder[AgeAvg] = Encoders.product
    
        override def outputEncoder: Encoder[Double] = Encoders.scalaDouble
      }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70]: /images/20221005/62c6dbf4bb9e461fbcae3ddd57b8cc69.png
[20210705193647885.png]: /images/20221005/671b0d1d2901418c987df3f330f23732.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 1]: /images/20221005/0dbf506f32044320b469122b4695164e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 2]: /images/20221005/7a1db0707ca04e05968ca90dff26cade.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 3]: /images/20221005/818a2bd832d14df6840bde1a277d6bdf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 4]: /images/20221005/29241a6ca5fd4912b693ae01e81877d0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 5]: /images/20221005/ef93ae4aa6e44915aa0e838e5ce82412.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 6]: /images/20221005/440f2363a9824415bead646fff14a999.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 7]: /images/20221005/38593aba3a36400fa74eb89db07ff8b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 8]: /images/20221005/ff37c270caa541d09b611586ad6fc580.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 9]: /images/20221005/37c80d6a9ce34840830600ece13f2cc3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 10]: /images/20221005/28ef82fc4a784faca4ea39b61a2eb2b8.png
[20210707165839112.png]: /images/20221005/4f84d33efbe04b53bb545e614d989c56.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 11]: /images/20221005/fdeb2a1c9abe4132bdc5ba276c8dbf8b.png
[20210707171059357.png]: /images/20221005/d7dbed6ca5394edc92f8e9f8a1e2e11a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 12]: /images/20221005/a63f7bf7c7554491b01b2081db6d8eb4.png
[20210707171201849.png]: /images/20221005/2149f8b0abea4712bccfd6f3c5a8b1b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 13]: /images/20221005/52dd7b771207421a81aba2adf1d41c2f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 14]: /images/20221005/c6800ca427944d79bff57b0ef2abe795.png
[2021070820142532.png]: /images/20221005/db27c013a1504de7adcc4f86360c742d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 15]: /images/20221005/3c8964a8bd174f25918c8b539dee25e0.png
[20210708201534369.png]: /images/20221005/a4271b8f85ae47829ce5bf5422f60f2b.png
[mysql_concat_concat_ws_concat_group]: https://blog.csdn.net/qq_35211818/article/details/78977276
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 16]: /images/20221005/6a7f3f45b8254feda247f9166a1f57e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 17]: /images/20221005/b24391ae1a054636af3ab8289ffdb137.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 18]: /images/20221005/78d90712feca465db078dc63192f9473.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 19]: /images/20221005/8f36e027bd7a479ba1286e2ccdc62011.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 20]: /images/20221005/53bee72f5c3842d9a3a753c58ebf2670.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 21]: /images/20221005/20be10473d7a466783799ce9a231b0b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 22]: /images/20221005/ff0c43b0ca8e4381b3dbd18b8599966b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 23]: /images/20221005/738be5b83be549e9a67e314ee46ebc92.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 24]: /images/20221005/f64790e974084d0a88ef7852d96e2734.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 25]: /images/20221005/273d662032614f0a9dbf324e5f870388.png
[20210707220217867.png]: /images/20221005/f889c36415584ee0a95bb2bf9e7fa857.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 26]: /images/20221005/f867585404074f379dca00553686e56f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 27]: /images/20221005/0be9b5297d814252873d15381954d7a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 28]: /images/20221005/a7ba20ff439940bc9861f70c79b5f287.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 29]: /images/20221005/15970ae0eada4db6b66c340b7a2ea639.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 30]: /images/20221005/aae3f2a4c2aa47ae8afb51c2f00fe661.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 31]: /images/20221005/5e02fde0b9ee47ae8314bc44ceec0f1e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 32]: /images/20221005/cb68276418454746b2cc0df5377f233e.png
[20210708194729937.png]: /images/20221005/dbd31e10133b4ffcb7977f5b0f170f86.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 33]: /images/20221005/fe7fbd29067c456796a693fa241dad77.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 34]: /images/20221005/47cbffdda87f4aea98ba23beecc8cb29.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 35]: /images/20221005/51d09c2343224f2e97831932ae39f1aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 36]: /images/20221005/a01dc187fad24d4f9d848463518e6856.png
[20210708202330511.png]: /images/20221005/ee879c8f29cd471a836c1d46127edfac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 37]: /images/20221005/f688cd42f8e143eaba4189fb001de67c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 38]: /images/20221005/3cdf92963d2a47a8b4175be360c7d8f4.png
[mysql_ lag_ _ lead_]: https://blog.csdn.net/qq_42363032/article/details/110734500
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 39]: /images/20221005/7b7fb8314e4a4339b1e28c461d3779c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 40]: /images/20221005/25390787f5f449fcb3bed07c8a7d4820.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 41]: /images/20221005/b803b989e9b54a08b47f104de2aeda25.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 42]: /images/20221005/32f4c536c57f4409af00441af095bb08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 43]: /images/20221005/5e86fa9d8fda4b87bdfaca9248d4e678.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 44]: /images/20221005/b173ab086a4d4eb59e07c254d0b1c91a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 45]: /images/20221005/25eac1535a174f80a19aa82aa1b25e04.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 46]: /images/20221005/93b22a3aa3c0451babfbc8cf78ee7892.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 47]: /images/20221005/68cddbda31614f7f83254201ccb007bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 48]: /images/20221005/4605323ca9fe434d85141fd911bffcc5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 49]: /images/20221005/234b4a7221904f48a2359171ce62cc69.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 50]: /images/20221005/002f361d57274782a4c4e07aebd066cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 51]: /images/20221005/ab33ade991d346ac8146ec42f65050ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 52]: /images/20221005/0db8aac518ee4975b1987731aea26bf8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 53]: /images/20221005/a0be2d28b71d46b3b9b7f1636cb2c2e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 54]: /images/20221005/b590d6e6e61a4f02b0acfe7857090d6b.png
[20210715192811676.png]: /images/20221005/9267c244b14b4f8fbfbe7cc5979a2845.png
[MySQL_SUBSTR_]: https://zhuanlan.zhihu.com/p/110142732
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 55]: /images/20221005/51fec2379e1444bea6d43a300cb2cb3b.png
[MySQL_ zgbn]: https://blog.csdn.net/ai_zxc/article/details/50627918
[mysql_count]: https://www.cnblogs.com/-flq/p/10302965.html
[sum_over_count_over]: https://www.bbsmax.com/A/n2d96qvYzD/
[20210715200107366.png]: /images/20221005/d3c71ea63af34646aff6ac44fb07787c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 56]: /images/20221005/460de52c314446bc80398dec95edfdb8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 57]: /images/20221005/d2a6b140e7924c3bbbb48deecb5947ec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 58]: /images/20221005/7f51075be8f9499d8670fa364c0d9950.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGVuZ3dlaTIzMDYxMg_size_16_color_FFFFFF_t_70 59]: /images/20221005/92262d522b6345d48abeaff0ad5092dd.png
[e7b7c5b061378784ad0ba89ed6a6cbed.png]: /images/20221005/0f1949ccd17e45959bea4b46360596f1.png
[dd702c6b9fa2b83d27ca066d5420528c.png]: /images/20221005/99e1420a4d244bf08c012ad5341d3dad.png
[084ab9f9b928f02946f411b56cf4c35a.png]: /images/20221005/5236752ccd664fad9000fa49509959ca.png
[0ecac3695651fad482ce001ceae7224a.png]: /images/20221005/10d44191ea7a45f7b4e75418799bf1b4.png
[a24b93fd931dd692e72d3dc804d1e996.png]: /images/20221005/c29486007681481ab29cb83e3a156d18.png
[1663942f9ac8e1a8f4b712290b80384a.png]: /images/20221005/9b5a3abc0e704842ad3fb0c150b49376.png
[ec919d289de43a05aeb352029bcc6e3f.png]: /images/20221005/7692e1e01d1844b6b4c173e5b325a83c.png
[e46e0098260b856a91425156c4815370.png]: /images/20221005/6a07e1ff2e6548079f6923ebd297666e.png