Existem ferramentas de benchmarking do MySQL? [fechado]

Question

giannis christofakis

Asked: 2012-01-11 13:07:45 +0800 CST2012-01-11 13:07:45 +0800 CST 2012-01-11 13:07:45 +0800 CST

Juntar três tabelas

772

eu tenho essas tres mesas

SELECT occuId as 'Occupation Id',occuDscr as 'Occupation Description' FROM occupation_field;
+---------------+------------------------+
| Occupation Id | Occupation Description |
+---------------+------------------------+
|             1 | Software Engineer      |
|             2 | Economics              |
|             3 | Structural Engineer    |
|             4 | Legal Advisors         |
|             5 | Plumbers               |
|             6 | Social Advisors        |
|             7 | Musicians              |
+---------------+------------------------+

SELECT usrId AS 'User Id',usrName AS 'User Name',usrOccuId AS 'User Occupation Id' FROM users;
+---------+------------+--------------------+
| User Id | User Name  | User Occupation Id |
+---------+------------+--------------------+
|       2 | goodys     |                  6 |
|       5 | realmadrid |                  7 |
|       6 | petousis   |                  6 |
+---------+------------+--------------------+

SELECT pstId AS 'Post Id',pstTitle AS 'Post Title',pstOccuId AS 'Post Occupation Id' FROM job_post ORDER BY pstId;
+---------+-------------------------------------------+--------------------+
| Post Id | Post Title                                | Post Occupation Id |
+---------+-------------------------------------------+--------------------+
|       4 | Software Engineer Recruit                 |                  1 |
|       5 | Web Developer Recruit                     |                  1 |
|       6 | Saxophonist                               |                  7 |
|       7 | Construction Company looking for plumber. |                  5 |
|       8 | Economic Analyst                          |                  2 |
|       9 | Legal Advisor                             |                  4 |
|      10 | Economic Assistant                        |                  2 |
+---------+-------------------------------------------+--------------------+

Eu quero combinar as duas tabelas abaixo para ter uma com as Descrições de Ocupação, Cargo por Cargo e Usuários por Cargo.

SELECT occuDscr job,count(pstOccuId) AS 'Posts' FROM job_post INNER JOIN occupation_field on pstOccuId = occuId GROUP BY job;
+-------------------+-------+
| job               | Posts |
+-------------------+-------+
| Economics         |     2 |
| Legal Advisors    |     1 |
| Musicians         |     1 |
| Plumbers          |     1 |
| Software Engineer |     2 |
+-------------------+-------+

SELECT occuDscr job,count(usrOccuId) AS 'Users' FROM users INNER JOIN occupation_field on usrOccuId = occuId GROUP BY job;
+-----------------+-------+
| job             | Users |
+-----------------+-------+
| Musicians       |     1 |
| Social Advisors |     2 |
+-----------------+-------+

Algo assim

    +-------------------+-------+-------+
    | job               | Posts | Users |
    +-------------------+-------+-------+
    | Economics         |     2 |     0 |
    | Legal Advisors    |     1 |     0 |
    | Musicians         |     1 |     1 |
    | Plumbers          |     1 |     0 |
    | Software Engineer |     2 |     0 |
    | Social Advisors   |     0 |     1 |
    +-------------------+-------+-------+

2 respostas

Voted

RolandoMySQLDBA · Answer 1 · 2012-01-11T14:06:04+08:00

Eu essencialmente executei um LEFT JOIN da occupation_fieldtabela em ambas as suas consultas, unindo por meio do campo occuDscr. Aqui está sua nova consulta:

SELECT
    oc.occuDscr job,
    IFNULL(postInfo.PostCount,0) Posts,
    IFNULL(userInfo.UserCount,0) Users
FROM
    (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
        FROM job_post INNER JOIN occupation_field
        ON pstOccuId = OccuId GROUP BY occuDscr
    ) postInfo
    USING (occuDscr)
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
        FROM users INNER JOIN occupation_field
        ON usrOccuId = occuId GROUP BY occuDscr
    ) userInfo
    USING (occuDscr)
;

Se a cláusula USING não estiver clara para você, aqui está sua cláusula sem a cláusula USING

SELECT
    oc.occuDscr job,
    IFNULL(postInfo.PostCount,0) Posts,
    IFNULL(userInfo.UserCount,0) Users
FROM
    (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
        FROM job_post INNER JOIN occupation_field
        ON pstOccuId = OccuId GROUP BY occuDscr
    ) postInfo
    ON oc.occuDscr = postInfo.occuDscr
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
        FROM users INNER JOIN occupation_field
        ON usrOccuId = occuId GROUP BY occuDscr
    ) userInfo
    ON oc.occuDscr = userInfo.occuDscr
;

Aqui estão seus dados de amostra carregados:

mysql> drop database if exists giannosfor;
Query OK, 3 rows affected (0.13 sec)

mysql> create database giannosfor;
Query OK, 1 row affected (0.00 sec)

mysql> use giannosfor
Database changed
mysql> create table occupation_field
    -> (occuId int not null auto_increment,
    -> occuDscr varchar(40),
    -> primary key (occuId));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into occupation_field (occuDscr) values
    -> ('Software Engineer'),('Economics'),('Structural Engineer'),
    -> ('Legal Advisors'),('Plumbers'),('Social Advisors'),('Musicians');
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> create table users
    -> (usrId int not null,
    -> usrName varchar(20),
    -> usrOccuId int not null);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into users values
    -> (2,'goodys',6),(5,'realmadrid',7),(6,'petousis',6);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table job_post
    -> (pstId int not null,pstTitle varchar(60),
    -> pstOccuId int not null,
    -> primary key (pstId));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into job_post values
    -> ( 4,'Software Engineer Recruit',1),
    -> ( 5,'Web Developer Recruit',1),
    -> ( 6,'Saxophonist',7),
    -> ( 7,'Construction Company looking for plumber.',5),
    -> ( 8,'Economic Analyst',2),
    -> ( 9,'Legal Advisor',4),
    -> (10,'Economic Assistant',2);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from occupation_field;
+--------+---------------------+
| occuId | occuDscr            |
+--------+---------------------+
|      1 | Software Engineer   |
|      2 | Economics           |
|      3 | Structural Engineer |
|      4 | Legal Advisors      |
|      5 | Plumbers            |
|      6 | Social Advisors     |
|      7 | Musicians           |
+--------+---------------------+
7 rows in set (0.00 sec)

mysql> select * from users;
+-------+------------+-----------+
| usrId | usrName    | usrOccuId |
+-------+------------+-----------+
|     2 | goodys     |         6 |
|     5 | realmadrid |         7 |
|     6 | petousis   |         6 |
+-------+------------+-----------+
3 rows in set (0.00 sec)

mysql> select * from job_post;
+-------+-------------------------------------------+-----------+
| pstId | pstTitle                                  | pstOccuId |
+-------+-------------------------------------------+-----------+
|     4 | Software Engineer Recruit                 |         1 |
|     5 | Web Developer Recruit                     |         1 |
|     6 | Saxophonist                               |         7 |
|     7 | Construction Company looking for plumber. |         5 |
|     8 | Economic Analyst                          |         2 |
|     9 | Legal Advisor                             |         4 |
|    10 | Economic Assistant                        |         2 |
+-------+-------------------------------------------+-----------+
7 rows in set (0.00 sec)

mysql>

Aqui está a execução da primeira consulta

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.00 sec)

mysql>

Aqui está a execução da segunda consulta

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     ON oc.occuDscr = postInfo.occuDscr
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     ON oc.occuDscr = userInfo.occuDscr
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.01 sec)

mysql>

De uma chance !!!

ATUALIZAÇÃO 2012-01-11 11:50 EDT

Para remover trabalhos sem postagens, altere o primeiro LEFT JOIN para INNER JOIN:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-------------------+-------+-------+
| job               | Posts | Users |
+-------------------+-------+-------+
| Economics         |     2 |     0 |
| Legal Advisors    |     1 |     0 |
| Musicians         |     1 |     1 |
| Plumbers          |     1 |     0 |
| Software Engineer |     2 |     0 |
+-------------------+-------+-------+
5 rows in set (0.00 sec)

mysql>

Para remover trabalhos sem usuários, altere o segundo LEFT JOIN para INNER JOIN:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-----------------+-------+-------+
| job             | Posts | Users |
+-----------------+-------+-------+
| Musicians       |     1 |     1 |
| Social Advisors |     0 |     2 |
+-----------------+-------+-------+
2 rows in set (0.00 sec)

mysql>

Para remover trabalhos sem usuários ou postagens, altere os dois LEFT JOINs para INNER JOINs:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-----------+-------+-------+
| job       | Posts | Users |
+-----------+-------+-------+
| Musicians |     1 |     1 |
+-----------+-------+-------+
1 row in set (0.00 sec)

mysql>

ATUALIZAÇÃO 2012-01-11 12:03 EDT

To remove Jobs with no Post or Users, add a WHERE clause to check if either cound is greater than zero:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> WHERE
    ->     IFNULL(postInfo.PostCount,0)>0 OR
    ->     IFNULL(userInfo.UserCount,0)>0
    -> ;
+-------------------+-------+-------+
| job               | Posts | Users |
+-------------------+-------+-------+
| Economics         |     2 |     0 |
| Legal Advisors    |     1 |     0 |
| Musicians         |     1 |     1 |
| Plumbers          |     1 |     0 |
| Social Advisors   |     0 |     2 |
| Software Engineer |     2 |     0 |
+-------------------+-------+-------+
6 rows in set (0.00 sec)

mysql>

Simon Righarts · Answer 2 · 2012-01-11T14:15:57+08:00

This should be what you're after (or at least enough to set you on the right track):

SELECT occuDscr job, 
       SUM(CASE WHEN pstOccuId IS NOT NULL THEN 1 ELSE 0 END) AS 'Posts', 
       SUM(CASE WHEN usrOccuId IS NOT NULL THEN 1 ELSE 0 END) AS 'Users'
FROM  occupation_field
          LEFT OUTER JOIN
      users ON usrOccuId = occuId
          LEFT OUTER JOIN
      job_post ON pstOccuId = occuId
GROUP BY job
HAVING (SUM(CASE WHEN pstOccuId IS NOT NULL THEN 1 ELSE 0 END) > 0 
     OR SUM(CASE WHEN usrOccuId IS NOT NULL THEN 1 ELSE 0 END) > 0)

occupation_field LEFT OUTER JOIN users ON usrOccuId = occuId is essentially saying "give me all rows from occupation_field, and if users doesn't have a matching row for this occuId then fill it in with NULL values". See also the wikipedia article on outer joins.

After that, we treat NULL values as 0 in the CASE statement, SUM it all and we're done.

Edit By @RolandoMySQLDBA 2012-01-11 11:42 EDT

Hey Simon. I ran your query against the dataset I made.

mysql> SELECT occuDscr job,
    ->        SUM(CASE WHEN pstOccuId IS NOT NULL THEN 1 ELSE 0 END) AS 'Posts',
    ->        SUM(CASE WHEN usrOccuId IS NOT NULL THEN 1 ELSE 0 END) AS 'Users'
    -> FROM  occupation_field
    ->           LEFT OUTER JOIN
    ->       users ON usrOccuId = occuId
    ->           LEFT OUTER JOIN
    ->       job_post ON pstOccuId = occuId
    -> GROUP BY job
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.00 sec)

mysql>

Your query works as published. +1 !!!

Juntar três tabelas

ATUALIZAÇÃO 2012-01-11 11:50 EDT

ATUALIZAÇÃO 2012-01-11 12:03 EDT

Como ver a lista de bancos de dados no Oracle?

Quão grande deve ser o mysql innodb_buffer_pool_size?

Listar todas as colunas de uma tabela especificada

restaurar a tabela do arquivo .frm e .ibd?

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Como selecionar a primeira linha de cada grupo?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Juntar três tabelas

2 respostas

ATUALIZAÇÃO 2012-01-11 11:50 EDT

ATUALIZAÇÃO 2012-01-11 12:03 EDT

relate perguntas