获取每月的交易余额
问题描述:
我收到了一个2列表,其中存储了更改时间(unix_time)和更改值的事务。获取每月的交易余额
create table transactions (
changed int(11),
points int(11)
);
insert into transactions values (UNIX_TIMESTAMP('2014-03-27 03:00:00'), +100);
insert into transactions values (UNIX_TIMESTAMP('2014-05-02 03:00:00'), +100);
insert into transactions values (UNIX_TIMESTAMP('2015-01-01 03:00:00'), -100);
insert into transactions values (UNIX_TIMESTAMP('2015-05-01 03:00:00'), +150);
为了得到当前的余额,你需要总结的所有值,并从你需要总结,如果更换时间这个值是过去取得平衡小于要求,如:
select
sum(case when changed < unix_timestamp('2013-12-01') then
points
else
0
end) as cash_balance_2013_11,
...
因此每个月需要有一个单独的SQL代码。我希望有SQL代码可以为我提供所有月份的余额。 (例如,从固定日期至今)
编辑:
答
你能只是group by
和order by
一个月?
UPDATE:获得运行总和,你必须参加个别月份的一组总计按一个月,在“小于或等于”匹配: -
select
m.single_month
, sum(month_of_change.total_points) as running total_by_month
from
(
select
sum(points) as total_points
, month_of_change
from
(
select
points
, MONTH(FROM_UNIXTIME(t.time_of_change)) as month_of_change -- assumes unix_time
from mytable t
) x
group by month_of_change
) monthly_totals
inner join
(
select distinct MONTH(FROM_UNIXTIME(t.time_of_change)) as single_month
) m
on monthly_totals.month_of_change <= m.single_month
group by m.single_month
(NB:没有测试过)
你可以分成这里的时间间隔 http://*.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range – 2014-11-20 18:28:53
@ Horst Walter:据我所知,这会在一个月内给我带来改变。为了获得平衡,您不仅需要从给定的月份中总结所有以前的值。 – Pueblo 2014-11-20 18:44:13