将查询集SQL-存为物理 OR 临时表

2022-10-19 19:08:19

最近的BI项目, 就是会涉及大量的 sql, 后台处理也全是 sql 来拼接成一张物理宽表, 然后前台也是用 sql 来做各种图形骚操作. 尤其是后台处理部分, 大量的sql, 有点尴尬的事情是, sql 好像没有编程语言的 "数据结构", 只是一些数据类型. 不像编程语言, 比如我最熟悉的 Python, 它有列表, 有二维数组, 或者在分析中最常用的 DataFrame, 可以用一个变量来进行指向. SQL 就尴尬了, 我在写的时候, 经常会有各种, 嵌套或子查询的代码, 大多是重复的, 不断复制粘贴, 会显得sql 很长, 且性能和可读性都比较差. 因此, 需要一个, "临时变量" 来存储中间过程, 就跟咱编程一样, 定义变量来指向某个 "数据结构" 是一样的.

虽然我当前用的是 IQ Sybase 数据库, 它也是关系型, 列式的, 查询贼快, 写入倒不大行. 关系型 sql 都是基于标准 sql 差不多的呀, 只是一个语法, 函数之类的不同而已. 这里呢, 以 mysql 为例, 切换会上网搜索就行.

我最需要的一个操作, 通常就是, 将一段 sql 查询的结果进行分情况处理, 大致如下:

存为物理表: create table 物理表名 as ( 查询集sql ) ;
insert 的方式: insert into 表名 (查询集sql); 前提是表存在哦;
存为临时表: create temporary table as (查询集sql) ;
存为视图表: crate view 视图名 as (查询集sql) ;

...

大致就这些情况, 然后来分别演练一把, 也是总结一下, 方面自己做笔记储备, 以后复制粘贴能更快一些呢.

数据准备

查询集 sql , 我也是随便写了一段, 用的是比较熟悉的超市数据集, 即 Tableau 自带的那个, 维度和度量都比较合适, 比较适合用来做 Demo 分析的呢.

如何将 Excel / csv 数据转为 sql : https://www.cnblogs.com/chenjieyouge/p/12865933.html

Pandas 将Excel 写入 Mysql: https://www.cnblogs.com/chenjieyouge/p/11811784.html

终端 : load data local infile 'D:/tb_01.csv' into cj_tb fields terminated by ',' lines terminated by '\n';

如何将数据导入数据库这种基础必备操作, 就不想谈了, 一搜一大把, 网上, 开心就好~

-- 不同地区的销售额
select 
	region, 
	sum(sales) as sum_sales
from cj.super_market 
group by region

+--------+------------+
| region | sum_sales  |
+--------+------------+
| 东北   | 2681567.48 |
| 中南   | 4137415.09 |
| 华东   | 4684506.51 |
| 华北   | 2447301.07 |
| 西北   |  815039.57 |
| 西南   | 1303124.51 |
+--------+------------+
6 rows in set (0.01 sec)

然后呢, 需要基于此, 查询集, 再增加个字段, N_S 就分为 "南方" 和北方. 当然正常操作是在底表上做, 这里只是,为了演示, 为了 "嵌套" 而嵌套哈.

select 
  region, 
  sum_sales, 
  case 
  	when region like '%北%' then '北方' 
  	when region in ('西南', '中南') then '南方'
  	else '未知'  
  end as N_S
from (
	select 
		region, 
		sum(sales) as sum_sales
	from cj.super_market 
	group by region 
) as a

+--------+------------+--------+
| region | sum_sales  | N_S    |
+--------+------------+--------+
| 东北   | 2681567.48 | 北方   |
| 中南   | 4137415.09 | 南方   |
| 华东   | 4684506.51 | 未知   |
| 华北   | 2447301.07 | 北方   |
| 西北   |  815039.57 | 北方   |
| 西南   | 1303124.51 | 南方   |
+--------+------------+--------+
6 rows in set (0.01 sec)

类似的, 子查询, 我是几乎每天都要这样写很多.... 而且远远比这个更加复杂, 嵌套也更多, 在性能和可读性上都非常难受, 自己都维护不了, 因此, 尽量要少用多层子查询, 而将一段逻辑就存为一个临时表多好.

存为临时表 temporary

临时表, 在会话结束后, 就会自动销毁, 类似于程序中的变量嘛, 用完就回收了, 尤其是在优化查询逻辑, 且不需要长期占用物理内存的时候, 特别有用哦.

对于查询集的写法: **create temporary table 表名 as (查询集sql) ; **

-- 可将第一个 group by查询集, 存为一个临时表 
drop table if exists tmp_market_01;  

create temporary table tmp_market_01 as (
	select 
		region, 
		sum(sales) as sum_sales
	from cj.super_market 
	group by region 
	
);

这样就将临时表 tmp_market_01 建好了, show tables; 是找不到该临时表的, 当然原理是它存在其他地方了哈, 当会后结束后, 就会自动销毁了呢. 最大用处在于可以优化子查询. 于是呢, 上面的子查询就可以这样优化了.

drop table if exists tmp_market_01;  

create temporary table tmp_market_01 as (
	select 
		region, 
		sum(sales) as sum_sales
	from cj.super_market 
	group by region 
	
); 

select 
  region, 
  sum_sales, 
  case 
  	when region like '%北%' then '北方' 
  	when region in ('西南', '中南') then '南方'
  	else '未知'  
  end as N_S
  
-- 查询集, 作为临时, 极大提高了性能和可读性, 难道不香嘛
from tmp_market_01;

+--------+------------+--------+
| region | sum_sales  | N_S    |
+--------+------------+--------+
| 东北   | 2681567.48 | 北方   |
| 中南   | 4137415.09 | 南方   |
| 华东   | 4684506.51 | 未知   |
| 华北   | 2447301.07 | 北方   |
| 西北   |  815039.57 | 北方   |
| 西南   | 1303124.51 | 南方   |
+--------+------------+--------+
6 rows in set (0.01 sec)

临时表用来, 优化子查询, 是真的香哇.. 尤其是我上篇那个, 嵌套了 6层的大sql, 简直是临时表优化, 势在必行呢. 这就跟 "变量" 一样, 每一个复杂的步骤, 就建一个临时表 (当然也不要太频繁, IO 也会影响性能), 这样, 对我作为开发来说, 是 可读性和可维护性 极大提高, 反例就来看看我之前的嵌套 5-6层的大SQL, 绝对让你们 "大开眼界".

存为视图 view

相对于存为临时表, 将查询结果集. 我基于之前的经验, 更多是存为一个视图. 当然视图本质上就是一个一段sql , 不是真正的表, 只是一段 "封装的sql 逻辑" , 并不会有多少查询上的优化, 好处是随着其物理表动态变化的, 我也是用的比较多些.

对于查询集的写法: **create view table 视图名 as (查询集sql) ; **

-- 存为视图, 同样能在可读性上做优化的
drop view if exists v_market_01;  

create view v_market_01 as (
	select 
		region, 
		sum(sales) as sum_sales
	from cj.super_market 
	group by region 
	
); 

select 
  region, 
  sum_sales, 
  case 
  	when region like '%北%' then '北方' 
  	when region in ('西南', '中南') then '南方'
  	else '未知'  
  end as N_S
-- 查询集作为视图, 只是隐藏逻辑而已, 其实性能上并未优化 
from v_market_01;

此时呢, 再进行 show tables; 就可以看到该视图了, 跟临时表不同. 如果是要经常用到的话, 当然建议用视图, 如果是优化性能和逻辑, 如生成宽表, 纯用 sql 的话, 临时表也许更加合适, 具体情景, 具体分析吧. 查询优先用视图, 写罗写打断逻辑, 优化用临时表.

存为物理表

也分为两种方式, create 和 insert into.

create 的方式, 如果表已经存在, 则会报错嘛 => create table 表名 as (查询集 sql);
insert into 的方式, 如表存在, 则会继续 insert, 表不存在, 则报错 => insert into 表名 (s查询集 sql);

IQ Sybase 中, create 方式的写法是 select * into 表名 from (sql 查询集)

具体怎么用, 也是看场景的, 比如我上篇那个, 需要先建一个目标表, 然后分步骤, 不断用查询集往里面 insert 数据; 另一种就是在数据处理的时候, 需要这么一张物理表, 就直接用 select 的方式了呀.

create 方式

对于查询集的写法: **create table 表名 as (查询集sql) ; **

drop table if exists tb_market_01;  

create table tb_market_01 as (
	select 
		region, 
		sum(sales) as sum_sales
	from cj.super_market 
	group by region 
	
);

如果表重复就会报错, 当然, 通常有配合 if exists ... 将结果存为为物理表, 尤其是我做BI的时候, 会经常用的, 基于一大堆或者多段的 sql 生成了一个 "宽表" 将其存为一个真实数据集, 在用 BI来连上前台, 继续进行各种 sql 操作.

insert into 方式

对于查询集的写法: **insert into table 表名 (查询集sql) ; **

-- insert into 表 (查询集),  这里没有 as 哦; 表不存在则报错
insert into tb_market_01  (
	select 
		region, 
		sum(sales) as sum_sales
	from cj.super_market 
	group by region 
	
);

当然还有一种骚操作, 就是先存一个空表字段, 然后再不断地 insert , 我有用过这样的.

drop table if exists tb_market_01;  

create table tb_market_01 as (
	select 
		region, 
		sum(sales) as sum_sales
	from cj.super_market 
    -- 这就跟创建表, 写成每个字段其实是一样的呢
	where 1 = 2
	group by region 
	
	
);

小结

create temporary table 表名 as (查询集sql) ;
create view table 视图名 as (查询集sql) ;
create table 表名 as (查询集sql) ;
insert into table 表名 (查询集sql) ;

码农公寓