Geek Answers Handbook

MySQL Data Summary Tricks - Is there UNGROUP BY?

I'm trying to make some data, and don't do SQL gymnastics often enough to find out what function I'm looking for. I would call it "ungroup by". I have a SELECT that displays this, reflecting a series of monthly subscription positions: when they were created, closed, and how many monthly expenses:

+----+---------------------------------+------------+------------+------------+ | id | description | created_on | closed_on | monthly | +----+---------------------------------+------------+------------+------------+ | 3 | Daily horoscope email | 2012-01-01 | null | 10000.0000 | | 5 | Pet food delivery | 2012-01-05 | null | 3500.0000 | | 6 | Dirty magazine subscription | 2012-01-09 | null | 1500.0000 | | 7 | Stupid nuts posted in a box | 2012-01-01 | 2012-01-04 | 1500.0000 | .... etc ...

What I'm trying to do is run the "execution speed" daily. Thus, each day is transferred with the current total current monthly obligation, that is, the above data will be displayed on:

 +------------+----------+ | date | run_rate | +------------+----------+ | 2012-01-01 | 11500 | | 2012-01-02 | 11500 | | 2012-01-03 | 11500 | | 2012-01-04 | 10000 | | 2012-01-05 | 13500 | | 2012-01-06 | 13500 | | 2012-01-07 | 13500 | | 2012-01-08 | 13500 | | 2012-01-09 | 15000 |

What I think is possible is to create a temporary table with one row for each day, and then write a LEFT JOIN / GROUP BY statement that references the first table to create my output. But I can only see how to create an everyday “difference”, and not the total amount, and I need to “ungroup” the first table into two records, a positive record to create a subscription, and a negative record when it is closed.

I would like to stick with MySQL and, if possible, in one mega statement. If this is not possible, I can add some stored procedures or temporary tables to my query structure. Or do I really need to grind my data through Ruby? (I know exactly how, but I hoped that I could keep all my logic in one place, and I'm trying to improve our current slow calculation, which uses ActiveRecord).

+4
source share
3 answers

Here is another way to do this using: INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY , where it will create a date inside and not create a temporary table. Explan plan can confirm which method is best for you.

SET @rrate: = 0;

 SELECT X.rdate, (@rrate: =@rrate + COALESCE(Y.summonthly,0) - COALESCE(Z.summonthly,0)) as run_rate FROM( SELECT date_add(P.createdon, interval `day` day) as rdate FROM (SELECT @i:= @i + 1 AS `day` FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY, (SELECT @i:= -1) AS i ) As D, rategroups P GROUP BY rdate HAVING rdate <= (SELECT MAX(createdon) FROM rategroups) ORDER BY rdate) X LEFT JOIN (SELECT createdon, sum(monthly) summonthly FROM rategroups GROUP BY createdon) Y ON X.rdate = Y.createdon LEFT JOIN (SELECT closed_on, sum(monthly) summonthly FROM rategroups GROUP BY closed_on) Z ON X.rdate = Z.closed_on GROUP BY X.rdate ; | RDATE | RUN_RATE | --------------------------------------------- | January, 01 2012 00:00:00+0000 | 11500 | | January, 02 2012 00:00:00+0000 | 11500 | | January, 03 2012 00:00:00+0000 | 11500 | | January, 04 2012 00:00:00+0000 | 10000 | | January, 05 2012 00:00:00+0000 | 13500 | | January, 06 2012 00:00:00+0000 | 13500 | | January, 07 2012 00:00:00+0000 | 13500 | | January, 08 2012 00:00:00+0000 | 13500 | | January, 09 2012 00:00:00+0000 | 15000 |
+1
source

Try something like this - you will get the desired results:

 SET @runtot:=0; SELECT mydates.seeddate, (@runtot := @runtot + IFNULL(m.amt,0) - IFNULL(m2.amt,0)) AS rt FROM mydates left join (Select createdon, SUM(monthly) amt FROM mytable group by createdon ) m on mydates.seeddate = m.createdon left join (Select closed_on, SUM(monthly) amt FROM mytable group by closed_on ) m2 on mydates.seeddate = m2.closed_on

And here is the SQL Fiddle .

Good luck.

+1
source

Try the following:

 select date,sum(monthly) from ( select created_on as date from yourtable union select closed_on from yourtable where closed_on is not null ) as alldates left outer join yourtable on date >= created_on and (closed_on is null or date < closed_on) where date between '2012-1-1' and '2012-1-31' group by date order by 1

According to your example data is output:

 +------------+--------------+ | date | sum(monthly) | +------------+--------------+ | 2012-01-01 | 11500.00 | | 2012-01-04 | 10000.00 | | 2012-01-05 | 13500.00 | | 2012-01-09 | 15000.00 | +------------+--------------+ 4 rows in set (0.00 sec)

We can understand that the date of the day is not there equal to the nearest. Let's say run_rate from '2012-01-02' is equal to run_rate from '2012-01-01'.

Suppose you already have a table containing all the dates of the month, we call it "mydate", with one column "date".

 mysql> select * from mydate where date >= '2012-1-1' and date <= '2012-1-31'; +------------+ | date | +------------+ | 2012-01-01 | | 2012-01-02 | | 2012-01-03 | | 2012-01-04 | | 2012-01-05 | | 2012-01-06 | | 2012-01-07 | ...

Then replace

 ( select created_on as date from yourtable union select closed_on from yourtable where closed_on is not null ) as alldates

with mydate

Done!

+1
source

More articles:


All Articles