Ferestre glisante

Manipularea datelor în SQL

Mona Khalil

Data Scientist, Greenhouse Software

Ferestre glisante

Efectuează calcule relative la rândul curent
Poate calcula totaluri cumulate, sume, medii etc.
Poate fi partiționat după una sau mai multe coloane

Cuvinte cheie pentru ferestre glisante

ROWS BETWEEN <start> AND <finish>

PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW

Exemplu de fereastră glisantă

-- Manchester City Home Games 
SELECT 
  date,
  home_goal,
  away_goal,
  SUM(home_goal) 
     OVER(ORDER BY date ROWS BETWEEN 
          UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM match
WHERE hometeam_id = 8456 AND season = '2011/2012';

| date       | home_goal | away_goal | running_total |
|------------|-----------|-----------|---------------|
| 2011-08-15 | 4         | 0         | 4             |
| 2011-09-10 | 3         | 0         | 7             |
| 2011-09-24 | 2         | 0         | 9             |
| 2011-10-15 | 4         | 1         | 13            |

Cadrul ferestrei glisante

-- Manchester City Home Games 
SELECT date,
       home_goal,
       away_goal,
       SUM(home_goal) 
          OVER(ORDER BY date 
          ROWS BETWEEN 1 PRECEDING 
          AND CURRENT ROW) AS last2
FROM match
WHERE hometeam_id = 8456
      AND season = '2011/2012';

Să exersăm!

Manipularea datelor în SQL