尝试将两个分组查询合并为一个

Question

I'm trying to get a list of all the clicks and sales associated with an account, with both tables having the account ID as an index. Right now I have:

SELECT ACCOUNT, SUM(REVENUE) AS R, COUNT(*) AS SALES
FROM CONVERSIONS
WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT

AND

SELECT ACCOUNT, COUNT(DISTINCT BIN_IP) AS CLICKS
FROM CLICKS
WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT

That gives me both lists, but then I have to do a ton of array manipulation to get them to uniquely line up. Trying to see if there's a way to join both queries, but thus far everything I've tried has failed.

Edit: For clarity, my issue with every join I've tried is I lose the case that theres sales and no clicks, or clicks and no sales. Joining them and checking that the table.ACCOUNT = table2.ACCOUNT only works if there's both clicks and sales present. This is essentially the part of the puzzle I'm struggling with.

Answer 1

If you are happy with the queries you have, you can use:

SELECT conv.ACCOUNT, conv.R, conv.SALES, clicks.CLICKS
FROM
(SELECT ACCOUNT, SUM(REVENUE) AS R, COUNT(*) AS SALES
 FROM CONVERSIONS
 WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT) conv
LEFT JOIN
(SELECT ACCOUNT, COUNT(DISTINCT BIN_IP) AS CLICKS
 FROM CLICKS
 WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT) clicks
ON conv.ACCOUNT = clicks.ACCOUNT
UNION
SELECT clicks2.ACCOUNT, conv2.R, conv2.SALES, clicks2.CLICKS
FROM
(SELECT ACCOUNT, COUNT(DISTINCT BIN_IP) AS CLICKS
 FROM CLICKS
 WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT) clicks2
LEFT JOIN
(SELECT ACCOUNT, SUM(REVENUE) AS R, COUNT(*) AS SALES
 FROM CONVERSIONS
 WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT) conv2
ON clicks2.ACCOUNT = conv2.ACCOUNT

This is messy because mySQL does not have a FULL JOIN. So instead you have to use two left join and UNION. Note that the first set of joins, conv and clicks, will include cases where there are only conversion records and where there are records in both tables. The second set of joins, conv2 and clicks2, will return cases where there are only clicks.