使用SQL Server聚合布尔类型的表

Question

我有一个表，所有值都是0或1。

a	b	c
1	0	0
1	1	0
0	1	0
1	1	1

而我想要这个表：

	a	b	c
a	3	2	1
b	2	3	1
c	1	1	1

最后这个表回答了关于{raw}和{col}都为1的行数的问题。例如，在第一个表中，有2行满足a = b = 1，所以cell(a,b) = 2。

我有一个查询语句对于大型表格不太合适。有没有可能让它更简单？

SELECT
    'a' AS ' ',  
    SUM(a) AS a, 
    (SELECT SUM(b) FROM tab WHERE a = 1) AS b, 
    (SELECT SUM(c) FROM tab WHERE a = 1) AS c 
FROM 
    tab

UNION

SELECT
    'b', 
    (SELECT SUM(a) FROM tab WHERE b = 1),
    SUM(b), 
    (SELECT SUM(c) FROM tab WHERE b = 1) 
FROM
    tab

UNION

SELECT
    'c', 
    (SELECT SUM(a) FROM tab WHERE c = 1), 
    (SELECT SUM(b) FROM tab WHERE c = 1),
    SUM(c) 
FROM
    tab

- Polux2

---

你说你有一个“布尔表”，但是在SQL Server中没有布尔类型。通常使用位数据类型来表示布尔值，但是你不能对位数据类型使用SUM函数。那么你实际使用的是什么数据类型？ - Sean Lange

Answer 1

您可以尝试使用条件聚合而不是子查询，这样可能会更加简洁： ```html

您可以尝试使用条件聚合而不是子查询，这样可能会更加简洁：

```

select 'a' [ ],
 Sum (case when a = a then a else 0 end) a,
 Sum (case when a = b then b else 0 end) b,
 Sum (case when a = c then c else 0 end) c
from t 
union all
select 'b' [ ],
 Sum (case when b = a then a else 0 end) a,
 Sum (case when b = b then b else 0 end) b,
 Sum (case when b = c then c else 0 end) c
from t 
union all
select 'c' [ ],
 Sum (case when c = a then a else 0 end) a,
 Sum (case when c = b then b else 0 end) b,
 Sum (case when c = c then c else 0 end) c
from t;

DB<>Fiddle

- Stu

---

比我的解决方案要短得多+1。 - The Impaler

Answer 2

您只能在表中聚合一次：

WITH 
  v AS (SELECT v FROM (VALUES ('a'), ('b'), ('c')) v(v)),
  s AS (
    SELECT SUM(a) a, SUM(b) b, SUM(c) c,
           SUM(a * b) ab, SUM(b * c) bc, SUM(c * a) ca
    FROM tab
  )  
SELECT v.v,
       CASE v.v WHEN 'a' THEN s.a WHEN 'b' THEN s.ab WHEN 'c' THEN s.ca END a,
       CASE v.v WHEN 'a' THEN s.ab WHEN 'b' THEN s.b WHEN 'c' THEN s.bc END b,
       CASE v.v WHEN 'a' THEN s.ca WHEN 'b' THEN s.bc WHEN 'c' THEN s.c END c
FROM v CROSS JOIN s
ORDER BY v.v;

请查看演示。

- forpas

Answer 3

这是一种易于扩展更多列的方法。只需在注释中有箭头的位置添加它们：

with t1 as (select *, row_number() over (order by a) as rn from t),
u(col, rn, val) as (
    select 'a', rn, a from t1 union all
    select 'b', rn, b from t1 union all
    select 'c', rn, c from t1 union all
    select 'd', rn, d from t1                           /* <-- */
), data as (
    select d1.col as Rx, d2.col as Cx,
        case when d1.val = 1 and d2.val = 1 then 1 else 0 end as v
    from u as d1 inner join u as d2 on d1.rn = d2.rn
)
select Rx as [ ], [a], [b], [c], [d]                    /* <-- */
from data as src
pivot (sum(v) for Cx in ([a], [b], [c], [d])) as pvt;   /* <-- */

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5f8022109a8885d661e81f4a2792b8c4

- shawnt00

---

我选择这个解决方案，因为它是最具可扩展性的，而且我将需要更多的列（用于相关研究），但Stu和forpas的解决方案也非常优雅。 - Polux2

@Polux2 谢谢。顺便说一下，我把这个清理了一下。 - shawnt00

Answer 4

我相信一定有更简单的解决方案，但我看不到。这是我想出来的：

with
d (s, d, x, y) as (
  select 'a', 'b', a, b from t
  union all select 'a', 'c', a, c from t
  union all select 'b', 'c', b, c from t
  union all select 'a', 'a', a, a from t
  union all select 'b', 'b', c, c from t
  union all select 'c', 'c', c, c from t
),
r (s, d, cnt) as (
  select s, d, count(*)
  from d
  where x = 1 and y = 1
  group by s, d
)
select s, max(a) as a, max(b) as b, max(c) as c
from (
  select s,
    sum(case when d = 'a' then cnt else 0 end) as a,
    sum(case when d = 'b' then cnt else 0 end) as b,
    sum(case when d = 'c' then cnt else 0 end) as c
  from r
  group by s
  union all 
  select d,
    sum(case when s = 'a' then cnt else 0 end) as a,
    sum(case when s = 'b' then cnt else 0 end) as b,
    sum(case when s = 'c' then cnt else 0 end) as c
  from r
  group by d
) x
group by s

结果：

 s  a  b  c 
 -- -- -- - 
 a  3  2  1 
 b  2  1  1 
 c  1  1  1 

请查看运行示例db<>fiddle。

- The Impaler

Answer 5

你可以使用 GROUPING SETS 来实现这个功能。

  Cols = CASE WHEN GROUPING(a) = 0 THEN 'a'
              WHEN GROUPING(b) = 0 THEN 'b'
              ELSE 'c' END,
  a = COUNT(NULLIF(a, 0)),
  b = COUNT(NULLIF(b, 0)),
  c = COUNT(NULLIF(c, 0))
FROM tab
GROUP BY GROUPING SETS (
  (a),
  (b),
  (c)
)
HAVING ISNULL(ISNULL(a, b), c) = 1
ORDER BY Cols;

db<>fiddle

假设您有一个bit列。否则，您应该执行SUM(a)等操作。

---

• GROUPING SETS 会为每组分组列输出一个单独的 GROUP BY，有点像 UNION ALL。
• GROUPING 函数告诉您某个列是否已被聚合。
• COUNT(NULLIF(someValue, 0)) 只计算非 0 的值。
• HAVING 过滤掉分组列为 0 的情况。

- Charlieface

---

这并不一定是无效的结果。交叉表中没有缺失信息，因为它实际上是一个镜像（沿对角线始终对称）。 - Charlieface

没有表格行或所有行均为0 —> 没有结果..某些内容已丢失 - lptr