SQL的规范

SQL的规范

1. 合理定义表名，数仓中不同层要以不同的层的名字作为开头，一般表名开头为字母

2. 注释，单行注释，多行注释，方便以后阅读

   -- 单行注释
   -- 从SomeTable中查询col_1 
   SELECT col_1
     FROM SomeTable;
   
   /*
   多行注释
   从 SomeTable 中查询 col_1 
   */
   SELECT col_1
     FROM SomeTable;
   

3. 注意缩进,提高可读性

   -- 好的缩进
   SELECT col_1, 
       col_2, 
       col_3,
       COUNT(*) 
     FROM tbl_A
    WHERE col_1 = 'a'
      AND col_2 = ( SELECT MAX(col_2)
                      FROM tbl_B
                     WHERE col_3 = 100 )
    GROUP BY col_1,
             col_2,
             col_3
   
   
   -- 坏的示例
   SELECT col1_1, col_2, col_3, COUNT(*)
   FROM   tbl_A
   WHERE  col1_1 = 'a'
   AND    col1_2 = (
   SELECT MAX(col_2)
   FROM   tbl_B
   WHERE  col_3 = 100
   ) GROUP BY col_1, col_2, col_3
   

4. 注意大小写的使用：

SELECT col_1, col_2, col_3,
    COUNT(*)
  FROM tbl_A
 WHERE col_1 = 'a'
   AND col_2 = ( SELECT MAX(col_2)
                   FROM tbl_B
                  WHERE col_3 = 100 )
 GROUP BY col_1, col_2, col_3

5. 注意空格的使用

-- 好的示例
SELECT col_1
  FROM tbl_A A, tbl_B B
 WHERE ( A.col_1 >= 100 OR A.col_2 IN ( 'a', 'b' ) )
   AND A.col_3 = B.col_3;

-- 坏的示例
SELECT col_1
  FROM tbl_A A,tbl_B B
 WHERE (A.col_1>=100 OR A.col_2 IN ('a','b'))
   AND A.col_3=B.col_3;