时间戳

首页 > 解决方案 > 在 BigQuery 中分割账户余额数据以生成借记报告

google-bigquery - 在 BigQuery 中分割账户余额数据以生成借记报告

问题描述

随着时间的推移，我收集了一系列帐户余额：

+-----------------+------------+-------------+-----------------------+
| account_balance | department | customer_id |  timestamp            |
+-----------------+------------+-------------+-----------------------+
| 5               | A          | 1           |  2019-02-12T00:00:00  |
| -10             | A          | 1           |  2019-02-13T00:00:00  |
| -35             | A          | 1           |  2019-02-14T00:00:00  |
| 20              | A          | 1           |  2019-02-15T00:00:00  |
+-----------------+------------+-------------+-----------------------+

每条记录显示客户在指定时间戳的总账户余额。例如，当客户用 55 充值他的帐户时，帐户余额从 -35 增加到 20。当客户使用服务时，他的帐户余额例如从 5 减少到 -10。

我想以两种方式聚合这些数据：

1）获取部门每月和每年的借方、贷方和余额（贷方-借方）。4 月的结果应该是之前所有月份的总结：

+---------+--------+-------+------------+-------+--------+
| balance | credit | debit | department | month |  year  |
+---------+--------+-------+------------+-------+--------+
| 5       | 10     | -5    | A          | 1     |  2019  |
| 20      | 32     | -12   | A          | 2     |  2019  |
| 35      | 52     | -17   | A          | 3     |  2019  |
| 51      | 70     | -19   | A          | 4     |  2019  |
+---------+--------+-------+------------+-------+--------+

客户的帐户余额可能不会每个月都发生变化。2月份可能有客户1的账户余额记录，但3月份没有。

解决方案注意事项：

2）按日期获取部门借方、贷方和余额的变化。

+---------+--------+-------+------------+-------------+
| balance | credit | debit | department |  date       |
+---------+--------+-------+------------+-------------+
| 5       | 10     | -5    | A          | 2019-01-15  |
| 15      | 22     | -7    | A          | 2019-02-15  |
| 15      | 20     | -5    | A          | 2019-03-15  |
| 16      | 18     | -2    | A          | 2019-04-15  |
+---------+--------+-------+------------+-------------+
  51       70       -19

当我创建增量的 SUM 时，我应该从 1) 中的结果中获得与最后一行相同的值。

解决方案注意事项：

标签： google-bigquery

解决方案

您的问题不清楚，但听起来您想在任何给定时间点获得未结余额。

以下查询在 1 个时间点执行此操作。

with calendar as (
  select cast('2019-06-01' as timestamp) as balance_calc_ts
),
most_recent_balance as (
  select customer_id, balance_calc_ts,max(timestamp) as most_recent_balance_ts
  from <table>
  cross join calendar
  where timestamp < balance_calc_ts -- or <=
  group by 1,2
)
select t.customer_id, t.account_balance, mrb.balance_calc_ts
from <table> t
inner join most_recent_balance mrb on t.customer_id = mrb.customer_id and t.timestamp = mrb.balance_calc_ts

如果您需要在一系列时间点计算它，则需要修改日历 CTE 以返回更多日期。这就是 BQ 中 CROSS JOINS 的美妙之处！

推荐阅读