返回计数和平均值的有效方法，无需将多个 CTE 连接在一起-解网

问：

在我的 PostgreSQL 14.8 数据库中，我有一个名为的表，如下所示：orders

CREATE TABLE orders (
  user_id int
, order_id int
, order_date date
, quantity int
, revenue float
, product text
);

INSERT INTO orders VALUES
(1, 1, '2021-03-05', 1, 15, 'books'),
(1, 2, '2022-03-07', 1, 3, 'music'),
(1, 3, '2022-06-15', 1, 900, 'travel'),
(1, 4, '2021-11-17', 2, 25, 'books'),
(2, 5, '2022-08-03', 2, 32, 'books'),
(2, 6, '2021-04-12', 2, 4, 'music'),
(2, 7, '2021-06-29', 3, 9, 'books'),
(2, 8, '2022-11-03', 1, 8, 'music'),
(3, 9, '2022-11-07', 1, 575, 'food'),
(3, 10, '2022-11-20', 2, 95, 'food'),
(3, 11, '2022-11-20', 1, 95, 'food'),
(4, 12, '2022-11-20', 2, 95, 'books'),
(4, 13, '2022-11-21', 1, 95, 'food'),
(4, 14, '2022-11-23', 4, 17, 'books'),
(5, 15, '2022-11-20', 1, 95, 'food'),
(5, 16, '2022-11-25', 2, 95, 'books'),
(5, 17, '2022-11-29', 1, 95, 'food');

请参阅 SQL fiddle：http://sqlfiddle.com/#!17/3dc69/1

我想得到以下信息：

在首次购买图书（首先根据）作为（在本例中为客户 ID 1 和 4）的客户中，我希望获得：order_dateproduct

A）该队列购买的书籍的平均值（在本例中为 2.25，即 1、2、2 和 4 的平均值）;quantity

B）这些购买的总和（在本例中为 152）。revenue

我尝试使用多个 CTE 来实现此目的。这是我的尝试：

WITH all_orders AS (
  SELECT
    *
    , ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.order_date) AS row_num
  FROM
    orders o
  ),

  first_product_is_books AS (
  SELECT
      DISTINCT(user_id) AS usr_id
  FROM
    all_orders
  WHERE product = 'books' AND row_num = 1
  ),

  temp_results AS (
  SELECT
    *
  FROM
    all_orders ao
  JOIN
    first_product_is_books AS fp
  ON ao.user_id = fp.usr_id
  )

  SELECT
    avg(quantity)
    , sum(revenue)
  FROM
    temp_results tr
  WHERE
     tr.product = 'books'

参见小提琴：http://sqlfiddle.com/#!17/3dc69/1

这适用于玩具数据集，但不适用于生产数据集，其中的表大约有 400,000 条记录。我认为这个查询很笨拙，没有真正优化。有没有更有效的方法？

SQL PostgreSQL 聚合函数 greatest-n-per-group

0赞 Vérace 9/29/2023

Among customers who first purchased a book as a product (in this case, customer IDs 1 and 4),- 我不明白 - 为什么客户 2（也有两次购买）不包括在内？1 和 2 的区别是什么？

0赞 zeroes_ones 9/29/2023

@Vérace - 客户 2 没有先购买图书。他/她于 2021 年 4 月 12 日首次购买音乐（日期不正常）。

答：

0赞 Larry Burholme 9/29/2023 #1

这个怎么样？

with order_cte as 
(
  SELECT
  *,
  ROW_NUMBER() OVER(partition by user_id order by order_date) as row_num
  from orders
 )
 
 SELECT
 AVG(QUANTITY)AVG_QUANTITY
 , SUM(REVENUE)TOTAL_REV
 FROM ORDERS
 WHERE PRODUCT = 'books'
   and USER_ID IN (
                   SELECT
                    USER_ID
                     FROM ORDER_CTE
                    WHERE PRODUCT = 'books'
                      AND ROW_NUM = 1
                 )

1赞 Vérace 9/29/2023

这个查询似乎工作得很好，但始终比@ErwinBrandstetter慢 - 请参阅此处（尽管不是很多）。我对您对小提琴的任何评论感兴趣 - 特别是 PK 的选择。我意识到在小数据集上进行的测试不一定是最可靠的。

0赞 Chris Albert 9/29/2023 #2

此版本的查询更易于阅读。

WITH first_orders AS (
    SELECT
        DISTINCT
        user_id, 
        FIRST_VALUE(product) OVER (PARTITION BY user_id ORDER BY order_date) as first_product
    FROM orders
    GROUP BY user_id, order_date, product
)

SELECT 
    AVG(o.quantity) AS avg_quantity, 
    SUM(o.revenue) AS total_revenue
FROM orders o
JOIN first_orders fo
ON o.user_id = fo.user_id
WHERE fo.first_product = 'books' AND o.product = 'books';

0赞 Vérace 9/29/2023

您的查询也比@ErwinBrandstetter慢得多 - 请参阅此处。我意识到在小数据集上进行的测试不一定是最可靠的。我对您对小提琴的任何评论感兴趣 - 特别是 PK 的选择。

0赞 Chris Albert 9/30/2023

@Erwin Brandstetter 在性能方面给出了最佳答案。我不认为我有任何其他意见会改进已经发布的内容。

1赞 Erwin Brandstetter 9/29/2023 #3

更简单的：DISTINCT ON

SELECT avg(o.quantity) AS avg_quantity
     , sum(o.revenue)  AS total_revenue
FROM  (
   SELECT DISTINCT ON (user_id)
          user_id, product
   FROM   orders
   ORDER  BY user_id, order_date
   ) init
JOIN   orders o USING (user_id, product)
WHERE  init.product = 'books';

小提琴

哪种查询样式最有效主要取决于基数。总共有多少行，有多少用户，有多少图书订单，有多少图书的初始订单，平均行大小，平均大小。product

如果还有一个表，则每个相关表包含一行，则可以更简单，更快捷。usersuser_id

您需要一个索引来支持查询。理想情况下，在 .同样，最好的指数取决于上述未披露的细节。(user_id, order_date, product)

并且必须是唯一的，否则存在歧义。(user_id, order_date)

看：

1赞 Vérace 9/29/2023

我对这里提出的三个查询进行了性能分析——我对你对小提琴的任何评论都很感兴趣——特别是我对 PK 的选择。

0赞 Erwin Brandstetter 9/29/2023

@Vérace：对不起，现在时间不多了。是的，这需要在大型（现实）数据集上进行测试。就像我上面阐述的那样，很大程度上取决于基数。通常是每组几行的赢家。第 16 页的一些重要改进......DISTINCT ON

0赞 Vérace 9/30/2023 #4

你的问题的逻辑是有缺陷的（下面的所有代码都可以在这里找到）！

> A) the average quantity of books that this cohort purchased (in this
> case, 2.25, which is the average of 1, 2, 2, and 4), and;
> 
> B) the total revenue of each of these purchases (in this case, 152).

您希望两个用户（1 和 4）的两本书的平均购买数量，但四次购买的总收入不是 152，而是 323！

这是由下式计算的（参见小提琴中的表人口）

user_1 = （1 x 15） + （2 x 25） = 15 + 50 = 65
user_2 = （2 x 95） + （4 x 17） = 190 + 68 = 258
65 + 258 = 323

323 是两个用户的总收入的正确总数。

顺便说一句，152 = 15 + 25 + 95 + 17，即在计算中没有考虑数量！

该表是根据您的数据构建和填充的。

然后，我运行了以下 SQL：

WITH cte AS
(
  SELECT
    user_id,   -- some of these fields are not necessary
    product,   -- they just help in getting the full picture.
    order_date,
    SUM(revenue * quantity) OVER (PARTITION BY user_id, product)     AS rev,
    AVG(quantity)  OVER (PARTITION BY user_id, product) AS avg_qty,
    ROW_NUMBER()            OVER (PARTITION BY user_id ORDER BY user_id, order_date) AS rn
  FROM 
    orders
  ORDER BY user_id, order_date
)
SELECT 
  SUM(rev)  AS tot_rev,
  ROUND(AVG(avg_qty), 2) AS avg_q
FROM 
  cte
WHERE 
  product = 'books' AND rn = 1;

结果：

tot_rev     avg_q
323     2.25

瞧 - 正确答案！

0赞 Chris Albert 9/30/2023

OP公布的预期产出为152，因此假设收入列反映了销售总额。当人们向 SO 发帖时，他们通常会省略多余的列，例如在这种情况下的单价。除非你向 OP 提出澄清问题，否则我认为你提供的答案不是 OP 想要的。

0赞 Vérace 9/30/2023

@ChrisAlbert - 嗯......OP从来没有人知道是错的？如果 OP 想要所有书籍的平均数量，那么我相信可以合理地假设他们也想要所有书籍的总收入——这是唯一有意义的事情——至少对我来说是这样！

0赞 Chris Albert 9/30/2023

如果 OP 是正确的怎么办？

0赞 Vérace 9/30/2023

@ChrisAlbert - 那么他们可以自由地忽略我的答案 - 或者如果他们愿意，甚至可以投反对票 - 但是，就我而言，如果你只拿其中一部分书的平均数量和总收入，那么你就是在混合苹果和橙子！

1赞 Chris Albert 9/30/2023

如果你是正确的，那么OP应该重新考虑列的名称。收入的定义：“收入是正常业务运营产生的资金，计算方法为平均销售价格乘以销售单位数量”。

上一个：使用具有非唯一值的条件进行 SQL 重复数据删除

下一个：试图解决最长子字符串的变体，但卡在while循环中[关闭]

返回计数和平均值的有效方法，无需将多个 CTE 连接在一起

Efficient way to return counts and averages without joining together multiple CTEs

评论

评论

评论

评论

评论