我有一个事务表,提供:字母数字PK、时间戳、user_id、输入/输出字符串列和金额列。
id time user_id io amount
38hw 2019-10-18 18:35:09 2 in 1
nv49 2019-10-18 18:35:10 3 in 50
83ha 2019-10-18 18:35:11 5 in 2
ja03 2019-10-18 18:35:12 4 out 2
019c 2019-10-18 18:35:13 1 out 75
ac5r 2019-10-18 18:35:14 3 in 20
as30 2019-10-18 18:35:15 3 in 3
34ds 2019-10-18 18:35:16 4 in 7
12my 2019-10-18 18:35:17 2 in 50
dk20 2019-10-18 18:35:18 4 in 50
sk18 2019-10-18 18:35:19 1 in 7
am35 2019-10-18 18:35:20 2 in 3
mc92 2019-10-18 18:35:21 2 out 8
alov 2019-10-18 18:35:22 3 in 4
ap34 2019-10-18 18:35:23 1 out 6
我正在尝试创建另一列,在每次显示时提供该user_id的运行总计。这些user_id最初并不以0的数量显示,因此必须在它们第一次显示时假设。
我已经考虑过使用一些helper列来实现这一点。我的思考过程是这样的:
- 创建一个列来指示user_id在
time
列中的值之前出现的次数。可能称为occurence_num
列 - 创建一个使amount更易于使用的列,如
(case when io='in' then amount else -1*amount end) as balance_adjust
- 按
user_id
和(每行)sum()
对所有balance_adjust
值进行分组,其中occurence_num
小于当前记录。
我有一个艰难的时间来测试这些想法虽然。我在一个相当大的数据库中工作,SQLite有2200万行。该表可以根据需要进行更改/更新。它以这种方式存储有利于保持ETL尽可能简单,因为有大量的数据要提取,有大量的页面要提取。我想要的输出看起来像这样:
id time user_id io amount running_total
38hw 2019-10-18 18:35:09 2 in 1 1
nv49 2019-10-18 18:35:10 3 in 50 50
83ha 2019-10-18 18:35:11 5 in 2 2
ja03 2019-10-18 18:35:12 4 out 2 -2
019c 2019-10-18 18:35:13 1 out 75 -75
ac5r 2019-10-18 18:35:14 3 in 20 70
as30 2019-10-18 18:35:15 3 in 3 73
34ds 2019-10-18 18:35:16 4 in 7 5
12my 2019-10-18 18:35:17 2 in 50 51
dk20 2019-10-18 18:35:18 4 in 50 55
sk18 2019-10-18 18:35:19 1 in 7 -68
am35 2019-10-18 18:35:20 2 in 3 54
mc92 2019-10-18 18:35:21 2 out 8 46
alov 2019-10-18 18:35:22 3 in 4 77
ap34 2019-10-18 18:35:23 1 out 6 -74
我可以通过这种方式获得每个用户的总体总数,但需要几分钟:
SELECT
user_id,
sum(case when io='in' then amount else -1*amount end) as balance
FROM
transactions
GROUP BY
user_id
我认为在此基础上进行扩展,OVER
/PARTITION
子句将是一个很好的调用,但考虑到这个数据库的大小,我不确定它是否是正确的调用。
谢谢你的帮助
编辑:我应该提到,真实的的数据可能包括重复的时间列。事务可能同时发生,因为它只精确到秒。
1条答案
按热度按时间xesrikrc1#
一个小的调整你的尝试应该做的。使用相应的窗口函数将您的总和转换为“running sum”就足够了,该窗口函数将通过按用户分区和按时间排序来计算运行量。
而如果你有并列的时间,你可以依靠按id排序,这将打破并列,使总和正确工作。
输出:
| 身份证|时间|用户ID|木卫一|数量|天平|
| --------------|--------------|--------------|--------------|--------------|--------------|
| 38hw| 2019-10-18 18:35:09|二|在|1| 1|
| nv49| 2019-10-18 18:35:10|三|在|五十|五十|
| 83公顷|2019-10-18 18:35:11|五|在|二|二|
| ja03| 2019-10-18 18:35:12|四|出局|二|-2|
| 019c| 2019-10-18 18:35:13| 1|出局|七十五|-75|
| AC5R| 2019-10-18 18:35:14|三|在|二十|七十|
| AS30| 2019-10-18 18:35:15|三|在|三|七十三|
| 34ds| 2019-10-18 18:35:16|四|在|七|五|
| 12my| 2019-10-18 18:35:17|二|在|五十|五十一|
| dk20| 2019-10-18 18:35:18|四|在|五十|五十五|
| SK18| 2019-10-18 18:35:19| 1|在|七|-68|
| am35| 2019-10-18 18:35:20|二|在|三|五十四|
| MC92| 2019-10-18 18:35:21|二|出局|八|四十六|
| 阿洛夫|2019-10-18 18:35:22|三|在|四|七十七|
| AP34| 2019-10-18 18:35:23| 1|出局|六|-74|
检查演示here。
注意:最后一个
ORDER BY
子句是不需要的:只是为了视觉化。