嵌套选择使用计数来计算Postgres中的每一行 [英] nested select using count to tally each row in Postgres

问题描述

我有一个表格，其中每一行都是一个唯一的订单，具有唯一的order_id，但用户可以有多个行/订单。

I have a table where each row is a unique order with a unique order_id, but users can have multiple rows/orders.

订单表 - > order_id，user_id ，orderedat（datetime），sales

Orders table -> order_id, user_id, orderedat (datetime), sales

我想返回一个查询，为每个order_id计算关联用户创建了多少个订单。 （基本上，这一行代表用户的第一个订单？5阶？20阶等等）

I am trying to return a query that calculates, for each order_id, how many previous orders the associated user has made. (Essentially, does this row represent the user's first order? 5th order? 20th order? etc.)

我在尝试类似这样的嵌套选择，错误由用作表达式的子查询返回的多行。

I'm trying something like this nested select, but am getting an error "more than one row returned by a subquery used as an expression."

SELECT
  order_id,
  user_id,
  COUNT (order_id) AS order_n
FROM
  orders
WHERE orderedat >= (
  SELECT
    orderedat
  FROM
    fulfillments
  GROUP BY
    order_id
)
GROUP BY
  order_id

有关如何在postgres中实现这一点的任何想法？

Any thoughts on how to achieve this in postgres?

///////////////
进一步的复杂性：使用另一个名为状态的列，如何只计算状态中具有特定值的行？我想跳过订单中的订单，除非他们的状态是已付费或已放置。例如：

/////////////// Further complication: with another column called "Status," how to only count rows with specific values in Status? I'd like to just skip orders in the number unless they have a status of "paid" or "placed". For example:

data:
order_id      user_id      orderedat      status
001            max          10/1/14        paid
002            max          10/20/14       placed
003            max          10/21/14       cancelled
004            bill         10/5/14        deleted
005            max          10/31/14       paid
006            bill         10/24/14       placed

results:
order_id      user_id      orderedat      orders_so_far
001            max          10/1/14        1
002            max          10/20/14       2
003            max          10/21/14       null
005            max          10/31/14       3
004            bill         10/5/14        null
006            bill         10/24/14       1

推荐答案

这可以使用窗口函数来完成：

This can be done using a window function:

SELECT order_id,
       user_id,
       orderdat,
       row_number() over (partition by user_id order by orderedat ) as orders_so_far
FROM orders
order by user_id, orderdat

这篇关于嵌套选择使用计数来计算Postgres中的每一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！