mysql - SQL 查询 : Combine multiple rows [using a CASE WHEN x IN (1, 2,3...)] 到一行

Question

我想将具有相同 media_type 值的所有行合并为一行。

这是我的查询(稍作修改以保持私有(private)信息的私密性):

SELECT 
CASE
    WHEN media_type IN ('1','3') THEN 'A'
    WHEN media_type IN ('2','4') THEN 'B'
    WHEN media_type IN ('5','21','22') THEN 'C'
    WHEN media_type IN ('7','8','23') THEN 'D'
    WHEN media_type IN ('9') THEN 'E'
END AS media_type,
SUM(imp) as imps,
SUM(seller_revenue) as seller_revenue,
SUM(seller_revenue) / SUM(imp)  as CPM

FROM
    table

WHERE 
    dd = '2016-01-19'

GROUP BY
    media_type

ORDER BY
    seller_revenue desc

LIMIT 100;

当前输出(出于隐私原因更改数据):

media_type  imps    seller_revenue  cpm
A   2681524581  4636356  1.729
B   18152099106 22345234 1.231
A   113355218.6 635356   5.605
A   83881452.47 235623   2.809
A   27994649.23 235435   8.41
C   4924414.093 63456    12.886
D   522212.1487 3456     6.618
C   569451.3099 3456     6.069
B   821059.4315 2542     3.096
B   220102.1505 409.39   1.86
D   103231.5978 345      3.342
A   88757.39645 345      3.887
E   300261.0966 345      1.149
A   213622.291  345      1.615
B   118701.5504 245      2.064
D   211678.8321 145      0.685
B   57798.16514 63       1.09
E   47619.04762 56       1.176
C   44191.91919 35       0.792
B   140186.9159 15       0.107
A   4288.777698 6        1.399 

所需的输出(排序并不重要):

media_type  seller_revenue  imps    cpm_calc
B    22,348,508      18,153,456,955      1.23 
A    5,743,466       2,907,062,569       1.98 
C    66,947          5,538,057           12.09 
D    3,946           837,123             4.71 
E    401             347,880             1.15 
Grand Total 28163268.39 21067242584 1.33682746

谢谢!

很惊讶我在一个小时的搜索中没有发现这个问题，我想知道这通常不是一个问题吗？

Answer 1

使用这样的 CTE:

WITH media_type as (
    SELECT old,new from (values
        ('1', 'A'),('3', 'A'),
        ('2', 'B'),('4', 'B'),
        ('5', 'C'),('21','C'),('22','C'),
        ('7', 'D'),('8', 'D'),('23','D'),
        ('9', 'E')
    )media_type(old,new)
)
SELECT 
    media_type.new AS media_type,
    SUM(imp) as imps,
    SUM(seller_revenue) as seller_revenue,
    SUM(seller_revenue) / SUM(imp)  as CPM

FROM
    table
JOIN media_type on media_type.old = table.media_type
WHERE 
    dd = '2016-01-19'

GROUP BY
    table.media_type

ORDER BY
    seller_revenue desc

LIMIT 100