-1

I need to get in columns the average maximum value between two rows with n steps. If the step is 6 (n = 6), I need to find out the average maximum value between 1 and 6 (not inclusive) rows, then between 6 and 12 (not inclusive), and so on. Both the step and the count of rows can be different.

|id|eventDate |  x  | y |
-------------------------
|1 |10.10.2021| 0.33|0.4|
-------------------------
|2 |10.11.2021| 0.34|0.5|
-------------------------
|3 |10.12.2021| 0.35|0.6|
-------------------------
|4 |10.13.2021| 0.36|0.7|
-------------------------
|5 |10.14.2021| 0.37|0.8|
-------------------------

Step = 3 (n = 3) The result should be

|id|eventDate |        x              |          y           |
--------------------------------------------------------------
|3 |10.12.2021| avr between id 1 and 3|avr between id 1 and 3|
--------------------------------------------------------------
|5 |10.14.2021| avr between id 3 and 5|avr between id 3 and 5|
--------------------------------------------------------------
Nathan Champion
  • 1,291
  • 1
  • 14
  • 23
Mr Doro
  • 11
  • 3
  • 2
    The "result" you're asking for doesn't include an average number. By non-inclusive do you mean n=6 would average 2 -> 5, not 1 and 6? Your question isn't very clear. – Nathan Champion Oct 20 '21 at 19:16
  • if n = 6 the average value in the range of lines 1,2,3,4,5,6 will be calculated – Mr Doro Oct 20 '21 at 19:24
  • So they're inclusive, just like the BETWEEN command https://stackoverflow.com/questions/749615/does-ms-sql-servers-between-include-the-range-boundaries/749663 "The BETWEEN operator is inclusive." "BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression." " – Nathan Champion Oct 20 '21 at 19:38

2 Answers2

0

If you want SQL to attempt to return groups you can use something like NTILE if your DBMS supports it. https://learn.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql?view=sql-server-ver15

I've added a 6th row to make the groups equal. In your question you specified you wanted to group 1-3 and 3-5 (inclusively), which would double-up the averaging row 3. I'm not sure if that's what you intended.

This works in MSSQL:

CREATE TABLE #Temp(
    ID INT,
    eventDate DATE,
    x NUMERIC(3,2),
    y NUMERIC(3,2)
)

DECLARE @Step INT = 3

INSERT INTO #Temp
VALUES 
(1,'10.10.2021',0.33,0.4),
(2,'10.11.2021',0.34,0.5),
(3,'10.12.2021',0.35,0.6),
(4,'10.13.2021',0.36,0.7),
(5,'10.14.2021',0.37,0.8),
(6,'10.15.2021',0.38,0.9)

DECLARE @GroupCount INT = (SELECT COUNT(*) FROM #Temp) / @Step

SELECT MAX(ID) as ID, MAX(eventDate) as eventDate, AVG(x) as AvgX, AVG(y) as AvgY
FROM(
    SELECT *, NTILE(@GroupCount) OVER(ORDER BY ID) Grp
    FROM #Temp
) as t
GROUP BY t.Grp
DROP TABLE #Temp
ID eventDate AvgX AvgY
3 2021-10-12 0.340000 0.500000
6 2021-10-15 0.370000 0.800000

Otherwise you'll probably have to resort to a CURSOR or WHILE loop.

Nathan Champion
  • 1,291
  • 1
  • 14
  • 23
  • will this work if instead of id there is a generated UUID? – Mr Doro Oct 20 '21 at 21:12
  • Yes, even using the date should be fine. – Nathan Champion Oct 20 '21 at 21:14
  • A query of this kind partially solves the problem, but unnecessary rows remain =============================================== SELECT eventDate, avg(x) OVER (ORDER BY eventDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avrX, avg(y) OVER (ORDER BY eventDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avrY FROM table
 ORDER BY eventDate – Mr Doro Oct 20 '21 at 21:18
  • i work with clickhouse – Mr Doro Oct 20 '21 at 21:19
  • Oh, then I assume you'll need to use whatever loop-like functions they have available. Clickhouse isn't actually SQL so I'm not sure. "ClickHouse supports an extended SQL-like language that includes arrays and nested data structures". I've updated the tags on your question so hopefully somebody with experience using it can help. – Nathan Champion Oct 21 '21 at 08:54
0

It's not clear whether you want your bands to overlap in the first/last rows. I suspect you don't. It's also not clear what you mean by "average maximum".

So if you use row_number() to associate a unique group number to each row I think you'll be able to aggregate.

with data as (
    select *,
        (row_number() over (order by eventDate) - 1)
          / groupSize /* aka n? */ as grp /* integer division */
    from tbl
)
select
    min(id) as min_id, min(eventDate) as min_eventDate,
    avg(x) as avg_x, avg(y) as avg_y
from data
group by grp;
shawnt00
  • 16,443
  • 3
  • 17
  • 22