SUM的SUM問題

當我執行查詢時，我總是遇到同樣的問題：

year_created|month_created|loc_id_1|loc_id_2|loc_id_3|Total
------------------------------------------------------------
 2016       |    7        |  0     | 0.40   |  0      0.40
 2016       |    7        |  0.50  | 0      |  0      0.50
 2016       |    7        |  0     | 0      |  0.30   0.30

我如何組合成一行：

year_created|month_created|loc_id_1|loc_id_2|loc_id_3|
-------------------------------------------------------
 2016       |    7        |  0.50  | 0.40   |  0.30

這是我的查詢：

SELECT
  MONTH(created_at) as month_created,
  YEAR(created_at) as year_created,
  1 - SUM(price)/SUM(order_total) as Total,
  CASE loc_id WHEN 1 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_1',
  CASE loc_id WHEN 2 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_2',
  CASE loc_id WHEN 3 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_3'
  FROM data_price
  WHERE YEAR(created_at) = YEAR(NOW())
 GROUP BY year_created, month_created
 ORDER BY year_created asc, month_created desc

問題是語句中的SUM(order)可以為 0 。CASE創建一個子查詢來傳遞price和order總計loc_id，然後如果總和不為 0，則使用它來進行計算order：

SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order'

然後 SQL 看起來像（檢查這是否可能vertica）：

SELECT year_created, month_created, 1 - total
      1 - CASE loc_id_1_order WHEN 0 THEN 0 ELSE loc_id_1_price/loc_id_1_order END as loc_id_1,
      1 - CASE loc_id_2_order WHEN 0 THEN 0 ELSE loc_id_2_price/loc_id_2_order END as loc_id_2,
      1 - CASE loc_id_3_order WHEN 0 THEN 0 ELSE loc_id_3_price/loc_id_3_order END as loc_id_3
FROM
 (SELECT
    MONTH(created_at) as month_created,
    YEAR(created_at) as year_created,
    SUM(price)/SUM(order_total) as Total,
    SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
    SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order',
    SUM(CASE loc_id WHEN 2 THEN price ELSE 0 END) AS 'loc_id_2_price',
    SUM(CASE loc_id WHEN 2 THEN order ELSE 0 END) AS 'loc_id_2_order',
    SUM(CASE loc_id WHEN 3 THEN price ELSE 0 END) AS 'loc_id_3_price',
    SUM(CASE loc_id WHEN 3 THEN order ELSE 0 END) AS 'loc_id_3_order'
  FROM data_price
  WHERE YEAR(created_at) = YEAR(NOW())
  GROUP BY year_created, month_created)
ORDER BY year_created asc, month_created desc

嘗試這個：

select distinct year_created, month_created,
 max(loc_id_1) over (partition by year_created, month_created) as loc_id_1,
 max(loc_id_2) over (partition by year_created, month_created) as loc_id_2,
 max(loc_id_3) over (partition by year_created, month_created) as loc_id_3,
 sum(loc_id_1) over (partition by year_created, month_created) + sum(loc_id_2) over (partition by year_created, month_created) + sum(loc_id_3) over (partition by year_created, month_created) as total
from (
 -- your query
) z;

或者一般來說，閱讀分析查詢。

引用自：https://dba.stackexchange.com/questions/143574