List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 24 2011 11:31pm
Subject:RE: Within-group aggregate query help please - customers and latest subscription row
View as plain text  
Okay, it seems I am learning... slowly...

So there needs to be a second WHERE in the sub-select...

To get ONE customer's last subscription (0.038s):

SELECT 
        c.customer_id,
        c.email,
        c.name,
        c.username,
        s.subscription_id,
        s.`date`
FROM
        customers AS c 
        INNER JOIN customers_subscriptions AS s 
                ON c.customer_id = s.customer_id 
        INNER JOIN 
                (SELECT 
                        MAX(`date`) AS LastDate,
                        customer_id 
                FROM
                        customers_subscriptions AS cs 
		  WHERE cs.customer_id = 7                
                GROUP BY customer_id
                ) AS `x`
                ON s.customer_id = x.customer_id 
                AND s.date = x.LastDate
WHERE c.customer_id = 7;

To get ALL customers and their last subscription row (1m:28s)

SELECT 
        c.customer_id,
        c.email,
        c.name,
        c.username,
        s.subscription_id,
        s.`date`
FROM
        customers AS c 
        INNER JOIN customers_subscriptions AS s 
                ON c.customer_id = s.customer_id 
        INNER JOIN 
                (SELECT 
                        MAX(`date`) AS LastDate,
                        customer_id 
                FROM
                        customers_subscriptions AS cs 
		  GROUP BY customer_id
                ) AS `x`
                ON s.customer_id = x.customer_id 
                AND s.date = x.LastDate
ORDER BY customer_id LIMIT 10;

Thanks to "you know who you are" for pointing me in the right direction. 

Hopefully this helps someone else.

d.

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped]
> Sent: Monday, October 24, 2011 4:06 PM
> To: mysql@stripped
> Subject: RE: Within-group aggregate query help please - customers and
latest
> subscription row
> 
> A kind (and shy) soul replied to me off list and suggested this solution,
> however,
> this takes 28 seconds (that's for a single customer_id, so this is not
going
> to scale).
> Got any other suggestions? :-)
> 
> SELECT
>         c.customer_id,
>         c.email,
>         c.name,
>         c.username,
>         s.subscription_id,
>         s.`date`
> FROM
>         customers AS c
>         INNER JOIN customers_subscriptions AS s
>                 ON c.customer_id = s.customer_id
>         INNER JOIN
>                 (SELECT
>                         MAX(`date`) AS LastDate,
>                         customer_id
>                 FROM
>                         customers_subscriptions AS cs
>                 GROUP BY customer_id) AS `x`
>                 ON s.customer_id = x.customer_id
>                 AND s.date = x.LastDate
> WHERE c.customer_id = 7;
> 
> There are 781,270 customers (nearly 1 million) and  1,018,092
> customer_subscriptions.
> 
> Our tables have many indexes on pretty much every column and for sure the
> ones we use here.
> 
> EXPLAIN says:
> 
>     id  select_type  table       type    possible_keys     key
> key_len  ref        rows  Extra
> ------  -----------  ----------  ------  ----------------  -----------
> -------  ------  -------  -------------------------------
>      1  PRIMARY      c           const   PRIMARY           PRIMARY      4
> const         1
>      1  PRIMARY      s           ref     date,customer_id  customer_id  4
> const         2
>      1  PRIMARY      <derived2>  ALL     (NULL)            (NULL)
> (NULL)   (NULL)   781265  Using where
>      2  DERIVED      cs          ALL     (NULL)            (NULL)
> (NULL)   (NULL)  1018092  Using temporary; Using filesort
> 
> > -----Original Message-----
> > From: Daevid Vincent [mailto:daevid@stripped]
> > Sent: Monday, October 24, 2011 1:46 PM
> > To: mysql@stripped
> > Subject: Within-group aggregate query help please - customers and latest
> > subscription row
> >
> > I know this is a common problem, and I've been struggling with it for a
> full
> > day now but I can't get it.
> >
> > I also tried a few sites for examples:
> > http://www.artfulsoftware.com/infotree/queries.php#101
> >
>
http://forums.devarticles.com/general-sql-development-47/select-max-datetime
> > -problem-10210.html
> >
> > Anyways, pretty standard situation:
> >
> > CREATE TABLE `customers` (
> >   `customer_id` int(10) unsigned NOT NULL auto_increment,
> >   `email` varchar(64) NOT NULL default '',
> >   `name` varchar(128) NOT NULL default '',
> >   `username` varchar(32) NOT NULL,
> > 	...
> > );
> >
> > CREATE TABLE `customers_subscriptions` (
> >   `subscription_id` bigint(12) unsigned NOT NULL default '0',
> >   `customer_id` int(10) unsigned NOT NULL default '0',
> >   `date` date NOT NULL default '0000-00-00',
> >   ...
> > );
> >
> > I want to show a table where I list out the ID, email, username, and
LAST
> > SUBSCRIPTION.
> >
> > I need this data in TWO ways:
> >
> > The FIRST way, is with a query JOINing the two tables so that I can
easily
> > display that HTML table mentioned. That is ALL customers and the latest
> > subscription they have.
> >
> > The SECOND way is when I drill into the customer, I already know the
> > customer_id and so don't need to JOIN with that table, I just want to
get
> > the proper row from the customers_subscriptions table itself.
> >
> > SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY
> > `date` DESC;
> >
> > subscription_id  processor  customer_id  date
> > ---------------  ---------  -----------  ----------
> >       134126370  chargem              7  2005-08-04
> >      1035167192  billme               7  2004-02-08
> >
> > SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7
> > GROUP BY customer_id;
> >
> > gives me 2005-08-04 obviously, but as you all know, mySQL completely
takes
> a
> > crap on your face when you try what would seem to be the right query:
> >
> > SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE
> > customer_id = 7 GROUP BY customer_id;
> >
> > subscription_id  MAX(`date`)
> > ---------------  -----------
> >      1035167192  2005-08-04
> >
> > Notice how I have the correct DATE, but the wrong subscription_id.
> >
> > In the example web sites above, they seem to deal more with finding the
> > MAX(subscription_id), which in my case will not work.
> >
> > I need the max DATE and the corresponding row (with matching
> > subscription_id).
> >
> > Thanks,
> >
> > d
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Within-group aggregate query help please - customers and latest subscription rowDaevid Vincent24 Oct
  • RE: Within-group aggregate query help please - customers and latest subscription rowDaevid Vincent25 Oct
    • RE: Within-group aggregate query help please - customers and latest subscription rowDaevid Vincent25 Oct
      • RE: Within-group aggregate query help please - customers andlatest subscription rowhsv25 Oct