mysql - 在 LEFT JOIN 中将 NULL 替换为默认值，但在 ROLLUP 中不会

Question

我有一个包含 LEFT JOIN、group by 和 ROLLUP 的查询，如下所示:

Select * from
(
    Select user_agent,
    value,
    recoqty,
    count(recoqty) as C
    from august_2016_search_stats SS
    LEFT JOIN august_2016_extra E
        on (SS.id = E.stats_id and E.key = 'personalized')
    where time >= '2016-08-22 00:00:00' and
          time <= '2016-08-22 23:59:59' and
          query_type = 'myfeed' and
          recoqty = 'topics'
    group by recoqty,
             user_agent,
             value
    with ROLLUP
    having recoqty is not null
) D
order by C desc;

结果如下:

+------------+-------+---------+------+
| user_agent | value | recoqty | C    |
+------------+-------+---------+------+
| NULL       | NULL  | topics  | 1330 |
| abscdef    | NULL  | topics  | 1330 |
| abscdef    | NULL  | topics  | 1285 |
| abscdef    | 1     | topics  |   25 |
| abscdef    | 0     | topics  |   20 |
+------------+-------+---------+------+

这里，值 (NULL 1285) 是由于 LEFT JOIN，值 (NULL 1330) 是由于汇总。但是，有没有办法只为 LEFT JOIN 而不是为 ROLLUP 替换 NULL 值？

Answer 1

这有点棘手，因为看起来来自您的数据的 NULL 值与来自汇总的 NULL 值无法区分。一种可能的解决方法是首先执行非聚合查询，其中将 value 列中的 NULL 值替换为 'NA'，或者一些其他占位符，使用 COALESCE。然后使用带有汇总的 GROUP BY 将其聚合为子查询。然后 value 列中的 NULL 值肯定来自汇总，而不是您的实际数据。

SELECT t.user_agent,
       t.value,
       t.recoqty,
       t.C
FROM
(
    SELECT user_agent,
           COALESCE(value, 'NA') AS value
           recoqty,
           COUNT(recoqty) AS C
    FROM august_2016_search_stats SS
    LEFT JOIN august_2016_extra E
        ON SS.id = E.stats_id AND
              E.key = 'personalized'
    WHERE time >= '2016-08-22 00:00:00' AND
          time <= '2016-08-22 23:59:59' AND
          query_type = 'myfeed' AND
          recoqty = 'topics'
) t
GROUP BY t.recoqty,
         t.user_agent,
         t.value
WITH ROLLUP
HAVING t.recoqty IS NOT NULL