mysql - Postgres - 基于多列的 Count()

Question

我有一张 table :device

----------------------------------------------------------------
| device__some_id   |   device__os_name  |  device__os_version |
 ---------------------------------------------------------------
|       0                   android               3.0            |

|       0                   android               3.0            |

|       0                   android               4.0            |

|       1                   ios                   8.0            |

|________________________________________________________________|

我有一个查询返回 count(*)对于每个租户，os_name，os_version 组合。 输出:

--------------------------------------------------------------------------
| device__some_id   |   device__os_name  |  device__os_version | Count |
 -------------------------------------------------------------------------
        0                    android               3.0               2
        0                    android               4.0               1
        1                    ios                   8.0               1

使用的查询:

SELECT f,p,q,count (*)
FROM (SELECT  device__some_id as f, device__os_name as p, device__os_version as q FROM device) as foo
GROUP BY f,p,q

问题:

我有另一个表dim_os，它具有以下字段:

 | dim_os__some_id   |   dim_os__os_type  |  dim_os__os_version |
     ------------------------------------------------------------
            1                 android               3.0               
            2                 android               4.0              
            3                 ios                   8.0          

我想做的就是替换每次出现的 device_os_name和device_os_version在 some_id 的第一个查询的输出中来自dim_os表。

我的尝试:

SELECT f, p, count (*)
FROM (SELECT  dev.device__some_id as f, j2.dim_os__some_id as p  FROM device AS dev 
INNER JOIN dim_os as j1 ON dev.device__os_name = j1.dim_os__os_type 
INNER JOIN dim_os as j2 ON dev.device__os_version = j2.dim_os__os_version) as foo
GROUP BY f,p ;      

这没有给我正确的计数。我哪里出错了？

Answer 1

好吧，不确定为什么要进行两个内部联接，我会尝试使用一个(有两个条件)。

但我不确定我是否理解您想要的输出!

无论如何:

SELECT f, p, count (*)
FROM 
     (SELECT  
       dev.device__some_id as f, 
       j1.dim_os__some_id as p  
      FROM device AS dev 
      INNER JOIN dim_os as j1 
          ON dev.device__os_name = j1.dim_os__os_type and                
             dev.device__os_version = j1.dim_os__os_version) as foo
GROUP BY f,p ; 

参见SqlFiddle