使用SQLite制作简单报表

撰写于2025年07月12日。

……久しぶりw

以前有些想记录的内容（数据库的GROUPING SETS之类），但由于没有趁手的数据库，没法实际测试，所以就一直搁置到现在。不过现在我连PostgreSQL都没配好，但又想趁早记录，所以就搭了一下SQLite的环境做出点样子来。

那么，现在有这样的需求：做一个表，以日期为单位记录收入，需要输出的报表是月度的，按星期做出收入的小计，按月份做出收入的合计。该怎样用SQLite实现呢？

环境配置

在Windows 11上用pwsh看看：

> sqlite3 --version
3.50.2 2025-06-28 14:00:48 ...

创建数据库和表

简单做一下就行。id为自增主键，proc_datetime为日期，income为收入（假设为日元，所以用INTEGER即可）。至于那个日期，SQLite并没有DATETIME类型，存TEXT类型即可。由于项目比较简单，我就不在create文里写注释了。

PS C:\prj\sqlite_test> sqlite3 test.db
SQLite version 3.50.2 2025-06-28 14:00:48
Enter ".help" for usage hints.
sqlite> CREATE TABLE test_1 (
(x1...>   id INTEGER PRIMARY KEY ASC
(x1...>   , proc_datetime TEXT NOT NULL
(x1...>   , income INTEGER DEFAULT 0
(x1...> ) STRICT;
sqlite> .quit

放入mock数据

总之结果如下：

使用了类似如下的insert文（不写自增主键）：

INSERT INTO test_1 (proc_datetime, income) VALUES ('2025-02-16', 6300);

执行查询语句，获取结果集

格式有些乱，凑合看：

WITH RECURSIVE dt_seq (dt) AS (
  SELECT date('2025-01-01')
  UNION ALL
  SELECT date(dt, '+1 day') FROM dt_seq WHERE dt < date('2025-02-01', '-1 day')
)
, test_1_summed AS (
  SELECT proc_datetime, sum(income) income_summed FROM test_1 GROUP BY proc_datetime
)
, test_1_joined AS (
  SELECT strftime('%Y %W', dt_seq.dt) week_of_year, dt_seq.dt datetime, test_1_summed.income_summed FROM dt_seq
  LEFT JOIN test_1_summed ON test_1_summed.proc_datetime = dt_seq.dt
)
SELECT week_of_year, datetime, income_summed FROM test_1_joined
UNION ALL
SELECT week_of_year, NULL, sum(income_summed) FROM test_1_joined GROUP BY week_of_year
UNION ALL
SELECT NULL, NULL, sum(income_summed) FROM test_1_joined
ORDER BY week_of_year NULLS LAST, datetime NULLS LAST
;

简单说明一下：

• dt_seq：此为一个递归CTE。罗列出从2025-01-01到2025-01-31的日期。
  很有趣的点是，dt应该小于1月的最后一天，原因是尽管在where句判定时用的是dt，但应该表示的结果却是dt的次日。

• test_1_summed：根据日期对收入求和（其实看不出来，毕竟没有同日期的数据。在此最重要的是将日期唯一化）。

• test_1_joined：将上述两个CTE连接起来，并记录年份和星期（列week_of_year，使用的是%Y %W格式。原本想用更合适的ISO 8601的%G %V格式，奈何我这边暂且不能使用，原因不明）。

• 最后的几个UNION ALL及ORDER BY：重申，由于SQLite没有GROUPING SETS功能，只好手动GROUP BY再连起来；说来有趣，现在使用的SQLite版本有判定排序时NULL是否在前的设定。
  结果集中以week_of_year分组的（datetime必为NULL）就是星期的小计，week_of_year和datetime两者都必为NULL的就是月度总计了。
  由于条件暂且限制为2025年01月，故不必做另外操作；如果报表输出多个月份的数据，则应该另外添加年月的列再分组求和。
  月度总计或许不太明显，请看week_of_year为2025 01（意义为当年第二周）的情况吧。

嗯，这样大概就好了吧。之后的事情就是些简单的缝缝补补而已。

后记

细想了一下，这个话题还是在去年秋天就想做的。当时实在很讨厌从数据库里抽出一大堆数据，再用Java或是别的什么来做小计合计之类，于是索性尽量在数据库查询时做好。至于这样好不好呢，我觉得还不错吧，毕竟sql文怎么都要写，sql文只稍微复杂了一丁点，而之后处理结果集的逻辑就会简练相当之多。

以后或许会有更多数据库相关的内容吧。那么，再会！

2025-07-15

趁正好有空，赶紧用WSL搭一下PostgreSQL的环境玩一下。搭建环境参考这篇文章：WSL（AlmaLinux）でPostgreSQL環境を構築する

创建表如下：

CREATE TABLE test_1 (
  id SERIAL PRIMARY KEY
  , proc_date DATE NOT NULL
  , income INTEGER DEFAULT 0
);

之前想处理处理时分秒的，但现在看来没必要，所以这里就直接使用DATE类型。

放入的数据和之前一样。

执行的SQL文如下：

WITH RECURSIVE date_seq(proc_date) AS (
  SELECT date ('2025-01-01')
  UNION ALL
  SELECT proc_date + 1 FROM date_seq WHERE proc_date < date ('2025-02-01') - 1
)
, test_1_summed AS (
  SELECT proc_date, sum(income) income_summed FROM test_1 GROUP BY proc_date
)
, test_1_joined AS (
  SELECT to_char(date_seq.proc_date, 'IYYY IW') week_of_year, date_seq.proc_date, test_1_summed.income_summed FROM date_seq
  LEFT JOIN test_1_summed ON test_1_summed.proc_date = date_seq.proc_date
)
SELECT week_of_year, proc_date, sum(income_summed) income_summed FROM test_1_joined
-- GROUP BY GROUPING SETS ((), (week_of_year), (proc_date, week_of_year))
GROUP BY ROLLUP (week_of_year, proc_date)
ORDER BY week_of_year NULLS LAST, proc_date NULLS LAST
;

结果和之前一样。当然临时表和字段名改了改。

这里要注意的是GROUP BY的时候使用了ROLLUP，其结果等价于上面被注释掉的GROUPING SETS那些。建议从GROUPING SETS那边理解：每一对括号内的字段作为聚集计算的指标来处理。结合上面SQLite的例子理解就好。

一点小细节：这里的week_of_year和SQLite例子不同，PostgreSQL例子的月份序数是从01开始的，与SQLite例子的00不同。实际使用时注意一下就好，孰优孰劣不论，可控就好。