mysql - 如何在mysql中选择每组的百分比值

Question

我正在尝试为 mySql 中的每个组选择百分比值。

我试着在内存中做，但是要维护的代码很多。如果我通过 SQL 来做，那就太好了。

我正在选择以下数据:

Gender,Category,Month,Year,NumberOfCustomers
'female','Feature Phones','1','2019','1000'
'male','Feature Phones','1','2019','10000'
'female','Smart Phones','1','2019','30000'
'male','Smart Phones','1','2019','200000'
'female','Feature Phones','2','2019','20000'
'male','Feature Phones','2','2019','15000'
'female','Smart Phones','2','2019','30000'
'male','Smart Phones','2','2019','150000'

使用查询:

SELECT gender, category, month, year, 
SUM(number_of_customers) AS numberOfCustomers
FROM customer_table 
WHERE brand_id IN (100) 
AND category in (1,2)        
AND month in (1,2) and year in (2019)
GROUP BY month, year, category, gender;

我想要的是每个月的客户百分比。例如:

Gender,Category,Month,Year,NumberOfCustomers,PercentageCustomersPerMonth
'female','Feature Phones','1','2019',5000,2.04
'male','Feature Phones','1','2019',10000,4.08
'female','Smart Phones','1','2019',30000,12.24
'male','Smart Phones','1','2019',200000,81.63
'female','Feature Phones','2','2019',20000,9.30
'male','Feature Phones','2','2019',15000,6.97
'female','Smart Phones','2','2019',30000,13.95
'male','Smart Phones','2','2019',150000,69.76

有没有办法轻松做到这一点？非常感谢。

Answer 1

您可以使用窗口函数:

SELECT gender, category, month, year, 
       SUM(number_of_customers) AS numberOfCustomers,
       (SUM(number_of_customers) /
        SUM(SUM(number_of_customers)) OVER (PARTITION BY year, month)
       ) as month_ratio
FROM customer_table 
WHERE brand_id IN (100) AND
      category in (1,2) AND   
      month in (1, 2) AND
      year in (2019)
GROUP BY month, year, category, gender;