Порівняння груп

Звітування в SQL

Tyler Pernes

Learning & Development Consultant

Типи метрик

Метрики обсягу
Метрики ефективності

Метрики обсягу

Зростають із розміром

Метрики обсягу

Зростають із розміром

Метрики обсягу

Зростають із розміром

Розрахунок відсотка від загального

basketball_points table
+----------+------------+---------+
| team_id  | player_id  | points  |
|----------|------------|---------|
| 1        | 482        | 92      |
| 1        | 165        | 47      |
| 2        | 222        | 64      |
+----------+------------+---------+

SELECT team_id, SUM(points) AS points
FROM basketball_points
GROUP BY team_id;

Розрахунок відсотка від загального

basketball_points table
+----------+------------+---------+
| team_id  | player_id  | points  |
|----------|------------|---------|
| 1        | 482        | 92      |
| 1        | 165        | 47      |
| 2        | 222        | 64      |
+----------+------------+---------+

+----------+---------+
| team_id  | points  |
|----------|---------|
| 1        | 782     |
| 2        | 625     |
| 3        | 487     |
| 4        | 398     |
+----------+---------+

Розрахунок відсотка від загального

Крок 1: обчисліть загальне

SELECT 
    team_id, 
    SUM(points) AS points
    SUM(points) OVER () AS total_points
FROM basketball_points
GROUP BY team_id;

Крок 2: обчисліть відсоток від загального

SELECT 
    team_id, 
    SUM(points) AS points
    SUM(points) / SUM(points) OVER () AS perc_of_total
FROM basketball_points
GROUP BY team_id;

Розрахунок відсотка від загального

Результати:

+----------+---------+---------------+
| team_id  | points  | perc_of_total |
|----------|---------|---------------|
| 1        | 782     | .34           |
| 2        | 625     | .27           |
| 3        | 487     | .21           |
| 4        | 398     | .17           |
+----------+---------+---------------+

Розрахунок відсотка від загального

Відсоток очок, набраних кожним гравцем у кожній команді:

SELECT 
    player_id,
    team_id,
    SUM(points) AS points
    SUM(points) / (SUM(points) OVER (PARTITION BY team_id)) AS perc_of_team
FROM basketball_points
GROUP BY player_id, team_id;

Розрахунок відсотка від загального

Результати:

+-----------+---------+---------+--------------+
| player_id | team_id | points  | perc_of_team |
|-----------|---------|---------|--------------|
| 482       | 1       | 92      | .12          |
| 165       | 1       | 47      | .06          |
| 222       | 2       | 64      | .10          |
+-----------+---------+---------+--------------+

Метрики ефективності

Не зростають із розміром
Зазвичай це відношення

Метрики ефективності

Не зростають із розміром
Зазвичай це відношення

Метрики ефективності

Не зростають із розміром
Зазвичай це відношення

Індекс продуктивності

Порівнюють результат із еталоном
Еталон — зазвичай середнє або медіана

Індекс продуктивності

basketball_summary table
+----------+--------+---------+
| team_id  | games  | points  |
|----------|--------|---------|
| 1        | 24     | 782     |
| 2        | 20     | 625     |
| 3        | 12     | 487     |
+----------+--------+---------+

Очки за гру як показник?

Індекс продуктивності

Крок 1: очки за гру для кожної команди

SELECT 
    team_id, 
    points/games AS team_ppg
FROM basketball_summary;

Крок 2: очки за гру для всієї ліги

SELECT 
    team_id, 
    points/games AS team_ppg,
    SUM(points) OVER () / SUM(games) OVER () AS league_ppg
FROM basketball_summary;

Індекс продуктивності

Крок 3: індекс продуктивності

SELECT 
    team_id, 
    points/games AS team_ppg,
    SUM(points) OVER () / SUM(games) OVER () AS league_ppg,
    (points/games) 
    / 
    (SUM(points) OVER () / SUM(games) OVER ()) AS perf_index
FROM basketball_summary;

Індекс продуктивності

Крок 3: індекс продуктивності

+----------+----------+------------+-------------+
| team_id  | team_ppg | league_ppg | perf_index  |    
|----------|----------|------------|-------------|
| 1        | 32.6     | 33.8       | 0.96        |
| 2        | 31.3     | 33.8       | 0.92        |
| 3        | 40.6     | 33.8       | 1.20        |
+----------+----------+------------+-------------+

¹ Результати чітко показують: команда 3 набирає на 20% більше очок, ніж у середньому по лізі.

Час для запиту!

Звітування в SQL