想问问大家 ROI 报表怎么实现的？

[deweixu]

比如一个游戏在

11.2 日的广告买量花费是 100 元，那么 11.2 的新增用户的内购付费就是收入

ROI 就是计算 11.2 新增用户在未来付费和 成本花费的比值

比如

    11.2 用户内购 10 元 当天的 ROI 就是 10%

   11.3 用户内购 10 元 ROI 就是 20%

    11.4          20           40%

这种需求怎么做好一点

想过把每天用户的付费 和 用户创建时间 都存到 mysql 表中，然后代码计算

有更好的方式吗？

以前没做过这类报表的需求, 谢谢大家

[wxf666]

有没有啥表结构和数据？数据库新手想试试，能不能用 SQL 解决

[Features]

全新开发，没有存量数据，如果是我做的话
创建一个 ROI 表，表的字段

buy_day | member_id | buy_sum | roi


注意 buy_day+member_id 是 unique index

每次用户付费，就查询该用户该用户的历史信息，并根据查询结果更新 roi

如果有存量数据，就先写一个脚本生成历史的 roi 记录

这样查询的成本会低一点吧？ 单纯用 SQL 的话，后期量大了优化起来就难了

[wxf666]

@deweixu @Features 这个 ROI 计算公式是啥？


1. 某天的 ROI = 该天所有新增用户，从当天到今天的总消费 / 该天广告花费？

每天都要投广告吗？没投广告的，岂不是 / 0 了？

而且，好像看楼主的计算结果，不是这样。。


2. 指定统计起始日期（如 11-02 ），某天的 ROI = 起始日期~当天，所有新增用户的总消费 / 起始日期~当天，所有广告总花费？

[wxf666]

@deweixu @Features 按照 #3 楼的第二种统计方式，用 SQLite 测试了生成整张表、统计整张表（文末附上源码）。结果如下：


日期范围　　新用户数　消费记录数　生成用时　　　统计用时　　　　内存使用
————————————————————————————————————
　３０天　　３００万　２６００万　　３０秒　２．４秒（单线程）　　３ＭＢ
　３０天　３０００万　　２．６亿　３００秒　７．７秒（四线程）　１４ＭＢ


（环境：i5-8250U 轻薄本，Windows 10 。感觉速度和内存占用表现都还可以）


## 数据生成规则（以 30 天内 300W 用户 2600W 消费记录为例）：

1. 每天新增 10W 用户 *（第一天新增 `user_id` 为 `[1, 10W]`，第二天新增 `uid` 为 `[10W+1, 20W]`，……）*
2. `uid` 为 `0` 的是老用户，在起始日期前一天（ 1999-12-31 ）注册 *（用于检查统计时，是否已把老用户数据剔除在外）*
3. 每个用户连续 10 天，每天充值 1 元 *（`uid = 0` 的老用户每天都在充值）*
4. 从第一天开始，每两天投广告 100W 元 *（即，2000-01-01 、2000-01-03 、……）*


## 统计结果预览（以 30 天内 300W 用户 2600W 消费记录为例）：

　　日期　　当天新用户收入　累计新用户收入　累计广告投入　　ＲＯＩ
———————————————————————————————————
０１－０１　　　１０Ｗ　　　　　１０Ｗ　　　　１００Ｗ　　１０．００％
０１－０２　　　２０Ｗ　　　　　３０Ｗ　　　　１００Ｗ　　３０．００％
０１－０３　　　３０Ｗ　　　　　６０Ｗ　　　　２００Ｗ　　３０．００％
０１－０４　　　４０Ｗ　　　　１００Ｗ　　　　２００Ｗ　　５０．００％
０１－０５　　　５０Ｗ　　　　１５０Ｗ　　　　３００Ｗ　　５０．００％
０１－０６　　　６０Ｗ　　　　２１０Ｗ　　　　３００Ｗ　　７０．００％
０１－０７　　　７０Ｗ　　　　２８０Ｗ　　　　４００Ｗ　　７０．００％
０１－０８　　　８０Ｗ　　　　３６０Ｗ　　　　４００Ｗ　　９０．００％
０１－０９　　　９０Ｗ　　　　４５０Ｗ　　　　５００Ｗ　　９０．００％
０１－１０　　１００Ｗ　　　　５５０Ｗ　　　　５００Ｗ　１１０．００％
０１－１１　　１００Ｗ　　　　６５０Ｗ　　　　６００Ｗ　１０８．３３％
０１－１２　　１００Ｗ　　　　７５０Ｗ　　　　６００Ｗ　１２５．００％
……
０１－２８　　１００Ｗ　　　２３５０Ｗ　　　１４００Ｗ　１６７．８６％
０１－２９　　１００Ｗ　　　２４５０Ｗ　　　１５００Ｗ　１６３．３３％
０１－３０　　１００Ｗ　　　２５５０Ｗ　　　１５００Ｗ　１７０．００％


## 源码使用方式：

去 SQLite 官网下载个 1 MB 的 sqlite3.exe ，然后保存下面的 SQLite 代码为 main.sql ，然后命令行运行：

```shell
sqlite3.exe data.db < main.sql
```

多线程用到了 Python 。在 sqlite3.exe 生成数据库后，可直接运行


## SQLite 建表和统计（单线程）代码：

*（ V 站排版原因，行首有全角空格）*

```sql
PRAGMA journal_mode = off; -- 取消日志记录。这会输出个 off 。。
PRAGMA synchronous = off; -- 提交写请求给操作系统后，就可继续后续计算

.param init

-- 投资数据生成配置（日期间隔、每次投资额、日期范围）
.param set $INVEST_INTERVAL_DAYS 2
.param set $INVEST_AMOUNT_PER_DAY 1000000
.param set $INVEST_START_DATE "'2000-01-01'"
.param set $INVEST_END_DATE "'2000-01-30'"

-- 用户消费数据生成配置（消费天数、每日新增用户数、日期范围）
.param set $CONSUME_DAYS 10
.param set $DAILY_NEW_USERS 100000
.param set $CONSUME_START_DATE "'2000-01-01'"
.param set $CONSUME_END_DATE "'2000-01-30'"

-- 查询数据配置
.param set $QUERY_START_DATE "'2000-01-01'"
.param set $QUERY_END_DATE "'2000-01-30'"


-- 建表：投资表
CREATE TABLE invest (
　　 date 　 DATE PRIMARY KEY,
　　 amount INT
);

-- 建表：消费记录表
CREATE TABLE consume (
　　 uid 　　　 INT,
　　 date 　　 DATE,
　　 reg_date DATE,
　　 amount 　 INT,
　　 PRIMARY KEY (date, reg_date, uid)
) WITHOUT ROWID;


-- 添加投资数据：在指定日期范围内，每 INVEST_INTERVAL_DAYS 天投 INVEST_AMOUNT_PER_DAY 元
INSERT INTO invest (date, amount)
SELECT day.value, $INVEST_AMOUNT_PER_DAY
　 FROM generate_series(unixepoch($INVEST_START_DATE) / 86400, unixepoch($INVEST_END_DATE) / 86400, $INVEST_INTERVAL_DAYS) day;

-- 添加消费记录
INSERT INTO consume (amount, uid, date, reg_date)

-- 1. 从起始日期前一天开始，user_id = 0 的老用户，每天消费 1 元，直至结束日期
SELECT 1, 0, date.value, unixepoch($CONSUME_START_DATE, '-1 day') / 86400
　 FROM generate_series(unixepoch($CONSUME_START_DATE, '-1 day') / 86400, unixepoch($CONSUME_END_DATE) / 86400) date
UNION ALL

-- 2. 在指定日期范围内，每天有 DAILY_NEW_USERS 名新用户，连续 CONSUME_DAYS 天消费 1 元
SELECT 1,
　　　 user.value,
　　　 unixepoch($CONSUME_START_DATE, (day.value - 1) || ' days') / 86400,
　　　 unixepoch($CONSUME_START_DATE, ((user.value - 1) / $DAILY_NEW_USERS) || ' days') / 86400
　 FROM generate_series(1, (unixepoch($CONSUME_END_DATE) - unixepoch($CONSUME_START_DATE)) / 86400 + 1) day
　 JOIN generate_series(MAX(0, day.value - $CONSUME_DAYS) * $DAILY_NEW_USERS + 1, day.value * $DAILY_NEW_USERS) user;


-- 统计：指定日期范围内，新用户投资回报率
-- （ user_id = 0 的用户，在起始日期前一天注册，是老用户，故不会统计）
WITH
　-- 每日新用户当天收入表
　 daily(date, income) AS (
　　 SELECT date, SUM(amount)
　　　 FROM consume
　　 WHERE reg_date BETWEEN unixepoch($QUERY_START_DATE) / 86400 AND unixepoch($QUERY_END_DATE) / 86400
　　 GROUP BY date
　)

SELECT date(daily.date * 86400, 'unixepoch') 日期,
　　　 income 当天新用户收入,
　　　 SUM(income) OVER win 累计新用户收入,
　　　 SUM(invest.amount) 累计广告投入,
　　　 FORMAT('%.2f%%', SUM(income) OVER win * 100.0 / SUM(invest.amount)) ROI
　 FROM daily
　 LEFT JOIN invest ON invest.date BETWEEN unixepoch($QUERY_START_DATE) / 86400 AND daily.date
GROUP BY daily.date
WINDOW win AS (ORDER BY daily.date);
```


## Python 多线程统计代码：

*（ V 站排版原因，行首有全角空格）*

```python
import time
import sqlite3
from contextlib import closing
from datetime import date, timedelta
from concurrent.futures import ThreadPoolExecutor

THREADS = 4 # 线程数
DB_FILE = 'data.db' # 数据库路径地址
QUERY_START_DATE = '2000-01-01'
QUERY_END_DATE = '2000-01-30'


def sub(days):
　 with closing(sqlite3.connect(DB_FILE)) as db:
　　 return db.execute('''
　　　 SELECT date, SUM(amount)
　　　 FROM consume
　　　 WHERE date = strftime('%s', ?) / 86400
　　　　 AND reg_date BETWEEN strftime('%s', ?) / 86400 AND strftime('%s', ?) / 86400
　　''', [
　　　 str(date.fromisoformat(QUERY_START_DATE) + timedelta(days=days)),
　　　 QUERY_START_DATE,
　　　 QUERY_END_DATE,
　　]).fetchone()


def main():
　 with closing(sqlite3.connect(DB_FILE)) as db, ThreadPoolExecutor(max_workers=THREADS) as executor:

　　 begin = time.time()
　　 data = list(executor.map(sub, range((date.fromisoformat(QUERY_END_DATE) - date.fromisoformat(QUERY_START_DATE)).days + 1)))

　　 db.execute('CREATE TEMP TABLE daily (date DATE PRIMARY KEY, income INT)')
　　 db.executemany('INSERT INTO daily VALUES (?, ?)', data)
　　 cursor = db.execute('''
　　　 SELECT date(daily.date * 86400, 'unixepoch') 日期,
　　　　　　 income 当天新用户收入,
　　　　　　 SUM(income) OVER win 累计新用户收入,
　　　　　　 SUM(invest.amount) 累计广告投入,
　　　　　　 PRINTF('%.2f%%', SUM(income) OVER win * 100.0 / SUM(invest.amount)) ROI
　　　　 FROM daily
　　　　 LEFT JOIN invest ON invest.date BETWEEN strftime('%s', ?) / 86400 AND daily.date
　　　 GROUP BY daily.date
　　　 WINDOW win AS (ORDER BY daily.date)
　　''', [QUERY_START_DATE])

　　 print(
　　　 f'Finished in {time.time() - begin:.2f} sec. Result:',
　　　[col[0] for col in cursor.description],
　　　*cursor,
　　　 sep='\n',
　　)


if __name__ == '__main__':
　 main()
```

[wxf666]

@Features 数据库新手请教一下，大佬怎么看待这种观点：

> 数据库，只能用自增主键。业务逻辑字段不能做主键，最多只能加索引

[Features]

@wxf666

“ 数据库，只能用自增主键”
这个应该说的是 MySQL ，其他数据库比如 SQL server 一般是用 uuid

"业务逻辑字段不能做主键，最多只能加索引"
主键的意思是记录的 id 值，业务字段比如 username ，member_id,user_id 都不适合表示记录的 id 值，所以不适合用作为主键

[wxf666]

@Features 可我觉得，有些业务字段做主键，可以极大提升数据库速度诶。。

比如 4 楼的『消费记录表』，使用 `(消费日期、用户注册日期、用户 ID)` 做主键，统计 2.6 亿条消费数据的 ROI ，也只需几秒钟（得益于大量的顺序读取）

如果用自增主键 /uuid/……，我不敢想象要多久才能统计完（因为要 2.6 亿次 `eq_ref` 级的 `WHERE id = ?`）

[Features]

@wxf666 你说的那些业务字段不能做主键，只是一些人为定的规范，你不一定要遵守的

[wxf666]

@Features 可我看一些帖子（比如 [这个帖子]( /t/654133 )），不用自增 /uuid/……，而用业务主键，简直是要被铺天盖地的教训和嘲讽淹没。。比如：

1. 工作中被同事打
2. 大学生毕业设计
3. 小学生设计
4. 没有经验胡乱设计
5. B+ 树随机插入，导致页分裂严重，导致性能很低
6. 阿里巴巴《 Java 开发手册》[强制]规定……
7. 合并表时用 uuid 很轻松

我很怀疑第 5 条：虽然聚集表是能顺序插入了，但索引也要随机插入，也会导致页分裂呀？而且总体工作量不是更大了（还要额外维护一个自增主键 /uuid/……）？

比较认同的是第 7 条

[Features]

@wxf666 我也不是大佬，但是我理解的是主键就是记录的 id ，这个是无可替代的

如果你用一个或多个业务字段做主键，那某天这个业务字段废弃了
只能填充默认值或 NULL ，那你所有的记录是不是都废了？

[wxf666]

@Features 所以，一招『自增 /uuid/……主键 + 业务字段加索引』吃天下，应该是没问题的？

#4 楼的 `SQL` 代码，改成自增主键 + `(date, reg_date, uid, amount)` 覆盖索引，应该也能很快


但，如果表结构有几个 `text` 字段（反正导致没法添加到覆盖索引里，一定要回表），会为了速度 /性能，牺牲这个原则，改为 `(date, reg_date, uid)` 主键吗？（按 4 楼例子说，可享受 30 次 `range` 级速度）

还是继续坚持原则，各种分库分表分布式大数据一通上？（ 2.6 亿次 `eq_ref` 级速度）

[deweixu]

感谢提供思路