Solutions Using Postgresql

发表于 2020年9月27日 ,分类：数据库 ,阅读需要13分钟。

（本篇为译文¹,如有侵权，联系删除。）

本篇不会解释PostgreSQL的一些内在原理，同样也不会解释怎样去编写类似select * from的简单语句。我们大多数都聚焦在解决可能日复一日发生的基本但希望用一种更好的方式去解决的问题，这篇文章更多的是告诉你关于你可能不知道的一些方法。

在这里我们讨论的每一种解决方案都有至少好多种解决优势，及可能存在的特殊不利方面（如果存在的话）。每一个小节都包含有自带的数据表，相当于它自有包含的数据单元，及独立存在的全部数据库模式。

下面的索引将展示主题目录，它将会帮助你导航到你选择的主题上：

Multi table joins (subset of data)
Data integrity with functions
Upsert
Aggregates (sum ordered, sum filter)

Multi table joins

------------------ creating tables ------------------
create table if not exists users(
    user_id bigserial primary key,
    first_name text,
    last_name text,
    user_name text
);
create table if not exists managers (
    manager_id bigserial primary key,
    user_id bigint references users(user_id),
    permissions text[]    
);
create table if not exists transactions(
    transaction_id bigserial primary key,
    user_id bigint references users(user_id) not null,
    is_approved bool,
    amount numeric,
    manager_id bigint references managers(manager_id)
);
------------------ inserting data ------------------
insert into users(first_name, last_name, user_name)
values ('John','Johnson','Johnerino'),
    ('Martin','Martini','Martioni');

insert into managers (user_id, permissions)
values (2,null);

insert into transactions(user_id, amount, is_approved, manager_id)
values (1,10,true,null),
  (1,352,true,1);

这是我们将要实施的架构，任务也简单：

查询所有被批准的交易数据的用户姓名和管理人员姓名，不是所有的交易数据都拥有管理人员的批准。

这个最大的问题在于管理人员姓名这个属性，这里存在一种非常简单且优雅的方式来解决这个问题：

select  
    t.transaction_id,
    t.user_id,
    u.user_name,
    t.amount,
    t.is_approved,
    mu.user_name as manager_name
from transactions t
   join users u on u.user_id = t.user_id
   left join managers m
       join users mu on mu.user_id = m.user_id
   on m.manager_id = t.manager_id;

SQL Fiddle

仅有的一段不常见的代码片段如下所示：

left join managers m
    join users mu on mu.user_id = m.user_id
on m.manager_id = t.manager_id

你可以想象这是一段子聚合的一种（就像子查询一样），你可能会想“这和正常的 left join子查询有什么不同？”：

left join (
    select m.manager_id, u.user_name
    from managers m
        join users u on u.user_id = m.user_id
) mu on mu.manager_id = t.manager_id

像这样绝对没有什么问题，你可以亲自去测试一下，你可看到他们具有一致且准确的查询计划和一模一样的结果。唯一的不同就是代码内的第一行更简明，在一些大的查询中更容易去仿效，这可以使你在将来的调试和编写一些复杂的查询时更容易。这种情况有什么不利的影响吗？没有。

Data integrity with functions

/*
 * This is going to be a library system example 
 * */

------------------ creating tables ------------------
create table if not exists user_types(
    user_type_id bigserial primary key,
    type_name text not null,
    sort_order text
);
create table if not exists users(
    user_id bigserial primary key,
    first_name text not null,
    last_name text not null,
    user_name text not null,
    address text
);
create table if not exists user_settings(
    user_id bigint references users(user_id) primary key,
    is_trusted bool, -- if he is, he may borrow books
    user_type_id bigint,
    assigned_groups jsonb -- This is just an example, so it's just here to help this table make more sense
);

------------------ inserting data ------------------

insert into user_types (type_name, sort_order)
values ('beginner_reader', '1');

数据库中最重要的一件事是数据一致性和完整性。同时，最佳实践之一就是在你的代码中处理数据完整性，当意外发生时，不要让你的DBMS抛出自身的错误。在调试中它会很好的帮助你给出一个可读的、可理解的错误消息。当你需要将数据插入多张数据表时，这是非常有用的，就好像下面的简单注册处理一样：当一个用户注册时，我们需要将他的设置初始化为允许的业务逻辑默认参数。下面的这个函数功能就是这样：

create or replace function register_user(
    p_first_name text,
    p_last_name text,
    p_user_name text,
    p_address text
)
  returns bigint
  language plpgsql
as
$$
declare
    v_user_id bigint;
    v_user_type_id bigint;
begin
    
    if (p_first_name is null or p_first_name = '') 
        or (p_last_name is null or p_last_name = '') 
        or (p_user_name is null or p_user_name = '')
        or (p_address is null or p_address = '')
    then
        raise 'Empty fields are not allowed';
    end if;
    
    if exists (select 1 from users u where u.user_name = p_user_name) then
        raise 'Username: %, already exists. Please choose another one.', p_user_name;
    end if;

    select ut.user_type_id into v_user_type_id
    from user_types ut
    where ut.type_name = 'beginner_reader';

/* ↑ one of the librarians might decide to change the naming of each type (so pro_reader might be id=1 tomorrow).
*  With this, we avoid hardcoding values that might result in very inconsistent behaviors.
*/

    insert into users (first_name, last_name, user_name, address)
    values (p_first_name, p_last_name, p_user_name, p_address)
    returning user_id into v_user_id;

    insert into user_settings (user_id, is_trusted, user_type_id, assigned_groups)
    values (v_user_id, true, v_user_type_id, null);
    
    return v_user_id;

end;
$$;

这不需要去处理每一种情况（像组设置或者其他的一些问题），但是，他提供来一个良好的提示怎样去解决你的环境内的相似问题。这种情况有一个最重要的益处，数据的一致性，知晓发生了什么和为什么。这对于去确认这些也是很重要的，因为两种为空的情况，这个包含我的另外两篇解释文章内(第一篇,第二篇)。

可能存在弊端吗？可能会有少量的维护。当正确实施和创建后，维护将不再成为问题。

Upsert

为了举例说明，我们使用上一节内的user_types表，但是我们将精简一下（以便他不牵涉一些业务逻辑），以满足最基本的upsert.

------------------ creating table ------------------
create table if not exists user_types(
    user_type_id bigserial primary key,
    type_name text constraint uq_type_name unique not null,
    sort_order text
);

upsert 不是新的东西，事实上，你可能已经编写过相似方式功能的代码，upsert跟update-insert更新-插入操作结合进一个函数体内相比类似，

create or replace function save_user_type(
    p_type_name text,
    p_sort_order text
)
  returns bigint
  language plpgsql
as
$$
declare
    v_user_type_id bigint;
begin
    
    insert into user_types (type_name, sort_order)
        values (p_type_name, p_sort_order)
    on conflict (type_name) do update set
        type_name = p_type_name,
        sort_order = p_sort_order
    returning user_type_id into v_user_type_id;

    return v_user_type_id;
    
end;
$$;

‘on conflict’关键字是PostgreSQL为引入Upsert所实施的。我建议去阅读链接的文章（https://www.postgresql.org/docs/9.5/sql-insert.html），它只能工作在拥有唯一的限制列上（否则将不能发现冲突）。在PostgreSQL 9.5版本之前的产品，你不得不去手动定义Upsert功能，更多的情况下，甚至于今时，你不得不做一些相似依赖于业务要求，和在插入/更新操作之前你需要去检查一些事情。

create or replace function save_user_type(
    p_type_name text,
    p_sort_order text,
    p_user_type_id bigint default null
)
  returns bigint
  language plpgsql
as
$$
declare
    v_user_type_id bigint;
begin
    
    select ut.user_type_id into v_user_type_id
    from user_types ut
    where ut.user_type_id = p_user_type_id
        or ut.type_name = p_type_name;
    
    if not found then
        insert into user_types (type_name, sort_order)
        values (p_type_name,p_sort_order)
        returning user_type_id into v_user_type_id;
    else
        update user_types set
            sort_order = coalesce(p_sort_order,sort_order)
        where user_type_id = v_user_type_id;
    end if;

    return v_user_type_id;
    
end;
$$;

这是一份类似“标准答案”的操作，if not found 表达式工作在查询语句之前。如果查询返回为空也就是你的查询为“什么都没有”。这样相当容易和简洁，这将成为你编写良好代码的一个很好的工具。这种方式的主要好处（谈及首个on conflict方式）跟要读取较少的操作、并减少磁盘I/O且可提高性能并戏曲性的降低到水平线以下是一样重要的，不像第一个操作，使用not found要求读取表数据。另一个好处在于很少维护，很少代码，仅当某些需要被改变时由函数来检查确认。缺点吗？就是不能被用于任何情况，像有时候期望的方式是插入但他仍旧需要更新操作。这种问题仅适用于第二种使用not found 并作为唯一值不允许你插入到任意副本中。

Aggregates

我确认我们已编写的包含至少一个sum或者count,但是我遇到过像每个聚合都需要拥有他们自属的排序和过滤的事情，这样的事PostgreSQL友好的提供了许多非常有用的工具。

------------------ creating tables ------------------
create table if not exists users(
    user_id bigserial primary key,
    first_name text,
    last_name text,
    user_name text
);
create table if not exists transactions(
    transaction_id bigserial primary key,
    user_id bigint references users(user_id) not null,
    amount numeric
);
------------------ inserting data ------------------
insert into users(first_name, last_name, user_name)
values ('John','Johnson','Johnerino'),
    ('Martin','Martini','Martioni'),
    ('Mark','Markinjo','Markarita'),
    ('Ron','Ronito','Ronaldinho');

insert into transactions(user_id, amount)
values (1,10),  (3,352),  (4,23.3),  (1,33.33),  (2,952),  (3,158),  (1,309),  (4,112),  (1,524),  (2,18.7),  (2,275),
  (1,53.4),  (1,391),  (1,720),  (2,11.43),  (1,9.23),  (3,4.50),  (4,1.50),  (3,900),  (3,132.99);

在这个特定的例子里我将使用json数据类型，因为他是大多数语言中一种通用可支持的数据类型，坦率的讲，这是最简单的方式去解释这个，因为PostgreSQL内在对jsonb支持（被熟知的还有bson）。

select
    jsonb_agg(jsonb_build_object(x.user_id,x.total_amount) order by x.total_amount desc) as total_amount,
    jsonb_agg(jsonb_build_object(x.user_id,x.sum_more_than_100) order by x.sum_more_than_100 desc) as more_than_100,
    jsonb_agg(jsonb_build_object(x.user_id,x.sum_less_than_100) order by x.sum_less_than_100 desc) as less_than_100
from (
    select 
        t.user_id,
        sum(t.amount) as total_amount,
        sum(t.amount) filter (where t.amount > 100) as sum_more_than_100,
        sum(t.amount) filter (where t.amount < 100) as sum_less_than_100
    from transactions t
    group by t.user_id
) x;

SQL Fiddle

开始子查询时，唯一新的事情就是filter关键字，filter关键字允许你去分配特定的聚合过滤，就我对PostgreSQL和SQLite所知的理解，你可以在其他的数据库管理系统里使用case when仿效这种行为.在主select片段里，你看到在聚合函数里有order by操作。你可以使用排序聚合而不需要去关心排序的其他变量，这样可以与所有的聚合函数一起使用。这种方式的弊端？接近于无，这比你在其他方面使用过的多子查询方案要性能好的多。但是这样对缺乏SQL经验的人来说理解起来稍微有点困难。

希望这篇文章可以帮助你或者教你一些新的东西，我非常有兴趣关注哪些编写代码的技术上来，（就好像降低磁盘I/O，使用声明变量或者其他一些），因此多思考，共享你的想法和经验。

https://medium.com/mop-developers/solutions-using-postgresql ↩

Solutions Using Postgresql

Multi table joins

Data integrity with functions

Upsert

Aggregates

Footnotes