主页 / dba / 问题 / 49434
Accepted
Jacobm001
Asked: 2013-09-07 14:31:52 +0800 CST

计算一组出现的次数

  • 772

我试图找出在我的数据库中特定分组发生了多少次。我有两个相关的表。

mysql> describe logins;
+-------------+-------------+------+-----+---------------------+----------------+
| Field       | Type        | Null | Key | Default             | Extra          |
+-------------+-------------+------+-----+---------------------+----------------+
| id          | int(11)     | NO   | PRI | NULL                | auto_increment |
| username    | varchar(10) | NO   | MUL |                     |                |
| login_time  | datetime    | NO   | MUL | 0000-00-00 00:00:00 |                |
| logout_time | datetime    | YES  |     | NULL                |                |
| duration    | int(11)     | YES  |     | NULL                |                |
| location    | varchar(15) | YES  | MUL | NULL                |                |
+-------------+-------------+------+-----+---------------------+----------------+

和

mysql> describe login_apps;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| sid         | int(11)     | YES  |     | NULL    |                |
| programName | varchar(63) | YES  |     | NULL    |                |
| duration    | int(11)     | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

假设我想知道给定会话中使用了哪些应用程序。我可以用：

mysql> select distinct programName from login_apps where sid="35" and programName!="NULL";
+--------------------+
| programName        |
+--------------------+
| Acrobat  Distiller |
| FireFox            |
| Minitab 16         |
+--------------------+

获取不同的程序名称，但我感兴趣的是特定应用程序组在给定会话中的使用频率。因此，从本质上讲，这三个应用程序多久被组合在一起。有没有一种方法可以让我以以下形式获得结果

application x-y-z, 12
application x-z, 9
application y-z, 7
mysql mysql-5.1

2 个回答

  1. 我可以提供我将如何在 MS SQL 中使用PIVOT和处理它ROW_NUMBER()。这些在 MySQL 中似乎不存在（也许最新版本已经添加了它们？），所以恐怕对您没有帮助。也许更熟悉 MySQL 的人可以使用它来帮助找到解决方案。

    SELECT
    "1"
    + CASE WHEN "2" IS NULL THEN '' ELSE '-' + "2" END
    + CASE WHEN "3" IS NULL THEN '' ELSE '-' + "3" END AS Applications,
    Instances
FROM
    (
    SELECT
        SID, ProgramName, ROW_NUMBER() OVER (PARTITION BY SID ORDER BY ProgramName) AS Seq, Instances
    FROM
        (
        SELECT
            SID, ProgramName, COUNT(*) AS Instances
        FROM
            Login_Apps
        GROUP BY
            SID, ProgramName
        ) AS X
    ) AS Y
PIVOT
    (MIN(ProgramName) FOR Seq IN ("1", "2", "3")) AS Z
    • 1
  2. Best Answer

    使用GROUP_CONCAT()函数感觉就像作弊：

    SELECT
      apps
    , COUNT(*) AS combination_count
FROM
      ( SELECT
              GROUP_CONCAT(DISTINCT programName ORDER BY programName) AS apps
        FROM 
              login_apps
        GROUP BY 
              sid
      ) AS grp
GROUP BY
      apps ;
    • 1

相关问题