mysql - SQL:基于多对多表的条件的不同用户的计数

Question

除了以下功能表之外，我还有一个典型的用户表

特点:

-----------------------
| userId |   feature  |
-----------------------
|   1    |   account  |
|   1    |  hardware  |
|   2    |   account  |
|   3    |   account  |
|   3    |  hardware  |
|   3    |    extra   |
-----------------------

基本上，我正在尝试获取一些用于报告目的的计数。特别是，我正在尝试查找拥有帐户和硬件的用户数量以及帐户总数。

我知道我可以执行以下操作来获取帐户总数

SELECT 
    COUNT(DISTINCT userId) as totalAccounts
FROM features
WHERE feature = "account";

但我不确定如何获取同时拥有帐户和硬件的用户数量。在此示例数据集中，我要查找的数字是 2。用户 1 和 3 都有帐户和硬件。

我更愿意在单个查询中执行此操作。可能使用 CASE(下面的 TotalAccounts 示例):

SELECT
    COUNT(DISTINCT(CASE WHEN feature = "account" THEN userId END)) as totalAccounts,
    COUNT( ? ) as accountsWithHardware
FROM features;

Answer 1

这是两个查询 - 一个用于所有用户计数，一个用于两种功能的用户计数 - 您可以将它们与交叉联接结合起来:

select 
  count_all_users.cnt as all_user_count, 
  count_users_having_both.cnt as two_features_user_count
from
(
  select count(distinct userid) as cnt
  from features
) count_all_users
cross join
(
  select count(*) as cnt
  from
  (
    select userid
    from features
    where feature in ('account', 'hardware')
    group by userid
    having count(*) = 2
  ) users_having_both
) count_users_having_both;

更新:经过一番思考，有一种更简单的方法。按用户分组，检测特征1和特征2是否存在。然后数数。

select
  count(*) as all_user_count,
  count(case when has_account = 1 and has_hardware = 1 then 1 end)
    as two_features_user_count
from
(
  select 
    userid,
    max(case when feature = 'account' then 1 else 0 end) as has_account,
    max(case when feature = 'hardware' then 1 else 0 end) as has_hardware
  from features
  group by userid
) users;