Postgres10高性能开发（5）高级SQL

电玩女神 2023-07-10 12:47 25阅读 0赞

# WITH CTE公用表达式查询 #

>  *  https://www.postgresql.org/docs/10/queries-with.html
>  *  https://www.postgresql.org/docs/10/sql-select.html

WITH提供了一种编写辅助语句以便在更大的查询中使用的方法。这些语句（通常称为公用表表达式或CTE）可以被视为定义仅针对一个查询存在的临时表。一个在每个辅助语句WITH子句可以是SELECT，INSERT，UPDATE，或DELETE; 和WITH本身所在的主语句也可以是SELECT，INSERT，UPDATE，或DELETE

*  WITH子句可以将在SQL中多次出现的SQL语句提取出来，简化SQL编写
 *  WITH子句无论使用多少次，都只会计算一次
    
     *  优点：性能比计算多次更好
     *  缺点：如果主句中有其他限制条件可以大幅加快表查找数据，使用WITH子句，在子句将无法使用该限制

WITH invoice_by_type AS ( SELECT cl_invoice_type, COUNT ( 1 ) COUNT FROM inv_t_invoice GROUP BY cl_invoice_type ),
    invoice_max_type AS ( SELECT cl_invoice_type FROM invoice_by_type ORDER BY COUNT DESC LIMIT 2 ) 
    SELECT
    i.cl_id,i.cl_invoice_type,T.count
    FROM
    	inv_t_invoice i
    	LEFT JOIN invoice_by_type T ON i.cl_invoice_type = T.cl_invoice_type 
    WHERE
    	i.cl_invoice_type IN (
    SELECT
    	cl_invoice_type 
    FROM
    	invoice_max_type)

*  有两个With子句invoice\_by\_type和invoice\_max\_type ，其中invoice\_max\_type用到了子句invoice\_by\_type

### RECURSIVE ###

递归语法

非递归语句 UNION [ ALL | DISTINCT ] 递归语句

*  递归自引用必须出现在右侧UNION
 *  每个查询只允许一次递归自引用
 *  不支持递归数据修改语句

例如

WITH RECURSIVE t(n) AS (
        VALUES (1)
      UNION ALL
        SELECT n+1 FROM t WHERE n < 100
    )
    SELECT sum(n) FROM t;

递归计算执行过程

1.  计算非递归语句，如果是UNION则先去重，将数据放入最终结果表和临时表中
2.  执行递归语句访问临时表进行查询，如果是UNION则和最终结果表去重，将数据追加到最终结果表，覆盖临时表
3.  如果临时表为空，则中断递归，否则返回第2步

需要注意防止递归出现无限循环，可以在主语句加入limit防止死循环

### 删除并转移数据 ###

删除products数据并移到products\_log

WITH moved_rows AS（
        DELETE FROM products 
        WHERE 
            “date”> ='2010-10-01'and 
            “date”<'2010-11-01'RETURNING 
        * 
    ）
    INSERT INTO products_log 
    SELECT * FROM moved_rows;

### 同时删除两张表数据，返回的影响行数是bar表行数 ###

WITH t AS(
        DELETE FROM foo 
    )
    DELETE FROM bar;

### 强制主句在WITH子句后执行 ###

如果主句没有使用WITH子句，主句和子句的执行顺序无法确定

WITH t AS (
        UPDATE products SET price = price * 1.05
        RETURNING *
    )
    SELECT * FROM products;

可通过RETURNING \*强制在主句中使用WITH子句，实现先执行用WITH子句，再执行主句

WITH t AS (
        UPDATE products SET price = price * 1.05
        RETURNING *
    )
    SELECT * FROM t;

# CLUSTER聚类表 #

> https://www.postgresql.org/docs/10/sql-cluster.html

将当前表转为索引聚类表，索引聚类表将数据和索引存储在一起，可以加快通过索引访问数据的速度

使用指定索引聚类表

CLUSTER [VERBOSE] table_name [ USING index_name ]

聚类所有表

CLUSTER [VERBOSE]

> 索引聚类表不能在事物中执行

*  如果有新增行，新行不会按索引排序
 *  如果原连续存储空间不足，更新行可能也不会按索引排序

可以通过命令重建索引聚类表，保障所有行按索引顺序

# Window Functions #

> https://www.postgresql.org/docs/10/tutorial-window.html

### 取得员工部门，员工编号，本部门平均工资 ###

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
    
    #Result
      depname  | empno | salary |          avg          
    -----------+-------+--------+-----------------------
     develop   |    11 |   5200 | 5020.0000000000000000
     develop   |     7 |   4200 | 5020.0000000000000000
     develop   |     9 |   4500 | 5020.0000000000000000
     develop   |     8 |   6000 | 5020.0000000000000000
     develop   |    10 |   5200 | 5020.0000000000000000
     personnel |     5 |   3500 | 3700.0000000000000000
     personnel |     2 |   3900 | 3700.0000000000000000
     sales     |     3 |   4800 | 4866.6666666666666667
     sales     |     1 |   5000 | 4866.6666666666666667
     sales     |     4 |   4800 | 4866.6666666666666667
    (10 rows)

### 取得员工部门，员工编号，薪资，部门内薪资排名 ###

SELECT depname, empno, salary,
           rank() OVER (PARTITION BY depname ORDER BY salary DESC)
    FROM empsalary;

# 并行查询 #

并行查询允许一个SQL同时使用多个CPU加快查询速度，主要用于OLAP场景