比如一个游戏在
11.2 日的广告买量花费是 100 元，那么 11.2 的新增用户的内购付费就是收入
ROI 就是计算 11.2 新增用户在未来付费和 成本花费的比值
比如
11.2 用户内购 10 元 当天的 ROI 就是 10%
11.3 用户内购 10 元 ROI 就是 20%
11.4 20 40%
这种需求怎么做好一点
想过把每天用户的付费 和 用户创建时间 都存到 mysql 表中，然后代码计算
有更好的方式吗？
以前没做过这类报表的需求, 谢谢大家
 |
1
wxf666 Nov 2, 2022
有没有啥表结构和数据？数据库新手想试试，能不能用 SQL 解决
|
 |
2
Features Nov 2, 2022
全新开发，没有存量数据，如果是我做的话
创建一个 ROI 表，表的字段 buy_day | member_id | buy_sum | roi 注意 buy_day+member_id 是 unique index 每次用户付费，就查询该用户该用户的历史信息，并根据查询结果更新 roi 如果有存量数据，就先写一个脚本生成历史的 roi 记录 这样查询的成本会低一点吧？ 单纯用 SQL 的话，后期量大了优化起来就难了 |
|
3
wxf666 Nov 2, 2022
|
|
4
wxf666 Nov 3, 2022
@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() ``` |
|
6
Features Nov 4, 2022
@wxf666
“ 数据库，只能用自增主键” 这个应该说的是 MySQL ，其他数据库比如 SQL server 一般是用 uuid "业务逻辑字段不能做主键，最多只能加索引" 主键的意思是记录的 id 值，业务字段比如 username ，member_id,user_id 都不适合表示记录的 id 值，所以不适合用作为主键 |
|
7
wxf666 Nov 4, 2022
@Features 可我觉得，有些业务字段做主键，可以极大提升数据库速度诶。。
比如 4 楼的『消费记录表』，使用 `(消费日期、用户注册日期、用户 ID)` 做主键，统计 2.6 亿条消费数据的 ROI ，也只需几秒钟（得益于大量的顺序读取） 如果用自增主键 /uuid/……，我不敢想象要多久才能统计完（因为要 2.6 亿次 `eq_ref` 级的 `WHERE id = ?`） |
|
9
wxf666 Nov 4, 2022
|
|
10
Features Nov 4, 2022
@wxf666 我也不是大佬，但是我理解的是主键就是记录的 id ，这个是无可替代的
如果你用一个或多个业务字段做主键，那某天这个业务字段废弃了 只能填充默认值或 NULL ，那你所有的记录是不是都废了？ |
|
11
wxf666 Nov 4, 2022
@Features 所以，一招『自增 /uuid/……主键 + 业务字段加索引』吃天下，应该是没问题的？
#4 楼的 `SQL` 代码，改成自增主键 + `(date, reg_date, uid, amount)` 覆盖索引，应该也能很快 但，如果表结构有几个 `text` 字段（反正导致没法添加到覆盖索引里，一定要回表），会为了速度 /性能，牺牲这个原则，改为 `(date, reg_date, uid)` 主键吗？（按 4 楼例子说，可享受 30 次 `range` 级速度） 还是继续坚持原则，各种分库分表分布式大数据一通上？（ 2.6 亿次 `eq_ref` 级速度） |
 |
12
deweixu OP 感谢提供思路
|
Select Language