Комплексный подсчет MySQL при перекрестном соединении

Спасибо за чтение, я пробовал это в течение четырех часов с некоторым хорошим прогрессом, но я просто не могу понять это.

Я упростил приложение до тех параметров, с которыми мне трудно.

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

Table - Gender
gender_code | gender_name
          1 | Male
          2 | Female
          3 | Other

Table - Service
service_code | service_name
           1 | WiFi
           2 | Network
           3 | Telephone

Table - Customers13
transactionid | customerid | service_code | gender_code
            1 |          4 |            3 |           1
            2 |          7 |            1 |           2
            3 |          9 |            1 |           1

Table - Customers14
transactionid | customerid | service_code | gender_code
            1 |         13 |            2 |           2
            2 |          4 |            2 |           1
            3 |         17 |            2 |           2

Я хочу получить возвращенную таблицу, в которой есть все возможные варианты обслуживания и пола, а также количество каждого из них за каждый год. Я начал с попытки правильно подсчитать и использовал следующий запрос MySQL:

select t.service_name, t.gender_name, count13, count14
from (select service_name, gender_name, count(Customers13.gender_code) as count13
from Customers13  
inner join Gender on Customers13.gender_code = Gender.gender_code  
inner join Service on Customers13.service_code = Service.service_code 
group by service_name, gender_name) t
inner join (select service_name, gender_name, count(Customers14.gender_code) as count14
from Customers14  
inner join Gender on Customers14.gender_code = Gender.gender_code  
inner join Service on Customers14.service_code = Service.service_code 
group by service_name, gender_name) m on m.service_name=t.service_name and 
m.gender_name=t.gender_name

Это приближает меня к возвращению таблицы, которую я хочу, но только там, где есть совпадение в обеих таблицах. Я хочу получить строку, даже если в таблицах нет совпадений, например:

service_name | gender_name | count13 | count14
WiFi         | Male        |       1 |       0
WiFi         | Female      |       1 |       0
WiFi         | Other       |       0 |       0
Network      | Male        |       0 |       1
Network      | Female      |       0 |       2
Network      | Other       |       0 |       0
Telephone    | Male        |       1 |       0
Telephone    | Female      |       0 |       0
Telephone    | Other       |       0 |       0

Моя последняя попытка состояла в том, чтобы использовать перекрестное соединение, чтобы получить правильные первые два столбца, но я понятия не имею, как перейти к заполнению полей счетчика оттуда:

выберите имя_службы, имя_пола из поля перекрестного соединения службы.


mysql count cross-join
person user1967034    schedule 18.09.2014    source источник
comment
Чтобы немного упростить это, рассматривали ли вы использование набора вместо того, что я предполагаю, это char или varchar? Что-то вроде set('WiFi','Сеть','Телефон')   -  person luis_pmb    schedule 18.09.2014
comment
Данные не мои, они в формате .csv, однако схема абсолютно моя, так что я точно могу настроить поля как захочу. Set - это не то, что я использовал раньше, но если это упрощает проблему, то это то, на что я очень согласен.   -  person user1967034    schedule 18.09.2014
comment
У вас есть таблица на каждый год? Но почему? Это кажется такой плохой и ненужной идеей.   -  person Strawberry    schedule 18.09.2014
comment
Также Gender_gender_code Верно?   -  person Strawberry    schedule 18.09.2014
comment
Во многих отраслях необходима ежегодная отчетность, это не означает, что данные хранятся в таблице базы данных за каждый год, просто иногда необходимо манипулировать выходными данными, которые разбиты на годовые файлы.   -  person user1967034    schedule 18.09.2014
comment
Спасибо поправили Gender.gender_code. Ошибка исходит от того, что я упрощаю проблему.   -  person user1967034    schedule 18.09.2014

Ответы (2)


arrow_upward
1
arrow_downward

Привяжите все гендеры ко всем службам с помощью перекрестного соединения, а затем присоедините левое соединение к подзапросу объединения:

SELECT
    s.service_name, g.gender_name, COUNT(c.cnt13), COUNT(c.cnt14)
FROM
    service s
CROSS JOIN
    gender g
LEFT JOIN
    (
        SELECT gender_code, service_code, 1 AS cnt13, NULL AS cnt14
        FROM Customers13
        UNION ALL
        SELECT gender_code, service_code, NULL, 1
        FROM Customers14
    ) AS c
    USING (gender_code, service_code)
GROUP BY s.service_name, g.gender_name
person Gervs    schedule 18.09.2014
comment
ты прекрасный, прекрасный человек! это работает отлично. Я был так близок, что просто не мог заставить их присоединиться должным образом, похоже, что ИСПОЛЬЗОВАНИЕ имело все значение. - person user1967034; 18.09.2014
comment
ИСПОЛЬЗОВАНИЕ - это просто еще один способ написания условий соединения, левое соединение в сочетании с перекрестным соединением сделало свое дело ;-) - person Gervs; 18.09.2014

arrow_upward
0
arrow_downward

Как насчет этого решения:

select
service_name, gender_name, sum(cnt13), sum(cnt14)
from
(
   select service_name, gender_name, count(c.GENDER_CODE) as cnt13, 0 as cnt14
   from SERVICE s,gender g,customers13 c
   where c.GENDER_CODE=g.GENDER_CODE
   and c.SERVICE_CODE=s.SERVICE_CODE
   group by service_name, gender_name
   union all
   select service_name, gender_name, 0 as cnt13, count(c.GENDER_CODE) as cnt14
   from SERVICE s,gender g,customers14 c
   where c.GENDER_CODE=g.GENDER_CODE
   and c.SERVICE_CODE=s.SERVICE_CODE
   group by service_name, gender_name
)
group by service_name, gender_name
person derlarsschneider    schedule 18.09.2014
comment
Я получаю сообщение об ошибке: «Код ошибки: 1248. Каждая производная таблица должна иметь собственный псевдоним». Спасибо за вашу помощь в этом вопросе. - person user1967034; 18.09.2014
comment
Итак, я изменил псевдоним c на d, и он работает до определенного момента. Это не дает мне подсчета, где нет совпадающих значений, и, по сути, помещает одну таблицу поверх другой, что является прискорбным. Спасибо за вашу помощь, хотя, это ценится. - person user1967034; 18.09.2014