Блог

Главная - Вопросы по программированию - При объединении нескольких таблиц сумма кратна тому, какой она должна быть

При объединении нескольких таблиц сумма кратна тому, какой она должна быть

#sql

#sql

Вопрос:

У меня есть 4 таблицы:

  • employee : employee_id (и несколько несвязанных столбцов)
  • calls : employee_id , date , number_of_calls (на указанную дату сотрудником)
  • employee_skills : employee_id , skills_id
  • skills : skills_id , primary_skill , secondary_skill

В качестве примечания к сайту: в employee_skills комбинациях primary_skill и secondary_skill может встречаться несколько раз с разными skill_id . Может быть две записи, такие как 234 | Python | Java и 235 | Python | Java .

Я хотел бы иметь SELECTS оператор -, который возвращает: employee_id , number_of_total_calls (этого сотрудника), number_of_unique_secondary_skills (этого сотрудника)

Проблема, с которой я сталкиваюсь, связана с number_of_total_calls_of_employee . Когда я только объединяю таблицы employee и calls , я получаю правильное количество (сумму) общих вызовов этого сотрудника. Однако, когда я также присоединяюсь к таблицам employee_skills и skills, я получаю кратное правильное число (например, в 6 раз больше правильного числа для employee_id 123). Я думаю, что это так, поскольку при этом employee_id происходит несколько раз в новой таблице, а не только один раз ( employee_id 123 происходит 6 раз в новой таблице), поскольку employee_id в таблице несколько раз employee_skills (например employee_id 123 , происходит 6 раз в employee_skills ).

Оператор SELECT -, который я использую, выглядит следующим образом:

 SELECT e.employee_id, SUM(c.number_of_calls), COUNT(DISTINCT s.secondary_skill)
FROM employees e
INNER JOIN calls c
ON e.employee_id = c.employee_id
INNER JOIN  employee_skills es
ON e.employee_id = es.employee_id
INNER JOIN skills s
ON es.skills_id = s.skills_id
GROUP BY e.employee_id;

Ответ №1:

Во-первых, вам не нужна таблица employees, потому employee_id что она есть в обеих других таблицах. Затем я бы рекомендовал агрегировать данные по обоим измерениям перед объединением:

 SELECT es.employee_id, c.number_of_calls, es.num_secondary_skills
FROM (SELECT c.employee_id, SUM(c.number_of_calls) as number_of_calls
      FROM calls c
      GROUP BY c.employe_id
     ) c JOIN
     (SELECT es.employee_id, COUNT(DISTINCT s.secondary_skill) as num_secondary_skills
      FROM employee_skills es JOIN
           skills s
           ON es.skills_id = s.skills_id
      GROUP BY es.employee_id
     ) es
     ON es.employee_id = c.employee_id;

Вышесказанное возвращает только тех сотрудников, у которых есть как призвания, так и навыки. Если некоторым сотрудникам не хватает того или иного, то они отфильтровываются.

Если вам нужны все сотрудники, тогда employees таблица удобна — с внешними соединениями:

 SELECT e.employee_id, c.number_of_calls, es.num_secondary_skills
FROM employees e LEFT JOIN
     (SELECT c.employee_id, SUM(c.number_of_calls) as number_of_calls
      FROM calls c
      GROUP BY c.employe_id
     ) c
     ON c.employee_id = e.employee_id LEFT JOIN
     (SELECT es.employee_id, COUNT(DISTINCT s.secondary_skill) as num_secondary_skills
      FROM employee_skills es JOIN
           skills s
           ON es.skills_id = s.skills_id
      GROUP BY es.employee_id
     ) es
     ON es.employee_id = e.employee_id;

Ответ №2:

Просто возьмите сумму вызовов отдельно как внутренний запрос.

 SELECT e.employee_id,
( SELECT SUM(c.number_of_calls) FROM calls c WHERE c.employee_id = e.employee_id ) as sum_of_calls,
COUNT(DISTINCT s.secondary_skill)
FROM employees e
INNER JOIN calls c
ON e.employee_id = c.employee_id
INNER JOIN  employee_skills es
ON e.employee_id = es.employee_id
INNER JOIN skills s
ON es.skills_id = s.skills_id
GROUP BY e.employee_id;
Метки: ,