代码之家 › 专栏 › 技术社区 › BFF

如何正确使用条件分组聚合

aggregate-functions group-by oracle sql

BFF · 技术社区 · 7 年前

我想能够计算出每一大洲苹果的总种类(仅有机),按国家分类;如果它们是混合的,包括总数。

最后,E1和F1都是来自老挝的富士苹果,但一个是有机的,另一个不是;所以总计数是2个富士袋,有机食品的总计数应该是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个富士包 . 我想要总数。(此外,如果我添加更多的食物,我的混合_袋中的每一条记录/每一行的计数大多为“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,它有3种有机苹果: fuji, red, granny 1 对于以下列: mixed_bag , organic_fd , fuji_bag , red_bag , granny_bag (除了先前的 1 gala_bag ).如果您添加H1,它与G1完全相同,那么它现在的总计数为 2 有关下列事项: 混合袋 , 有机食品 , 富士包 , 红包 , 奶奶包

2 回复 | 直到 7 年前

Andrei Odegov 7 年前

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 .

Ponder Stibbons 7 年前

你们之间有一对多的关系 x 和 z ,和join可能会为中的每一行生成许多行 十、 ,就像A1的情况一样。因此,您必须对中的行进行编号 十、 首先,这是我的子查询 t1 除了映射值之外,其他都可以。然后分组服用 max() 对于每个计数列(granny、organic等),如子查询中 t2 . 最后求和值。

^{dbfiddle demo}

with
  t1 as (
    select rn, food_item, food_area, food_loc country, food_exp, food_type,
           decode(substr(food_area, 4, 1), 's', 'SA', 'n', 'NA', 'e', 'EU', 'a', 'AS') continent,
           case organic when 'Y' then 1 else 0 end org,
           case when food_type = '189' then 1 else 0 end gra,
           case when food_type = '190' then 1 else 0 end red,
           case when food_type = '191' then 1 else 0 end gol,
           case when food_type = '192' then 1 else 0 end gal,
           case when food_type = '193' then 1 else 0 end fuj 
      from (select rownum rn, x.* from x) x join y using (food_item) join z using (food_item)
      where food_exp > sysdate),
  t2 as (
    select rn, country, continent, max(org) org, max(gra) gra, 
           max(red) red, max(gol) gol, max(gal) gal, max(fuj) fuj,
           case when max(gra) + max(red) + max(gol) + max(gal) + max(fuj) > 1 
                then 1 else 0 
            end mix
       from t1 group by rn, country, continent)
select continent, country, sum(org) organic_fd, sum(gra) granny, sum(red) red_delc, 
       sum(gol) golden_bag, sum(gal) gala_bag, sum(fuj) fuji_bag, sum(mix) mixed_bag 
  from t2 
  group by continent, country

上面的查询给出了预期的输出,请测试它并根据需要进行调整。我注意到你使用左连接。如果有可能,对于中的某些行 X 中没有数据 Y 或 Z 你可能需要补充一点 nvl() 这是计算中的错误。也许您还应该将映射的、硬编码的值放入表中。硬编码并不是好的做法。希望这有帮助:)