CODEWITHSUNDEEP
sql-servert-sqlgroup-bycommon-table-expressionwindow-functions
Kostya
Kostya

Reputation: 141

SQL Server group rows

I have table that have data like this

OrdersDate OrdersQuantity
2021-03-10 40
2021-03-11 80
2021-03-12 63
2021-03-13 20
2021-03-14 80
2021-03-15 80
2021-03-16 70
2021-03-17 20
2021-03-18 80
2021-03-19 80
2021-03-20 80
2021-03-21 80
2021-03-22 80

I need to group this data by 10 rows in each started from first, but groups formed like 1-10 rows, then 2-11, then 3-12. So groups intersected. I tried add ROW_NUMBER and group rows somehow based on it, but because groups intersected I dont know how to specify group conditions.

I expect result like

OrdersDate OrdersQuantity
1st group (from 2021-03-10 to 2021-03-19) AVG()
2nd group (from 2021-03-11 to 2021-03-20) AVG()
3rd group (from 2021-03-12 to 2021-03-21) AVG()
4th group (from 2021-03-13 to 2021-03-22) AVG()

I solved this with cursor but it works well only on small sample data, when I tried it on >2kk rows it took a lot of time.

Is there a way to group rows and calculate AVG of each group?

Upvotes: 0

Views: 104

Answers (3)

Stu
Stu

Reputation: 32579

Using a CTE to first get the qualifying date range start dates using mod then grouping by the start and end dates concatenated:

with r as (
    select ordersdate StartDate, DateAdd(day,9,ordersdate) EndDate,
        DateAdd(day, Count(*) over() % 10, Min(ordersdate) over()) MaxStart
    from t
)
select Daterange, Avg(t.OrdersQuantity) OrdersQuantity
from r
cross apply (values( Concat(r.StartDate, ' to ', EndDate)))v(Daterange)
join t on t.ordersdate between StartDate and EndDate
where r.StartDate  < = r.MaxStart
group by Daterange

Demo Fiddle

Upvotes: 0

forpas
forpas

Reputation: 164069

You can use window functions that operate on each row and the next 9 rows to create the groups of 10:

WITH cte AS (
  SELECT OrdersDate from_Date,
         MAX(OrdersDate) OVER (ORDER BY OrdersDate ROWS BETWEEN CURRENT ROW AND 9 FOLLOWING) to_Date,
         AVG(1.0 * OrdersQuantity) OVER (ORDER BY OrdersDate ROWS BETWEEN CURRENT ROW AND 9 FOLLOWING) average_qty,
         ROW_NUMBER() OVER (ORDER BY OrdersDate) rn,
         COUNT(*) OVER () total_rows
  FROM tablename
)
SELECT from_Date, to_Date, average_qty 
FROM cte 
WHERE rn <= total_rows - 9 OR rn = 1;

See the demo.

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

You can try to use cte recursive to make a calendar table for your logic range date by DATEADD function, then use self-join by date range.

Query 1:

;WITH CTE AS (
  SELECT MIN(OrdersDate) s_date,
         MAX(OrdersDate) e_date
  FROM T
  UNION ALL
  SELECT DATEADD(day , 1 ,  s_date) ,e_date
  FROM CTE 
  WHERE DATEADD(day , 10 ,  s_date)  <= e_date
) 
SELECT CONCAT(t1.s_date,' to ' , t1.final_date) OrdersDate,
      AVG(t2.OrdersQuantity) OrdersQuantity
FROM (
  SELECT s_date,DATEADD(day , 9 ,  s_date) final_date
  FROM CTE
) t1 INNER JOIN T t2 
ON t2.OrdersDate BETWEEN t1.s_date AND t1.final_date
GROUP BY CONCAT(t1.s_date,' to ' , t1.final_date) 
option ( MaxRecursion 0 );

Results:

|               OrdersDate | OrdersQuantity |
|--------------------------|----------------|
| 2021-03-10 to 2021-03-19 |             61 |
| 2021-03-11 to 2021-03-20 |             65 |
| 2021-03-12 to 2021-03-21 |             65 |
| 2021-03-13 to 2021-03-22 |             67 |

Upvotes: 0

Related Questions