时间戳

首页 > 解决方案 > MySQL，DISTINCT 中的 SUM 操作

mysql - MySQL，DISTINCT 中的 SUM 操作

问题描述

目前我试图根据用户性别计算我的应用程序中唯一用户访问的数量。这是计算所有访问的示例查询（不是唯一的）

SELECT
    DATE(v.visited_at) AS visit_date,
    SUM(IF(u.gender = 'M', 1, 0)) AS male_visit,
    SUM(IF(u.gender = 'F', 1, 0)) AS female_visit,
    SUM(IF(u.gender = '' OR u.gender IS NULL, 1, 0)) AS unknown_visit
FROM 
    visits v
    INNER JOIN users u ON v.user_id = u.id
WHERE
    DATE(v.visited_at) >= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
    AND v.duration > 30
GROUP BY
    DATE(v.visited_at)

尝试使用子查询并计数不同它的工作原理，但它慢了 4 倍。

SELECT
    DATE(visited_at) as visit_date,
    (SELECT COUNT(DISTINCT u.id) FROM visits v JOIN users u ON v.user_id = u.id WHERE u.gender = 'M' AND DATE(v.visited_at) = visit_date AND v.duration > 30) AS male_visit,
    (SELECT COUNT(DISTINCT u.id) FROM visits v JOIN users u ON v.user_id = u.id WHERE u.gender = 'F' AND DATE(v.visited_at) = visit_date AND v.duration > 30) AS female_visit,
    (SELECT COUNT(DISTINCT u.id) FROM visits v JOIN users u ON v.user_id = u.id WHERE u.gender = '' OR u.gender IS NULL AND DATE(v.visited_at) = visit_date AND v.duration > 30) AS unknown_visit
FROM 
    visits v
WHERE
    DATE(visited_at) >= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
GROUP BY
    DATE(visited_at)

对此有何建议？

标签： mysqlsqlsum

解决方案

COUNT(DISTINCT)总是会比COUNT(). 你可以试试：

SELECT DATE(v.visited_at) AS visit_date,
       COUNT(DISTINCT CASE WHEN u.gender = 'M' THEN u.id END) AS male_visit,
       COUNT(DISTINCT CASE WHEN u.gender = 'F' THEN u.id END) AS female_visit,
       COUNT(DISTINCT CASE WHEN u.gender = '' OR u.gender IS NULL THEN u.id END) AS unknown_visit
FROM visits v INNER JOIN
     users u
     ON v.user_id = u.id
WHERE DATE(v.visited_at) >= DATE_SUB(SYSDATE(), INTERVAL 30 DAY) AND
      v.duration > 30
GROUP BY DATE(v.visited_at);

不过，我不知道它是否会更快。

推荐阅读