MySQL SUM(查询有效但返回意外结果)

Question

我正在对两个表执行 MySQL 查询

关键字统计

+-------------+---------------+------+-----+-------------------+----------------+
| Field       | Type          | Null | Key | Default           | Extra          |
+-------------+---------------+------+-----+-------------------+----------------+
| id          | int(11)       | NO   | PRI | NULL              | auto_increment |
| keywordid   | int(11)       | NO   | MUL | NULL              |                |
| campaignid  | int(11)       | NO   |     | NULL              |                |
| clicks      | int(11)       | NO   | MUL | NULL              |                |
| impressions | int(11)       | NO   | MUL | NULL              |                |
| cost        | decimal(10,2) | NO   | MUL | NULL              |                |
| conversions | int(11)       | NO   | MUL | NULL              |                |
| timestamp   | timestamp     | NO   | MUL | CURRENT_TIMESTAMP |                |
| statsdate   | date          | NO   | MUL | NULL              |                |
+-------------+---------------+------+-----+-------------------+----------------+

和

关键字表

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| clientid   | int(11)      | NO   | MUL | NULL    |                |
| campaignid | int(11)      | NO   |     | NULL    |                |
| keywordid  | int(11)      | NO   | MUL | NULL    |                |
| text       | varchar(125) | NO   | MUL | NULL    |                |
| status     | varchar(10)  | NO   | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

使用以下 SQL 查询

SELECT
    k.status, statsdate, 
    SUM( impressions ) AS impressions, SUM( clicks ) AS clicks, 
    SUM( conversions ) AS conversions, SUM( cost ) AS cost
    FROM keyword_stats
        LEFT JOIN
            (
                SELECT
                    text,keywordid,status
                FROM keywords
            ) AS k USING (keywordid)
    WHERE 
       campaignid = 56486451
       AND statsdate BETWEEN '2011-03-01' AND '2011-03-23'
       AND k.status = "enabled"
    GROUP BY keywordid
    ORDER BY conversions DESC, clicks DESC, impressions DESC LIMIT 0, 10

结果

+---------+------------+-------------+--------+-------------+-----------+
| status  | statsdate  | impressions | clicks | conversions | cost      |
+---------+------------+-------------+--------+-------------+-----------+
| enabled | 2011-03-01 |    71256166 | 242079 |        4247 | 891572.71 |
| enabled | 2011-03-01 |        1101 |     10 |           1 |     43.19 |
| enabled | 2011-03-01 |         210 |      6 |           0 |     23.40 |
| enabled | 2011-03-01 |         331 |      4 |           0 |     15.29 |
| enabled | 2011-03-01 |         672 |      3 |           0 |     11.41 |
| enabled | 2011-03-01 |         486 |      2 |           0 |      8.93 |
| enabled | 2011-03-01 |         254 |      2 |           0 |      9.48 |
| enabled | 2011-03-01 |        2201 |      1 |           0 |      2.96 |
| enabled | 2011-03-01 |         581 |      1 |           0 |      3.25 |
| enabled | 2011-03-01 |         483 |      1 |           0 |      4.39 |
+---------+------------+-------------+--------+-------------+-----------+
10 rows in set (12.12 sec)

查询按预期运行和执行，除了您可能会看到第一个结果包含意外结果，因为任何关键字都不可能拥有我试图计算的总计点击次数、转化次数或费用MySQL 合并总数以实现该结果

编辑添加的关键字ID

+------------+---------+------------+-------------+--------+-------------+-----------+
| keywordid  | status  | statsdate  | impressions | clicks | conversions | cost      |
+------------+---------+------------+-------------+--------+-------------+-----------+
| 2147483647 | enabled | 2011-03-01 |    71256166 | 242079 |        4247 | 891572.71 |
|  101936939 | enabled | 2011-03-01 |        1101 |     10 |           1 |     43.19 |
|   23039553 | enabled | 2011-03-01 |         210 |      6 |           0 |     23.40 |
|  117364874 | enabled | 2011-03-01 |         331 |      4 |           0 |     15.29 |
|   18862051 | enabled | 2011-03-01 |         672 |      3 |           0 |     11.41 |
|   16695651 | enabled | 2011-03-01 |         486 |      2 |           0 |      8.93 |
|   14690232 | enabled | 2011-03-01 |         254 |      2 |           0 |      9.48 |
|   18046691 | enabled | 2011-03-01 |        2201 |      1 |           0 |      2.96 |
|   22232901 | enabled | 2011-03-01 |         581 |      1 |           0 |      3.25 |
|   15072731 | enabled | 2011-03-01 |         483 |      1 |           0 |      4.39 |
+------------+---------+------------+-------------+--------+-------------+-----------+
10 rows in set (11.99 sec)

Answer 1

keywordid 在两个表中都不是 PRIMARY KEY。

最有可能的是，您有一个 keywordid，在两个表中都有很多记录，这导致在这个 keywordid 上进行交叉连接。

另请注意，首先，LEFT JOIN 在您的查询中是多余的，因为您正在过滤 k.status，其次，MySQL 在优化内联 View 方面不是很好。

只需使用这个:

SELECT  k.status, statsdate, 
        SUM( impressions ) AS impressions, SUM( clicks ) AS clicks, 
        SUM( conversions ) AS conversions, SUM( cost ) AS cost
FROM    keyword_stats
JOIN    keywords
USING   (keywordid)
WHERE   campaignid = 56486451
        AND statsdate BETWEEN '2011-03-01' AND '2011-03-23'
        AND k.status = "enabled"
GROUP BY
        keywordid
ORDER BY
        conversions DESC, clicks DESC, impressions DESC
LIMIT   0, 10