I want to combine three tables - date, lead and click - in a query.
我想在查询中组合三个表 - 日期,潜在客户和点击。
The tables looks like this:
表格如下:
date:
|date|
lead:
id|time|commission
click:
id|time|commission
The table date is just storing dates and is used when getting dates with no click or lead.
表日期只是存储日期,用于获取没有点击或潜在客户的日期。
So if we have the following data in the tables:
因此,如果表中包含以下数据:
date:
2009-06-01
2009-06-02
2009-06-03
lead:
1|2009-06-01|400
2|2009-06-01|300
3|2009-06-03|350
click:
1|2009-06-01|1
2|2009-06-03|2
3|2009-06-03|2
4|2009-06-03|0
I would like to get date, number of click, commission generated by clicks (there are clicks that don't give commission), number of leads, commission generated by leads and total commission. So with the tables above I would like to get:
我想获得日期,点击次数,点击产生的佣金(有不提供佣金的点击次数),潜在客户数量,潜在客户产生的佣金和总佣金。因此,根据上面的表格,我想得到:
2009-06-01|1|1|2|700|701|
2009-06-02|0|0|0|0|0
2009-06-03|3|4|1|350|354|
I have tried with the following union:
我尝试过以下联合:
SELECT
campaign_id,
commission_date,
SUM( click_commission ) AS click_commission,
click,
SUM( lead_commission ) AS lead_commission ,
lead,
SUM( total_commission ) as total_commission
FROM(
SELECT
click.campaign_id AS campaign_id,
DATE( click.time ) AS commission_date,
click.commission AS click_commission,
(SELECT count(click.id) from click GROUP BY date(click.time)) as click,
0 as lead_commission,
0 as lead,
click.commission AS total_commission
FROM click
UNION ALL
SELECT
lead.campaign_id AS campaign_id,
DATE( lead.time ) AS commission_date,
0 as click_commission,
0 as click,
lead.commission AS lead_commission,
lead.id as lead,
lead.commission AS total_commission
FROM lead
UNION ALL
SELECT
0 AS campaign_id,
date.date AS commission_date,
0 AS click_commission,
0 as click,
0 AS lead_commission,
0 as lead,
0 AS total_commission
FROM date
) AS foo
WHERE commission_date BETWEEN '2009-06-01' AND '2009-07-25'
GROUP BY commission_date
ORDER BY commission_date LIMIT 0, 10
But this does not work to count both the number of clicks and leads, the code above gives the right amount of clicks bot 0 on all leads. If I move the code around and put the select from the lead table I get the leads right bot 0 on all clicks. I have not been able to find a way to get both of the counts from the query.
但这不能同时计算点击次数和潜在客户数量,上面的代码可以为所有潜在客户提供正确的点击量0。如果我移动代码并从引导表中选择选择,我会在所有点击中获得引导权限0。我无法找到从查询中获取两个计数的方法。
So I tried a left-join instead:
所以我尝试了一个左连接:
SELECT
date.date as date,
count( DISTINCT click.id ) AS clicks,
sum(click.commission) AS click_commission,
count( lead.id ) AS leads,
sum(lead.commission) AS lead_commission
FROM date
LEFT JOIN click ON ( date.date = date( click.time ) )
LEFT JOIN lead ON ( date.date = date( lead.time ) )
GROUP BY date.date
LIMIT 0 , 30
The problem with this query is if there are more than one clicks or leads on a date it will return the expected value * 2. So on 2009-06-01 it will return 1400 instead on the expected 700 for lead commission.
此查询的问题是,如果日期上有多个点击或潜在客户,它将返回预期值* 2.因此,在2009-06-01,它将返回1400而不是预期的700佣金佣金。
So in the UNION I have problems with the count and in the left join it is the SUM that is not working.
所以在UNION中我有计数问题,在左连接中,它是SUM不起作用。
I would really like to stick to the UNION if possible, but I haven't found a way to get both counts from it.
如果可能的话,我真的很想坚持UNION,但是我还没有办法从中得到两个计数。
(This is a follow up to this earlier question, but since I didn't ask for the count in that I posted a new question.)
(这是对此前一个问题的跟进,但由于我没有要求计数,因此我发布了一个新问题。)
2 个解决方案
#1
SELECT date,
COALESCE(lcomm, 0), COALESCE(lcnt, 0),
COALESCE(ccomm, 0), COALESCE(ccnt, 0),
COALESCE(ccomm, 0) + COALESCE(lcomm, 0),
COALESCE(ccnt, 0) + COALESCE(lcnt, 0)
LEFT JOIN
(
SELECT date, SUM(commission) AS lcomm, COUNT(*) AS lcnt
FROM leads
GROUP BY
date
) l
ON l.date = d.date
LEFT JOIN
(
SELECT date, SUM(commission) AS ccomm, COUNT(*) AS ccnt
FROM clicks
GROUP BY
date
) с
ON c.date = d.date
FROM date d
#2
The code that I used, built from the suggestion from Quassnoi:
我使用的代码是根据Quassnoi的建议构建的:
SELECT date,
COALESCE(ccomm, 0) AS click_commission, COALESCE(ccnt, 0) AS click_count,
COALESCE(lcomm, 0) AS lead_commision, COALESCE(lcnt, 0) AS lead_count,
COALESCE(ccomm, 0) + COALESCE(lcomm, 0) as total_commission
FROM date d
LEFT JOIN
(
SELECT DATE(time) AS lead_date, SUM(commission) AS lcomm, COUNT(*) AS lcnt
FROM lead
GROUP BY
lead_date
) l
ON lead_date = date
LEFT JOIN
(
SELECT DATE(time) AS click_date, SUM(commission) AS ccomm, COUNT(*) AS ccnt
FROM click
GROUP BY
click_date
) с
ON click_date = date
#1
SELECT date,
COALESCE(lcomm, 0), COALESCE(lcnt, 0),
COALESCE(ccomm, 0), COALESCE(ccnt, 0),
COALESCE(ccomm, 0) + COALESCE(lcomm, 0),
COALESCE(ccnt, 0) + COALESCE(lcnt, 0)
LEFT JOIN
(
SELECT date, SUM(commission) AS lcomm, COUNT(*) AS lcnt
FROM leads
GROUP BY
date
) l
ON l.date = d.date
LEFT JOIN
(
SELECT date, SUM(commission) AS ccomm, COUNT(*) AS ccnt
FROM clicks
GROUP BY
date
) с
ON c.date = d.date
FROM date d
#2
The code that I used, built from the suggestion from Quassnoi:
我使用的代码是根据Quassnoi的建议构建的:
SELECT date,
COALESCE(ccomm, 0) AS click_commission, COALESCE(ccnt, 0) AS click_count,
COALESCE(lcomm, 0) AS lead_commision, COALESCE(lcnt, 0) AS lead_count,
COALESCE(ccomm, 0) + COALESCE(lcomm, 0) as total_commission
FROM date d
LEFT JOIN
(
SELECT DATE(time) AS lead_date, SUM(commission) AS lcomm, COUNT(*) AS lcnt
FROM lead
GROUP BY
lead_date
) l
ON lead_date = date
LEFT JOIN
(
SELECT DATE(time) AS click_date, SUM(commission) AS ccomm, COUNT(*) AS ccnt
FROM click
GROUP BY
click_date
) с
ON click_date = date