【数据分析实战经验】0415 近期思考

最新推荐文章于 2024-04-30 17:45:50 发布

Mr_charm

最新推荐文章于 2024-04-30 17:45:50 发布

阅读量278

点赞数

分类专栏：数据分析实战经验文章标签：经验分享大数据数据分析

本文链接：https://blog.csdn.net/Mr_charm/article/details/115391520

版权

数据分析实战经验专栏收录该内容

15 篇文章 6 订阅

订阅专栏

近期思考这个系列，是把我在工作时、上下班的地铁上，所思所想所惑之处写出来。

一、PG数据库复用

实际工作中，SQL代码复用比较不方便，因为涉及到建临时表，所以在不用pysql的情况下，思考如何在PG库本身复用。

第一种：通过定义函数实现

CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNS text
AS
$$
 SELECT CASE
        WHEN $3 THEN UPPER($1 || ' ' || $2)
        ELSE LOWER($1 || ' ' || $2)
        END;
$$
LANGUAGE SQL IMMUTABLE STRICT;

##使用一下
SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World');

第二种：通过定义参数实现

--## 设置一个session级别的参数，通过current_setting取值
--之后每次都修改这个即可，亲测不会影响到原先加过的索引
set session "asasd.time" to "2021-3-25";

## 使用一下

set session "asasd.begin_time" to "2021-3-25";
set session "asasd.end_time" to "2021-3-28";
set session "asasd.ouzhou" to "1910";
set session "asasd.yazhou" to "10468";
set session "asasd.mezhou" to "20566";


create temp table a as
select * from gos_dws_tran_local_1
	where  local_date between current_setting('asasd.begin_time')::date  
	and current_setting('asasd.end_time')::date 
	and ((server_id between '1' and current_setting('asasd.ouzhou')::integer)
	or (server_id between'10001'and current_setting('asasd.yazhou')::integer)
	or (server_id between '20001' and current_setting('asasd.mezhou')::integer));

二、关系型数据库与非关系型数据库

关系型数据库

定义：关系型数据天然就是二维表格式的，因此存储在数据表的行和列中。数据表可以彼此关联协作存储，也很容易提取数据。

优点：
1、容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解
2、使用方便：通用的SQL语言使得操作关系型数据库非常方便
3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率

缺点
1、网站的用户并发性非常高，往往达到每秒上万次读写请求，对于传统关系型数据库来说，硬盘I/O是一个很大的瓶颈
2、网站每天产生的数据量是巨大的，对于关系型数据库来说，在一张包含海量数据的表中查询，效率是非常低的
3、在基于web的结构当中，数据库是最难进行横向扩展的，当需要对数据库系统进行升级和扩展时，往往需要停机维护和数据迁移。

举例：Oracle，Microsoft SQL Server，MySQL，PostgreSQL

非关系型数据库

定义：指非关系型的，分布式的，以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。

优点：
1、用户可以根据需要去添加自己需要的字段，为了获取用户的不同信息，不像关系型数据库中，要对多表进行关联查询。仅需要根据id取出相应的value就可以完成查询。
2、应用场景广泛

缺点：
1、只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，关系型数据库显的更为合适。不适合持久存储海量数据
2、无事务处理。

举例：Redis、MongoDB、Microsoft Azure Cosmos DB

关系型与非关系型数据库的比较

1、成本：Nosql数据库简单易部署，基本都是开源软件，不需要像使用Oracle那样花费大量成本购买使用，相比关系型数据库价格便宜。
2、查询速度：Nosql数据库将数据存储于缓存之中，而且不需要经过SQL层的解析，关系型数据库将数据存储在硬盘中，自然查询速度远不及Nosql数据库。
3、存储数据的格式：Nosql的存储格式是key,value形式、文档形式、图片形式等等，所以可以存储基础类型以及对象或者是集合等各种格式，而数据库则只支持基础类型。
4、持久存储：Nosql不使用于持久存储，海量数据的持久存储，还是需要关系型数据库
5、数据一致性：非关系型数据库一般强调的是数据最终一致性，不像关系型数据库一样强调数据的强一致性，从非关系型数据库中读到的有可能还是处于一个中间态的数据，Nosql不提供对事务的处理。

三、数据中台

定义：目标是为企业的前台业务和后台系统提供数据支持，数据中台是一个用技术连接大数据计算存储能力，用业务连接数据应用场景能力的平台，通过数据技术，对海量数据进行采集、计算、存储、加工，同时统一标准和口径
简单来说就是数据仓库+数据服务中间件

构成：
数据存储：可以高效存储结构数据、非结构数据、离线数据、实时数据等等，提供数据应用的物质基础

数据治理：可以解决元数据管理、数据标准、数据清洗等问题，帮助数据标准和质量统一

数据共享：提供各类数据服务，诸如数据推送、加密脱敏、安全审计等功能，实现数据的高效调用。
例如：决策支持类：主题报表（月度/季度/年度/专题）、、大屏数据可视化展示
数据分析类：BI商业智能、OLAP分析、数据挖掘、数据驱动的机器学习
数据检索类：全文检索、日志分析、数据血缘分析、数据地图
数据共享开放类：实时数据订阅、离线数据接触、数据API接出

解决什么问题：
1、各业务之间数据孤岛、有效复用数据（最简单的就是统一数据字典之类的）。
2、可复用，提升服务效率

附上阿里云数据中台的构建流程

在这里插入图片描述

四、一些补充

sql语句写完之后的执行情况

客户端、连接器、查询缓存、分析器、优化器、执行器、存储引擎、调用文件系统
在这里插入图片描述

explain 的字段

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

重点看的

1、其中system、const是基本达不到的理想状态，只能优化到index--range--ref这个级别，同时all也是最差的一个级别
2、ref   非唯一性索引，对于每个索引键的查询，返回匹配的所有行（可以0，可以1，可以多）。
      eq_ref    唯一性索引，对于每个索引键的查询，返回匹配唯一行数据(有且只有1个，不能多 、不能0)，并且查询结果和表中数据条数必须一致。此种情况常见于唯一索引和主键索引。
3、range   检索指定范围的行 ,where后面是一个范围查询(between, >, <, >=, in),in有时候会失效，从而转为无索引时候的ALL
4、index  查询全部索引中的数据(扫描整个索引)
5、ALL   查询全部源表中的数据(暴力扫描全表)

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

随机森林的超参数

1、一个是对决策树的个数进行调优，正常是对n_estimators参数择优，其他参数仍然是默认值。
调优法是GridSearchCV，网格法
在这里插入图片描述

2、另一个是优化决策树参数的最大特征数max_features，其他参数设置为常数，且n_estimators为之前输出的最优解。
在这里插入图片描述

元组、列表、集合、字典的区别

1、首先英文名分别是tuple、list、set、dict
2、除了元组只支持读，不支持修改，其他类别都支持读写
3、除了集合不可重复外，其他都可重复、
4、字典的存储方式是键值对。集合是键（不重复）
5、集合、字典是无序的，列表、元组是有序的
6、列表可以使用多种方法实现添加和修改列表元素，而元组没有办法，因为不能向元组中添加或修改元素，同样也不能删除元素，如果是仅访问，不进行修改，建议使用元组
7、列表、字典、元组、集合都支持包含多类别的数据