How do i calculate minimum and maximum for groups in a sequence in SQL Server? -
i having following data in database table in sql server:
id date val_a val_b val_c avg vector minmaxpoints 329 2016-01-15 78.09 68.40 70.29 76.50 below 68.40 328 2016-01-14 79.79 75.40 76.65 76.67 below 75.40 327 2016-01-13 81.15 74.59 79.00 76.44 above 81.15 326 2016-01-12 81.95 77.04 78.95 76.04 above 81.95 325 2016-01-11 82.40 73.65 81.34 75.47 above 82.40 324 2016-01-08 78.75 73.40 77.20 74.47 above 78.75 323 2016-01-07 76.40 72.29 72.95 73.74 below 72.29 322 2016-01-06 81.25 77.70 78.34 73.12 above 81.25 321 2016-01-05 81.75 76.34 80.54 72.08 above 81.75 320 2016-01-04 80.95 75.15 76.29 70.86 above 80.95 the column mimmaxpoints should contain lowest of val_b until vector 'below' , highest of val_a until vector 'above'. so, have following values in minmaxpoints:
minmaxpoints 68.40 68.40 82.40 82.40 82.40 82.40 72.29 81.75 81.75 81.75 is possible without cursor?
any appreciated!.
at first apply classic gaps-and-islands determine groups (gaps/islands/above/below) , calculate min , max each group.
i assume id column defines order of rows.
tested on sql server 2008. here sql fiddle.
sample data
declare @t table ([id] int, [dt] date, [val_a] float, [val_b] float, [val_c] float, [avg] float, [vector] varchar(5)); insert @t ([id], [dt], [val_a], [val_b], [val_c], [avg], [vector]) values (329, '2016-01-15', 78.09, 68.40, 70.29, 76.50, 'below'), (328, '2016-01-14', 79.79, 75.40, 76.65, 76.67, 'below'), (327, '2016-01-13', 81.15, 74.59, 79.00, 76.44, 'above'), (326, '2016-01-12', 81.95, 77.04, 78.95, 76.04, 'above'), (325, '2016-01-11', 82.40, 73.65, 81.34, 75.47, 'above'), (324, '2016-01-08', 78.75, 73.40, 77.20, 74.47, 'above'), (323, '2016-01-07', 76.40, 72.29, 72.95, 73.74, 'below'), (322, '2016-01-06', 81.25, 77.70, 78.34, 73.12, 'above'), (321, '2016-01-05', 81.75, 76.34, 80.54, 72.08, 'above'), (320, '2016-01-04', 80.95, 75.15, 76.29, 70.86, 'above'); query
to understand better how works examine results of each cte.
cte_rownumbers calculates 2 sequences of row numbers.
cte_groups assigns number each group (above/below).
cte_minmax calculates min/max each group.
final select picks min or max return.
with cte_rownumbers ( select [id], [dt], [val_a], [val_b], [val_c], [avg], [vector] ,row_number() on (order id desc) rn1 ,row_number() on (partition vector order id desc) rn2 @t ) ,cte_groups ( select [id], [dt], [val_a], [val_b], [val_c], [avg], [vector] ,rn1-rn2 groups cte_rownumbers ) ,cte_minmax ( select [id], [dt], [val_a], [val_b], [val_c], [avg], [vector] ,max(val_a) over(partition groups) maxa ,min(val_b) over(partition groups) minb cte_groups ) select [id], [dt], [val_a], [val_b], [val_c], [avg], [vector] ,case when [vector] = 'below' minb when [vector] = 'above' maxa end minmaxpoints cte_minmax order id desc; result
+-----+------------+-------+-------+-------+-------+--------+--------------+ | id | dt | val_a | val_b | val_c | avg | vector | minmaxpoints | +-----+------------+-------+-------+-------+-------+--------+--------------+ | 329 | 2016-01-15 | 78.09 | 68.4 | 70.29 | 76.5 | below | 68.4 | | 328 | 2016-01-14 | 79.79 | 75.4 | 76.65 | 76.67 | below | 68.4 | | 327 | 2016-01-13 | 81.15 | 74.59 | 79 | 76.44 | above | 82.4 | | 326 | 2016-01-12 | 81.95 | 77.04 | 78.95 | 76.04 | above | 82.4 | | 325 | 2016-01-11 | 82.4 | 73.65 | 81.34 | 75.47 | above | 82.4 | | 324 | 2016-01-08 | 78.75 | 73.4 | 77.2 | 74.47 | above | 82.4 | | 323 | 2016-01-07 | 76.4 | 72.29 | 72.95 | 73.74 | below | 72.29 | | 322 | 2016-01-06 | 81.25 | 77.7 | 78.34 | 73.12 | above | 81.75 | | 321 | 2016-01-05 | 81.75 | 76.34 | 80.54 | 72.08 | above | 81.75 | | 320 | 2016-01-04 | 80.95 | 75.15 | 76.29 | 70.86 | above | 81.75 | +-----+------------+-------+-------+-------+-------+--------+--------------+ 
Comments
Post a Comment