MySQL: SELF JOIN, using dates as columns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

mcmurphy510
Forum Newbie
Posts: 6
Joined: Tue Jul 28, 2009 12:13 pm

MySQL: SELF JOIN, using dates as columns

Post by mcmurphy510 »

I think what I'm looking for here is some sort of advanced (SELF?) JOIN, and it's currently beyond me. I'm hoping (at least) one of you can help. :)

I have a data table with a few fields. id (primary key), summary_date, realm, mou and tab. For this query, the only fields I'm interested in are summary_date, realm and mou. Here's the table structure:'

Code: Select all

CREATE TABLE `peerReport` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `summary_date` date NOT NULL,
  `realm` varchar(127) NOT NULL,
  `mou` int(10) UNSIGNED NOT NULL,
  `tab` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3226 ;
SAMPLE DATA:

Code: Select all

INSERT INTO `peerReport` (`id`, `summary_date`, `realm`, `mou`, `tab`) VALUES 
(1, '2009-06-24', 'aaacom', 17072, 'term'),
(2, '2009-06-24', 'backbonecom', 82269, 'term'),
(3, '2009-06-24', 'telco1', 4166, 'term'),
(4, '2009-06-24', 'zeuscom', 2112, 'term'),
(5, '2009-06-25', 'aaacom', 8788, 'term'),
(6, '2009-06-25', 'backbonecom', 145, 'term'),
(7, '2009-06-25', 'telco1', 5877, 'term'),
(8, '2009-06-25', 'zeuscom', 3243, 'term'),
(9, '2009-06-26', 'queuetel', 5434, 'term'),
(10, '2009-06-26', 'othertel', 15545, 'term'),
(11, '2009-06-26', 'telco1', 7841, 'term'),
(12, '2009-06-27', 'aaacom', 7763, 'term'),
(13, '2009-06-24', 'aaacom', 233, 'orig');
What I need to do is develop a query that will return the `realm` as row names and `summary_date` as colum names like this. Or something similar:

Code: Select all

REALM       2009-06-24      2009-06-25      2009-06-26
______________________________________________________
aaacom      17072           8788            NULL
backbonecom 82269           145             NULL
telco1      4166            5877            7841
zeuscom     2112            3243            NULL
queuetel    NULL            NULL            5434                
othertel    NULL            NULL            15545
I plan on supplying a date range in PHP (in this case 2009-06-24 thru 2009-06-26) and have PHP build the query.

Here is the query I have so far:

Code: Select all

SELECT
    p.realm,
    a.mou AS '2009-06-24',
    b.mou AS '2009-06-25',
    c.mou AS '2009-06-26'
FROM peerReport p
    JOIN peerReport a ON p.realm=a.realm AND a.summary_date = '2009-06-24'
    JOIN peerReport b ON p.realm=b.realm AND b.summary_date = '2009-06-25'
    JOIN peerReport c ON p.realm=c.realm AND c.summary_date = '2009-06-26'
WHERE
    p.tab = 'term'

But it produces far too many results, some of which contain unwanted (wrong) data.

So, what I'm looking for is a way to modify that query (or a new query) so that it only produces one line per 'realm' and will only give results that have a 'cat' value of 'term' for the correct date (and in the correct date column).


-- Thanks, McMurphy
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL: SELF JOIN, using dates as columns

Post by Eran »

Your query was close, you can achieve the results you want by changing the join to a LEFT JOIN (when you don't specify the type, it's an INNER JOIN) and grouping by the realm.

Code: Select all

SELECT
    p.realm,
    a.mou AS '2009-06-24',
    b.mou AS '2009-06-25',
    c.mou AS '2009-06-26'
FROM peerReport p
    LEFT JOIN peerReport a ON p.realm=a.realm AND a.summary_date = '2009-06-24'
    LEFT JOIN peerReport b ON p.realm=b.realm AND b.summary_date = '2009-06-25'
    LEFT JOIN peerReport c ON p.realm=c.realm AND c.summary_date = '2009-06-26'
WHERE
    p.tab = 'term'
GROUP BY realm
To make it run in a reasonable time, you should add a couple of indexes:

Code: Select all

ADD INDEX `tab` ( `tab` , `realm` );
ADD INDEX `realm` ( `realm` )
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

mcmurphy510 wrote:I think what I'm looking for here is some sort of advanced (SELF?) JOIN,

...

Code: Select all

REALM       2009-06-24      2009-06-25      2009-06-26
______________________________________________________
aaacom      17072           8788            NULL
backbonecom 82269           145             NULL
telco1      4166            5877            7841
zeuscom     2112            3243            NULL
queuetel    NULL            NULL            5434                
othertel    NULL            NULL            15545
Just a quick notice: what you are trying to achive is called a "cross-tab" or a "pivot table".

PS: You may generate the dynamic query by using a stored procedure : http://rpbouman.blogspot.com/2005/10/cr ... mysql.html
There are 10 types of people in this world, those who understand binary and those who don't
mcmurphy510
Forum Newbie
Posts: 6
Joined: Tue Jul 28, 2009 12:13 pm

Re: MySQL: SELF JOIN, using dates as columns

Post by mcmurphy510 »

Thanks pytrin,

The query works, however it runs very slow with more than three dates listed, even with the indexes. Should I put another index on there for 'summary_date' as well?

Also, if there are no entries for a given realm across all the dates listed, it still shows that realm, but it shows NULL values all the way across (which is correct, but I would like it not to show up at, unless there is data for at least one of the dates).

For example if we added a new row of data as so:

Code: Select all

INSERT INTO `peerReport` (`id`, `summary_date`, `realm`, `mou`, `tab`) VALUES
(14, '2009-06-28', 'xyztelecom', 12345, 'term')
and then ran the query (exactly as you listed it), this is what it gives me:

Code: Select all

REALM       2009-06-24      2009-06-25      2009-06-26
______________________________________________________
aaacom      17072           8788            NULL
backbonecom 82269           145             NULL
telco1      4166            5877            7841
zeuscom     2112            3243            NULL
queuetel    NULL            NULL            5434               
othertel    NULL            NULL            15545
xyztelco    NULL            NULL            NULL  
so since xyztelco is NULL across all the dates in the query, I would like it not show up at all.

I have this query:

Code: Select all

SELECT
    p.realm,
    a.mou AS '2009-06-24',
    b.mou AS '2009-06-25',
    c.mou AS '2009-06-26'
FROM peerReport p
    JOIN peerReport a ON p.realm=a.realm AND a.summary_date = '2009-06-24' AND a.tab = 'term'
    JOIN peerReport b ON p.realm=b.realm AND b.summary_date = '2009-06-25' AND b.tab = 'term'
    JOIN peerReport c ON p.realm=c.realm AND c.summary_date = '2009-06-26' AND c.tab = 'term'
WHERE
    p.id=a.id
Which seems to execute fast, but I have the opposite problem. This one will only show rows that have values in ALL dates across the range, so I get this:

Code: Select all

REALM       2009-06-24      2009-06-25      2009-06-26
______________________________________________________
telco1      4166            5877            7841

Thanks again for you help.

VladSun,

Thanks for the info. That's some pretty heavy duty stuff, so it'll take a while for me to digest. I knew what I was trying to do had a name, just not what it was... lol

Thanks.

-- McMurphy
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL: SELF JOIN, using dates as columns

Post by Eran »

Can you run EXPLAIN on the query and post here the results?
mcmurphy510
Forum Newbie
Posts: 6
Joined: Tue Jul 28, 2009 12:13 pm

Re: MySQL: SELF JOIN, using dates as columns

Post by mcmurphy510 »

Since it runs fine on 1, 2 and 3 dates, here's the one with 4 that takes so long:

Code: Select all

EXPLAIN SELECT
    p.realm,
    a.mou AS '2009-06-24',
    b.mou AS '2009-06-25',
    c.mou AS '2009-06-26',
    d.mou AS '2009-06-27'
FROM peerReport p
    LEFT JOIN peerReport a ON p.realm=a.realm AND a.summary_date = '2009-06-24'
    LEFT JOIN peerReport b ON p.realm=b.realm AND b.summary_date = '2009-06-25'
    LEFT JOIN peerReport c ON p.realm=c.realm AND c.summary_date = '2009-06-26'
    LEFT JOIN peerReport d ON p.realm=d.realm AND d.summary_date = '2009-06-27'
WHERE
    p.tab = 'termination'
GROUP BY realm
Gives me this:

Code: Select all

id  select_type     table   type    possible_keys   key     key_len     ref                 rows    Extra
1   SIMPLE          p       ref     tab             tab     42          const               1316    Using where; Using index
1   SIMPLE          a       ref     realm           realm   129         peerReport.p.realm  42   
1   SIMPLE          b       ref     realm           realm   129         peerReport.p.realm  42   
1   SIMPLE          c       ref     realm           realm   129         peerReport.p.realm  42   
1   SIMPLE          d       ref     realm           realm   129         peerReport.p.realm  42  
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL: SELF JOIN, using dates as columns

Post by Eran »

index-wise it looks as optimized as it can be. the only way to improve performance would have to come from changing the query, or performing the aggregation in PHP.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

I like more the IF() version than the LEFT JOIN version (it's SQL strict also :) ):
[sql]SELECT    peerReport.realm,   
sum(        IF(peerReport.summary_date = '2009-06-24',           peerReport.mou,        NULL    ) AS '2009-06-24',   
sum(        IF(peerReport.summary_date = '2009-06-25',           peerReport.mou,        NULL    ) AS '2009-06-25',   
sum(        IF(peerReport.summary_date = '2009-06-26',           peerReport.mou,        NULL    ) AS '2009-06-26'
FROM     peerReportWHERE    peerReport.tab = 'term'
GROUP BY     peerReport.realm[/sql]
It has no JOINS, so it should perform better ... Does it?
Last edited by VladSun on Tue May 14, 2013 6:47 am, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

mcmurphy510 wrote:Also, if there are no entries for a given realm across all the dates listed, it still shows that realm, but it shows NULL values all the way across (which is correct, but I would like it not to show up at, unless there is data for at least one of the dates).
...

Code: Select all

REALM       2009-06-24      2009-06-25      2009-06-26
______________________________________________________
aaacom      17072           8788            NULL
backbonecom 82269           145             NULL
telco1      4166            5877            7841
zeuscom     2112            3243            NULL
queuetel    NULL            NULL            5434               
othertel    NULL            NULL            15545
xyztelco    NULL            NULL            NULL  
so since xyztelco is NULL across all the dates in the query, I would like it not show up at all.
You may use a HAVING clause together with a condition using a COALESCE
http://dev.mysql.com/doc/refman/5.0/en/ ... n_coalesce
There are 10 types of people in this world, those who understand binary and those who don't
mcmurphy510
Forum Newbie
Posts: 6
Joined: Tue Jul 28, 2009 12:13 pm

Re: MySQL: SELF JOIN, using dates as columns

Post by mcmurphy510 »

VladSun,

Not only does it work... it's FAST!!! It did a whole 31 days worth of columns in under 1/10 of a second! I may need to go out to a quarter (90 days) of data, but given that speed, I doubt it will be a problem.

I'll try HAVING and COALESCE to see about limiting the return tomorrow... but it's past my bed time here in the States... and I'm off the clock (but I really wanted to see if your query worked :))

Thanks again... you really are a DevNet Master!

--McMurphy
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

Heh, thanks :)

Glad to hear it has solved your problems :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL: SELF JOIN, using dates as columns

Post by Weirdan »

VladSun wrote:I like more the IF() version than the LEFT JOIN version (it's SQL strict also :) )
Myself I prefer to multiply by Boolean condition:

Code: Select all

 
...
  sum(peerReport.mou * (peerReport.summary_date = '2009-06-24')) AS '2009-06-24'
 
It's shorter and as a side effect does not produce those pesky nulls in output :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

Weirdan wrote:
VladSun wrote:I like more the IF() version than the LEFT JOIN version (it's SQL strict also :) )
Myself I prefer to multiply by Boolean condition:

Code: Select all

 
...
  sum(peerReport.mou * (peerReport.summary_date = '2009-06-24')) AS '2009-06-24'
 
It's shorter and as a side effect does not produce those pesky nulls in output :)
Nice :)
Though "those pesky nulls" might be meaningful in some cases (zero is a value, NULL is not)

With the "IF() version" one can easily replace NULL with 0 ;)
Well, in both "versions" the OP requirements are still not met (no list of "empty" rows)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL: SELF JOIN, using dates as columns

Post by Weirdan »

VladSun wrote:Well, in both "versions" the OP requirements are still not met (no list of "empty" rows)
well, that only requires a join to the table where the full list of those 'realms' is stored.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: SELF JOIN, using dates as columns

Post by VladSun »

Weirdan wrote:
VladSun wrote:Well, in both "versions" the OP requirements are still not met (no list of "empty" rows)
well, that only requires a join to the table where the full list of those 'realms' is stored.
By "no list of "empty" rows" I meant that rows like:
realm NULL NULL NULL
or
realm 0 0 0
must not be listed in the result, that's why I suggested using HAVING clause.

Solutions suggested till now list all of the realms, including "all-zeroes" rows
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply