SQL 对具有类别总计的不同行的非不同权重求和

Question

我想在一个人可以为多个单元格做出贡献的情况下对一些加权调查数据进行交叉制表。挑战在于确保在不重复计算的情况下完成小计和总计。

我可以使用类似于 How do I SUM DISTINCT Rows? 中的解决方案的方法获取单个单元格值，但不能获取总计值或 Sum Distinct By Other Column .我正在尝试使用 Oracle CUBE 语句以一种很好的方式获取总数。

这是一个小例子。假设我们根据他们拥有的宠物和他们的爱好来统计人数。问题是一个人可能有不止一只宠物，或者不止一种爱好。我们需要转这组单位记录:

person_id, weight
1, 10
2, 10
3, 12

person_id, pet
1, "cat"
1, "dog"
2, "cat"
3, "cat"

person_id, hobby
1, "chess"
2, "chess"
2, "skydiving"
3, "skydiving"

进入这对表:

    Unweighted count

      | chess | skydiving | total
------+-------+-----------+--------
cat   |  2    |  2        | 3
------+-------+-----------+--------
dog   |  1    |  0        | 1
------+-------+-----------+--------
total |  2    |  2        | 3      


Weighted count

      | chess | skydiving | total
------+-------+-----------+--------
cat   |  20   |  22       | 32
------+-------+-----------+--------
dog   |  10   |  0        | 10
------+-------+-----------+--------
total |  20   |  22       | 32     

请注意，“猫”行的未加权总数是 3，而不是 2+2=4，因为第 2 个人是在两个不同的地方计算的。只有三个不同的人对这一行做出贡献。其他总数也是如此。

请注意，“猫，国际象棋”的加权总数为 20=10+10，因为两个不同的人分别为该单元格贡献权重 10。

请注意，加权表的总计为 32。这是因为第 1 个人和第 2 个人各贡献了 10 个，第 3 个人贡献了 12 个。总和不仅仅是所有单个单元格的总和!

对于未加权的计数，我可以通过以下方式获得所有细胞计数和总数:

CREATE TABLE weights(person_id INTEGER, weight INTEGER);
INSERT INTO weights(person_id,weight) VALUES (1,10);
INSERT INTO weights(person_id,weight) VALUES (2,10);
INSERT INTO weights(person_id,weight) VALUES (3,12);

CREATE TABLE pets(person_id INTEGER, pet VARCHAR(3));
INSERT INTO pets(person_id,pet) VALUES (1,'cat');
INSERT INTO pets(person_id,pet) VALUES (1,'dog');
INSERT INTO pets(person_id,pet) VALUES (2,'cat');
INSERT INTO pets(person_id,pet) VALUES (3,'cat');

CREATE TABLE hobbies(person_id INTEGER, hobby VARCHAR(9));
INSERT INTO hobbies(person_id,hobby) VALUES (1,'chess');
INSERT INTO hobbies(person_id,hobby) VALUES (2,'chess');
INSERT INTO hobbies(person_id,hobby) VALUES (2,'skydiving');
INSERT INTO hobbies(person_id,hobby) VALUES (3,'skydiving');

SELECT pet, hobby, COUNT(DISTINCT weights.person_id)
FROM weights JOIN pets on weights.person_id=pets.person_ID
JOIN hobbies on weights.person_id=hobbies.person_id
GROUP BY CUBE(pet, hobby);

COUNT(DISTINCT ...) 和 CUBE 的组合给出了正确的总数。

对于加权计数，如果我尝试相同的想法:

SELECT pet, hobby, SUM(DISTINCT weight)
FROM weights JOIN pets on weights.person_id=pets.person_ID
JOIN hobbies on weights.person_id=hobbies.person_id
GROUP BY CUBE(pet, hobby);

“猫，国际象棋”单元格变为 10 而不是 20，因为人 1 和 2 的体重相同。删除“distinct”关键字意味着单个单元格计数是正确的，但总数是错误的(它产生的总计是 52，而本应是 32，因为第 1 个人和第 2 个人在总数中被重复计算)。

有什么建议吗？

Answer 1

您可以使用嵌套查询来执行此操作，其中内部查询指定从行到表格单元格的映射(即哪些记录在每个表格单元格的范围内)，外部查询指定汇总函数应用:

SELECT pet, hobby, COUNT(1), SUM(weight) FROM
(SELECT pet, hobby, weights.person_ID, weight
FROM weights JOIN pets on weights.person_id=pets.person_ID
JOIN hobbies on weights.person_id=hobbies.person_id
GROUP BY CUBE(pet, hobby), weights.person_ID, weight)
GROUP BY pet, hobby;

Results

旁白:您也可以不使用 CUBE 运算符编写内部查询，但这样会更困惑:

WITH
    pet_cube_map as (SELECT DISTINCT pet, NULL as pet_cubed FROM pets UNION ALL SELECT DISTINCT pet, pet as pet_cubed FROM pets),
    hobby_cube_map as (SELECT DISTINCT hobby, NULL as hobby_cubed FROM hobbies UNION ALL SELECT DISTINCT hobby, hobby as hobby_cubed FROM hobbies)
SELECT DISTINCT pet_cubed as pet, hobby_cubed as hobby, weights.person_ID, weight
FROM weights
    JOIN pets on weights.person_ID=pets.person_ID
    JOIN pet_cube_map on pets.pet=pet_cube_map.pet
    JOIN hobbies on weights.person_ID=hobbies.person_ID
    JOIN hobby_cube_map on hobbies.hobby=hobby_cube_map.hobby
;