基于前置列的SELECT实现咨询：COUNT函数正确用法求解

It sounds like you’re trying to compute a COUNT that’s tied to each individual row (rather than collapsing rows into groups, which is what GROUP BY does). Window functions are exactly the mechanism you need here—they let you run aggregations over a set of related rows without grouping the entire result set.

Let’s use a concrete example to make this clear, since you mentioned needing specific input and output.

Example Setup #

Suppose we have an orders table with the following sample data:

Scenario 1: Total Completed Orders per Customer (Alongside Every Row) #

If you want to keep every order row in your result, plus show the total number of completed orders for that customer across all dates, here’s how to implement it:

SELECT
  customer_id,
  order_date,
  order_status,
  COUNT(CASE WHEN order_status = 'completed' THEN 1 END) OVER (PARTITION BY customer_id) AS total_completed_orders
FROM orders;

Expected Output: #

Scenario 2: Running (Cumulative) Completed Orders per Customer #

If you want a running count of completed orders up to the date of each order (so later orders don’t affect earlier rows), add an ORDER BY clause to the window:

SELECT
  customer_id,
  order_date,
  order_status,
  COUNT(CASE WHEN order_status = 'completed' THEN 1 END) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_completed_orders
FROM orders;

Expected Output: #

Why GROUP BY Didn’t Work #

GROUP BY collapses all rows in a group into a single row. For example, if you tried:

-- This only returns one row per customer, losing individual order details
SELECT customer_id, COUNT(*) FROM orders WHERE order_status = 'completed' GROUP BY customer_id;

This gives you the total completed orders per customer but discards all other order data. Window functions solve this by keeping every row while computing the aggregation over the specified window.

Alternative: Correlated Subqueries (If Window Functions Aren’t Available) #

If you’re working with an older SQL dialect that doesn’t support window functions, you can use a correlated subquery to achieve the same result as Scenario 1:

SELECT
  o1.customer_id,
  o1.order_date,
  o1.order_status,
  (SELECT COUNT(*) FROM orders o2 
   WHERE o2.customer_id = o1.customer_id 
   AND o2.order_status = 'completed') AS total_completed_orders
FROM orders o1;

Note that window functions are far more efficient for large datasets, but this works as a fallback.

内容的提问来源于stack exchange，提问作者TajniakOsz