Suppose there is the following table:

product | price ------------------ A | 10 B | 20 C | 45 D | 47 E | 10 F | 60 G | 15 H | 15 I | 90

You need to get something like (for X = 3):

  products | avg ------------------ A | 25 -- 10+20+45 D | 39 -- 47+10+60 G | 40 -- 15+15+90

How can I do it?

RED: Set ascending by product column.

  • one
    In relational databases, row order is not guaranteed. Therefore, in SQL there is no concept of "consecutive lines" by itself. Consecutive rows can only be for a given sort. You must obviously have a sort on the products column. - Alexander Petrov
  • @AlexanderPetrov, yes, I'm sorry, the condition specifies sorting by ascending by the product column. - J. Doe
  • one
    Try using analytic functions with a "floating window". For example, for oracle how it is done you can see interface.ru/fset.asp?Url=/oracle/anal-itiv.htm here. - faksel
  • @faksel, I looked, but still I didn’t quite understand that this would help me solve the problem. I suspect that this whole thing is with RANGE / ROWS BETWEEN, etc. and that's what I need, but for this article, I just can’t figure out all these PRECEDING and FOLLOWING. Are there more resources with similar information? - J. Doe
  • @ J.Doe, oracleplsql.ru/lead-function.html , for example, you can use offsetting 1, 2 and 3 positions in the subquery and count the result in an external query. Window functions are described here my-oracle.it-blogs.com.ua/post-72.aspx - faksel

1 answer 1

Used SqlServer.

Create and fill in the test table:

 create table #t (product char, price int) insert #t values ('A', 10), ('B', 20), ('C', 45), ('D', 47), ('E', 10), ('F', 60), ('G', 15), ('H', 15), ('I', 90)

Request:

 select distinct first_value(product) over(partition by num order by num) as [products], avg(price) over (partition by num) as [avg] from ( select product, price, (row_number() OVER(ORDER BY product) - 1 ) / 3 as [num] from #t ) as temp

I suspect that the request is highly inefficient. On the implementation plan, three sorting. I hope someone will bring the best option.

Option proposed by i-one in the comments:

 select min(product) as [products], avg(price) as [avg] from ( select product, price, (row_number() OVER(ORDER BY product) - 1 ) / 3 as [num] from #t ) as temp group by num

Judging by the implementation plan, this query is more efficient.

  • Is it possible to somehow avoid the nested select, and do everything only through one sample? - J. Doe
  • one
    @ J.Doe - if I knew a simpler way, I would write it. I am almost sure that you cannot get rid of the nested select. But distinct, you can probably remove. But something no one offers the best options. - Alexander Petrov
  • one
    @AlexanderPetrov, the window for first_value most likely meant by partition by num order by product , as for "distinct, you can probably remove" , then in an external query you can try to do with the usual aggregation, not the window: select min(product), avg(price) from (...) temp group by num . - i-one