mysql - 在计算列内项目的出现次数后，在 MySql 表列上应用公式。

Question

我有一张看起来像这样的表:

brand satisfaction
    A   offered
    N   good
    L   bad
    A   good
    N   good
    L   good
    A   bad
    N   bad
    L   offered
    A   offered
    N   bad
    L   bad
    N   bad
    L   bad

通过申请:

SELECT  brand,satisfaction, COUNT(satisfaction) as Number
FROM
table
WHERE satisfaction LIKE 'good' OR satisfaction LIKE 'bad' OR satisfaction LIKE 'offered'
GROUP BY brand, satisfaction

给我一​​张新表 (table2)，它看起来像:

brand satisfaction Number
A   offered        2
A   good           1
A   bad            1
N   offered        0
N   good           2
N   bad            3
L   offered        1
L   good           1
L   bad            3

我想应用按品牌分组的公式 ((good-bad)/good+bad+offered) 来获得如下结果表 (table3):

brand afterformula
A     0
N    -0.2
L    -0.5

到目前为止我尝试了什么？我已经能够解决问题，但只能先将 table2 保存在数据库中并应用以下查询:

SELECT brand, (MAX(CASE WHEN satisfaction = 'good' THEN Number  ELSE 0 end )-MAX(CASE WHEN satisfaction = 'bad' THEN Number  ELSE 0 end ))
/ (MAX(CASE WHEN satisfaction = 'bad' THEN Number  ELSE 0 end )+  MAX(CASE WHEN satisfaction = 'good' THEN Number  ELSE 0 end )+ MAX(CASE WHEN satisfaction = 'offered' THEN  Number ELSE 0 end )) AS
'afterformula' 
FROM  table2  
group by brand

问题是我需要将公式应用于第一个表本身以完成我需要它的自动化作业，而原始表很大，大约有 500 万行。任何人都可以帮助我或给我提示，我怎样才能达到结果。感谢您的时间。

Answer 1

使用条件聚合计算单行中的每个值:

SELECT brand,
       SUM(satisfaction = 'good') as good,
       SUM(satisfaction = 'bad') as  bad,
       SUM(satisfaction = 'offered') as offered
FROM table
WHERE satisfaction LIKE 'good' OR satisfaction LIKE 'bad' OR satisfaction LIKE 'offered'
GROUP BY brand;

然后你可以使用子查询(或上面的复杂公式):

SELECT b.*,
       (good - bad) / (good + bad + offered) as formula
FROM (SELECT brand,
             SUM(satisfaction = 'good') as good,
             SUM(satisfaction = 'bad') as  bad,
             SUM(satisfaction = 'offered') as offered
      FROM table
      WHERE satisfaction LIKE 'good' OR satisfaction LIKE 'bad' OR satisfaction LIKE 'offered'
      GROUP BY brand
     ) b;