MySQL 频率/分布

Question

我创建了一项调查并将其发送出去。该调查要求用户提供电子邮件，然后要求他们从包含 8 个不同选项的下拉菜单中选择要吃哪顿饭。有些人使用同一封电子邮件多次填写调查，但食物选择不同。

我有一个如下所示的 MySQL 数据库:

ID | Email | Type of computer

1 | abc@example.com | Pork
2 | abc@example.com | Chicken
3 | gfh@example.com | Pork
4 | xyz@example.com | Beef

我有超过 300,000 行的数据。我做了一个 MySQL 查询来组织它，所以它看起来像我上面显示的那样。现在，我想统计有多少人选择了 pig 肉和鸡肉，有多少人选择了 pig 肉和牛肉等。有什么帮助吗？

更具体地说，我需要确定数据库中有多少人(有多少唯一的电子邮件)包含“ pig 肉”和“鸡肉”或“ pig 肉”和“牛肉”(等)

Answer 1

要统计有多少人选择了 pig 肉和鸡肉:

SELECT COUNT(*) AS LikesPorkChicken
FROM MyTable t1
  INNER JOIN Mytable t2 ON t1.Email = t2.Email
WHERE t1.Type ='Pork' 
  AND t2.Type = 'Chicken'

要统计有多少人选择了 pig 肉和牛肉:

SELECT COUNT(*) AS LikesPorkBeef
FROM MyTable t1
  INNER JOIN Mytable t2 ON t1.Email = t2.Email
WHERE t1.Type ='Pork' 
  AND t2.Type = 'Beef'

如果您希望在一个查询中使用它，您可以执行如下操作 ( SQL Fiddle ):

SELECT SUM(m.PC) AS PorkChicken, SUM(m.PB) AS PorkBeef, SUM(CB) AS ChickenBeef
FROM 
(
  SELECT 
    CASE WHEN t1.Type = 'Pork' AND t2.Type = 'Chicken' THEN 1 ELSE 0 END AS PC,
    CASE WHEN t1.Type = 'Pork' AND t2.Type = 'Beef' THEN 1 ELSE 0 END AS PB,
    CASE WHEN t1.Type = 'Chicken' AND t2.Type = 'Beef' THEN 1 ELSE 0 END AS CB
  FROM MyTable t1
    INNER JOIN Mytable t2 ON t1.Email = t2.Email 
          AND t1.id <> t2.id
) m;

为确保不计算任何重复项，请使用以下命令 ( SQL Fiddle ):

SELECT SUM(m.PC) AS PorkChicken, SUM(m.PB) AS PorkBeef, SUM(CB) AS ChickenBeef
FROM 
(
  SELECT 
    CASE WHEN ss.type1 = 'Pork' AND ss.type2 = 'Chicken' THEN 1 ELSE 0 END AS PC,
    CASE WHEN ss.type1 = 'Pork' AND ss.type2 = 'Beef' THEN 1 ELSE 0 END AS PB,
    CASE WHEN ss.type1 = 'Chicken' AND ss.type2 = 'Beef' THEN 1 ELSE 0 END AS CB
  FROM 
  (
    SELECT DISTINCT t1.Email, t1.Type AS type1, t2.Type AS type2
    FROM MyTable t1
    INNER JOIN Mytable t2 ON t1.Email = t2.Email
      AND t1.id <> t2.id
  ) ss
) m;