SQL 电子商务数据库 - 获取仅由一个用户购买的产品数量

Question

在我的 Rails 应用程序中，我在 Postgres 9.6 数据库中有一个典型的电子商务模式。这是它的简化版本:

users table
  :name

products table
  :name

shopping_carts table
 :user_id

line_items table
  :price
  :qty
  :product_id
  :shopping_cart_id

我有一个有效的查询来返回每个用户购买的不同产品的数量:

SELECT COUNT(distinct p.*), u.name FROM products p
INNER JOIN line_items l ON p.id = l.product_id
INNER JOIN shopping_carts sc ON l.shopping_cart_id = sc.id
INNER JOIN users u ON sc.user_id = u.id
GROUP BY u.name

但我还想为每个用户计算只有该特定用户购买过的产品。在 Ruby 中一个可能的方法(一旦一切都用 ActiveRecord 设置好)可能类似于:

def unique_prod(user)
  user.products.select { |p| p.users.length == 1 }.count
end

但是如何在 SQL 中实现呢？我想我需要使用两个计数来做到这一点 - 一个是给定产品的 shopping_carts 中不同 user_id 的数量(我们称这个计数为 user_count)，然后是一个计数user_count = 1 的产品数量。我在以工作方式合并多个 COUNT 和 GROUP BY 语句时遇到问题。有什么建议吗？

Answer 1

在一个查询中完成所有操作:

SELECT scl.user_id, u.name, ct_dist_prod, ct_dist_prod_exclusive
FROM  (
   SELECT sc.user_id
        , count(DISTINCT l.product_id) AS ct_dist_prod
        , count(DISTINCT l.product_id)
                FILTER (WHERE NOT EXISTS (
                           SELECT 1
                           FROM   shopping_carts sc1
                           JOIN   line_items     l1 ON l1.shopping_cart_id = sc1.id
                           WHERE  l1.product_id = l.product_id 
                           AND    sc1.user_id  <> sc.user_id)) AS ct_dist_prod_exclusive
   FROM   shopping_carts sc
   JOIN   line_items     l ON l.shopping_cart_id = sc.id
   GROUP  BY 1
   ) scl
JOIN   users u ON u.id = scl.user_id;

我将 user_id 添加到结果中，因为我不能假设 name 被定义为唯一的(这会使您的原始查询稍微不正确)。

聚合 FILTER 子句需要 Postgres 9.4 或更高版本:

• How can I simplify this game statistics query?

如何？

假设引用完整性由 FK 约束强制执行，对于此查询，您根本不需要连接到表 products 。

一开始，users 表都没有。基本查询归结为:

SELECT sc.user_id, count(DISTINCT l.product_id)
FROM   shopping_carts sc
JOIN   line_items     l ON l.shopping_cart_id = sc.id
GROUP  BY 1;

将第二个计数添加到这个更便宜的查询中，其中排除了所有包含产品的行，其中存在具有相同产品和不同用户的另一行(即也被不同的用户购买)。

然后加入用户以添加名称。更便宜。

---

计算仅独占计数更简单。示例:

SELECT sc.user_id, count(DISTINCT l.product_id) AS ct_dist_prod_exclusive
FROM   shopping_carts  sc
JOIN   line_items      l ON l.shopping_cart_id = sc.id
LEFT   JOIN (
             shopping_carts sc1
       JOIN  line_items     l1 ON l1.shopping_cart_id = sc1.id
       ) ON l1.product_id = l.product_id 
    AND sc1.user_id <> sc.user_id
WHERE  l1.product_id IS NULL
GROUP  BY 1;

注意基本的括号。

相关:

• Select rows which are not present in other table

或(回应您的评论):

SELECT user_id, count(*) AS ct_dist_prod_exclusive
FROM  (
   SELECT max(user_id) AS user_id, l1.product_id
   FROM   line_items l1
   INNER  JOIN shopping_carts sc1 ON l.shopping_cart_id = sc1.id
   GROUP  BY l1.product_id
   HAVING COUNT(DISTINCT sc1.user_id) = 1  -- DISTINCT!
   ) p1
GROUP  BY user_id;

HAVING COUNT(DISTINCT sc1.user_id) = 1 因为

products purchased by only one user

允许同一用户多次购买该产品。