问题描述:

I have some data that I pull as follows:

select date_key, count(id) from table1 where date_key between x and y group by date_key;

I would like to get some other data as follows:

select date_key2, count(id) from table2 where date_key2 between x-7 days and y-7 days group by date_key2;

I would then like to divide one by the other and get the result. To this end, I tried:

SELECT t1.date_key,

I.Converts,

I.Converts /(SELECT J.Starts FROM (SELECT t2.`date_key`,

count(id) AS Starts

FROM `table2` t2

WHERE date_key BETWEEN 20140225 AND 20140303

GROUP BY t2.`date_key`)J)

FROM

(

SELECT t1.`date_key`,

count(id) AS Converts

FROM `table` t1

WHERE date_key BETWEEN 20140304 AND 20140411

GROUP BY t1.`date_key`)I;

I get a Subquery returns more than 1 row error. When I run them individually, ie, select the separate parts and run them, they run fine, but together, it crashes. What am I doing wrong?

网友答案:

To get your query working, you'll probably want to do a join. Basically do each grouping query separately and then join them together at the end.

In the query that follows I am making the assumption that you want to calculate a daily conversion rate. So for example, users that start on Day 1 are eligible to become 'Converts' on Day 8.

SELECT I.date_key,
I.Converts,
I.Converts / J.`Starts`
FROM
(
    SELECT t2.`date_key`,
    count(id) AS `Starts`
    FROM `table2` AS t2
    WHERE date_key BETWEEN '2014-02-25' AND '2014-03-03'
    GROUP BY t2.`date_key`
) AS J
JOIN
(
    SELECT t1.`date_key`,
    count(id) AS Converts
    FROM `table` AS t1
    WHERE date_key BETWEEN '2014-03-04' AND '2014-04-11'
    GROUP BY t1.`date_key`
) AS I
ON I.`date_key`=DATE_ADD(J.`date_key`, INTERVAL 7 DAY);

Note the day offset being applied at the bottom. This telling SQL to join each Converts date with the Starts date from 7 days prior.

相关阅读:
Top