超级个体的技术底座：用 SQLite 搭一人经营的交付台账

CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at TEXT NOT NULL,                  -- 接到需求的日期 YYYY-MM-DD
    channel TEXT NOT NULL,                     -- 需求来源渠道：老客介绍/内容平台/行业社群
    deliverable TEXT NOT NULL,                 -- 交付物类型：设计稿/咨询报告/课程内容
    status TEXT NOT NULL DEFAULT '进行中',      -- 进行中/已交付/已验收
    delivered_at TEXT,                         -- 交付日期
    rework_rounds INTEGER DEFAULT 0,           -- 修改轮次
    is_repeat INTEGER DEFAULT 0                -- 是否复购 1/0
);

"""每周复盘：按渠道统计交付量、平均耗时、修改轮次与复购情况。"""
import sqlite3
from datetime import date, timedelta


def weekly_report(db="ledger.db"):
    conn = sqlite3.connect(db)
    since = (date.today() - timedelta(days=7)).isoformat()
    rows = conn.execute(
        """SELECT channel,
                  COUNT(*),
                  ROUND(AVG(JULIANDAY(delivered_at) - JULIANDAY(created_at)), 1),
                  ROUND(AVG(rework_rounds), 1),
                  SUM(is_repeat)
           FROM orders
           WHERE created_at >= ? AND status = '已验收'
           GROUP BY channel""",
        (since,),
    ).fetchall()
    print(f"近 7 天（自 {since} 起）")
    for channel, n, avg_days, avg_rework, repeats in rows:
        print(f"{channel}：交付 {n} 单，平均耗时 {avg_days} 天，"
              f"平均修改 {avg_rework} 轮，复购 {repeats} 单")
    conn.close()


if __name__ == "__main__":
    weekly_report()