问题:SQL计算级联价格规则

我正在构建一个工具,允许人们将项目发布到类别中。在发布项目之前,需要进行价格计算以确定向发布项目的用户收取的价格。

我提出了使用名为price_rule的表来定义规则的概念,它看起来像这样:

+------------ +
| Field       |
--------------+
| id          |
| user        |
| category    |
| price       |
+-------------+

作为一般规则,此数据库将始终有一个备用行。此行用于在没有其他规则与特定上下文匹配的情况下确定价格。该行会是这样的:

+-----------+---------+-------------+-------+
| id        | user    | category    | price |
+-----------+---------+-------------+-------+
| 1         | NULL    | NULL        | 10.00 |
+-----------+---------+-------------+-------+

有了这一行,没有其他行,一个帖子的费用总是 10.00 美元。

现在假设添加了一个额外的行。该表现在如下所示:

+-----------+---------+-------------+-------+
| id        | user    | category    | price |
+-----------+---------+-------------+-------+
| 1         | NULL    | NULL        | 10.00 |
+-----------+---------+-------------+-------+
| 2         | Bob     | NULL        | 8.00  |
+-----------+---------+-------------+-------+

添加此规则后,Bob 将在所有类别中为每个帖子支付 8.00 美元,其余用户将在所有类别中支付 10.00 美元。

如果我们添加更多包含特定用户和特定类别的行:

+-----------+---------+-------------+-------+
| id        | user    | category    | price |
+-----------+---------+-------------+-------+
| 1         | NULL    | NULL        | 10.00 |
+-----------+---------+-------------+-------+
| 2         | Bob     | NULL        | 8.00  |
+-----------+---------+-------------+-------+
| 3         | Bob     | Bicycles    | 9.50  |
+-----------+---------+-------------+-------+
| 4         | Meghan  | Bicycles    | 5.00  |
+-----------+---------+-------------+-------+

当 Bob 在自行车类别中发帖时,他的价格为 9.50 美元。任何其他类别,Bob 将支付 8.00 美元。

梅根现在将为自行车类别的帖子支付 5.00 美元,为每个其他类别支付 10.00 美元。

在任何类别(包括自行车)中发布的每个其他用户都将支付 10.00 美元的默认价格。

在现实世界中,该表可能有数百行,这将允许对发布的价格进行精细控制。

如果您想知道,这个概念背后有商业动机,因为在这个系统中发布帖子的成本并不总是确定的。相反,它基于与发布帖子的用户的业务关系。

当尝试设计一个返回适用于正在发布的帖子的最相关定价规则的查询时,问题就暴露出来了。查询此表时,我可以访问以下信息:用户和类别。我尝试了查询的组合,并阅读了一些 SQL 概念,例如IFNULL和COALESCE,但我无法确定正确的查询。

另一个问题是,在我们的实际应用程序中,price_rule表中有一个附加列来计算价格,但我省略了这个细节以使这个问题中使用的示例更简单。我觉得无论有 2 列还是 3 列用于计算,相同的解决方案都可能适用。

请注意,应用程序代码中强制执行了一些约束,以防止添加重复规则。

我们还使用 Doctrine ORM 和 Doctrine DBAL,因此如果您的查询使用 Query Builder 或 DQL 开箱即用,您的答案将被认为更有价值。可以在 PostgreSQL 或 MySQL 中使用的标准 SQL 解决方案也是可以接受的。

尽管我想尽可能避免这种情况,但有效的解决方案还可能包括从price_rule表中获取每一行并使用应用程序代码确定适用的规则。如果您的解决方案基于此概念,请包含相关伪代码。

解答

Postgresql 小提琴

create or replace function price_rule(
    _user varchar(50), _category varchar(50)
) returns setof price_rule as $$

select id, "user", category, price
from (
    select *, 0 as priority
    from price_rule
    where category = _category and "user" = _user

    union

    select *, 1 as priority
    from price_rule
    where "user" = _user and category is null

    union

    select *, 2 as priority
    from price_rule
    where "user" is null and category is null
) s
order by priority
limit 1
;
$$ language sql;

我把上面的查询变成了一个函数,只是为了方便测试。但是,如果您愿意,只需打开它。

在 MySQL 中,我不记得如何创建一个函数,所以它是原始的:

MySQL 小提琴

select id, `user`, category, price
from (
    select *, 0 as priority
    from price_rule
    where category = 'Bicycles' and `user` = 'Bob'

    union

    select *, 1 as priority
    from price_rule
    where `user` = 'Bob' and category is null

    union

    select *, 2 as priority
    from price_rule
    where `user` is null and category is null
) s
order by priority
limit 1;