mysql - 如何使用SQL计算累计值

Question

我哪里错了？ cum_duration 应该产生 activity_duration 的累积值，但它返回与 activity_duration 相同的值。如何获取累计值？

 SELECT
      fact_daily_summary.day_date,
      fact_daily_summary.day_number,
      fact_activity.activity_duration/3600 AS activity_duration,
      (SELECT
        SUM(fc.activity_duration)/3600
      FROM fact_activity fc
      WHERE fc.fact_activity_uid = fact_activity.fact_activity_uid
      AND fc.fact_activity_uid <= fact_activity.fact_activity_uid
      GROUP BY fact_activity.fact_activity_uid
      ORDER BY fact_activity.fact_activity_uid)
      AS cum_duration,
      depth_md_msl
    FROM 
         fact_daily_summary,
         fact_activity
    WHERE fact_activity.daily_uid = fact_daily_summary.daily_uid
    AND fact_daily_summary.operation_uid = fact_operation_summary.operation_uid

结果

day_date             day_number  activity_duration  cum_duration  depth_md_msl  
-------------------  ----------  -----------------  ------------  --------------
2012-06-17 00:00:00  2                    0.500000      0.500000        -1025.15
2012-06-17 00:00:00  2                    1.500000      1.500000        -1025.15
2012-06-17 00:00:00  2                    0.500000      0.500000        -1025.15
2012-06-17 00:00:00  2                    1.500000      1.500000        -1017.15
2012-06-17 00:00:00  2                   19.000000     19.000000         -998.65
2012-06-17 00:00:00  2                    0.499722      0.499722         -998.65
2012-06-17 00:00:00  2                    0.249722      0.249722         -998.65
2012-06-17 00:00:00  2                    0.249722      0.249722         -998.55
2012-06-17 00:00:00  2                    0.500000      0.500000        -1025.15
2012-06-17 00:00:00  2                    1.500000      1.500000        -1025.15
2012-06-17 00:00:00  2                    0.500000      0.500000        -1025.15
2012-06-17 00:00:00  2                    1.500000      1.500000        -1017.15
2012-06-17 00:00:00  2                   19.000000     19.000000         -998.65
2012-06-17 00:00:00  2                    0.499722      0.499722         -998.65
2012-06-17 00:00:00  2                    0.249722      0.249722         -998.65
2012-06-17 00:00:00  2                    0.249722      0.249722         -998.55
2012-06-17 00:00:00  2                    0.500000      0.500000        -1025.15
2012-06-17 00:00:00  2                    1.500000      1.500000        -1025.15
2012-06-17 00:00:00  2                    0.500000      0.500000        -1025.15

Answer 1

我认为您只需要在子查询中使用日期。好吧，group by 也是不必要的:

SELECT fds.day_date, fds.day_number,
       fa.activity_duration/3600 AS activity_duration,
       (SELECT SUM(fc2.activity_duration)/3600
        FROM fact_activity fc2
        WHERE fc2.fact_activity_uid = fa.fact_activity_uid AND
              fc2.day_date <= fa.day_date
       ) as cum_duration,
       depth_md_msl
    FROM fact_operation_summary fos JOIN
         fact_daily_summary fds
         ON fds.operation_uid = fos.operation_uid JOIN
         fact_activity fa
         ON fa.daily_uid = fds.daily_uid;

请注意，我还修复了 FROM 子句以使用显式 JOIN 语法。简单规则:永远不要在 FROM 子句中使用逗号。