sql - 当它有/没有特定值时，如何过滤组并进行聚合？

Question

示例数据

id1 | id2 | id3 | col1 | col2
-----------------------------
113 | 10  | 100 |   8  |  10
113 | 10  | 100 |   8  |  20
113 | 10  | 100 |   6  |  30
114 | 10  | 200 |   5  |  40
114 | 10  | 200 |   6  |  50
115 | 10  | 250 |   4  |  60
116 | 10  | 300 |   2  |  70

期望的结果:

id1 | id2 |  output
------------------
113 | 10  |   70
114 | 10  |   200
115 | 10  |   250
116 | 10  |   300

计算过程是这样的。

• 对于 id1 的每个组合, id2 - 如果存在值 8在 col1 的子组中, 那么我应该只选择具有 8 的特定组, 然后做 output = id3 - sum(col2) .进一步解释，对于 id1=113和 id2=10存在两个子群 8和 6在 col1 .我应该只子组8并进行聚合。如果8子组中不存在，然后查看下面的要点。

• 如果这样 8子组中不存在，我应该做output = id3 .

• 注意 - id3每对 id1 都是独一无二的和 id2 .

我不知道如何使用 group by为此具有这样的过滤条件。我使用的引擎是 SPARK-SQL。我更喜欢使用 SQL 而不是供应商特定的命令。

我在这个 question at dba stackexchange 中问过类似的问题

Answer 1

检查这个:

模式(MySQL v5.7)

CREATE TABLE table1 (
  `id1` INTEGER,
  `id2` INTEGER,
  `id3` INTEGER,
  `col1` INTEGER,
  `col2` INTEGER
);

INSERT INTO table1
  (`id1`, `id2`, `id3`, `col1`, `col2`)
VALUES
  ('113', '10', '100', '8', '10'),
  ('113', '10', '100', '8', '20'),
  ('113', '10', '100', '6', '30'),
  ('114', '10', '200', '5', '40'),
  ('114', '10', '200', '6', '50'),
  ('115', '10', '250', '4', '60'),
  ('116', '10', '300', '2', '70');

---

查询#1

select 
  id1, 
  id2, 
  sum(distinct id3) - sum(case when col1 = 8 then col2 else 0 end) output
from table1
group by id1, id2;

输出

| id1 | id2 | output |
| --- | --- | ------ |
| 113 | 10  | 70     |
| 114 | 10  | 200    |
| 115 | 10  | 250    |
| 116 | 10  | 300    |

---

View on DB Fiddle