sql—如何将多个列的和作为一个列

2w3rbyxf 于 2021-06-26 发布在 Impala

关注(0)|答案(2)|浏览(378)

我正在努力得到的gsm，车辆段，ipmsan…等百分比，我想在每一列内的计数总和，对应于每个

WITH q2 AS ( SELECT c2.compteur, c1.police ,
       ( SUM(c1.jan2016 + c1.feb2016 + c1.mar2016) * 100 / 
         SUM(SUM(c1.jan2016 + c1.feb2016 + c1.mar2016)) OVER ()
       )  as Perc16 FROM compteur2016 c1 JOIN
     compteur c2 
     ON c1.compteur = c2.compteur GROUP BY c2.compteur, c1.police ORDER BY Perc16 desc) , q1 AS  (SELECT c2.naturebat, c1.police ,
       ( SUM(c1.feb15 + c1.jan15 + c1.mar15) * 100 / 
         SUM(SUM(c1.feb15 + c1.jan15 + c1.mar15)) OVER ()
       )  as Perc15 FROM compteur2015 c1 JOIN
     compteur c2 
     ON c1.compteur = c2.compteur GROUP BY c2.naturebat, c1.police ORDER BY Perc15 desc) select q1.naturebat ,  q1.Perc15 , q2.Perc16  FROM q1 JOIN q2  join compteur  ON q1.police = q2.police and compteur.police = q2.police where Perc15 is not null  and Perc16 is not null  group by q1.naturebat, q1.Perc15 , q2.Perc16  ORDER BY q2.Perc16 desc

这是实际结果：

naturebat   perc15  perc16
GSM 1.138445261 7.9194700502
DEPOT   0.2139114866    5.02366065
CENTRAL 0.1749475287    3.7602955112
GSM 0.4925794953    3.2469454867
CENTRAL 0.6270803657    3.1276458277
CENTRAL 1.0978046969    2.3177302785
GSM 1.0811312649    2.1836340303
GSM 1.3243077123    1.7440525492
GSM 1.3697459021    1.6935745684
CENTRAL 0.6196699515    1.6862378058
GSM 1.0996573005    1.5648312198
CENTRAL 0.1434532683    1.445898706
GSM 1.5603738673    1.4334178177
COMMERCIALE 1.7122966215    1.4151447349
GSM 0.1990906582    1.2812710765
IPMSAN  0.5979734938    1.2495047058
GSM 0.4243499591    1.1800590088
CENTRAL 5.2499178247    1.1620225875
GSM 1.0780435924    1.1589626182
GSM 0.6086815423    1.156915816
GSM 1.1052151111    1.1563833915
GSM 2.012420867 1.1558924149

我想要的结果是：

naturebat   perc15  perc16
GSM             percentage of all GSM in 2015  percentage of all GSM in 2016
..
..
..

sql impala cloudera hql

来源：https://stackoverflow.com/questions/56421243/how-to-get-sum-of-many-columns-as-one

2条答案

按热度按时间

jw5wzhpr1#

假设“实际结果”是一个名为result的表。
你能做到的

SELECT naturebat , SUM(perc15), SUM(perc16)
  FROM RESULT
 GROUP BY naturebat;

赞(0）回复(0）举报 2021-06-26

gudnpqoy2#

使用以下查询在cte中选择：

SELECT q1.naturebat,
           SUM(q1.Perc15),
           SUM(q2.Perc16)
    FROM q1
      JOIN q2
      JOIN compteur
        ON q1.police = q2.police
       AND compteur.police = q2.police
    WHERE Perc15 IS NOT NULL
    AND   Perc16 IS NOT NULL
    GROUP BY q1.naturebat,
    ORDER BY q2.Perc16 DESC

赞(0）回复(0）举报 2021-06-26

我来回答

sql—如何将多个列的和作为一个列

2条答案

相关问题

热门标签

最新问答