通過變動日誌獲取某一時刻所有使用者的帳號餘額

壹頁書發表於2016-06-22

初始化資料:

create table account(
userid int not null,
diamondCount int not null,
primary key(userid)
);
create table account_log(
id int not null primary key auto_increment,
userid int ,
changeCount int,
createtime timestamp
);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (2,483);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (3,377);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (4,334);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (5,449);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (6,576);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (7,624);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (8,450);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (9,566);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (10,463);
INSERT INTO `account` (`userid`,`diamondCount`) VALUES (11,410);
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (1,2,59,'2016-06-22 17:19:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (2,2,43,'2016-06-22 15:18:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (3,2,84,'2016-06-22 14:28:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (4,2,7,'2016-06-22 16:53:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (5,2,68,'2016-06-22 10:50:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (6,2,30,'2016-06-22 09:24:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (7,2,45,'2016-06-22 10:32:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (8,2,88,'2016-06-22 12:08:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (9,2,59,'2016-06-22 08:44:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (10,3,34,'2016-06-22 16:42:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (11,3,4,'2016-06-22 15:33:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (12,3,64,'2016-06-22 13:25:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (13,3,73,'2016-06-22 12:18:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (14,3,13,'2016-06-22 17:52:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (15,3,75,'2016-06-22 11:19:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (16,3,12,'2016-06-22 11:23:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (17,3,51,'2016-06-22 07:20:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (18,3,51,'2016-06-22 16:24:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (19,4,4,'2016-06-22 16:37:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (20,4,18,'2016-06-22 17:02:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (21,4,98,'2016-06-22 13:03:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (22,4,5,'2016-06-22 16:26:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (23,4,7,'2016-06-22 13:07:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (24,4,77,'2016-06-22 17:24:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (25,4,2,'2016-06-22 07:37:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (26,4,42,'2016-06-22 12:32:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (27,4,81,'2016-06-22 05:58:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (28,5,60,'2016-06-22 17:05:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (29,5,7,'2016-06-22 16:01:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (30,5,84,'2016-06-22 16:15:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (31,5,29,'2016-06-22 15:34:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (32,5,99,'2016-06-22 11:13:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (33,5,14,'2016-06-22 15:44:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (34,5,74,'2016-06-22 06:25:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (35,5,78,'2016-06-22 06:26:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (36,5,4,'2016-06-22 09:28:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (37,6,75,'2016-06-22 17:59:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (38,6,91,'2016-06-22 16:33:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (39,6,51,'2016-06-22 17:03:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (40,6,48,'2016-06-22 13:01:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (41,6,37,'2016-06-22 13:26:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (42,6,68,'2016-06-22 10:52:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (43,6,56,'2016-06-22 10:35:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (44,6,53,'2016-06-22 08:35:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (45,6,97,'2016-06-22 07:24:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (46,7,65,'2016-06-22 17:52:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (47,7,57,'2016-06-22 16:15:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (48,7,99,'2016-06-22 16:33:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (49,7,56,'2016-06-22 12:18:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (50,7,65,'2016-06-22 12:32:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (51,7,36,'2016-06-22 10:01:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (52,7,94,'2016-06-22 14:37:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (53,7,65,'2016-06-22 13:12:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (54,7,87,'2016-06-22 14:09:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (55,8,69,'2016-06-22 16:56:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (56,8,27,'2016-06-22 17:00:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (57,8,78,'2016-06-22 13:27:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (58,8,29,'2016-06-22 13:40:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (59,8,43,'2016-06-22 16:34:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (60,8,61,'2016-06-22 13:12:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (61,8,62,'2016-06-22 11:03:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (62,8,17,'2016-06-22 17:41:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (63,8,64,'2016-06-22 16:18:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (64,9,67,'2016-06-22 16:27:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (65,9,82,'2016-06-22 17:23:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (66,9,54,'2016-06-22 17:38:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (67,9,81,'2016-06-22 12:51:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (68,9,48,'2016-06-22 17:32:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (69,9,87,'2016-06-22 16:34:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (70,9,14,'2016-06-22 15:09:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (71,9,83,'2016-06-22 12:46:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (72,9,50,'2016-06-22 13:53:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (73,10,91,'2016-06-22 16:53:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (74,10,78,'2016-06-22 15:22:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (75,10,67,'2016-06-22 13:25:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (76,10,64,'2016-06-22 15:25:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (77,10,7,'2016-06-22 16:44:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (78,10,58,'2016-06-22 13:45:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (79,10,41,'2016-06-22 09:20:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (80,10,52,'2016-06-22 13:50:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (81,10,5,'2016-06-22 13:55:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (82,11,25,'2016-06-22 17:25:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (83,11,18,'2016-06-22 15:39:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (84,11,8,'2016-06-22 16:50:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (85,11,98,'2016-06-22 17:06:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (86,11,79,'2016-06-22 13:47:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (87,11,20,'2016-06-22 13:37:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (88,11,63,'2016-06-22 08:50:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (89,11,8,'2016-06-22 17:39:57');
INSERT INTO `account_log` (`id`,`userid`,`changeCount`,`createtime`) VALUES (90,11,91,'2016-06-22 11:14:57');

account是帳號表,儲存使用者ID和餘額(diamondCount)
account_log 是帳號變動表,每次帳號的變動,都有相關的記錄.

通過account_log,可以批量獲取使用者在某個時間點的帳號餘額資訊.
主要是用於核驗資訊.

查詢2016-06-22 10:00:00 所有使用者的帳號餘額

select
userid,
diamondcount - (select
sum(changeCount)
from
account_log log
where
log.createtime >= '2016-06-22 10:00:00'
and log.userid = a.userid) changecount
from
account a;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2120736/，如需轉載，請註明出處，否則將追究法律責任。

最終一致性落地實踐--獲取接收方的帳號餘額
2016-06-14
LocalDateTime獲取今天最早時刻和最晚時刻
2024-09-12
LDA
思路清奇：通過 JavaScript 獲取移動裝置的型號
2017-07-14
JavaScript
iOS 通過runtime獲取某個類中所有的變數和方法
2019-02-25
iOS變數
分析24小時日誌獲取交易時間分佈
2014-01-12
通過Java反射動態獲取資訊
2013-06-07
Java反射
光貓超級帳號密碼，重置光貓獲取超級帳號密碼
2020-03-21
密碼
日誌系統實戰(二)-AOP動態獲取執行時資料
2014-11-27
微信小程式-選擇時間（一週的某一時刻）
2018-12-04
微信小程式
通過JQuery ajax獲取伺服器時間
2018-10-22
jQuery伺服器
反射-通過反射獲取成員變數並使用
2017-06-11
反射變數
網際網路時代如何優雅地獲取使用者注意力？觸動時刻（二）
2017-06-13
java獲取redis的日誌資訊和動態監控資訊
2017-04-04
JavaRedis
通過 Systemd Journal 收集日誌
2019-03-11
智慧手環core日誌獲取
2021-11-15
智慧手環guard日誌獲取
2021-11-16
獲取Tomcat更詳細的日誌
2019-04-08
Tomcat
微信開發——通過授權獲取使用者的基本資訊
2014-05-27
python通過TimedRotatingFileHandler按時間切割日誌
2019-07-17
Python
C#通過反射獲取物件屬性，列印所有欄位屬性的值
2017-12-29
C#反射物件
通過EPROCESS獲取程式名
2016-03-18
iOS 執行時獲取類的所有屬性
2015-11-10
iOS
phpMyadmin通過日誌寫webshell
2020-11-23
PHPWebshell
DB2_獲取診斷日誌
2011-07-14
DB2
獲取波場（Tron）錢包TRX、USDT餘額和剩餘頻寬、能量 - 筆記
2023-06-29
筆記
檢視pod下面某一個容器的日誌
2024-05-28
通過vftps和虛擬帳號增強ftp的安全性
2017-11-12
FTP
JavaScript 通過class獲取元素物件
2019-05-16
JavaScript物件
通過用shellcode獲取shell
2020-10-08
Spring Boot利用AOP獲取使用者操作實現日誌記錄
2018-12-19
Spring Boot
通過日誌審計追蹤外部***
2010-06-09
通過反射獲取類的類名，方法和內部成員變數
2016-06-26
反射變數
獲取兩天內的告警日誌（bash|shell|oracle）
2017-08-17
Oracle
獲取一週內的告警日誌（python|Oracle）
2017-08-11
PythonOracle
Oracle日誌組新增冗餘檔案和日誌組
2017-09-13
Oracle
如何快速過濾出一次請求的所有日誌？
2021-11-24
Java實時讀取日誌檔案
2020-03-01
Java
餘額寶：在過去11年間餘額寶已為使用者累計賺取了4499億元的收益
2024-06-20

通過變動日誌獲取某一時刻所有使用者的帳號餘額

相關文章