# -*- coding: utf-8 -*-
"""只操作本程序创建的临时对象；全部数据为合成样本。"""
import os, sys, json, sqlite3, tempfile, pathlib, subprocess, time, socket, stat
from pathlib import Path
def emit(**kw):
    print(json.dumps(kw, ensure_ascii=False, sort_keys=True))
assert sqlite3.sqlite_version_info >= (3,25,0), '窗口函数要求实际SQLite至少3.25'
c=sqlite3.connect(':memory:')
c.executescript("CREATE TABLE calls(tenant TEXT,id INTEGER,t INTEGER,n INTEGER); INSERT INTO calls VALUES('A',1,1,2),('A',2,1,3),('A',3,2,4),('B',4,1,7);")
range_rows=c.execute('SELECT tenant,id,sum(n) OVER(PARTITION BY tenant ORDER BY t) FROM calls ORDER BY tenant,id').fetchall()
rows=c.execute('SELECT tenant,id,sum(n) OVER(PARTITION BY tenant ORDER BY t,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM calls ORDER BY tenant,id').fetchall()
assert [x[2] for x in range_rows]==[5,5,9,7]
assert [x[2] for x in rows]==[2,5,9,7]
c.execute("INSERT INTO calls VALUES('A',5,3,-1)")
refund=c.execute("SELECT sum(n) FROM calls WHERE tenant='A'").fetchone()[0]
assert refund==8
emit(sqlite_version=sqlite3.sqlite_version,range_totals=[x[2] for x in range_rows],row_totals=[x[2] for x in rows],refund_total=refund)