改善 Programmer 生活的 SQL 技巧

改善 Programmer 生活的 SQL 技巧

改善 Programmer 生活的SQL 技巧

REF:
http://sqlfiddle.com/#!2/fbf31/5/0

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

DEMO DB
Host ec2-107-20-224-218.compute-1.amazonaws.com
Database dbkrqa4hb2ckgl
User qzacwszmuccqrc
Port 5432
Password SByaWWQCEiCYqwNAfl8iwCmqPa

2227f4c4854b68d4c8c82355131fe500?s=128

Rack Lin (阿土伯)

July 01, 2013
Tweet

Transcript

  1. 改善 Programmer  生活的   SQL  技能 #1   Rack  Lin

     阿土伯  @RubyTue24  
  2. about:me   •  ViViPOS  Co.,  Ltd    技術總監   – 

    利用 JavaScript  寫 〞收銀機〞(傳統產業,全新感 受)   •  PHP  /  JavaScript  LiveScript  /  Java  (目前移情於 Scala  )     •  racklin@gmail.com   •  hKps://twiKer.com/racklin   •  hKp://www.plurk.com/racklin   •  hKps://www.facebook.com/racklin1002  
  3. 一開始題目是   • Programmer  如何與            DBA

     真心相愛  
  4. 但是   • Programmer  如何與            DBA

     真心相愛   • 苦命的 PG  通常兼 DBA  
  5. 今天也不談 DBA   •  這裡有堂課好便宜     – OCP-­‐DBA    

    今天也不是 SQL 教學  
  6. 何謂   • 改善 Programmer 生活   – 更快、更好、更簡潔   – 多一點時間陪家人、朋友  

    – 別限制在框架中  
  7. 關於 Rails   By  ihower  

  8. Rails  對其它語言影響   By  ihower  

  9. Rails  超讚,影響深遠   在 ruby  場子,政治正確  

  10. 但是關於 ORM  ….   •  hKp://file.ithome.com.tw/20120720/401_1615-­‐1710%20JRuby_Wang-­‐ Shen%20Lu.pdf  

  11. 你了解 ORM  做了什麼嗎?   •  Rails   •  Java  

    – Hibernate   – Grails   •  PHP   – Lavara   – Phalcon   – CakePHP  
  12. CakePHP  2.3.6  #1  

  13. CakePHP  2.3.6  #2  

  14. 你的 DB  只做 CRUD ?   •  CREATE  TABLE  

    INSERT  INTO   SELECT   DELETE  FROM      
  15. 老舊的 Database  Server  ?   •  mSQL  ?  (1993)  

    •  MySQL  <  3.x  ?    
  16. ORM  迷思   •  ORM  開發者企圖簡化了使用者直接操作 SQL  ,  但不會產出 “較美觀”SQL

     .   – hasAndBelongsToMany  (CAKEPHP  SourceCode)   •  SELECT  a  from  A  where  a  IN(  …………..  )  產出超長 SQL  。   •  上線一陣子後就死的不明不白。   •  ORM  開發者企圖讓您在各個資料庫平台中 移植你的程式,所以不會產出 “最佳 化”SQL.      
  17. 關於 MySQL   •  曾經我也是用 MySQL  ,直到我膝蓋中了一 箭。    

    – SELECT  *  FROM  users  WHERE  username=‘rack’   AND  paswd  =  0;     – hKp://sqlfiddle.com/#!2/pf31/5/0  
  18. ‘STRING’  is  equal  to  0  

  19. Type  handling   •  了解 Framework  如何處理變數   – DB  Column

     Type  or  Input  Data  Type  ?     – bindParam  with  custom  query   •  SELECT  a.user  as  aa  ,  a.passwd  as  bb  WHERE  aa  =  ?  AND     bb  =  ?   •  Resqul  Services   •  XML   •  JSON  
  20. PostgreSQL  Type  Handling   看到這 Hint  ,  感動到眼淚都流下來了!  

  21. 今天的 DEMO  是基於   PostgreSQL  9.2    

  22. Database  被搞壞了  

  23. Schema   •  Linux  CHROOT  ,  FreeBSD  jail  .  

      •  Per  User  Schema   –  永遠不要信任:   •  使用者   •  新手開發者   •  Per  App  Schema   –  正式環境中的 Staging   •  Schema  And  search_path  .  
  24. Schema  in  VIVIPOS   •  SELECT  COUNT(schema_name)  as  K  FROM

      informaton_schema.schemata;   –   372  schema   – 為難搞的客戶和新手鎖在建立獨立的 Schema    
  25. Database  變慢   Big  Data  !?  

  26. Big  定義是什麼?  

  27. Big  Data   •  你的  Big  Data   –  百萬筆級

      •  別人的 Small  Data     –  VIVIPOS  單一客戶      -­‐  (ec2  large  +  500G  EBS)   •  1000        transactons/day   •  1500        clients     •  4  *365                    years   =  2,190,000,000        (21億筆)   •  我的 Big  Data,  金融業的 Small  Data   –  一天幾百萬上下  
  28. PostgreSQL  特殊功能   •  Partton  Table   •  Partal  Index

      •  DBLink   – Connect  Remote  DB  Server  as  View   •  Data  Wrappers   – MySQL  /  Oracle  /  Redis  /  Mongo hKp://wiki.postgresql.org/wiki/Foreign_data_wrappers  
  29. Database  View   •  Fine-­‐grained  Views   – Users  Table  

    •  Username,password,email,cellphones…..   – Views   •  user_auths    (username,  password)   •  User_profiles  (不含 password)     •  Mapping  Model  to  VIEW   •  降低 Mass  Assignment  帶來的風險.    
  30. Updatable  View   •  View  是唯讀的   – Model  Mapping  to

     View 也是唯讀的.     •  MySQL  5.5  後支援   •  PostgreSQL   – USE  RULE   CREATE  RULE  user_login_update  AS    ON  UPDATE  TO  user_login      DO  INSTEAD            UPDATE  users  SET  password  =  new.password              WHERE  users.account  =  new.account;    
  31. Database  View   •  Simplify  Model  associatons   –  User

      •  belongsTo  Compony   –  Compony   •  hasMany  User   •  belongsTo  Region   –  Region   •  hasMany  Machine   •  Get  User  Machines  Lists  ?   –  N+1  Query  ?     –  Prepare  in  Controller  ?  
  32. Database  View   •  建立 user_machines  VIEW      

      •  Mapping  UserMachine  to  ‘user_machines’   VIEW   CREATE  View  user_machines  AS  (   SELECT  u.id  as  user_id,  m.*  FROM  users  u   INNER  JOIN  user_componies  ….   INNER  JOIN  compony_regions  ….   LEFT  JOIN  region_machines  ……   )    
  33. Complex  SQL  and  Joins   未完! 待續……..  共四頁!  

  34. Complex  SQL  and  Joins   •  需要多個 Sub-­‐Query    

    •  需要 Temporary  Table  儲存運算過程中的資 料,並重覆引用   •  SQL  複雜到爸媽都認不得      
  35. Common  Table  Expression   •  在單⼀一  SELECT、INSERT、UPDATE、DELETE   或  CREATE

     VIEW  陳述式之執⾏行範圍內定義 的暫存結果集。CTE  與衍⽣生資料表類似的地 ⽅方在於,它不會儲存為物件,⽽而且其⽣生命 週期僅限於查詢期間.   – 建⽴立遞迴查詢   – 您不需要在中繼資料中儲存定義   – 在相同陳述式中多次參考結果資料表  
  36. CTE  Example   •  Top  Terminal  Product  Sales   – Products_in_top_terminals

     
  37. CTE  Example   •  Top  Terminal  Product  Sales   – Products_in_top_terminals

     
  38. 天不從人願   •  貨幣匯率表   Create  Table  exgRate  (  

         exgRateId  serial  primary  key,  -­‐-­‐  主鍵        factTime  tmestamp,                       -­‐-­‐  匯入時間        currency  char(3),                            -­‐-­‐  貨幣別        purpose  varchar(2),                       -­‐-­‐  匯率種類        currType  varchar(10),                   -­‐-­‐  貨幣種類        rate  numeric(18,6)                         -­‐-­‐  匯率   );  
  39. 工程師角度期望的報表  

  40. 客戶期望報表1  

  41. 客戶期望報表2   一個月後…………    

  42. 客戶期望報表N   三個月後…………    

  43. Data  Mining  ?   •  RD  找 PM  抱怨  

    •  PM  找客戶抱怨   •  客戶向天抱怨   – 為什麼 Data  Mining  變成 Data  Money  $$$  
  44. PIVOT  /  Crosstab   •  一般稱為「樞紐分析表」,PostgreSQL  為 「交叉表」   • 

    SELECT  *  FROM  crosstab('...')  AS  ct(row_name   text,  category_1  text,  category_2  text);  
  45. crosstab  demo   •  客戶報表其實很簡單!   – 客戶期望表 N  -­‐  Crosstab_2

       
  46. Stored  Procedure   OK!    那我們跳過不談 Stored  Procedure  .  

  47. Trigger   •  觸發程序是⼀一種在資料庫伺服器發⽣生事件 時,會⾃自動執⾏行的特殊預存程序   – Logging     • 

    Logging  in  controller  !?   – Backup  data   – Calling  Stored  Procedure  from  Triggers  
  48. PLV8   PostgreSQL  procedual  language  powered  by  V8   JavaScript

     Engine  
  49. 1003  tables  in  cookpad?  

  50. PostgreSQL  on  the  Mac   •  Postgres.app  

  51. postgres.heroku.com  

  52. Thanks