日常学习笔记

1、零散概念

1.1、SQL

1.1.1、透视与逆透视

透视

​ 透视数据 (pivoting) 是一种把数据从行的状态旋转为列的状态的处理， 在这个过程中可能须要对值进行聚合，或者更多的可以理解为对数据分类聚合，进行解读，和理解的过程。先考虑一个需求：生成一个报表，包含每个雇员和客户组合之间的总订货量。

​ 透视阶段一般有三个阶段：分组，扩展，聚合。

逆透视

​ 逆透视转换是一种把数据从列的状态旋转为行的状态的技术。 通常，它涉及查询数据的透视状态， 将来自单个记录中多个列的值扩展为单个列中具有相同值的多个记录。 换句话说， 把透视表中的每个源行潜在地转换成多个行， 每行代表源透视表的一个指定的列值。

​ 逆透视阶段一般有三个阶段：生成副本，提取元素，删除不相关的交叉。

1.1.2、where 1=1的作用

不管sql语句中 where 1=1还是1<>2，‘a’=‘a’,‘a’<>‘b’，其目的就只有一个，where 的条件为永真，得到的结果就是未加约束条件的。为了动态SQL中连接and条件。

假如后面确定有条件语句，就可以不用1=1； 不能确定where后面是否有语句的时候就要加上1=1了，当没有其他条件语句时候，还有1=1，就不会出错了 。

1.1.3、存储过程

1.1.4、表的分类

实体表

​ 一般是指一个现实存在的业务对象，比如用户，商品，商家，销售员等等。

维度表

​ 一般是指对应一些业务状态，编号的解释表。也可以称之为码表。比如地区表，订单状态，支付方式，审批状态，商品分类等等。

事务性事实表

​ 一般指随着业务发生不断产生的数据。特点是一旦发生不会再变化。一般比 如，交易流水，操作日志，出库入库记录等等。

周期性事实表

​ 一般指随着业务发生不断产生的数据。 与事务型不同的是，数据会随着业务周期性的推进而变化。比如订单，其中订单状态会周期性变化。再比如，请假、贷 款申请，随着批复状态在周 期性变化。

1.1.5、set与select

​ SET是SQL Server 中对已经定义的变量赋值的方式，经常与update语句一起使用。

语法：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

例子如下：

update A set name=小张 where name =张三 //将小张的姓名改为张三

update A set name=小王 where name =王五  //将小王的姓名改为王五

set与select的区别

• SELECT可以在一条语句里对多个变量同时赋值,而SET只能一次对一个变量赋值

• 表达式返回多个值时，用SET将会出错，而SELECT将取最后一个值

• 表达式无返回值时，用SET将置变量值为NULL,用SELECT交保持变量值

• 使用标量子查询时，如果无返回值，SET和SELECT一样，都将置为NULL

1.1.6、MDX表达式（多维表达式）

​ MDX的全称是Mutil Dimensional Expressions ，是由 Microsoft ，Hyperion 等公司研究多维查询表达式，是所有OLAP 高级分析所采用的核心查询语言。

​ MDX 可以用来进行以下操作：

• ​ calculated members （计算成员）
• ​ Calculated Cells （计算单元）
• ​ Security Settion （安全设置）
• ​ Custom member formula ( 自定义函数 )
• ​ Custom level rollup （）
• ​ Actions （动作）
• ​ Named “ sets ” ( 命名集合 )
• ​ Server side formatting （服务器数据格式化

1.1.7、union和full join on的区别

​ union和full join on的区别，“full join on 列合并和 union 行合并”

	full join 使用on条件时，select * 相当于把两个表(左表有m列p行和右表有n列q行)的所有列拼接成了一个有m+n列的结果表。

​	select * from table1 full join table2 on(table1.student_no=table2.student_no);



	而union相当于把 相当于把两个查询结果(左查询结果表有m列p行和右查询结果表有n列q行)的所有行进行了拼接，形成具有p+q行的查询结果。

​	select student_no tb1_student_no,student_name from table1 union select student_no as tb2_student_no,class_no from table2;
	注意：左查询结果表和右查询结果表，必须有相同的列，即m=q相等，否则会报错误

1.1.8、截取字符串

substr函数

​ 作用：截取字符串

​ 注：SUBSTR( )与SUBSTRING( )意思相等

    SUBSTR (str, pos, len)
    
    
    substring（str, pos） 
    substring（str, pos, length） 
    说明：substring（被截取字段，从第几位开始截取） 
    substring（被截取字段，从第几位开始截取，截取长度） 
    str为列名/字符串；
    pos为起始位置；mysql中的起始位置pos是从1开始的；如果为正数，就表示从正数的位置往下截取字符串（起始坐标从1开始），反之如果起始位置pos为负数，那么 表示			就从倒数第几个开始截取；
    len为截取字符个数/长度。
    
    例：select substring（content,5） as abstract from my_content_t 
    select substring（content,5,200） as abstract from my_content_t 
    （注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度） 
    实例
    SELECT SUBSTR('2018-08-17',6);与SELECT SUBSTR('2018-08-17' FROM 6);意思相同

left

作用：从左开始截取字符串

left（str, length） 
说明：left（被截取字段，截取长度） 
例：select left（content,200） as abstract from my_content_t 

right

作用：从右开始截取字符串

right（str, length） 
说明：right（被截取字段，截取长度） 
例：select right（content,200） as abstract from my_content_t 

substring_index

作用：按关键字截取字符串

substring_index(str,delim,count)

   str:要处理的字符串

   delim:分隔符

   count:计数

例子：str=www.wikibt.com

   substring_index(str,'.',1)

   结果是：www

   substring_index(str,'.',2)

   结果是：www.wikibt

   如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容

1.1.9、explain

作用：查看MySQL中的执行计划

一般的，expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

字段概述：
    id:选择标识符
    select_type:表示查询的类型。
    table:输出结果集的表
    partitions:匹配的分区
    type:表示表的连接类型
    possible_keys:表示查询时，可能使用的索引
    key:表示实际使用的索引
    key_len:索引字段的长度
    ref:列与索引的比较
    rows:扫描出的行数(估算的行数)
    filtered:按表条件过滤的行百分比
    Extra:执行情况的描述和说明
    
字段详解：
	id
		这是SELECT查询序列号，查询序号即为sql语句执行的顺序
    select_type：
        SIMPLE: 指示非子查询和union的简单查询。
        PRIMARY: 指示在有子查询的语句中最外面的select，主查询。
        UNION： 指示在使用union语句的第二个或后面的select。
        DEPENDENT UNION: 子查询union语句的第二个或后面的select
        UNION RESULT：子查询union语句的第二个或后面的select
        SUBQUERY：子查询语句
        DEPENDENT SUBQUERY：子查询中第一个语句。
        DERIVED：派生表的SELECT(FROM子句的子查询)。
    table
    	显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称
    partitions
    	官方定义为The matching partitions(匹配的分区),该字段看table所在的分区, 值为NULL表示表未被分区
    type
    	all: Full Table Scan, 全表扫描.
        index: Full Index Scan, 全索引扫描. index与all区别为index只遍历索引树, 通常比all快, 因为索引文件通常比数据文件小.
        range: 只检索给定范围的行, 使用一个索引来检索行, 可以在key列中查看使用的索引, 一般出现在where条件中, 比如使用between, <, >, in等查询. 这种				 索引的范围扫描比全表扫描要好, 因为索引的开始点和结束点都固定, 不用扫描全索引.
        ref: 非唯一性索引扫描, 返回匹配某个单独值的所有行. 本质上也是一种索引访问, 返回匹配某值(某条件)的多行数据, 属于查找和扫描的混合体.
        eq_ref: 类似ref, 区别在于使用的索引是唯一索引, 对于每个索引键值, 表中只有一条记录匹配, 常见于主键或唯一索引扫描.
        const、system: 表示通过一次索引就找到了结果, 常见于primary key或unique索引, 因为只匹配一行数据, 所以查询非常快. 如将主键置于where列表中, 				   MySQL就能将该查询转换为一个常量. system是const类型的特例, 当查询的表只有一行的情况下, 使用system.
    possible_keys
    	显示可能应用在表中的索引, 可能一个或多个. 查询涉及到的字段若存在索引, 则该索引将被列出, 但不一定被查询实际使用
    key
    	key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
		如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
    key_len
    	表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算		而得，不是通过表内检索出的）
		不损失精确性的情况下，长度越短越好 
    ref
		列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
	rows
		估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
	filtered
		百分比值, 表示存储引擎返回的数据经过滤后, 剩下多少满足查询条件记录数量的比例.
	Extra
		该列包含MySQL解决查询的详细信息,有以下几种情况：
            Using filesort：使用外部的索引排序，而不是按照表内的索引顺序进行读取。（一般需要优化）
            Using temporary：使用了临时表保存中间结果。常见于排序order by和分组查询group by（最好优化）
            Using index：表示select语句中使用了覆盖索引，直接冲索引中取值，而不需要回行（从磁盘中取数据）
            Using where：使用了where过滤
            Using index condition：5.6之后新增的，表示查询的列有非索引的列，先判断索引的条件，以减少磁盘的IO
            Using join buffer：使用了连接缓存
            impossible where：where子句的值总是false

1.1.10、约束

键约束

主键约束

		1、关键字 ：primary key
		2、特点：增加主键约束的列（字段）的值必须是非空 + 唯一的，一个表只有一个主键约束
		3、作用：保证表中不会出现两条无法区分的记录
		4、要求：每一张表都必须有主键约束
		5、分类
			单列主键约束
			复合主键约束

唯一键约束

			1、关键字：unique key
			2、特点：指定了唯一键的列的值必须唯一，不能重复
			3、作用：给主键以外的列，限定唯一性
			4、唯一键分类
				单列的唯一
				复合唯一

唯一键和主键的区别

（1）主键不能为空，唯一键可以为空
（2）主键约束，一个表只能有一个，而唯一键可以有很多个

外键约束

外键约束不是必须的，而且现在很多大的公司，数据量比较大时，不建议在数据库层面设计外键，
因为他觉得这样效率低，把这个数据的约束挪到代码层面去判断。

	1、关键字：foreign key
	2、特点：
	（1）约束的是两张表的关系
		需要两张表，或者一张表虚拟成两张表
	（2）两张表分为主表（父表）和从表（子表）
		外键的建立/指定是在从表（子表）上建立。
	（3）被参考的表称为主表，主表的被参考列必须是主键或唯一键
	（4）一个表可以有多个外键

​

非空约束

​ not null，某一列的值不能为null

默认约束

​ default默认值，在插入数据时某列如果没指定其他的值,那么会将默认值添加到新记录。

自增约束

	1、关键字：auto_increment
	2、特点：
	（1）一个表只能有一个自增列
	（2）自增列必须是整型的
	（3）自增列必须是键列，例如：主键，唯一键

1.1.11、sql执行顺序

1. from

2. join

3. on

4. where

5. group by(开始使用select中的别名，后面的语句中都可以使用)

6. avg,sum…

7. having

8. select

9. distinct

10. order by

11. limit

    from -> join -> on -> where -> group by -> avg/sum… -> having -> select -> orderby -> limit

1.1.12、select *、select 1与select anycol

select 1 from table与select anycol(目的表集合中的任意一行） from table与select * from table 从作用上来说是没有差别的，都是查看是否有记录，一般是作条件查询用的。

select 1 from 中的1是一常量（可以为任意数值），查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表

1.1.13、OLTP、OLAP与HTAP

1.1.14、顺序读写和随机读写

顺序读写比随机读写快的原因

​ ①顺序读写，主要时间花费在了传输时间，而这个时间两种读写可以认为是一样的。 随机读写，需要多次寻道和旋转延迟。而这个时间可能是传输时间的许多倍。

​ ②顺序读写，磁盘会预读，预读即在读取的起始地址连续读取多个页面 (现在不需要的页面也读取了，这样以后用时就不用再读取，当一个页面用到时，大多数情况下，它周围的页面也会被用到) 而随机读写，因为数据没有在一起，将预读浪费掉了。

​ ③另一个原因是文件系统的overhead。 读写一个文件之前，得一层层目录找到这个文件，以及做一堆属性、权限之类的检查。 写新文件时还要加上寻找磁盘可用空间的耗时。 对于小文件，这些时间消耗的占比就非常大了。

1.1.15、内存读数据和磁盘读数据

​ 当程序要读取的数据时传入内存地址（行地址+列地址），如果数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，通过柱面号，磁头号，扇区号定位磁盘位置，找到数据的起始位置并向后连续读取一页或几页载入内存中。

​ 由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

​ 当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

​ 由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

​ 预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

小结：在日常项目开发中在设计存储时，一定要考虑顺序和随机，优化其性能达到最高。例如，当前大多数数据库使用的都是传统的机械磁盘，因此,整个系统设计要尽可能顺序I/O，避免昂贵的寻道时间和旋转延迟的开销

1.2、大数据

1.2.1、数据仓库分层

数据运营层：ODS（Operational Data Store）

​ ODS层：原始数据层，存放原始数据，直接加载原始日志、数据，数据保持原貌不做处理

数据仓库层：DW（Data Warehouse）

​ DWD层：对ODS层数据进行清洗（去除空值，脏数据，超过极限范围的数据）、维度退化脱敏等

​ DWS层：以DWD为基础，按天进行轻度汇总。又称为数据集市或者宽表，按照业务划分，例如流量，订单，用户等，生成字段比较多的宽表，用于后续的业务 查询，OLAP分析，数据分析等

​ DIM层：基于维度建模理念思想，建立整个企业的一致性维度。降低数据计算口径和算法不统一风险。公共维度层的表通常也被称为逻辑维度表，维度和维度逻 辑表通常一一对应

数据应用层：ADS（Application Data Store）

​ ADS层：应用层是根据业务需要，由前面三层数据统计而出的结果，可以直接提供查询展现，或传输到下游应用

1.2.2、ETL和ELT的联系和区别

ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。ETL一词较常用在数据仓库，但其对象并不限于数据仓库。ETL是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去。

ELT，只负责提供图形化的界面来设计业务规则，数据的整个加工过程都在目标和源的数据库之间流动，ELT协调相关的数据库系统来执行相关的应用，数据加工过程既可以在源数据库端执行，也可以在目标数据仓库端执行（主要取决于系统的架构设计和数据属性）。当ETL过程需要提高效率，则可以通过对相关数据库进行调优，或者改变执行加工的服务器就可以达到。

ETL 维护比较复杂，是因为 Transform （转换）和 Load （加载）两个环节耦合过紧导致的，最底层的改变是E、T、L彻底的解耦了。解耦之后好处多多，比如突破性能瓶颈、程序简化、组件替换、维护成本降低等等。不过最重要的还是解耦导致的极致的灵活，可以适应当前复杂多变的市场环境。

1.2.3、常用的数据压缩方式及对比

数据压缩方式	优点	缺点	使用场景
gzip	压缩比在几种压缩方式中较高；hadoop本身自带，在应用中处理gzip格式的文件就和直接处理文本一样；有hadoop native库；大部分linux系统都自带gzip命令，使用方便	不支持split，速度较慢	如果压缩后大小和Block差不多大可以使用。也适合磁盘不富裕要求压缩比且对压缩时间无特别要求的场景，如异步离线压缩归档，比如HBase写入后的刷盘
lzo	压缩/解压速度也比较快，合理的压缩率；支持split，是hadoop中最流行的压缩格式；支持hadoop native库；需要在linux系统下自行安装lzop命令，使用方便	压缩率比gzip要低；hadoop本身不支持，需要安装；lzo虽然支持split，但需要对lzo文件建索引，否则hadoop也是会把lzo文件看成一个普通文件（为了支持split需要建索引，需要指定inputformat为lzo格式）	特点是支持切片
snappy	压缩速度快；支持hadoop native库；需要注意的是，实际生产环境中Snappy的表现通常比 LZO 好，应当进行测试对比再决定	不支持split；压缩比低；hadoop本身不支持；linux系统下没有对应的命令	实时写入的Hive底层HDFS文件可用LZO方式压缩
bzip2	支持split；具有很高的压缩率，比gzip压缩率都高；hadoop本身支持，但不支持native；在linux系统下自带bzip2命令，使用方便	压缩/解压速度慢；不支持native	适合很老的、极低频使用的历史文件（冷数据）归档

总结：不同的场景选择不同的压缩方式，肯定没有一个一劳永逸的方法，如果选择高压缩比，那么对于cpu的性能要求要高，同时压缩、解压时间耗费也多；选择压缩比低的，对于磁盘io、网络io的时间要多，空间占据要多；对于支持分割的，可以实现并行处理。

1.3、网络

1.3.1、VPC

私有网络（VPC）是针对公有云的基础网络（经典网络）来定义的一种概念。VPC（Virtual Private Cloud）是公有云上自定义的逻辑隔离网络空间，是一块可我们自定义的网络空间，与我们在数据中心运行的传统网络相似，托管在VPC内的是我们在私有云上的服务器资源，如云主机、负载均衡、云数据库等。我们可以自定义网段划分、IP地址和路由策略等，并通过安全组和网络ACL等实现多层安全防护。同时也可以通过VPN或专线连通VPC与我们的数据中心，灵活部署混合云。

使用私有网络的好处

1）灵活部署：自定义网络划分、路由规则、配置实施立即生效

2）安全隔离：100%逻辑隔离的网络空间，我的地盘听我的

3）丰富接入：支持公网VPN接入和专线接入

4）访问控制：精确到端口的网络控制，满足金融政企的安全要求

私有网络（VPC）于基础网络（经典网络）区别

经典网络：公有云上所有用户共享公共网络资源池，用户之间未做逻辑隔离。用户的内网IP由系统统一分配，相同的内网IP无法分配给不同用户。

VPC：是在公有云上为用户建立一块逻辑隔离的虚拟网络空间。在VPC内，用户可以自定义网段划分、IP地址和路由策略，安全可提供网络ACL及安全组的访问控制，因此，VPC由更高的灵活性和安全性。

1.4、算法

1.4.1、pearson相关系数

​ Pearson相关系数（Pearson Correlation Coefficient）是用来衡量两个数据集合是否在一条线上面，它用来衡量定距变量间的线性关系。

​ 相关系数解释：

• 0.8-1.0 极强相关
• 0.6-0.8 强相关
• 0.4-0.6 中等程度相关
• 0.2-0.4 弱相关
• 0.0-0.2 极弱相关或无相关

​ 对于x,y之间的相关系数r ：

• 当r大于0小于1时表示x和y正相关关系
• 当r大于-1小于0时表示x和y负相关关系
• 当r=1时表示x和y完全正相关，r=-1表示x和y完全负相关
• 当r=0时表示x和y不相关

1.5、其他

1.5.1、PaaS

​ PaaS是（Platform as a Service）的缩写，是指平台即服务。 把服务器平台作为一种服务提供的商业模式，通过网络进行程序提供的服务称之为SaaS(Software as a Service)，是云计算三种服务模式之一， [4] 而云计算时代相应的服务器平台或者开发环境作为服务进行提供就成为了PaaS(Platform as a Service)。

云计算中讨论的服务包括基础设施即服务（IaaS），平台即服务（PaaS）和软件即服务（SaaS）三个层次的服务。 平台即服务（PaaS）是云计算的重要组成部分，提供运算平台与解决方案服务 [1] 。在云计算的典型层级中，PaaS层介于软件即服务与基础设施即服务之间。PaaS提供用户将云端基础设施部署与创建至客户端，或者借此获得使用编程语言、程序库与服务。用户不需要管理与控制云端基础设施（包含网络、服务器、操作系统或存储），但需要控制上层的应用程序部署与应用托管的环境。PaaS将软件研发的平台做为一种服务，以软件即服务（SaaS）模式交付给用户。PaaS 提供软件部署平台（runtime），抽象掉了硬件和操作系统细节，可以无缝地扩展（scaling）。开发者只需要关注自己的业务逻辑，不需要关注底层。即PaaS 为生成、测试和部署软件应用程序提供一个环境。

​ PaaS 是云中的完整开发和部署环境，其资源使组织能够提供从简单的基于云的应用到复杂的支持云的企业应用程序的所有内容。 资源是按照“即用即付”的方式从云服务提供商处购买的，并通过安全的 Internet 连接进行访问。

1.5.2、数据建模之总线矩阵

定义：总线矩阵提供一种分解企业DW/BI规划任务的合理方式，行是业务过程，列是公共维度（一致性维度），图表中的X表示的是哪些列与哪些行有关系，也表示这一个业务过程需要有哪些公共维度。

参考：https://blog.csdn.net/qq_36690988/article/details/103402918

1.5.3、粗粒度和细粒度

​ 理解：粒度似乎是根据项目模块划分的细致程度区分的，一个项目模块（或子模块）分得越多，每个模块（或子模块）越小，负责的工作越细，就说粒度越细，否则为粗粒度。

​ 粗粒度与细粒度是一个相对的概念，也不是说对所有会涉及到粗粒粒度问题的工作都是一致偏向的。粗粒度和细粒度的区别主要是出于重用的目的。像类的设计,为尽可能重用,所以采用细粒度的设计模式,将一个复杂的类(粗粒度)拆分成高度重用的职责清晰的类(细粒度)。 对于数据库的设计，原则:尽量减少表的数量与表与表之间的连接,能够设计成一个表的情况就不需要细分,所以可考虑使用粗粒度的设计方式。

例如在java中

粗粒度：表示类别级，即仅考虑对象的类别(the  type  of  object)，不考虑对象的某个特定实例。比如，用户管理中，创建、删除，对所有的用户都一视同仁，并不区分操作的具体对象实例。  

细粒度：表示实例级，即需要考虑具体对象的实例(the  instance  of  object)，当然，细粒度是在考虑粗粒度的对象类别之后才再考虑特定实例。比如，合同管理中，列表、删除，需要区分该合同实例是否为当前用户所创建。