将SQL组按多维数据集进行分组，并获取每个组合的总数

I have a question about using Group by Cube option in T-SQL. I am using SQL Server 2012.

我有一个关于在T-SQL中使用Group by Cube选项的问题。我正在使用SQL Server 2012。

So I have a table like below:

我有如下表格:

UserID  | A | B | C | D
------------------------
  1     | 1 | 1 | 1 | 1
  2     | 1 | 1 | 1 | 1
  3     | 0 | 0 | 1 | 1
  4     | 1 | 1 | 0 | 0
  5     | 1 | 1 | 1 | 1
  6     | 1 | 1 | 0 | 0
  7     | 1 | 1 | 1 | 0
  8     | 1 | 1 | 0 | 0
  9     | 1 | 1 | 0 | 0
  10    | 1 | 1 | 0 | 0
  11    | 1 | 1 | 1 | 1
  12    | 0 | 1 | 1 | 1
  13    | 1 | 1 | 1 | 0
  14    | 1 | 1 | 0 | 0
  15    | 1 | 1 | 1 | 0
  16    | 1 | 1 | 1 | 0
  17    | 1 | 1 | 1 | 1
  18    | 1 | 1 | 1 | 1
  19    | 1 | 1 | 1 | 0
  20    | 1 | 1 | 0 | 0
  21    | 1 | 1 | 1 | 1
  22    | 0 | 1 | 1 | 1

Using Group By Cube on A,B,C and D, I get 15 combinations excluding the empty set. Now, I want to find how many users fall under each combination. If no user has one such combination, I still want to to see the combination but the column with count of users can be zero. so the result output should be

使用A、B、C、D上的分组，我得到了除去空集之外的15个组合，现在我想知道每个组合下有多少用户。如果没有用户有这样的组合，我仍然希望看到组合，但是具有用户计数的列可以是零。结果输出应该是

A   B   C   D   Qty
1               0
    1           0
        1       0
            1   0
1   1           7
1       1       0
1           1   0
1   1   1       5
1   1   1   1   7
    1   1       0
    1       1   0
    1   1   1   2
        1   1   1
1   1       1   0
1       1   1   0

Any way I can achieve this? Any help would be immensely appreciated!

我有什么办法可以做到这一点吗?非常感谢您的帮助!

2 个解决方案

#1

My approach would be to create a table with all possibilities and use a LEFT JOIN against the normal GROUP BY you would do.

我的方法是创建一个包含所有可能性的表，并使用左连接来表示正常组。

If this is for an occasional run, maybe the temporary table might be okay. If not, a permanent table would be best so you are not constantly creating and dropping the temporary table.

如果只是偶尔运行，那么临时表可能没问题。如果不是，最好使用一个永久表，这样您就不会经常创建和删除临时表。

/* Create a temporary table */
CREATE TABLE TEMPSO11052016 (A int, B int, C int, D int);

/* Insert all possibilities on the temporary table */
INSERT INTO TEMPSO11052016 (A, B, C, D)
VALUES (1,1,1,1),(1,1,1,0),(1,1,0,1),(1,0,1,1),(0,1,1,1),
       (1,1,0,0),(1,0,1,0),(0,1,1,0),(1,0,0,1),(0,1,0,1),(0,0,1,1),
       (1,0,0,0),(0,1,0,0),(0,0,1,0),(0,0,0,1),(0,0,0,0);

/* Run the selection query */
SELECT T.A, T.B, T.C, T.D, ISNULL(CT, 0)         
FROM TEMPSO11052016 T
LEFT JOIN (SELECT A, B, C, D, COUNT(ID) AS CT FROM SO11052016 GROUP BY A, B, C, D) S
ON S.A=T.A AND S.B=T.B AND S.C=T.C AND S.D=T.D;

/* Drop the temporary table */
DROP TABLE TEMPSO11052016;

#2

Similar to @Edu's response, but using cross joins to create the "all combinations" table.

类似于@Edu的响应，但使用交叉连接创建“所有组合”表。

; with CTE as (select '' as Value
               union all
               select '1')
, CTE2 as (Select a.Value as A, b.Value as B, c.Value as C, d.Value as D  
                    from CTE a
                    cross join CTE b
                    cross join CTE c
                    cross join CTE d)

select a.*, count(distinct b.UserID) from CTE2 a
left join #mydata b
on a.a = b.a and a.b = b.b and a.c = b.c and a.d = b.d
group by a.A, a.B, a.C, a.D
order by a.A, a.B, a.C, a.D

#1