如何创建按列分组的累计和

104

达因

我有一个表，其中有两个或多个表示状态的列

╔════════════╤═══════════╤═══════════╗
║ updated_at │ state_one │ state_two ║
╠════════════╪═══════════╪═══════════╣
║ 12/31/1999 │ 1         │ 2         ║
╟────────────┼───────────┼───────────╢
║ 1/1/2000   │ 2         │ 3         ║
╟────────────┼───────────┼───────────╢
║ 1/2/2000   │ 0         │ 3         ║
╚════════════╧═══════════╧═══════════╝

我希望能够编写一个简单的查询来计算状态列state_one和的给定状态下每一行的累积总和state_two。一个查询，给我类似的东西：

╔════════════╤═══════════╤═══════════╤══════════════════════╤══════════════════════╗
║ updated_at │ state_one │ state_two │ cumulative_sum_one_1 │ cumulative_sum_two_2 ║
╠════════════╪═══════════╪═══════════╪══════════════════════╪══════════════════════╣
║ 12/31/1999 │ 1         │ 2         │ 1                    │ 1                    ║
╟────────────┼───────────┼───────────┼──────────────────────┼──────────────────────╢
║ 1/1/2000   │ 2         │ 2         │ 1                    │ 2                    ║
╟────────────┼───────────┼───────────┼──────────────────────┼──────────────────────╢
║ 1/2/2000   │ 0         │ 1         │ 1                    │ 2                    ║
╚════════════╧═══════════╧═══════════╧══════════════════════╧══════════════════════╝

会有更多的列，但是会有更多的状态，因此会有更多的列。

我正在使用MySQL 5.6.35版本。虽然我知道自己做错了，但这是我到目前为止的查询，但是它计算所有行的累加总和：

select
    row.day,
    case row.state when 1 then "foo"
                   when 2 then "bar"
                   else "baz"
    end as state,
    row.state_count,
    @running_total:= (
        @running_total + row.state_count
     ) as cumulative_sum
from (
    select
        date_format(from_unixtime(updated_at), '%m/%d/%Y') as day,
        count(state) as state_count,
        state
    from 
        table_of_interest
    group by day
) row
join (select @running_total:=0) r
order by row.day

戈登·利诺夫

您可以使用相关的子查询：

select t.*,
       (select count(*)
        from table_of_interest t2
        where t2.update_at <= t.updated_at and t2.state_one = 1
       ) as cumulative_sum_one_1,
       (select count(*)
        from table_of_interest t2
        where t2.update_at <= t.updated_at and t2.state_one = 2
       ) as cumulative_sum_two_2
from table_of_interest t;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。