mysql 脚本 - 数学解决方案

Question

我有一个包含 16 列的表格:Id, Product_Id, Sunday, SundayCnt, Monday, MondayCnt,...,SaturdayCnt

如您所见，有工作日列和工作日计数列

只有当工作日计数列的值大于零时，我才想对每个工作日的值取平均值

例子

 Sunday=30    SundayCnt=0 
 Monday=27    MondayCnt=2 
 Tuesday=2    TuesdayCnt=0 
 Wednesday=75 WednesdayCnt=0 
 Thursday=2   ThursdayCnt=1 
 Friday=12    FridayCnt=0 
 Saturday=15  SaturdayCnt=22

对于此示例，平均值必须仅取 (27+2+15)/3=14.66，因为那些日子的 Cnt 列大于 0

关于如何在一个简单的脚本上做这个的任何想法

Answer 1

这不会很漂亮。

  SELECT id, Product_Id, 
         IF( denominator = 0, null, numerator / denominator) as average
  FROM 
     (
      SELECT Id, Product_Id,
         (
             if( SundayCnt=0, 0, Sunday) +
             if( MondayCnt=0, 0, Monday) +
             if( TuesdayCnt=0, 0, Tuesday) +
             if( ThursdayCnt=0, 0, Wednesday) +
             if( FridayCnt=0, 0, Thursday) +
             if( SaturdayCnt=0, 0, Saturday) 
         ) as numerator ,
         (
             if( SundayCnt=0, 0, 1) +
             if( MondayCnt=0, 0, 1) +
             if( TuesdayCnt=0, 0, 1) +
             if( ThursdayCnt=0, 0, 1) +
             if( FridayCnt=0, 0, 1) +
             if( SaturdayCnt=0, 0, 1) 
         ) as denominator
      FROM YourTable
   ) as T

但是你应该考虑把你的表格改成

ID    ProductID   Sales   Counter   Day
1      1          30        0      Sunday
2      1          27        2      Monday
3      1          2         0      Tuesday
4      1          75        0      Wednesday
5      1          2         1      Thursday
6      1          12        0      Friday
7      1          15        22     Saturday

那么你的查询就很简单了

  SELECT product_id, IF(Count(*) = 0, null, SUM(Sales)/Count(*))
  From YourTable
  WHERE Counter <> 0
  GROUP BY product_id