如何通过聚合正确地使用条件组
我想要能够统计来自每一个大陆的苹果的总种类(只有有机的)，按国家分类；如果它们是混合的，包括总数。
例如，食品B1是来自美国的有机金苹果。因此，应该有一个"1“golden_bag和"1”的有机计数。现在，A1也是来自阿根廷的有机产品--不过，它有奶奶和红色美味的苹果--因此它被计算为"1“mixed_bag和"1”对于granny_bag和red_bag也是"1“。
最后，E1和F1都是来自老挝的富士苹果，但是一个是有机的，另一个不是，所以总数是2 fuji_bag，organic_fd的总数应该是1。
Table X:
food_item | food_area | food_loc | food_exp
A1 lxgs argentina 1/1/20
B1 iyan usa 5/31/21
C1 lxgs peru 4/1/20
D1 wa8e norway 10/1/19
E1 894a laos 5/1/19
F1 894a laos 9/17/19
Table Y:
food_item | organic
A1 Y
B1 Y
C1 N
D1 N
E1 Y
F1 N
Table Z:
food_item | food_type
A1 189
A1 190
B1 191
C1 189
D1 192
E1 193
F1 193
SELECT continent, country,
SUM(organic) AS organic_fd, SUM(Granny) AS granny_bag,
SUM(Red_delc) AS red_bag, SUM(Golden) AS golden_bag,
SUM(Gala) AS gala_bag, SUM(Fuji) AS fuji_bag,
SUM(CASE WHEN Granny + Red_delc + Golden + Gala + Fuji > 1 THEN 1 ELSE 0 END) AS mixed_bag
FROM (SELECT (CASE SUBSTR (x.food_area, 4, 1)
WHEN 's' THEN 'SA' WHEN 'n' THEN 'NA'
WHEN 'e' THEN 'EU' WHEN 'a' THEN 'AS' ELSE NULL END) continent,
x.food_loc country, COUNT(y.organic) AS Organic
COUNT(CASE WHEN z.food_type = '189' THEN 1 END) AS Granny,
COUNT(CASE WHEN z.food_type = '190' THEN 1 END) AS Red_delc,
COUNT(CASE WHEN z.food_type = '191' THEN 1 END) AS Golden,
COUNT(CASE WHEN z.food_type = '192' THEN 1 END) AS Gala,
COUNT(CASE WHEN z.food_type = '193' THEN 1 END) AS Fuji
FROM x LEFT JOIN z ON x.food_item = z.food_item
LEFT JOIN y on x.food_item = y.food_item and y.organic = 'Y'
WHERE x.exp_date > sysdate
GROUP BY SUBSTR (x.food_area, 4, 1), x.food_loc, y.organic) h
GROUP BY h.continent, h.country, h.organic我没有得到正确的产出，因为，例如，老挝将两次说明有机计数和非有机计数。因此，它将显示1 organic_fd、0 organic_fd和1 fuji_bag，而另一行将是另一条1 fuji_bag。我想要总数。(此外，如果我添加了更多的食物，我的mixed_bag显示的大部分是"1“计数的每条记录/行)。
以下是所需的输出：
| continent | country |organic_fd | granny_bag| red_bag| golden_bag| gala_bag|fuji_bag | mixed_bag
| SA | argentina | 1 | 1 | 1 | 0 | 0 | 0 | 1
| SA | peru | 0 | 1 | 0 | 0 | 0 | 0 | 0
| NA | usa | 1 | 0 | 0 | 1 | 0 | 0 | 0
| EU | norway | 0 | 0 | 0 | 0 | 1 | 0 | 0
| AS | laos | 1 | 0 | 0 | 0 | 0 | 2 | 0所以，假设我想添加另一种食物，挪威的G1，它有三种有机苹果：fuji, red, granny.然后，挪威将对以下列进行1计数：mixed_bag、organic_fd、fuji_bag、red_bag、granny_bag (除了以前的1 gala_bag计数)。如果您添加与H1完全相同的G1，那么现在它将有一个用于以下方面的2总数：mixed_bag、organic_fd、fuji_bag、red_bag、granny_bag
回答 1
Stack Overflow用户
发布于 2019-02-07 12:19:08
查询：
WITH
t AS (
SELECT
CASE SUBSTR(X.food_area, LENGTH(X.food_area), 1)
WHEN 's' THEN 'SA'
WHEN 'n' THEN 'NA'
WHEN 'e' THEN 'EU'
WHEN 'a' THEN 'AS'
ELSE NULL
END AS continent,
x.food_loc AS country,
COUNT(DISTINCT CASE Y.organic WHEN 'Y' THEN X.food_item END) OVER (
PARTITION BY x.food_loc
) AS organic_fd,
CASE
WHEN MIN(Z.food_type) OVER (
PARTITION BY x.food_loc, X.food_item
) = Z.food_type AND
MAX(Z.food_type) OVER (
PARTITION BY x.food_loc, X.food_item
) > Z.food_type THEN 1 END AS mixed,
Z.food_type
FROM X
JOIN Y ON X.food_item = Y.food_item
JOIN Z ON Y.food_item = Z.food_item
)
SELECT
continent, country, organic_fd,
COUNT(CASE WHEN food_type = '189' THEN 1 END) AS Granny,
COUNT(CASE WHEN food_type = '190' THEN 1 END) AS Red_delc,
COUNT(CASE WHEN food_type = '191' THEN 1 END) AS Golden,
COUNT(CASE WHEN food_type = '192' THEN 1 END) AS Gala,
COUNT(CASE WHEN food_type = '193' THEN 1 END) AS Fuji,
COUNT(mixed) AS mixed_bag
FROM t
GROUP BY continent, country, organic_fd您可以在这里尝试这个查询：https://rextester.com/TSSH87409。
https://stackoverflow.com/questions/54565482
复制相似问题
腾讯云开发者
