V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX  ›  wxf666  ›  全部回复第 8 页 / 共 25 页
回复总数  483
1 ... 4  5  6  7  8  9  10  11  12  13 ... 25  
2022-11-04 01:28:30 +08:00
回复了 deweixu 创建的主题 程序员 想问问大家 ROI 报表怎么实现的?
@Features 可我看一些帖子(比如 [这个帖子]( /t/654133 )),不用自增 /uuid/……,而用业务主键,简直是要被铺天盖地的教训和嘲讽淹没。。比如:

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

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

比较认同的是第 7 条
2022-11-04 01:05:25 +08:00
回复了 deweixu 创建的主题 程序员 想问问大家 ROI 报表怎么实现的?
@Features 可我觉得,有些业务字段做主键,可以极大提升数据库速度诶。。

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

如果用自增主键 /uuid/……,我不敢想象要多久才能统计完(因为要 2.6 亿次 `eq_ref` 级的 `WHERE id = ?`)
2022-11-03 23:32:50 +08:00
回复了 maosu 创建的主题 Linux V 友们,请教个提取两个字符串中的语句并加引号的 sed 写法
```shell
$ sed "s/dare/'&'/" <<<'howdareyou'
how'dare'you
```
2022-11-03 23:30:44 +08:00
回复了 deweixu 创建的主题 程序员 想问问大家 ROI 报表怎么实现的?
@Features 数据库新手请教一下,大佬怎么看待这种观点:

> 数据库,只能用自增主键。业务逻辑字段不能做主键,最多只能加索引
2022-11-03 23:04:15 +08:00
回复了 deweixu 创建的主题 程序员 想问问大家 ROI 报表怎么实现的?
@deweixu @Features 按照 #3 楼的第二种统计方式,用 SQLite 测试了生成整张表、统计整张表(文末附上源码)。结果如下:


日期范围  新用户数 消费记录数 生成用时   统计用时    内存使用
————————————————————————————————————
 30天  300万 2600万  30秒 2.4秒(单线程)  3MB
 30天 3000万  2.6亿 300秒 7.7秒(四线程) 14MB


(环境: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 消费记录为例):

  日期  当天新用户收入 累计新用户收入 累计广告投入  ROI
———————————————————————————————————
01-01   10W     10W    100W  10.00%
01-02   20W     30W    100W  30.00%
01-03   30W     60W    200W  30.00%
01-04   40W    100W    200W  50.00%
01-05   50W    150W    300W  50.00%
01-06   60W    210W    300W  70.00%
01-07   70W    280W    400W  70.00%
01-08   80W    360W    400W  90.00%
01-09   90W    450W    500W  90.00%
01-10  100W    550W    500W 110.00%
01-11  100W    650W    600W 108.33%
01-12  100W    750W    600W 125.00%
……
01-28  100W   2350W   1400W 167.86%
01-29  100W   2450W   1500W 163.33%
01-30  100W   2550W   1500W 170.00%


## 源码使用方式:

去 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()
```
2022-11-03 10:34:39 +08:00
回复了 JinTianYi456 创建的主题 MySQL SQL 中 on 条件与 where 条件的区别
反正 SQLite 的 [文档]( https://sqlite.org/lang_select.html#where_clause_filtering_ ) 说过这个问题:

> For a JOIN or INNER JOIN or CROSS JOIN, there is **no difference** between a constraint expression in the WHERE clause and one in the ON clause. However, for a LEFT JOIN or LEFT OUTER JOIN, the difference is very important. ……

我觉得,1 MB 的 SQLite 都能做到无区别,其他数据库肯定至少也可以做到无区别
2022-11-02 23:26:53 +08:00
回复了 sdjl 创建的主题 Linux 请问如何让 ls 命令显示的 “文件夹” 使用斜体?
有意思,学着用了下 LS_COLORS ,下面命令在 Bash 里可以『加粗、下划线、斜体』显示目录

```shell
LS_COLORS='di=1;3;4' ls
```
2022-11-02 22:49:07 +08:00
回复了 deweixu 创建的主题 程序员 想问问大家 ROI 报表怎么实现的?
@deweixu @Features 这个 ROI 计算公式是啥?


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

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

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


2. 指定统计起始日期(如 11-02 ),某天的 ROI = 起始日期~当天,所有新增用户的总消费 / 起始日期~当天,所有广告总花费?
2022-11-02 20:23:33 +08:00
回复了 deweixu 创建的主题 程序员 想问问大家 ROI 报表怎么实现的?
有没有啥表结构和数据?数据库新手想试试,能不能用 SQL 解决
@shade 这种做法。。不就是枚举路径?
2022-11-02 09:58:21 +08:00
回复了 mmm159357456 创建的主题 Python Python 的多层嵌套循环如何优化?
@mmm159357456 像 #48 楼、#52 、#60 那样,编个数据不就好了。。

只要给出的解决方案也能通用到你原始数据上,目的不就达到了。。
如果是要获取所有后代,我倒觉得枚举路径和嵌套集会比这俩更高效

检索目录?全文索引?
@sadhen 硬编码图像和视频??
@sadhen 可以再多写写应用场景吗?还能在哪些方面解决现有编程语言的什么痛点?
2022-11-01 22:26:34 +08:00
回复了 mmm159357456 创建的主题 Python Python 的多层嵌套循环如何优化?
@mmm159357456 我好奇问一下,为啥你不愿意放出原始问题呢?

不怕问成 xy problem ,束缚大家的看问题的角度和解决思路么。。

- 比如,有回 换 Python 其他写法、上协程 /多线程 /多进程、升级 Python 、换 C/C++/Rust 提升三次 for 速度的,

- 有剔除重复数据、剪枝来减少 for 数量空间的

- 还有零星几个回复是改变 pandas 运算方法,改变数据存储结构使得能顺序读取的


万一,根本不用三次 for 呢?(比如,如果真的只是计算滑窗数据的话,真的不用三层 for 。另外,个人觉得,既然你用了 pandas ,就该少让 python 掺和进来,多用 pandas 的方法去整体计算 dataframe )

万一,换种数据存储结构,就能高效读取数据和计算呢?(比如,不用随机读,减少 groupby 、sort 、join 了)

万一,有数学大佬能推出个啥神奇公式,能 O(1) 解决问题了呢?😂
2022-11-01 20:29:50 +08:00
回复了 mmm159357456 创建的主题 Python Python 的多层嵌套循环如何优化?
@mmm159357456 楼主最后还用了啥方法?大概用时多久?占多少内存?
2022-11-01 10:18:38 +08:00
回复了 mmm159357456 创建的主题 Python Python 的多层嵌套循环如何优化?
@specter119 请教一下,像 60 楼那样的数据( 2600W 行数据),spark 计算 4 个不同的滑窗,需要多久?总共要多少内存?
2022-11-01 09:55:29 +08:00
回复了 mmm159357456 创建的主题 Python Python 的多层嵌套循环如何优化?
@mmm159357456 这些都是单线程计算。

如果你是 8 核 CPU ,那可以同时计算 8 张表。

那么 40 张表总共只需不到 1 小时即可完成。



如果你自己转数据(即,用不到 generate_series 表值函数),可以直接在 Python 里用 sqlite 标准库。开个多进程,刷刷刷~
2022-11-01 09:40:06 +08:00
回复了 mmm159357456 创建的主题 Python Python 的多层嵌套循环如何优化?
@mmm159357456 写漏了。。使用方式应该要将结果转成 csv ,再重定向至文件:

```shell
sqlite3.exe -csv data.db < main.sql > result.csv
```
2022-11-01 09:29:59 +08:00
回复了 mmm159357456 创建的主题 Python Python 的多层嵌套循环如何优化?
@mmm159357456 用 SQLite 测试了生成整张表、计算整张表,结果如下:

  项目      结果大小    用时
————————————————————
生成数据库 1.02GB的数据库 2分钟
计算整张表 1.35GB的CSV 7分钟


## 数据库预览( CSV 形式预览。200 年 x 365/366 天 x 360 个经纬度 = 26297640 行):

year,dateday,geometry_x,geometry_y,element1,element2,element3,element4
1900,1900-01-01,0.0,0.0,1,1001,2001,3001
1900,1900-01-02,0.0,0.0,2,1002,2002,3002
1900,1900-01-03,0.0,0.0,3,1003,2003,3003
……
1900,1900-12-29,0.0,0.0,363,1363,2363,3363
1900,1900-12-30,0.0,0.0,364,1364,2364,3364
1900,1900-12-31,0.0,0.0,365,1365,2365,3365
1900,1900-01-01,1.0,-1.0,1,1001,2001,3001
1900,1900-01-02,1.0,-1.0,2,1002,2002,3002
1900,1900-01-03,1.0,-1.0,3,1003,2003,3003
……
1900,1900-12-29,359.0,-359.0,363,1363,2363,3363
1900,1900-12-30,359.0,-359.0,364,1364,2364,3364
1900,1900-12-31,359.0,-359.0,365,1365,2365,3365
1901,1901-01-01,0.0,0.0,1,1001,2001,3001
1901,1901-01-02,0.0,0.0,2,1002,2002,3002
1901,1901-01-03,0.0,0.0,3,1003,2003,3003
……
2099,2099-12-29,359.0,-359.0,363,1363,2363,3363
2099,2099-12-30,359.0,-359.0,364,1364,2364,3364
2099,2099-12-31,359.0,-359.0,365,1365,2365,3365


## 计算结果预览( CSV 文件):

1900,0.0,0.0,1900-01-01,1.0,1001.0,2001.0,3001.0
1900,0.0,0.0,1900-01-02,1.5,1001.5,2001.5,3001.5
1900,0.0,0.0,1900-01-03,2.0,1002.0,2002.0,3002.0
1900,0.0,0.0,1900-01-04,2.5,1002.5,2002.5,3002.5
1900,0.0,0.0,1900-01-05,3.0,1003.0,2003.0,3003.0
1900,0.0,0.0,1900-01-06,4.0,1003.5,2003.5,3004.0
……


## 计算方式预览( CSV 形式。可保存后用 Excel 查看):

1900,0.0,0.0,1900-01-01,(1)/1,(1001)/1,(2001)/1,(3001)/1
1900,0.0,0.0,1900-01-02,(1+2)/2,(1001+1002)/2,(2001+2002)/2,(3001+3002)/2
1900,0.0,0.0,1900-01-03,(1+2+3)/3,(1001+1002+1003)/3,(2001+2002+2003)/3,(3001+3002+3003)/3
1900,0.0,0.0,1900-01-04,(1+2+3+4)/4,(1001+1002+1003+1004)/4,(2001+2002+2003+2004)/4,(3001+3002+3003+3004)/4
1900,0.0,0.0,1900-01-05,(1+2+3+4+5)/5,(1001+1002+1003+1004+1005)/5,(2001+2002+2003+2004+2005)/5,(3001+3002+3003+3004+3005)/5
1900,0.0,0.0,1900-01-06,(2+3+4+5+6)/5,(1001+1002+1003+1004+1005+1006)/6,(2001+2002+2003+2004+2005+2006)/6,(3002+3003+3004+3005+3006)/5
1900,0.0,0.0,1900-01-07,(3+4+5+6+7)/5,(1001+1002+1003+1004+1005+1006+1007)/7,(2001+2002+2003+2004+2005+2006+2007)/7,(3003+3004+3005+3006+3007)/5
1900,0.0,0.0,1900-01-08,(4+5+6+7+8)/5,(1001+1002+1003+1004+1005+1006+1007+1008)/8,(2001+2002+2003+2004+2005+2006+2007+2008)/8,(3004+3005+3006+3007+3008)/5
1900,0.0,0.0,1900-01-09,(5+6+7+8+9)/5,(1001+1002+1003+1004+1005+1006+1007+1008+1009)/9,(2001+2002+2003+2004+2005+2006+2007+2008+2009)/9,(3005+3006+3007+3008+3009)/5
1900,0.0,0.0,1900-01-10,(6+7+8+9+10)/5,(1001+1002+1003+1004+1005+1006+1007+1008+1009+1010)/10,(2001+2002+2003+2004+2005+2006+2007+2008+2009+2010)/10,(3006+3007+3008+3009+3010)/5
1900,0.0,0.0,1900-01-11,(7+8+9+10+11)/5,(1002+1003+1004+1005+1006+1007+1008+1009+1010+1011)/10,(2001+2002+2003+2004+2005+2006+2007+2008+2009+2010+2011)/11,(3007+3008+3009+3010+3011)/5
……
1900,0.0,0.0,1900-12-31,(361+362+363+364+365)/5,(1356+1357+1358+1359+1360+1361+1362+1363+1364+1365)/10,(2351+2352+2353+2354+2355+2356+2357+2358+2359+2360+2361+2362+2363+2364+2365)/15,(3361+3362+3363+3364+3365)/5
1900,1.0,-1.0,1900-01-01,(1)/1,(1001)/1,(2001)/1,(3001)/1
1900,1.0,-1.0,1900-01-02,(1+2)/2,(1001+1002)/2,(2001+2002)/2,(3001+3002)/2
……


## 使用方式:

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

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


## SQLite 代码:

*( V 站排版原因,行首有全角空格)*

```sql
-- PRAGMA journal_mode = off; -- 取消日志记录。但这会输出个 off 。。
PRAGMA synchronous = off; -- 提交写请求给操作系统后,就可继续后续计算
PRAGMA cache_size = -8192; -- 占用 8 MB 内存

-- 建表
CREATE TABLE IF NOT EXISTS data (
   year INT,
   dateday DATE,
   geometry_x REAL,
   geometry_y REAL,
   element1 INT,
   element2 INT,
   element3 INT,
   element4 INT,
   PRIMARY KEY (year, geometry_x, geometry_y, dateday)
) WITHOUT ROWID;

-- 生成数据
INSERT INTO data
SELECT year.value,
    DATE(year.value || '-01-01', day_of_year.value || ' days'),
    area.value * 1.0,
    area.value * -1.0,
    day_of_year.value + 1,
    day_of_year.value + 1001,
    day_of_year.value + 2001,
    day_of_year.value + 3001
  FROM generate_series(1900, 2099) year,
    generate_series(0, STRFTIME('%j', year.value || '-12-31') - 1) day_of_year,
    generate_series(0, 359) area;

-- 计算表
SELECT year,
    geometry_x,
    geometry_y,
    dateday,
    /* -- 下面 4 行用于预览平均值的计算方式对不对
    FORMAT('(%s)/%d', GROUP_CONCAT(element1, '+') OVER win5, COUNT(*) OVER win5),
    FORMAT('(%s)/%d', GROUP_CONCAT(element2, '+') OVER win10, COUNT(*) OVER win10),
    FORMAT('(%s)/%d', GROUP_CONCAT(element3, '+') OVER win15, COUNT(*) OVER win15),
    FORMAT('(%s)/%d', GROUP_CONCAT(element4, '+') OVER win5, COUNT(*) OVER win5)
    */
    AVG(element1) OVER win5,
    AVG(element2) OVER win10,
    AVG(element3) OVER win15,
    AVG(element4) OVER win5
FROM data
WINDOW win AS (PARTITION BY year, geometry_x, geometry_y ORDER BY dateday),
    win5 AS (win ROWS 4 PRECEDING),
    win10 AS (win ROWS 9 PRECEDING),
    win15 AS (win ROWS 14 PRECEDING);
```
1 ... 4  5  6  7  8  9  10  11  12  13 ... 25  
关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5917 人在线   最高记录 6543   ·     Select Language
创意工作者们的社区
World is powered by solitude
VERSION: 3.9.8.5 · 41ms · UTC 02:37 · PVG 10:37 · LAX 19:37 · JFK 22:37
Developed with CodeLauncher
♥ Do have faith in what you're doing.