Классификация пользователей по классам

Question

## Условие

Дана таблица user_classes со следующей структурой:
- user_id (INT) — идентификатор пользователя
- class (CHAR) — класс пользователя ("a" или "b")

У одного пользователя может быть несколько записей с разными классами.

Напишите SQL-запрос для подсчёта пользователей по классам со следующим правилом:
- Если у пользователя есть записи с обоими классами ("a" и "b"), отнести его ТОЛЬКО к классу "b"
- Если у пользователя записи только одного класса, отнести его к этому классу

## Пример данных
| user_id | class |
|---------|-------|
| 1 | a |
| 1 | b |
| 2 | a |
| 3 | b |
| 4 | a |

## Ожидаемый результат
| class | user_count |
|-------|------------|
| a | 2 |
| b | 2 |

Объяснение: user_id=1 имеет оба класса, поэтому считается как "b". Users 2, 4 — только "a". Users 1, 3 — "b".

claude-haiku-4.5 · Accepted Answer

## Решение

Задача требует условной классификации пользователей с приоритетом: если пользователь имеет записи обоих классов, его нужно отнести только к классу "b".

### Подход к решению

**Логика:**
1. Для каждого пользователя определить, какие классы у него есть
2. Применить правило приоритета ("b" имеет приоритет над "a")
3. Подсчитать пользователей по итоговому классу

### SQL-запрос (основной вариант)

```sql
WITH user_classes_distinct AS (
  SELECT 
    user_id,
    COUNT(DISTINCT class) AS unique_classes,
    MAX(CASE WHEN class = 'a' THEN 1 ELSE 0 END) AS has_a,
    MAX(CASE WHEN class = 'b' THEN 1 ELSE 0 END) AS has_b
  FROM user_classes
  GROUP BY user_id
),
user_final_class AS (
  SELECT 
    user_id,
    CASE 
      WHEN has_a = 1 AND has_b = 1 THEN 'b'  -- Приоритет для "b"
      WHEN has_a = 1 THEN 'a'
      WHEN has_b = 1 THEN 'b'
    END AS final_class
  FROM user_classes_distinct
)
SELECT 
  final_class AS class,
  COUNT(*) AS user_count
FROM user_final_class
GROUP BY final_class
ORDER BY final_class;
```

### Пошаговое объяснение

**1. CTE `user_classes_distinct`**
   - `COUNT(DISTINCT class)` — подсчитываем уникальные классы для каждого пользователя
   - `MAX(CASE WHEN class = 'a' THEN 1 ELSE 0 END) AS has_a` — флаг наличия класса 'a'
   - `MAX(CASE WHEN class = 'b' THEN 1 ELSE 0 END) AS has_b` — флаг наличия класса 'b'
   - Результат: для каждого пользователя знаем, какие классы у него есть

**2. CTE `user_final_class`**
   - CASE WHEN логика с приоритетом:
     - Если есть оба класса (has_a = 1 AND has_b = 1) → отнести к "b"
     - Если только "a" → отнести к "a"
     - Если только "b" → отнести к "b"

**3. Финальный SELECT**
   - GROUP BY final_class — группируем по итоговому классу
   - COUNT(*) — подсчитываем пользователей в каждом классе

### Альтернативный вариант: более компактный

```sql
WITH user_final_class AS (
  SELECT 
    user_id,
    CASE 
      WHEN COUNT(DISTINCT class) = 2 THEN 'b'  -- Оба класса → 'b'
      ELSE MAX(class)  -- Только один класс
    END AS final_class
  FROM user_classes
  GROUP BY user_id
)
SELECT 
  final_class AS class,
  COUNT(*) AS user_count
FROM user_final_class
GROUP BY final_class
ORDER BY final_class;
```

**Как работает:**
- `COUNT(DISTINCT class) = 2` означает наличие обоих классов
- `MAX(class)` вернёт 'b' для одного класса (так как 'b' > 'a' в лексикографическом порядке)

### Вариант с использованием DISTINCT ON (PostgreSQL)

```sql
WITH user_classes_ranked AS (
  SELECT DISTINCT ON (user_id)
    user_id,
    class,
    COUNT(DISTINCT class) OVER (PARTITION BY user_id) AS unique_classes
  FROM user_classes
  ORDER BY user_id, class DESC  -- 'b' идёт первым при DESC
),
user_final_class AS (
  SELECT 
    user_id,
    CASE 
      WHEN unique_classes = 2 THEN 'b'
      ELSE class
    END AS final_class
  FROM user_classes_ranked
)
SELECT 
  final_class,
  COUNT(*) AS user_count
FROM user_final_class
GROUP BY final_class
ORDER BY final_class;
```

Это использует DISTINCT ON для выбора одной записи на пользователя с приоритетом.

### Пример выполнения

Исходные данные:
```
user_id | class
--------|------
1       | a
1       | b
2       | a
3       | b
4       | a
```

**Этап 1: Определение классов для каждого пользователя**
```
user_id | unique_classes | has_a | has_b | Логика
--------|----------------|-------|-------|-------------------
1       | 2              | 1     | 1     | Оба класса → 'b'
2       | 1              | 1     | 0     | Только 'a' → 'a'
3       | 1              | 0     | 1     | Только 'b' → 'b'
4       | 1              | 1     | 0     | Только 'a' → 'a'
```

**Этап 2: Определение финального класса**
```
user_id | final_class
--------|------------
1       | b
2       | a
3       | b
4       | a
```

**Этап 3: Подсчёт пользователей по классам**
```
class | user_count
------|----------
a     | 2  (users 2, 4)
b     | 2  (users 1, 3)
```

### Вариант с процентным распределением

```sql
WITH user_final_class AS (
  SELECT 
    user_id,
    CASE 
      WHEN COUNT(DISTINCT class) = 2 THEN 'b'
      ELSE MAX(class)
    END AS final_class
  FROM user_classes
  GROUP BY user_id
)
SELECT 
  final_class AS class,
  COUNT(*) AS user_count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM user_final_class
GROUP BY final_class
ORDER BY final_class;
```

Это добавляет процентное распределение.

### Для более сложной логики (3+ классов)

```sql
WITH user_class_count AS (
  SELECT 
    user_id,
    COUNT(DISTINCT class) AS unique_classes
  FROM user_classes
  GROUP BY user_id
),
user_final_class AS (
  SELECT 
    uc.user_id,
    CASE 
      WHEN ucc.unique_classes > 1 THEN 'b'  -- Если несколько классов → 'b'
      ELSE uc.class
    END AS final_class
  FROM user_classes uc
  INNER JOIN user_class_count ucc ON uc.user_id = ucc.user_id
  WHERE NOT EXISTS (
    -- Исключаем строки, если есть более предпочтительный класс
    SELECT 1 FROM user_classes uc2
    WHERE uc2.user_id = uc.user_id
    AND uc2.class = 'b'
    AND uc.class != 'b'
  )
)
SELECT 
  final_class,
  COUNT(DISTINCT user_id) AS user_count
FROM user_final_class
GROUP BY final_class;
```

Этот подход более гибок для добавления новых правил.

### Вариант с использованием ROW_NUMBER (для явного выбора)

```sql
WITH user_class_priority AS (
  SELECT 
    user_id,
    class,
    COUNT(DISTINCT class) OVER (PARTITION BY user_id) AS unique_classes,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY CASE WHEN class = 'b' THEN 0 ELSE 1 END) AS priority_rank
  FROM user_classes
),
user_final_class AS (
  SELECT 
    user_id,
    CASE 
      WHEN unique_classes > 1 THEN 'b'
      ELSE class
    END AS final_class
  FROM user_class_priority
  WHERE priority_rank = 1
)
SELECT 
  final_class,
  COUNT(*) AS user_count
FROM user_final_class
GROUP BY final_class
ORDER BY final_class;
```

Этот подход явно показывает приоритизацию.

### Важные моменты

✓ **MAX(class) для одного класса** — работает потому что 'b' > 'a' лексикографически

✓ **COUNT(DISTINCT class)** — правильно определяет наличие обоих классов

✓ **GROUP BY user_id на первом этапе** — критично для получения информации по пользователю

✓ **CASE WHEN с приоритетом** — явно показывает правило классификации

✓ **Масштабируемость** — если будут другие классы ('c', 'd'), нужно обновить CASE WHEN логику

✓ **Производительность** — первый вариант с has_a и has_b флагами обычно быстрее

✓ **Альтернативное правило** — если 'a' имеет приоритет, измените CASE WHEN на:
   ```sql
   CASE 
     WHEN COUNT(DISTINCT class) = 2 THEN 'a'  -- Приоритет 'a'
     ELSE MAX(class)
   END
   ```

### Проверка результата

```sql
-- Верификация: проверить, что все пользователи учтены
SELECT 
  (SELECT COUNT(DISTINCT user_id) FROM user_classes) AS total_users,
  (SELECT COUNT(*) FROM user_final_class) AS counted_users,
  (SELECT COUNT(DISTINCT user_id) FROM user_classes) = (SELECT COUNT(*) FROM user_final_class) AS is_valid;
```

Классификация пользователей по классам

Условие

Пример данных

Ожидаемый результат

Комментарии (1)

Решение

Подход к решению

SQL-запрос (основной вариант)

Пошаговое объяснение

Альтернативный вариант: более компактный

Вариант с использованием DISTINCT ON (PostgreSQL)

Пример выполнения

Вариант с процентным распределением

Для более сложной логики (3+ классов)

Вариант с использованием ROW_NUMBER (для явного выбора)

Важные моменты

Проверка результата