发表于 | 分类于 |

批量插入

  • 方式一 

    1
    INSERT INTO table_name SELECT...FROM source_table

  • 方式二 

    1
    INSERT INTO VALUES (),(),…()

比一条SQL插入一条数据的方式能减少和数据库的交互，减少数据库WAL（Write-Ahead Logging）日志的生成，提升插入效率

  • 方式三
    1
    copy aa from '/home/digoal/aa.csv' with (delimiter U&'\0009');

U&’\0009’ TAB作为分隔符

RETURNING关键字

UPDATE|INSERT|DELETE 支持RETURNING返回更改过的数据

UPSERT语法

1
2
3
insert into table_name values(...) on conflict (conflict_target) 
DO NOTHING    --什么也不做
DO UPDATE SET -- 更新

conflict_target字段必须为主键或者唯一键

数据抽样

  • BERNOULLI抽样方式
    更好的随机性,但比SYSTEM抽样方式性能低.
  • SYSTEM抽样
    随机抽取表上数据块的数据.

    聚合函数

  • string_agg(expression, delimiter)
    函数返回的类型和输入参数类型一致
  • array_agg(expression)
    返回数组类型

    窗口函数

Function Return Type Description Function
row_number() bigint number of the current row within its partition, counting from 1 row_number()
rank() bigint rank of the current row with gaps; same as row_number of its first peer rank()
dense_rank() bigint rank of the current row without gaps; this function counts peer groups dense_rank()
percent_rank() double precision relative rank of the current row: (rank - 1) / (total rows - 1) percent_rank()
cume_dist() double precision relative rank of the current row: (number of rows preceding or peer with current row) / (total rows) cume_dist()
ntile(num_buckets integer) integer integer ranging from 1 to the argument value, dividing the partition as equally as possible ntile(num_buckets integer)
lag(value any [, offsetinteger [, default any ]]) same type as value returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Bothoffset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null lag(value any [, offsetinteger [, default any ]])
lead(value any [, offsetinteger [, default any ]]) same type as value returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Bothoffset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null lead(value any [, offsetinteger [, default any ]])
first_value(value any) same type as value returns value evaluated at the row that is the first row of the window frame first_value(value any)
last_value(value any) same type as value returns value evaluated at the row that is the last row of the window frame last_value(value any)
nth_value(value any, nthinteger) same type as value returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row nth_value(value any, nthinteger)

发表于 | 分类于 |

PostgreSQL 递归查询

示例数据

id name fatherid
1 中国 0
2 辽宁 1
3 山东 1
4 沈阳 2
5 大连 2
6 济南 3
7 和平区 4
8 沈河区 4

查询规则

当给定一个id时能得到它完整的地名，例如当id=7时，地名是：中国辽宁沈阳和平区，当id=5时，地名是：中国辽宁大连，这是一个典型的层次数据递归应用场景。

PostgreSQL实现

创建示例数据

1
2
3
4
5
6
7
8
9
CREATE TABLE test_area(id int4,name varchar(32),fatherid int4);
INSERT INTO test_area VALUES (1, '中国'   ,0);
INSERT INTO test_area VALUES (2, '辽宁'   ,1);
INSERT INTO test_area VALUES (3, '山东'   ,1);
INSERT INTO test_area VALUES (4, '沈阳'   ,2);
INSERT INTO test_area VALUES (5, '大连'   ,2);
INSERT INTO test_area VALUES (6, '济南'   ,3);
INSERT INTO test_area VALUES (7, '和平区' ,4);
INSERT INTO test_area VALUES (8, '沈河区' ,4);

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
with RECURSIVE t as
  (select *
   from test_area
   where id=8
   union all select test_area.*
   from test_area,
        t
   where t.fatherid=test_area.id )
select string_agg(name, '')
from
  (select name
   from t
   order by id) foo

结果

中国辽宁沈阳沈河区

RECURSIVE：引用自己的输出
string_agg: PG聚合函数，类比Oracle的listagg

Oracle实现

创建示例数据

1
2
3
4
5
6
7
8
9
CREATE TABLE test_area(id int,name varchar(32),fatherid int);
INSERT INTO test_area VALUES (1, '中国'   ,0);
INSERT INTO test_area VALUES (2, '辽宁'   ,1);
INSERT INTO test_area VALUES (3, '山东'   ,1);
INSERT INTO test_area VALUES (4, '沈阳'   ,2);
INSERT INTO test_area VALUES (5, '大连'   ,2);
INSERT INTO test_area VALUES (6, '济南'   ,3);
INSERT INTO test_area VALUES (7, '和平区' ,4);
INSERT INTO test_area VALUES (8, '沈河区' ,4);

查询

1
2
3
4
5
6
select listagg(name, '')  within 
group (
       order by id) as name
from test_area
start with id=8 connect by
prior FATHERID=id

结果

中国辽宁沈阳沈河区

发表于 | 分类于 |

#Markdown 语法测试

注意下面每一个标记如果和内容同行，中间都加一个空格

我是大标题，和一级标题长得一样

我是次级标题，和二级标题长得一样

这是一级标题

这是二级标题

一直到六级标题

列表

  • 无序写法1
  • 无序写法2
  • 无序写法3
    • 嵌套加一个Tab
      • 再套一层
        • 再来
  1. 有序列表只有这一种写法
  2. 数字加点

表格

字段1 字段2 字段3
内容 内容 内容
这列左对齐 这列居中 这列右对齐

分割线

注意：如果用—要和前方空行防止被当成标题符号

字体

斜体1
斜体2
粗体
粗体2
斜粗体
斜粗体2
删除线

超链接

百度

插入图片

！图片alt

引用

引用内容

嵌套引用

代码块

单行代码

1
代码块