Page 2 of 2

Re: MySQL: SELF JOIN, using dates as columns

Posted: Fri Jul 31, 2009 8:32 am
by Weirdan
VladSun wrote:
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
Well, to not include them into result set it's better to limit result before grouping, by specifying the date range that is retrieved. Then "empty" rows will disappear.

Re: MySQL: SELF JOIN, using dates as columns

Posted: Fri Jul 31, 2009 8:40 am
by VladSun
Weirdan wrote:Well, to not include them into result set it's better to limit result before grouping, by specifying the date range that is retrieved. Then "empty" rows will disappear.
8O Example, please? I couldn't get the "by specifying the date range that is retrieved" part.

You can't use aggregated fields in the WHERE clause, you have to use them in the HAVING clause.

Re: MySQL: SELF JOIN, using dates as columns

Posted: Fri Jul 31, 2009 8:52 am
by Eran
I think weirdan meant something like

Code: Select all

WHERE peerReport.tab = 'term' AND peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26'
Which would filter the rows that don't have a summary date in the range of the report

Re: MySQL: SELF JOIN, using dates as columns

Posted: Fri Jul 31, 2009 8:56 am
by VladSun
pytrin wrote:I think weirdan meant something like

Code: Select all

WHERE peerReport.tab = 'term' AND peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26'
Which would filter the rows that don't have a summary date in the range of the report
:oops:

Re: MySQL: SELF JOIN, using dates as columns

Posted: Fri Jul 31, 2009 8:57 am
by Weirdan
VladSun wrote:
Weirdan wrote:Well, to not include them into result set it's better to limit result before grouping, by specifying the date range that is retrieved. Then "empty" rows will disappear.
8O Example, please? I couldn't get the "by specifying the date range that is retrieved" part.
Sure. Here it is:

Code: Select all

 
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
    peerReport
WHERE
    peerReport.tab = 'term'
    AND peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26' -- <==== this is what I was talking about
GROUP BY
    peerReport.realm
 

Re: MySQL: SELF JOIN, using dates as columns

Posted: Fri Jul 31, 2009 8:58 am
by VladSun
:oops: :oops:

:lol:

Yeah ... I finally got it!

I think I should turn on the air conditioning :) hahaha

PS: This LEFT JOIN is to be blamed for everything :)

Re: MySQL: SELF JOIN, using dates as columns

Posted: Fri Jul 31, 2009 9:01 am
by VladSun
The [sql]BETWEEN '2009-06-24' AND '2009-06-26'[/sql] will also speed up the query, IMHO

Re: MySQL: SELF JOIN, using dates as columns

Posted: Fri Jul 31, 2009 1:12 pm
by mcmurphy510
VladSun,

I used HAVING and COALESCE as you suggested, like this:

Code: Select all

SELECT
    peerReport.realm,
    sum(IF(peerReport.summary_date BETWEEN '2009-07-24' AND '2009-07-29',peerReport.mou,0)) AS TOTAL,
    sum(IF(peerReport.summary_date = '2009-07-29',peerReport.mou,NULL)) AS '2009-07-29',
    sum(IF(peerReport.summary_date = '2009-07-28',peerReport.mou,NULL)) AS '2009-07-28',
    sum(IF(peerReport.summary_date = '2009-07-27',peerReport.mou,NULL)) AS '2009-07-27',
    sum(IF(peerReport.summary_date = '2009-07-26',peerReport.mou,NULL)) AS '2009-07-26',
    sum(IF(peerReport.summary_date = '2009-07-25',peerReport.mou,NULL)) AS '2009-07-25',
    sum(IF(peerReport.summary_date = '2009-07-24',peerReport.mou,NULL)) AS '2009-07-24'
FROM
    peerReport
WHERE
    peerReport.tab = 'termination'
GROUP BY
    peerReport.realm
WITH ROLLUP
HAVING COALESCE(
    sum(IF(peerReport.summary_date = '2009-07-29',peerReport.mou,NULL)),
    sum(IF(peerReport.summary_date = '2009-07-28',peerReport.mou,NULL)),
    sum(IF(peerReport.summary_date = '2009-07-27',peerReport.mou,NULL)),
    sum(IF(peerReport.summary_date = '2009-07-26',peerReport.mou,NULL)),
    sum(IF(peerReport.summary_date = '2009-07-25',peerReport.mou,NULL)),
    sum(IF(peerReport.summary_date = '2009-07-24',peerReport.mou,NULL))
    )
LIMIT 0, 300;
It works great,

I also tried removing the HAVING COALESCE and adding

Code: Select all

peerReport.summary_date BETWEEN '2009-07-24' AND '2009-07-29'
Both work great. Hard to tell which one is faster as both are quite fast.

I also added WITH ROLLUP, to give sums across the bottom row, as well as:

Code: Select all

sum(IF(peerReport.summary_date BETWEEN '2009-07-24' AND '2009-07-29',peerReport.mou,0)) AS TOTAL
Which gives sums for each realm across the specified date range.

I'll experiment with both versions, and post some details here.

Thanks again for all your help.

--McMurphy

Re: MySQL: SELF JOIN, using dates as columns

Posted: Tue Aug 04, 2009 11:59 am
by mcmurphy510
I think that

Code: Select all

peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26'
is a bit faster. However, HAVING COALESCE produces a bit cleaner results (it gets rid of rows with all zeroes as well as NULLs).

I wonder if it would make sense to do both. The table will start to get quite large over time, adding about 500 rows a day on average.

Thanks

McMurphy