TERADATA TD

  SQL學習roadmap https://allaboutdataanalysis.medium.com/%E9%9B%B6%E5%9F%BA%E7%A4%8E-sql-%E8%B3%87%E6%96%99%E5%BA%AB%E5%B0%8F%E7%99%BD-%E5%BE%9E%E5%85%A5%E9%96%80%E5%88%B0%E7%B2%BE%E9%80%9A%E7%9A%84%E5%AD%B8%E7%BF%92%E8%B7%AF%E7%B7%9A%E8%88%87%E6%9B%B8%E5%96%AE-d9918e64389f SQL風格 https://www.sqlstyle.guide/zh-tw/#%E5%91%BD%E5%90%8D%E6%85%A3%E4%BE%8B https://github.com/huangxinping/sql-style-guide/blob/zh-cn/README.md http://index-of.es/Databases/mssql/SQL%20Programming%20Style%20-%20Apr%202005.pdf

 直轉橫 技巧：SUM() + DECODE() 範例： id fee amt date 1 01 20 2021-01-01 1 02 30 2021-01-01 2 01 25 2021-01-01 1 01 15 2021-01-02 2 02 40 2021-01-02 1 03 50 2021-01-03 SELECT id ,SUM(DECODE(fee,'01',amt,0)) AS fee01 ,SUM(DECODE(fee,'02',amt,0)) AS fee02 ,SUM(DECODE(fee,'03',amt,0)) AS fee03 FROM t1 GROUP BY 1 ; id fee01 fee02 fee03 1 35 20 50 2 25 40 0

CHAR(n)固定是用掉n Bytes(LATIN; UNICODE則是2倍) VARCHAR每個字元用掉n Bytes，另外再加2Bytes放長度資訊。 所以，資料是NULL，在 CHAR(10)　=> 10 BYTES VARCHAR(10) => 0+2 BYTES 選用規則：視資料內容變動性來決定，如果資料平均變動(少於 N的長度)大於3，則選用VARCHAR在空間上就有節省的空間。 例如：要用CHAR(10) 或是VARCHAR(10)? 看資料平均長度是小於等於7，選VARCHAR(10)就可以省空間。 若有一定的資料是NULL，也可以考慮用CHAR(n) COMPRESS，把NULL壓起來。 VARCHAR(1)、VARCHAR(2)在空間上絕對是不合適的定義。 https://downloads.teradata.com/forum/database/recommendation-on-varhar-char-columns  CHAR(n) always need exactly n bytes for LATIN or n*2 bytes for UNICODE while VARCHARs need 1 or two bytes for each character stored. But VARCHARs also need two byte additional overhead (a word indicating the length). Assuming your columns are mainly Y/N or similar in LATIN a single character needs 3 bytes as a VARCHAR, but only 1 byte as CHAR. You never ever do a VARCHAR(1) on Teradata :-)  When the actual length of a string greatly varies and the average is at least 3 characters than the maximum you start using VARCHARS. Q2: FastLoad calculates the number of

 在針對文字欄位做處理時，有時候會因為資料中有一些髒資料、怪異的字元，導致SQL出現錯誤無法執行，這時可以用TRANSLATE_CHK()把有問題的資料找出來做特別處理。 以下是如何列出這些有問題的資料之SQL範例： SELECT * FROM table WHERE  TRANSLATE_CHK( col  USING  Lating_To_Unicode ) >0