PrepBro
← Назад к вопросам

Пары штатов с похожими потоками

2.0 Middle🔥 131 комментариев
#SQL и базы данных

Условие

Дана таблица state_streams со следующей структурой:

  • state (VARCHAR) — название штата
  • total_streams (INT) — общее количество потоков

Напишите SQL-запрос для получения пар штатов, у которых количество потоков отличается не более чем на 1000.

Требования

  • Использовать CROSS JOIN или самообъединение
  • Исключить дубликаты пар (например, (CA, NY) и (NY, CA) — одна и та же пара)
  • Исключить пары штата с самим собой

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

statetotal_streams
CA50000
NY50500
TX45000
FL50800

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

state1state2streams1streams2
CANY5000050500
CAFL5000050800
NYFL5050050800

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

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение

Задача требует нахождения пар штатов с похожей популярностью потоков. Ключевой момент — исключить дубликаты пар и пары с самим собой.

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

Логика:

  1. Использовать CROSS JOIN для создания всех возможных комбинаций штатов
  2. Отфильтровать пары, где разница потоков <= 1000
  3. Исключить пары штата с самим собой
  4. Исключить дубликаты пар (использовать упорядочение по названию)

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

SELECT 
  s1.state AS state1,
  s2.state AS state2,
  s1.total_streams AS streams1,
  s2.total_streams AS streams2,
  ABS(s1.total_streams - s2.total_streams) AS streams_diff
FROM state_streams s1
CROSS JOIN state_streams s2
WHERE s1.state < s2.state  -- Исключает дубликаты и пары с самим собой
  AND ABS(s1.total_streams - s2.total_streams) <= 1000
ORDER BY s1.state, s2.state;

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

1. CROSS JOIN

  • Создаёт все возможные комбинации строк из двух таблиц
  • Если в таблице 4 штата, CROSS JOIN создаст 4 × 4 = 16 комбинаций

2. Условие s1.state < s2.state

  • Исключает пары штата с самим собой (CA < CA = FALSE)
  • Исключает дубликаты пар (CA < NY = TRUE, но NY < CA = FALSE)
  • Гарантирует каждую пару только один раз в алфавитном порядке
  • Это критичный момент для задачи!

3. ABS(s1.total_streams - s2.total_streams) <= 1000

  • ABS — абсолютное значение разницы (работает в обе стороны)
  • Выбираем пары, где разница <= 1000

4. Вывод разницы потоков

  • Добавляю столбец streams_diff для наглядности

Альтернативный вариант: С использованием самообъединения (Self-Join)

SELECT 
  s1.state,
  s2.state,
  s1.total_streams,
  s2.total_streams
FROM state_streams s1
JOIN state_streams s2 
  ON s1.state < s2.state
  AND ABS(s1.total_streams - s2.total_streams) <= 1000
ORDER BY s1.state, s2.state;

Этот вариант более компактен и использует JOIN вместо CROSS JOIN с WHERE.

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

WITH pairs AS (
  SELECT 
    s1.state AS state1,
    s2.state AS state2,
    s1.total_streams AS streams1,
    s2.total_streams AS streams2,
    ABS(s1.total_streams - s2.total_streams) AS diff,
    ROW_NUMBER() OVER (
      PARTITION BY LEAST(s1.state, s2.state), GREATEST(s1.state, s2.state)
      ORDER BY s1.state
    ) AS rn
  FROM state_streams s1
  CROSS JOIN state_streams s2
  WHERE s1.state != s2.state
    AND ABS(s1.total_streams - s2.total_streams) <= 1000
)
SELECT 
  state1,
  state2,
  streams1,
  streams2
FROM pairs
WHERE rn = 1
ORDER BY state1, state2;

Это более сложный, но универсальный подход с LEAST/GREATEST.

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

Исходные данные:

state | total_streams
------|---------------
CA    | 50000
NY    | 50500
TX    | 45000
FL    | 50800

После CROSS JOIN (неполный список):

s1.state | s2.state | s1.streams | s2.streams | difference
---------|----------|------------|------------|----------
CA       | CA       | 50000      | 50000      | 0        ❌ (пара сама с собой)
CA       | NY       | 50000      | 50500      | 500      ✓
CA       | TX       | 50000      | 45000      | 5000     ❌ (> 1000)
CA       | FL       | 50000      | 50800      | 800      ✓
NY       | CA       | 50500      | 50000      | 500      ❌ (дубликат)
NY       | NY       | 50500      | 50500      | 0        ❌ (пара сама с собой)
NY       | TX       | 50500      | 45000      | 5500     ❌ (> 1000)
NY       | FL       | 50500      | 50800      | 300      ✓
TX       | CA       | 45000      | 50000      | 5000     ❌ (дубликат)
TX       | NY       | 45000      | 50500      | 5500     ❌ (дубликат)
TX       | TX       | 45000      | 45000      | 0        ❌ (пара сама с собой)
TX       | FL       | 45000      | 50800      | 5800     ❌ (> 1000)
FL       | CA       | 50800      | 50000      | 800      ❌ (дубликат)
FL       | NY       | 50800      | 50500      | 300      ❌ (дубликат)
FL       | TX       | 50800      | 45000      | 5800     ❌ (дубликат)
FL       | FL       | 50800      | 50800      | 0        ❌ (пара сама с собой)

Применение условия s1.state < s2.state:

CA < CA = FALSE ❌
CA < NY = TRUE ✓
CA < TX = TRUE ✓
CA < FL = TRUE ✓
NY < CA = FALSE ❌
NY < NY = FALSE ❌
NY < TX = TRUE ✓
NY < FL = TRUE ✓
TX < CA = FALSE ❌
TX < NY = FALSE ❌
TX < TX = FALSE ❌
TX < FL = TRUE ✓
FL < CA = FALSE ❌
FL < NY = FALSE ❌
FL < TX = FALSE ❌
FL < FL = FALSE ❌

Финальный результат (разница <= 1000):

state1 | state2 | streams1 | streams2 | diff
-------|--------|----------|----------|------
CA     | NY     | 50000    | 50500    | 500
CA     | FL     | 50000    | 50800    | 800
NY     | FL     | 50500    | 50800    | 300

Вариант с ранжированием пар по разнице

SELECT 
  s1.state,
  s2.state,
  s1.total_streams,
  s2.total_streams,
  ABS(s1.total_streams - s2.total_streams) AS diff,
  RANK() OVER (ORDER BY ABS(s1.total_streams - s2.total_streams) ASC) AS similarity_rank
FROM state_streams s1
JOIN state_streams s2 
  ON s1.state < s2.state
  AND ABS(s1.total_streams - s2.total_streams) <= 1000
ORDER BY diff ASC, s1.state, s2.state;

Это показывает, какие пары наиболее похожи (меньшая разница).

Расширенный вариант с статистикой

SELECT 
  s1.state,
  s2.state,
  s1.total_streams,
  s2.total_streams,
  ABS(s1.total_streams - s2.total_streams) AS difference,
  ROUND(ABS(s1.total_streams - s2.total_streams)::NUMERIC / LEAST(s1.total_streams, s2.total_streams) * 100, 2) AS diff_percentage,
  CASE 
    WHEN ABS(s1.total_streams - s2.total_streams) < 100 THEN 'Very Similar'
    WHEN ABS(s1.total_streams - s2.total_streams) < 500 THEN 'Similar'
    WHEN ABS(s1.total_streams - s2.total_streams) < 1000 THEN 'Somewhat Similar'
    ELSE 'Not Similar'
  END AS similarity_level
FROM state_streams s1
JOIN state_streams s2 
  ON s1.state < s2.state
  AND ABS(s1.total_streams - s2.total_streams) <= 1000
ORDER BY difference ASC;

Это добавляет процентную разницу и категорию сходства.

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

✓ s1.state < s2.state — критичное условие для исключения дубликатов

  • Работает для текстовых сравнений (алфавитный порядок)
  • Гарантирует каждую пару только один раз
  • Автоматически исключает пары штата с самим собой

✓ ABS() для абсолютного значения — работает независимо от порядка чисел

  • |50000 - 50500| = |50500 - 50000| = 500

✓ CROSS JOIN vs JOIN — оба работают, но JOIN явно показывает условие

✓ Производительность — для большого количества штатов может быть медленно

  • Сложность: O(n²) где n — количество штатов
  • Для 50 штатов: 50 × 50 = 2500 комбинаций

✓ Индексы — рекомендуется индекс на (total_streams) для оптимизации

✓ Числовые сравнения — если нужно сравнивать по процентной разнице, используй:

WHERE ABS(s1.total_streams - s2.total_streams) <= LEAST(s1.total_streams, s2.total_streams) * 0.05  -- 5% разница