带有 group by 的条件聚合查询 [英] Conditional aggregation query with a group by

问题描述

我有一个系统，用户可以从不同的公司登录.我正在尝试运行查询以查看每个公司的活跃用户数与用户总数.

I have a system where users log in from different companies. I'm trying to run a query to see the number of active users vs total number of users for each company.

Users 表:

userID、companyID、lastLogin

期望的输出:

companyID、totalCompanyUsers、numUsersWhoLoggedInWithinLastMonthFromCompany

查询尝试:

SELECT companyID, COUNT(userID) AS `numUsersWhoLoggedInWithinLastMonth`
FROM Users
WHERE IFNULL(TIMESTAMPDIFF(MONTH, lastLogin, NOW()),- 1) = 1
GROUP BY companyID;

我正在努力弄清楚如何拥有两个聚合函数，其中一个是有条件的.

I'm struggling to figure out how to have two aggregation functions, where one is conditional.

推荐答案

你已经很接近了，你只需要将条件放在要执行的条件聚合的聚合函数中:

You're pretty close, you just need to put the conditions inside the aggregate function for the conditional aggregation you want to perform:

SELECT
companyID, 
COUNT(userID) AS `totalCompanyUsers`,
SUM(CASE 
  WHEN 
    TIMESTAMPDIFF(MONTH, lastLogin, NOW()) < 1 THEN 1 
  ELSE 0
END
) AS `numUsersWhoLoggedInWithinLastMonth`
FROM Users
GROUP BY companyID;

结果仍按companyID分组，但第二个聚合函数根据用户是否在上个月内登录进行1和0的求和.

The results are still grouped by companyID, but the second aggregation function performs a sum of 1s and 0s depending on whether the user logged in within the last month or not.

这篇关于带有 group by 的条件聚合查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！