如何编写批量表递增大小窗口计算的SQL语句？

Got it, let's tackle this problem where you need to compute a cumulative average with an expanding window—each window grows to include all prior rows up to the current one for each product. This is a common rolling calculation scenario, and we can implement it cleanly with SQL window functions.

Sample Data #

Source Table #

Desired Result #

Solution: Using Window Functions #

Most modern SQL databases support window functions, which make this calculation straightforward. We'll cover both the numeric average (for actual business use) and the formatted expression (matching your sample display).

General SQL (PostgreSQL, Spark SQL, BigQuery, etc.) #

SELECT
  datetime,
  productId,
  -- Numeric cumulative average (for calculations)
  ROUND(AVG(price) OVER (
    PARTITION BY productId 
    ORDER BY datetime 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ), 2) AS average_value,
  -- Formatted expression matching your sample (for display)
  CONCAT(
    '(', 
    STRING_AGG(CAST(price AS VARCHAR), '+' ORDER BY datetime), 
    ')/', 
    COUNT(price) OVER (PARTITION BY productId ORDER BY datetime)
  ) AS average
FROM your_source_table;

MySQL-Specific Version #

MySQL uses GROUP_CONCAT() instead of STRING_AGG(), but the core logic stays the same:

SELECT
  datetime,
  productId,
  -- Numeric cumulative average
  ROUND(AVG(price) OVER (
    PARTITION BY productId 
    ORDER BY datetime 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ), 2) AS average_value,
  -- Formatted expression
  CONCAT(
    '(', 
    GROUP_CONCAT(price ORDER BY datetime SEPARATOR '+'), 
    ')/', 
    ROW_NUMBER() OVER (PARTITION BY productId ORDER BY datetime)
  ) AS average
FROM your_source_table;

Breakdown of the Logic #

• PARTITION BY productId: Groups rows by product, so each product's calculation is independent (critical if you have multiple products in your table).
• ORDER BY datetime: Ensures the window expands in chronological order—no mixing up row order!
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Explicitly defines the window as all rows from the start of the product group up to the current row. Fun fact: This is the default behavior when using ORDER BY in a window, so you can actually omit this clause if you want to keep things concise.
• The formatted expression uses string aggregation to list all prices in the window, paired with a row count to replicate the sum/count format from your sample.

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