【資料庫】 postgresql 和 SQL初級使用（TPC-H資料應用）

標籤：

工     具：postgresql

 運行環境：ubantu 14.0

資料來源：TPC-H dbgen

 

TASK1:

1.下載postgresql(sudo install postgresql 即可)，成果如下。

 

TASK2:

1.按照教程操作，進入dbgen檔案夾修改makefile檔案。

  然後執行make運用TPC-H產生資料。這裡可以看http://www.cnblogs.com/joyeecheung/p/3599698.html 的前面5點。

另外，TPC-H的關係圖檔案在官網上就可以找到：http://www.tpc.org/tpc_documents_current_versions/pdf/tpch2.17.1.pdf

 

2.將tbl格式檔案轉換成json格式檔案。

 

1）代碼：主要使用c++檔案讀寫來完成，讀取指定tbl檔案，並按照tpch官

 網介紹的資料模型來轉換，寫json檔案，樣本為轉換orders.tbl檔案。

當然這裡為了想的快，就直接一個一個表格轉換，也可以直接對所有的檔案同時進行轉換操作。

 

#include<iostream>#include<fstream>#include<string>#include<vector>using namespace std;vector<string> split(string s, string c) {string::size_type pos;vector<string> result;int size = s.size();for (int i = 0; i < size; i++) {pos = s.find(c, i);if(pos < size) {result.push_back(s.substr(i, pos-i));i = pos + c.size() -1;}}return result;}

　　

int main() {string s;int i, j;ifstream in;in.open("orders.tbl");ofstream out;out.open("orders.json");string title[9] = { "\"orderkey\"","\"custkey\"","\"orderstatus\"","\"totalprice\"","\"orderdate\"","\"orderpriority\"","\"clerk\"","\"shippriority\"","\"comment\""};if (in.is_open()) {for (i = 1; getline(in, s); i++) {vector<string> result = split(s, "|");/*if (i == 3270184 ){cout << "!!!" << s << endl;break;}*/s = "\t{";if (result.size() == 9) {out << i;for (j = 0; j < 8; j++) {s = s + title[j] + ":\"" + result[j] + "\",";}s = s + title[j] + ":\"" + result[j] + "\"}\n";out << s;}}out.close();}}

　

2)效果：我的電腦就要崩了.jpg

 

 

 

TASK3:

1.建立資料庫和對應的表，並向裡面匯入轉換好的json格式檔案。

 

 

2.開始查詢。 

  1）開始查詢之前先學習SQL查詢的知識，尤其是要知道它的選表、拼表原理，比如select、from、where、group by等等的語句的執行順序，from的原理，否則你開始做題目的時候就要擔心你的查詢語句是否是你所期望的了，以及相關的聚集合函式的用法等等；

  2）要弄清楚這個資料庫的三個資料實體customer，part，supplier以及他們之間的聯絡集之間的關係，這樣才能使用恰當的方法查詢到你想要的查詢結果。

 

 

Q1:Display the key and quantity of parts which are shipped in March 13th, 1996.

查詢語句（SQL）：

select data->>‘partkey‘ as part, data->>‘quantity‘ as quantity    from lwhere data->>‘shipdate‘ = ‘1996-03-13‘;    

查詢結果：

 

 

 

Q2: For each ship mode, ?nd the total quantity of items that are shipped before 

查詢語句（SQL）：

select sum(cast(data->>‘quantity‘ as numeric)) as total, data->>‘shipmode‘ as shipmode                                                            from lwhere data->>‘shipdate‘ < ‘1998-12-01‘group by data->>‘shipmode‘order by total asc;

查詢結果：

 

Q3：Display the total quantity of parts which are satis?ed the following conditions in LINEITEM: – the ship mode is AIR – the priority of the order that the parts belong to is URGENT

查詢語句（SQL）：

select sum(cast(data->>‘quantity‘ as numeric)) as totalfrom lwhere data->>‘shipmode‘=‘AIR‘ and data->>‘orderkey‘ in (select data->>‘orderkey‘ from o where data->>‘orderpriority‘=‘1-URGENT‘);

查詢結果：

 

 

 

Q4：

查詢語句（SQL）：

select data->>‘name‘ as name, data->>‘nationkey‘ as nation,data->>‘regionkey‘ as regionfrom nwhere data->>‘nationkey‘ in (select s.data->>‘nationkey‘ from s, ps where s.data->>‘suppkey‘=ps.data->>‘suppkey‘ and ps.data->>‘partkey‘=‘100‘ and ps.data->>‘partkey‘ < ps.data->>‘suppkey‘);  

查詢結果：

 

 

Q5：

查詢語句（SQL）：

select count(distinct data->>‘custkey‘)from cwhere data->>‘custkey‘ in (select o.data->>‘custkey‘from o, lwhere o.data->>‘orderkey‘ = l.data->>‘orderkey‘ and l.data->>‘partkey‘ in (select data->>‘partkey‘from pwhere data->>‘type‘ like ‘%STEEL%‘));

查詢結果：

 

 

 

Q6：

查詢語句（SQL）：

（方法1，效率比較慢）：

select data->>‘partkey‘ as key, data->>‘name‘ as name, data->>‘brand‘ as brand, (select sum(cast(l.data->>‘quantity‘ as numeric)) as total from l where l.data->>‘shipdate‘ like ‘%1997%‘ and l.data->>‘partkey‘ = p.data->>‘partkey‘group by l.data->>‘partkey‘)from porder by total desc limit 10;

（方法2，效率比較快）：

select p.data->>‘partkey‘ as key, p.data->>‘name‘ as name, p.data->>‘brand‘ as brand, s.totalfrom p, (select l.data->>‘partkey‘ as partkey, sum(cast(l.data->>‘quantity‘ as numeric)) as totalfrom l where l.data->>‘shipdate‘ like ‘%1997%‘group by l.data->>‘partkey‘order by total desclimit 10) as swhere p.data->>‘partkey‘ = s.partkey;

查詢結果：

方法1和方法2的主要差別其實就是先選10行再拼表還是先拼表再選行，法2十幾秒內就可以出結果，法1需要更長的時間。當然，我其實還有法0，直接將l和p進行拼表然後選取……睡覺前輸入查詢，睡醒了都沒出結果（七八個鐘頭）= =。

 

Q7：

查詢語句（SQL）：

select c.data->>‘custkey‘ as customer, c.data->>‘name‘ as name,c.data->>‘address‘ as address, c.data->>‘phone‘ as phone,   op.count as count   from c, (select o.data->>‘custkey‘ as cust, count(*) as count     from o   group by o.data->>‘custkey‘   having count(*) >= 10) as op   where c.data->>‘custkey‘ = op.cust;   

 

查詢結果：

 

 

 

 

Q8：

查詢語句（SQL）：

方法1（由Q6受到啟發，降序選第一個即為最大值）

select cast(data->>‘extendedprice‘ as numeric) as maxpricefrom l, (select data->>‘orderkey‘ as orderkey from owhere data->>‘orderdate‘ like ‘%1998%‘) as opwhere l.data->>‘orderkey‘ = op.orderkeyorder by maxprice desclimit 1;

方法2（正常人的思維，應用sum的聚集合函式）

select max(cast(lp.price as numeric)) as maxextendedpricefrom (select l.data->>‘extendedprice‘ as price from l where l.data->>‘orderkey‘ in (select o.data->>‘orderkey‘from owhere o.data->>‘orderdate‘ like ‘%1998%‘)) as lp;

查詢結果：

 

 

可以發現這兩種方法得到的查詢結果是一樣的~至於效率，直觀感覺是差不多的，如果資料庫的排序是通過堆排序的話，那確實從演算法上來說兩種方法的效率是一樣的。（特別要注意的是使用排序的時候要先將資料強制轉換成numeric類型，否則此時是文字格式設定，比較的結果就會是9999……）

 

Q9：

查詢語句（SQL）：

select sum(cast(l.data->>‘quantity‘ as numeric)) as total, l.data->>‘shipmode‘ as modefrom lwhere l.data->>‘orderkey‘ in (select o.data->>‘orderkey‘ from owhere o.data->>‘orderdate‘ like ‘1995-05%‘) and l.data->>‘shipmode‘ <> ‘TRUCK‘ and l.data->>‘shipmode‘ <> ‘RAIL‘group by l.data->>‘shipmode‘; 

查詢結果：

 

 

 

心得體會：

1.安裝過程完全按照教程來走，沒有遇到任何問題。

2.其實這次作業是很簡單的，但開始的時候我很盲目地就開始做作業，都沒搞清楚原理，遇到什麼屬性就去轉化什麼檔案再匯入進資料庫。後來把資料全刪掉了，首先先弄明白TPC-H的實體和聯絡之間的關係，也才能正確理解題意。第二件重要的事情就是學習SQL語句，尤其重點是語句執行的順序，因為先執行group by、having再執行where還是順序顛倒，得到的結果是很不一樣的。

3.查詢的時候我的模式基本上是：滿足XX的並且XX的XX的XX……先找主語再找所在的表。

 

4.SQL查詢在資料很大的時候查詢反應好慢……然後聽同學說建立索引來進行查詢，都只是幾秒的事情……

【資料庫】 postgresql 和 SQL初級使用（TPC-H資料應用）