SQL计算用户留存率,原理及流程拆解

问题及分析

根据用户记录,按一定时间周期,计算用户留存量、留存率。

留存率 = 留存量 / 初始量 * 100%

从某一个时间段作为起点,作为初始用户,一年后这些用户还有多少,两年后还有多少... 以此即可做计算。这里有个问题,就是这个起点的用户,是否也是从更早以前即存在、并留存到现在的;如果把本身即是“留存”而来的,留存率是不准确的。所以,通常要以用户首次出现开始计算。

简单来说,就是从新用户开始,计算其N一个时间段后留存;而每个时间段,都有新用户,所以这是个斜三角形的表。

以客户的销售记录为例,分步拆解计算原理。

已有原始数据表,包含了每个客户的销售记录,包括销售时间、销售数量、销售金额等。按年计算客户留存。

第一步,计算出初级表。

按年(yr)计算每个客户(cust)的统计信息,销量(sales)、销售金额(amount)。逻辑上,该结果表有个惟一键 (yr+cust),后续所有计算都从该表出发。

表结构如下

CREATE TABLE `yrept` (
  `cust` int(11) NOT NULL DEFAULT 0 COMMENT '客户',
  `yr` int(11) NOT NULL DEFAULT 0 COMMENT '年份',
  `sales` int(11) NOT NULL DEFAULT 0 COMMENT '销量',
  `amount` decimal(30,2) NOT NULL DEFAULT 0.00 COMMENT '金额',
  UNIQUE KEY `cust_yr` (`cust`,`yr`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
 COMMENT='每个客户cust在每个年份yr上的统计';
-- 数据表见附件
每个客户cust在每个年份yr上的统计

每个客户cust在每个年份yr上的统计

从原始数据表计算出一张每个时间段里每个用户的统计信息,

第二步,计算每个客户首次出现的年份。

SELECT cust,min(yr) as yr FROM `yrept` GROUP BY `cust`;

第三步,从原表yrept入手,扩展表,联入每个行对应客户的首次出现年份,预览数据。

原表yrept是每客户在各年份上的统计,那为该表增加一个字段,该行客户cust的首次出现的年份yr_1st ,语句如下

SELECT a.*,'|' as s,b.*,'|' as p, a.yr-b.yr_1st AS `later_yr`
FROM `yrept` a INNER JOIN (
    SELECT cust,min(yr) as yr_1st FROM `yrept` GROUP BY `cust`
) b ON a.cust=b.cust
WHERE 1
ORDER BY b.cust, b.yr_1st
;

另加一个计算列,相对于客户首次出现后的年数 later_yr。

汇总统计

上步结果表,关注其中的 cust, yr_1st, yr 三列,即是客户cust首次出现起,之后每年留存的客户信息(有对应记录即是留存)。对 yr_1st + yr 做汇总统计,即是每个年份新增客户数目、及之后每个年份的留存统计。

SELECT b.yr_1st,a.yr AS in_yr,count(*) as cnt
  ,count(DISTINCT a.cust) as unq_cust, SUM(a.sales) as sales 
FROM yrept a INNER JOIN (
SELECT cust,min(yr) as yr_1st FROM yrept GROUP BY cust 
) b ON a.cust=b.cust 
WHERE 1 
GROUP BY b.yr_1st,in_yr 
ORDER BY b.yr_1st,in_yr
;

换个形式,以 将 yr 换成later_yr做统计,即客户cust首次出现起,N年后的留信息。事实这两种形式是等价的。

SELECT b.yr_1st,a.yr-b.yr_1st AS later_yr,count(*) as cnt
  ,count(DISTINCT a.cust) as unq_cust, SUM(a.sales) as sales
FROM yrept a INNER JOIN (
    SELECT cust,min(yr) as yr_1st FROM yrept GROUP BY cust
) b ON a.cust=b.cust
WHERE 1
GROUP BY b.yr_1st,later_yr
ORDER BY b.yr_1st,later_yr
;

以上两个结果,其中的 cnt 与unq_cust 两列,事实上也是等价的,就是惟一客户人数。

按需要平摊成需要的形式

比如以yr_1st为行、按later_yr或in_year 分成多列展示。

计算留存率

观察上面平摊表,从新客户开始逐年的留存率的计算就很直观了。

如果对按右表整列求和,得到按年的所有新客户、逐年总留存率,可视为多年的新客户整体留存率,也是有意义的。虽然不太直观。

甚至,还可以对右表整列求和,并计算留率,可视为每年的所有客户(包括新客与留存客户)在之后每年的留存率。

postgres中执行DELETE ... LEFT/RIGHT JOIN

问题:有两张表 users, addrs,需要从users表中删除一些行,条件为在“addrs"表中没有对应id的行。

table-users

table-users

table-addrs

table-addrs

事实上很简单,只是个left join查询,SELECT a.*,'|' s,b.* FROM users a LEFT JOIN addrs b ON a.id=b.id;,预览如下:

overview-join

overview-join

然而,postgres本身不支持 DELETE ... LEFT JOIN ... 这样的语句,这点不像MySQL那样方便。

有一个曲线救国方法:

把上面LEFT JOIN的结果表视为一张中间表,把原users表、按主键 inner join 到中中间表,然后从users表中按删除指定行。语句如下

DELETE FROM users
USING users a LEFT JOIN addrs b ON a.id=b.id
WHERE users.id=a.id AND b.name IS NULL;

问题得解。

当然也可以使用子查询,从中间表中取出id主键,以子查询形式删除。不推荐。

前文用表及数据

CREATE TABLE users (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    name character varying(255)
);
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id);

CREATE TABLE addrs (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    addr character varying(255)
);
ALTER TABLE addrs ADD CONSTRAINT addrs_pkey PRIMARY KEY (id);

INSERT INTO "users" VALUES (1, 'Tom');
INSERT INTO "users" VALUES (2, 'Jarry');
INSERT INTO "users" VALUES (3, 'Alias');
INSERT INTO "users" VALUES (4, 'Bob');
INSERT INTO "users" VALUES (5, 'somebody');

INSERT INTO "addrs" VALUES (1, 'Luoyang');
INSERT INTO "addrs" VALUES (2, 'Shanghai');
INSERT INTO "addrs" VALUES (3, 'Hongkong');

EOF.

 

MySQL/MariaDB下索引基数cardinality的更新问题

起因与问题

使用MySQL做数据,有时会隐约感觉到一些语句执行速度极其慢,而理论上应该是很快的。通常使用phpMyAdmin作为客户端,在表结构页里可以方便的看到索引状态,对基数cardinalyty一知半解,隐约理解为惟一值个数。

但前两天写一条查询语句执行速度非常非常慢,看到一个索引的基数竟然是空的,而且明明应该有很多值。猜测MySQL出bug了,于是删除并重建了索引,基数正常了,语句也飞快跑完。于是稍多留意了一下索引基数。同一天,看到一个基数为1的索引,也是很多惟一值的字段,这也不正常。因为是MyISAM表,直接打包了对应的.frm, .MYD, MYI 三个文件,保留一个现场,有时间再做研究。

回顾了问题表生成语句,整理出一个简单化的重现过程,下面讨论。

问题重现

测试平台为 MySQL 5.5, MariaDB 10.3,其它版本应该也是类似。直接上语句

/* ************************************************* */
-- 一张数据表,并填充一些数据,稍微大一些
DROP  TABLE IF EXISTS `d_src` ;
CREATE TABLE `d_src` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `org` char(32) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `org` (`org`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

INSERT INTO `d_src`(org) VALUES (md5('abcdefg'));
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;
INSERT INTO `d_src`(org) SELECT md5(concat(org,id,rand()*id)) FROM `d_src`;

-- 新建一张表,这里先使用 MyISAM 存储引擎,用来重现问题。插入数据,加字段及索引,UPDATE
DROP TABLE IF EXISTS `d_making`;
CREATE TABLE `d_making` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `org` varchar(50) NOT NULL DEFAULT '',
 `cnt` int(10) unsigned NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `org` (`org`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into `d_making`(`org`)
SELECT `org` FROM `d_src` 
ORDER BY id desc;

ALTER TABLE `d_making` ADD org_fix varchar(50) NOT NULL DEFAULT '', ADD INDEX (org_fix);

UPDATE `d_making` SET org_fix=left(org,2 );

-- watch the index status, the Cardinality of Key org_fix is 1
SHOW index FROM d_making;

此时查看索引状态,注意org_fix索引的基数,竟然是1 !  删除并重建索引,再看

-- rebuild index and show key again, the Cardinality NOT 1 
ALTER TABLE `d_making` DROP INDEX `org_fix`, ADD INDEX `org_fix` (`org_fix`); 
SHOW index FROM d_making;

org_fix的基数应该是256左右(因为是原始数据是随机数,大概在256的附近)。

按上脚本,只d_making表改用InnoDB存储引擎,结果也类似,也许不是1,但也明显不对。

不过,在MySQL 8.0 (CentOS 8默认配置)下,得到了 258,比真实值256要大。

原因探求

最早注意到索引基数时,搜索过相关资料,但仍是一知半解。这次通过 "mysql cardinality rebuild index" 查询,找到stackoverflow上有类似问题,最终找到mysql官方手册的一段说明

Cardinality

An estimate of the number of unique values in the index. To update this number, run ANALYZE TABLE or (for MyISAM tables) myisamchk -a.

Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

理解下来,说是索引基数只是个估算值,并不一定可靠,可以用 ANALYZE TABLE 表 更新。事实上,删除并重建索引并不一定奏效。SQL语句执行时,优化器会依据索引基数决定是否使用索引,于是,基数不正确(过小)的索引,就不能被选用,于是这个索引事实上无效了。

结论

  1.  MySQL索引基数,可能不准确(过大过小都有可能),从而可能造成语句执行时忽略该索引(索引失效)。
  2.  要想让该值准确,得在表数据有改动后跑一遍 ANALYZE TABLE table_name

对使用者来说,这是个潜在的坑。手工运行SQL语句前,可以事先关注一下索引基数是否正常(或先 explain一下)。如果是在线生产环境,可能就要自求多福了,或者搞一大群的计划任务,跑ANALYZE TABLE. 不过,理论上,没有大面积UPDATE的字段的索引,这个问题不大。

后记

再深入一点,索引基数即是MySQL统计信息,关系型数据库的查询优化器要依据统计信息确定是否使用某个索引;而统计信息并非实时更新的。本问题的即是统计信息未及时更新的一个极端案例。也就是说,这个问题并非MySQL本身的缺陷,应该在关系型数据库中普遍存在。

英文中连续字母频次统计

这只是个无聊的小把戏。

最近又看到关于qwerty键盘布局的讨论,有提到好的键盘布局标准之一:把连续的按键分散在左右两手上,这样第一只手击键的同时,第二只手可以提前做好准备,提高效率。

于是从网上找了一批古典英文小说的txt电子版,写了个傻傻的脚本,统计其中连续两个字母的频次(忽略大小写),其中频度最高的前30项如下表。这30项共计占总频次的43.3%.

如前述,“是否可以把连续击键分散在两只手上”,即表格最后一列。从结果上看,分散与否各15项,所以qwerty在这个标准上优势似乎并不明显。

当然,这并不能证明qwerty键盘是不合理的;毕竟评估因素非常多。

序号 字母组 频次 占比 双手分散*
1 he 390103 4.04% Y
2 th 366573 3.80% Y
3 in 234621 2.43%
4 er 221732 2.30%
5 an 205618 2.13% Y
6 re 163779 1.70%
7 nd 148125 1.53% Y
8 ha 135536 1.40% Y
9 ou 133357 1.38%
10 ed 132822 1.38%
11 on 132233 1.37%
12 at 128740 1.33%
13 en 124127 1.29% Y
14 ng 117178 1.21% Y
15 hi 116461 1.21%
16 to 112860 1.17% Y
17 it 108636 1.13% Y
18 is 103327 1.07% Y
19 as 102013 1.06%
20 ar 99665 1.03%
21 es 98518 1.02%
22 te 96810 1.00%
23 or 94932 0.98% Y
24 le 92578 0.96% Y
25 st 92218 0.96%
26 of 91493 0.95% Y
27 se 87755 0.91%
28 ve 84620 0.88%
29 me 80898 0.84% Y
30 ea 77540 0.80%

使用的小说素材有:傲慢与偏见, 安娜卡列尼娜, 巴黎圣母院, 悲惨世界, 格列佛游记, 白衣女人, 飘, 呼啸山庄, 尤利西斯, 德伯家的苔丝, 爱玛, 白鲸, 黑骏马。

傻傻的脚本。脚本本身区分大小写的,整理统计结果时在excel里合并了大小写。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

fp=open('english_novels_all.txt')
raw=fp.read()
fp.close()

ch='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
for i in range(len(raw)-2):
  if raw[i] not in ch or raw[i+1] not in ch:
    continue
  k=raw[i:i+2]    # k=raw[i:i+2].lower()
  if k in st:
    st[k]+=1
  else:
    st[k]=1

fp=open('st_out.txt','w+')
for k in st:
  fp.write('%s %s\r\n'%(k,st[k]))

fp.close()

Adblock Plus 的个人设置

FireFox 扩展Adblock Plus

使用原则:只阻止过于反感的广告,其他广告一律放行;亦即,自带的过滤列表一律不激活。

“Adblock Plus 设置 - 高级” 设置页

创建和编辑您的过滤列表

//pos.baidu.com/
//static.mediav.com/js/

算法收集

这是算法收集板

最大公共子序列(Longest CommonSubstring, LCS)Python版

def lcs(x, y):
    matrix = [''] * (len(x) + 1)
    for index_x in range(len(matrix)):
        matrix[index_x] = [''] * (len(y) + 1)
    for index_x in range(1,len(x) + 1):
        for index_y in range(1,len(y) + 1):
            if x[index_x - 1] == y[index_y - 1]:#这里利用属性一
                matrix[index_x][index_y] = matrix[index_x - 1][index_y - 1] + x[index_x - 1]
            elif len(matrix[index_x][index_y - 1]) > len(matrix[index_x -1][index_y]):#这里和下面利用属性二
                matrix[index_x][index_y] = matrix[index_x][index_y - 1]
            else:
                matrix[index_x][index_y] = matrix[index_x - 1][index_y]
    return matrix[len(x)][len(y)]

来源 https://www.jianshu.com/p/a1806f2fef52

MySQL的缺陷/Bug/异常/陷阱/注意事项

MySQL的缺陷/Bug/异常/陷阱/注意事项

这里只是个记录,踩过的坑

REGEXP的中文支持

REGEXP 对中文的错误识别,如下语句,结果竟然是1,在 MySQL 5.5.53, MariaDB 5.5.60, MySQL 5.7.24 下测试结果一致.

SELECT '区中医院'  regexp '[一二三四五六七八九十〇]{6,}'  as mt

这个问题是在utf8-general-ci 数据表上做regexp匹配连续的数字汉字时发现,暂时没测试否与字符集的选择相关,猜测是regexp本身行为对宽字符集支持的问题。

已确认 MySQL 8.0.4 以后解决了该 bug

变量/设置选项:sql-mode相关

sql-mode变量默认是空的,这里造成很多问题,强烈建议,至少加入如下的设置。

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

否则,下面的一系列陷阱在等着你...

超过字段长度的字符串会自动截断

插入数据时超过字段长度的字符串会自动截断。这是MySQL的默认行为,很坑很坑;即使这对于初学者来说显得“友好”些,然而它还是个大坑。而且还会因此带来更多诡异行为,比如:按用户名汇总(group by)的统计结果插入另一表,如果不小心目标表用户名字段长度不足、并且设置了惟一键,可能收到报错说重复值(举例统计结果里“阮小二”、“阮小五”、“阮小七”都被截断成“阮小”,就是三条重复数据)。

往表中写入数据中途出错后竟然保留不完整的数据

 

关于sql-mode的后记

设置了sql-mode,你会用得很开心的。然而,一旦切换到其它环境时,更大的麻烦也可能随之而来。或许只有你把时时刻刻记得这些缺陷,并在每行代码里规避它们。

sql-mode有很多选项,请参考官方手册。

索引统计信息非实时更新,从而会造成索引无效

即索引基数 cardinality ,尤其在对表有大规模写入后容易出现。这个问题比较复杂,在不同版本不同存储引擎下的表现并不一致,8.x的新版本表现似乎明显更好。参看MySQL/MariaDB下索引基数cardinality的更新问题

convert/cast做数据类型转换后结果无法按预期写入

希望从字符串中提取一段数字,如果提取结果为非法数字,使用convert或cast函数强制转换为数字;在select结果记录集里,非数字被转换为0,这是符合预期的;但如果把结果写入字段中示例写入表中,非法数据的写入仍然是失败的,就像写入的是未做过转换的原始值一样。如下示例表,希望从birth字段中提取年份数字,写入到y字段中。

use `test`;
CREATE TABLE IF NOT EXISTS `foo` (
  `id` int(6) NOT NULL,
  `birth` varchar(20) NOT NULL DEFAULT '',
  `y` smallint(6) NOT NULL DEFAULT 0,
  `yt` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `foo` (`id`, `birth`, `y`, `yt`) VALUES
(1, '1945年11月', 0, ''),
(2, 'x年x月', 0, ''),
(3, '1940年', 0, ''),
(4, '1962年12月', 0, '');

-- 在select中非法数字转换为0,是符合预期的。
SELECT *,cast(left(birth,locate('年',birth)-1) as UNSIGNED) as x FROM `foo`;

-- 但update到y字段中,就报错了
update foo set y=cast(left(birth,locate('年',birth)-1) as UNSIGNED) ;
#1292 - Truncated incorrect INTEGER value: 'x'

-- 如果不转换直接写入,报错消息是有所不同的
update foo set y=left(birth,locate('年',birth)-1) ;
#1366 - Incorrect integer value: 'x' for column `test`.`foo`.`y` at row 2

Windows下的安装

注册为windows服务时, --install 参数必须写在其他参数前面。如果指定defaults-file 参数,那么 --install 参数后要跟个服务名,可以写成MySQL、MariaDB10或其他名字。

通过zip包升级安装新版本,升级后,要运行 mysql_upgrade.exe 让它升级系统表。如果mysql的root用户有登录密码,需要带上-u -p参数, mysql_upgrade.exe -uroot -pyourpassword

8.x 以后用户授权与此前有重大改变,全新的手工安装后设置root密码将麻烦很多,旧的方式多半已无效。

混用left/right/inner join的查询结果很可能非预期

<TODO>{本节内容似乎有误,未核实}严格来说,这不是mysql的问题。查询优化器会在参与join的表中排出先后次序,这个次序很可能并不是它们在where子句中出现的前后次序,inner join会丢弃无完全匹配的行,而left/right则不是;如果比预期丢早或丢晚了,就很可能造成结果非预期。回想mysql文档中的join都是 xx join (a, b) ON ... 这样的写法,甚至较早版本并不支持 xx join a ... xx join b... 的写法,可在一定程度上避免类似问题。

暂不举实例了。实际应用中已遇到这个问题,实际问题太复杂,等以后有时间再编个小的示例。

娱乐一下:测试WinRAR与7-Zip压缩比(使用wordpress源码文件及相机照片)

测试环境

软件:采用当前(2019/03/28)的最新x64位版,软件都从官方下载。

  • WinRAR:  v5.70简体中文版      winrar-x64-570sc.exe
  • 7-Zip: 7-Zip 19.00 (2019-02-21) 7z1900-x64.msi

测试素材: 1) WordPress源码解压缩后的文件夹,是 wordpress-3.5.2-zh_CN.zip 比较老,没有特别原因,只是电脑上正好有这个zip包而已。  2) 一批数码相机照片,计400多张,370M.

测试项目

使用WinRAR与7-Zip分别压缩,含标准压缩、最大压缩,压缩成各家独特格式、zip格式,然后比较压缩包大小。

结果

$ ls -l --block-size=K
total 1566840K
drwxrwx---+ 1 feng None      0K Mar 28 17:50 photos

-rwxrwx---+ 1 feng None 377416K Mar 28 17:52 photos_7z19_stand.7z
-rwxrwx---+ 1 feng None 379106K Mar 28 17:51 photos_winrar57_stand.rar

-rwxrwx---+ 1 feng None 377081K Mar 28 17:55 photos_7z19_max.7z
-rwxrwx---+ 1 feng None 379106K Mar 28 17:53 photos_winrar57_max.rar

drwxrwx---+ 1 feng None      0K Jun 22  2013 wordpress

-rwxrwx---+ 1 feng None   6199K Mar 28 17:31 wordpress_winrar57_stand.rar
-rwxrwx---+ 1 feng None   4996K Mar 28 17:19 wordpress_7z19_stand.7z

-rwxrwx---+ 1 feng None   6196K Mar 28 17:45 wordpress_winrar57_max.rar
-rwxrwx---+ 1 feng None   6374K Mar 28 17:58 wordpress_winrar57_max.zip

-rwxrwx---+ 1 feng None   6381K Mar 28 17:58 wordpress_winrar57_stand.zip
-rwxrwx---+ 1 feng None   6337K Mar 28 18:00 wordpress_7z19_stand.zip

-rwxrwx---+ 1 feng None   4969K Mar 28 18:02 wordpress_7z19_max.7z
-rwxrwx---+ 1 feng None   6292K Mar 28 17:57 wordpress_7z19_max.zip

-rwxrwx---+ 1 feng None   6172K Mar 28 17:19 wordpress_winrar371_stand.rar

[注] 为方便对照,调整了条目的次序,并非ls原始次序。

简评

从结果上看,所有测试项目都是7-Zip胜出,这在意料之中。但同样压缩成zip格式,7-Zip都好于WinRAR.

甚至,winrar 3.71版比 5.70版压缩率还更高!实在让人大跌眼镜!

存储过程实现MySQL表数据变更记录追踪(含历史快照)

直接上代码及调用示例,调用示例:

假设有一张表user_status_in_playground,存储了用户在每个游戏场的统计数据,如总积分 、玩过次数、最近玩的时间、最近次得分。逻辑上 user_id + playground_id 是个惟一键,我们希望按照这个惟一键追踪其它字段的数据变更记录(建数据快照)。需要快照时执行一次存储过程,快照数据存到表archive_user_status_in_playground里。其中归档表只需要指定表名即可,将由存储过程自动创建。

SET @data_source='source_db.`user_status_in_playground`';
SET @archive_target='archive_db.archive_user_status_in_playground';
SET @identify_columns='user_id,playground_id';
SET @track_columns='score,paly_times,last_play_time,last_score';

call revision_archive(@data_source, @archive_target, @identify_columns, @track_columns, @dmesg);

如果我想按指定的时间点倒出当时的完整快照,其中revision是时间点(年月日时分秒),快照表也是自动创建的,指定表名即可。

SET @archive_location='archive_db.archive_user_status_in_playground';
SET @dump_location='`tmp`.`user_status_in_playground__dump`';
SET @identify_columns='user_id,playground_id';
SET @track_columns='score,paly_times,last_play_time,last_score';
SET @revision=20190603100255;

call revision_dump(@archive_location, @dump_location, @identify_columns, @track_columns, @revision, @dmesg);

(原始存储过程见附件)

db_revision_archive.mysql.sql

Pages: 1 2 3 4 5 6 7 8 9 10 ... 47 48 49 Next