Kompleks MySQL mengandalkan gabungan silang

Terima kasih telah membaca, saya sudah mencobanya selama empat jam sekarang dengan beberapa kemajuan yang baik tetapi saya tidak dapat memikirkannya.

Saya telah menyederhanakan aplikasi menjadi hanya parameter yang saya kesulitan.

Saya memiliki database pelanggan yang telah menggunakan layanan, satu untuk setiap tahun dan dua tabel pencarian untuk memungkinkan pengkodean hasilnya.

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

Apa yang ingin saya dapatkan adalah tabel kembali yang memiliki semua kemungkinan permutasi layanan dan gender dan jumlah masing-masing untuk setiap tahun. Saya mulai mencoba menghitung dengan benar dan menggunakan kueri MySQL berikut:

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

Ini membuat saya hampir mengembalikan tabel yang saya inginkan tetapi hanya jika ada kecocokan di kedua tabel. Yang saya inginkan adalah mendapatkan garis meskipun tidak ada kecocokan di tabel, misalnya:

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

Upaya terakhir saya adalah menggunakan gabungan silang untuk mendapatkan dua kolom pertama dengan benar, tetapi saya tidak tahu cara melakukan lompatan untuk mengisi bidang penghitungan dari sana:

pilih nama_layanan, nama_gender dari Layanan gabung silang Gender


mysql count cross-join
person user1967034    schedule 18.09.2014    source sumber
comment
Untuk menyederhanakannya sedikit, sudahkah Anda mempertimbangkan untuk menggunakan satu set, daripada yang saya asumsikan sebagai char atau varchar? Sesuatu seperti set('WiFi','Jaringan','Telepon')   -  person luis_pmb    schedule 18.09.2014
comment
Datanya bukan milik saya, melainkan dalam format .csv, namun skemanya sepenuhnya milik saya sehingga saya dapat mengatur kolom sesuai keinginan saya. Set bukanlah sesuatu yang pernah saya gunakan sebelumnya tetapi jika itu menyederhanakan masalah maka itu adalah sesuatu yang sangat saya setujui.   -  person user1967034    schedule 18.09.2014
comment
Anda punya meja untuk setiap tahun? Tapi kenapa? Sepertinya itu ide yang buruk dan tidak perlu.   -  person Strawberry    schedule 18.09.2014
comment
Juga Gender_gender_code Apakah benar?   -  person Strawberry    schedule 18.09.2014
comment
Di banyak industri, pelaporan tahunan diperlukan, bukan berarti data disimpan dalam tabel database setiap tahunnya, hanya saja terkadang output perlu dimanipulasi dan dipecah menjadi file tahunan.   -  person user1967034    schedule 18.09.2014
comment
Terima kasih telah mengoreksi Gender.gender_code. Kesalahannya datang dari saya menyederhanakan masalah.   -  person user1967034    schedule 18.09.2014

Jawaban (2)


arrow_upward
1
arrow_downward

Ikat semua jenis kelamin ke semua layanan dengan gabungan silang, lalu gabung ke kiri dengan subkueri gabungan:

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
kamu cantik, orang cantik! itu bekerja dengan sempurna. Saya sangat dekat sehingga saya tidak bisa membuat mereka bergabung dengan benar, sepertinya PENGGUNAAN membuat perbedaan besar. - person user1967034; 18.09.2014
comment
USING hanyalah cara lain untuk menulis kondisi join, join kiri dalam kombinasi dengan cross joinj berhasil ;-) - person Gervs; 18.09.2014

arrow_upward
0
arrow_downward

Bagaimana dengan solusi ini:

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
Saya mendapatkan pesan kesalahan: 'Kode Kesalahan: 1248. Setiap tabel turunan harus memiliki aliasnya sendiri.' Terima kasih atas bantuan Anda sampai saat ini. - person user1967034; 18.09.2014
comment
Oke, jadi saya telah mengubah alias c menjadi d dan berhasil sampai titik tertentu. Itu tidak memberi saya hitungan jika tidak ada nilai yang cocok dan pada dasarnya menempatkan satu tabel di atas tabel lainnya yang sangat disayangkan. Terima kasih atas bantuan Anda, ini sangat dihargai. - person user1967034; 18.09.2014