Need to take a average of every 3 rows in sql, kindly see the image for further clarification.

Thanks in advance!

CodePudding user response：

You can use the ROW_NUMBER and AVG analytic functions:

SELECT product_id,
       price,
       AVG(price) OVER (PARTITION BY CEIL(rn/3)) AS avg_price
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (ORDER BY product_id) AS rn
  FROM   table_name t
) r;

Which, for the sample data:

CREATE TABLE table_name (
  product_id  NUMERIC,
  price       NUMERIC
);

INSERT INTO table_name (product_id, price)
SELECT 1, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 5, 5 UNION ALL
SELECT 6, 6 UNION ALL
SELECT 7, 7 UNION ALL
SELECT 8, 8 UNION ALL
SELECT 9, 9;

Outputs:

product_id	price	avg_price
1	1	2.0000
2	2	2.0000
3	3	2.0000
4	4	5.0000
5	5	5.0000
6	6	5.0000
7	7	8.0000
8	8	8.0000
9	9	8.0000

Or, to get the aggregated rows:

SELECT MIN(product_id) AS min_product_id,
       MAX(product_id) AS max_product_id,
       AVG(price) AS avg_price
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (ORDER BY product_id) AS rn
  FROM   table_name t
) r
GROUP BY CEIL(rn/3);

Which outputs:

min_product_id	max_product_id	avg_price
1	3	2.0000
4	6	5.0000
7	9	8.0000

db<>fiddle here

CodePudding user response：

If I understood you correctly, I think you could use a workaround like this:

1. add column with row number;
2. add column with group of desired step (3 rows in your case). For example you could identify them as a power of 3 for your case.

So you'll add these two rows to your initial table from the image:

| row_number | row_group |
| --------   | --------- |
| 1          | 1         |
| 2          | 1         |
| 3          | 1         |
| 4          | 2         |
| 5          | 2         |
| 6          | 2         |
...

Then use window function to add AVG to each row

select 
a.row_number,
a.row_group,
avg(a.price) over(partition by row_group)

from YOUR_TABLE a