Использование SQLite и внутренних объединений для поиска уникальных пар и количества совпадений пар

Если у нас есть следующие две таблицы, «учителя» и «классы», как мы можем использовать оператор INNER JOIN для создания итоговой таблицы, в которой

  1. "Teacher1_id" и "Teacher2_id" представляют все уникальные комбинации учителей, которые вели один и тот же класс как минимум 2 раза.
  2. "pair_count" представляет собой количество тех же классов, в которых преподавали "учитель1_id" и "учитель2_id", и
  3. "average_teacher_rating" представляет собой средний рейтинг тех же классов, которые преподаются с помощью "teacher1_id" и "teacher2_id"?

"стол учителя

teachers

class_id    teacher_id    teacher_name    class_count
1111        1234          Smith           2
1111        2345          Jones           4
2222        1234          Smith           2
2222        2345          Jones           4
1111        5678          Taylor          2
3333        2345          Jones           4
4444        5678          Taylor          2
4444        2345          Jones           4

таблица "классы"

classes

id        class_name      rating
1111      Math            60
2222      Biology         70
3333      Psychology      50
4444      Physics         80
5555      Chemistry       30
6666      Economics       60
7777      Computing       70
8888      Statistics      90

И мы хотим, чтобы итоговая таблица выглядела так:

teacher1_id      teacher2_id     pair_count   average_teacher_rating
1234             2345            2            65           
2345             5678            2            70

Я попробовал следующий код в sqlite, но он не дал желаемого результата (как показано в последней таблице выше).

SELECT 
    t.teacher1_id
    , t.teacher2_id
    , COUNT(t.teacher1_id = t.teacher2_id)
    , (c.rating1 + c.rating2) / COUNT(t.teacher1_id = t.teacher2_id)
FROM teachers t
INNER JOIN classes c on c.id = t.class_id
WHERE COUNT(t.teacher1_id = t.teacher2_id) > 1;

Как вы определяете, что такое teacher1 и что такое teacher2, и как вы знаете, как их сочетать? Почему ваш вывод содержит только 2 строки, все строки вашего ввода имеют class_count> = 2 и, за исключением class_id = 3333, у всех есть несколько учителей в классе?

Joakim Danielson 09.09.2018 07:08
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
1
146
2

Ответы 2

Я считаю, что следующее может быть примерно таким, как вы хотите:

WITH cte1 AS
    (
        SELECT 
            t1.teacher_id AS teacher1_id, 
            t1.teacher_name AS t1name,
            t2.teacher_id AS teacher2_id, 
            t2.teacher_name AS t2name, 
            c.class_name,
            avg(c.rating) AS average_teacher_rating,
            count(c.class_name) AS pair_count
        FROM teachers AS t1
        LEFT JOIN classes AS c ON t1.class_id = c.id
        INNER JOIN teachers AS t2 ON t1.class_id = t2.class_id AND t1.teacher_id < t2.teacher_id
        GROUP BY teacher1_id,teacher2_id
        HAVING pair_count > 1
    )
SELECT teacher1_id, teacher2_id, pair_count, average_teacher_rating FROM cte1
;

Это приводит к: -

  • Вышеизложенное можно упростить, поскольку подзапрос не требуется. Однако дополнительные значения могут оказаться полезными.

т.е. это может быть: -

SELECT 
    t1.teacher_id AS teacher1_id, 
    t2.teacher_id AS teacher2_id, 
    avg(c.rating) AS average_teacher_rating,
    count(c.class_name) AS pair_count
FROM teachers AS t1
JOIN teachers AS t2 ON t1.class_id = t2.class_id AND t1.teacher_id < t2.teacher_id
JOIN classes AS c ON t1.class_id = c.id
GROUP BY teacher1_id,teacher2_id
HAVING pair_count > 1
;

Хорошая добавка в качестве моего ответа.

PaichengWu 09.09.2018 12:28

Пытаться:

select 
    t1.teacher_id as t1
    , t2.teacher_id as t2
    , count()
    , avg(c.rating)
from teachers t1 inner join teachers as t2
on t1.class_id = t2.class_id and t1.teacher_id < t2.teacher_id
left join classes c on c.id = t1.class_id
group by t1, t2
having count()>1;

см. sqlfiddle.

Другие вопросы по теме