时间戳

首页 > 解决方案 > mysql中的累积和列 - 设置功能不起作用

mysql - mysql中的累积和列 - 设置功能不起作用

问题描述

我正在尝试创建一个表格来显示用户的每周支出并显示当月的运行总额以与目标进行比较。

我尝试了两种不同的变体，set @csm :=0但似乎都没有工作，但我似乎找不到替代解决方案。

我还尝试在略有不同的情况下对案例进行分组：

when day(date) BETWEEN 0 AND 15 then 'Week_2'

但这似乎也不起作用，我希望能弄清楚为什么这对创建运行总数不起作用！

我现在尝试使用的代码如下所示：

select
    case
        when day(date) < 8 then 'Week_1'
        when day(date) < 15 then 'Week_2'
        when day(date) < 22 then 'Week_3'
        when day(date) < 29 then 'Week_4'
    else 'Week_5'
    end as Week_Num,
    sum(case when status = 'Complete' then charged_amount else 0 end) as Charged,
    (@csm:= @csm + sum(case when status = 'Complete' then charged_amount else 0 end)) as cumulative
from
    my_table 
where
    DATE_FORMAT(date,'%Y-%m') = DATE_FORMAT(now(),'%Y-%m')
group by 1

但我有另一个版本开始于：

select
    my_table.Week_Num,
    my_table.Charged,
    (@runtot := @runtot + my_table.Charged) as mtd

但这也没有用。

我正在寻找一个表格，显示该周Week_Num的Charged金额，然后Cumulative是当月迄今为止的总和：

Week_Num || Charged || Cumulative
Week_1   || 1       || 1
Week_2   || 2       || 3
Week_3   || 3       || 6
Week_4   || 4       || 10
Week_5   || 5       || 15

根据 Tim Biegeleisen 的回复，现在的代码如下所示：

SELECT
    CASE WHEN (DAY(date) - 1) / 7 < 1 THEN 'Week_1'
         WHEN (DAY(date) - 1) / 7 < 2 THEN 'Week_2'
         WHEN (DAY(date) - 1) / 7 < 3 THEN 'Week_3'
         WHEN (DAY(date) - 1) / 7 < 4 THEN 'Week_4'
         ELSE 'Week_5' END AS Week_Num,
    SUM(CASE WHEN status = 'Complete' THEN charged_amount ELSE 0 END) AS Charged,
    (SELECT 
        SUM(CASE WHEN mt2.status = 'Complete' THEN mt2.charged_amount ELSE 0 END)
    FROM my_table mt2
        WHERE 
            (DAY(mt2.date) - 1) / 7 <= (DAY(mt1.date) - 1) / 7
        and 
            DATE_FORMAT(date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')) AS Cumulative
FROM my_table mt1
WHERE
    DATE_FORMAT(date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')
GROUP BY
    1
ORDER BY
    1

该表应如下所示：

Week_Num || Charged || Cumulative
Week_1   || 4526    || 4526
Week_2   || 45093   || 49619
Week_3   || 38869   || 88488
Week_4   || 123987  || 212475
Week_5   || 7920    || 220389

实际上看起来是这样的：

Week_Num || Charged || Cumulative
Week_1   || 4526    || 3835
Week_2   || 45093   || 7733
Week_3   || 38869   || 88488
Week_4   || 123987  || 174355
Week_5   || 7920    || 218228

这可能是一个巧合，但Week_3令人困惑的是为什么会出现这种情况，最终的累积量只有大约 2000 美元。

样本数据：

Payment_ID || date       || Charged_Amount || Status
000001     || 2019-06-01 || 4526           || Complete
000002     || 2019-06-08 || 45093          || Complete
000003     || 2019-06-15 || 38869          || Complete
000004     || 2019-06-22 || 123987         || Complete
000005     || 2019-06-29 || 7920           || Complete

标签： mysql

解决方案

我会通过将天数除以 7 来检测周数来表达您的查询：

SELECT
    CASE Week_Num WHEN 0 THEN 'Week_1'
                         WHEN 1 THEN 'Week_2'
                         WHEN 2 THEN 'Week_3'
                         WHEN 3 THEN 'Week_4'
                                ELSE 'Week_5' END AS Week_Num,
    SUM(CASE WHEN status = 'Complete' THEN charged_amount ELSE 0 END) AS Charged,
    (SELECT SUM(CASE WHEN mt2.status = 'Complete' THEN mt2.charged_amount ELSE 0 END)
     FROM my_table mt2
     WHERE (DAY(mt2.date) - 1) / 7 <= mt1.Week_Num) AS Cumulative
FROM
(
    SELECT *, (DAY(date) - 1) / 7 AS Week_Num
    FROM my_table
) mt1
WHERE
    DATE_FORMAT(date, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')
GROUP BY
    Week_Num
ORDER BY
    Week_Num;

演示

推荐阅读