将查詢集SQL-存為實體 OR 臨時表

将查詢集 sql 存為 臨時表, 實體表, 視圖 等操作, 用來優化邏輯, 提高可讀性等.

最近的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 

Pandas 将Excel 寫入 Mysql:​

終端 : 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) ;