Building personal budgets and bills database against consumerism

当前市面上的记账工具都只是记录了花钱与消费，而并没有关注这笔钱花的到底值不值。
本文提出了一种价值的度量，即单位时间价值=总消费/总有效时间。因为消费行为具有瞬时性，而使用行为具有延时性。需要数据库建立这两个表格。

消费时刻表

item

这个是 ID
表示的是消费项，比如 “iPhone 5”

说明：比如租房，毕业前暑假租房item和毕业后租房item属于不同的item

category

所属类别，比如 “通讯工具” 或 “电子产品”

description

expense

消费金额

moment

消费发生的时间

使用时间表

item-id

链接到 item

description

check-point

记录时的实际有效使用时间。

也可再细分为轻度使用、中度使用和重度使用。

后期考虑加入通货膨胀率或居民物价增长指数

check flag

0: normal
1: end

Mariadb installation

sudo pacman -Syu mariadb
su
mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

to install MariaDB/MySQL system tables in /var/lib/mysql

Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to be system ‘root’ user to connect. Use, for example, sudo mysql.
The second is mysql@localhost, it has no password either, but you need to be the system ‘mysql’ user to connect.
After connecting you can set the password, if you would need to be able to connect as any of these users with a password and without sudo.

You can start the MariaDB daemon with:
cd ‘/usr’ ; /usr/bin/mysqld_safe –datadir=’/var/lib/mysql’

You can test the MariaDB daemon with mysql-test-run.pl
cd ‘/usr/mysql-test’ ; perl mysql-test-run.pl

systemctl enable mariadb.service
systemctl start mariadb.service
mysql

create user 'wsd'@'localhost' identified by '190701';
create database consumption;
grant all privileges on consumption.* to 'wsd'@'localhost';
flush privileges;
quit;

then you can use common to login

mysql [-u wsd] -p # 如果当前用户就是wsd则中括号内容可忽略

Selecting a database

use consumption

Access using Apache phpMyAdmin

sudo pacman -Syu php apache php-apache phpmyadmin

Make sure php can be run on Apache:
https://wiki.archlinux.org/index.php/Apache_HTTP_Server#PHP

Then add phpmyadmin to Apche:
https://wiki.archlinux.org/index.php/PhpMyAdmin#Apache

Create a table

CREATE TABLE `consumption`.`ItemInfo` ( `item` VARCHAR(100) NOT NULL , `category` CHAR(1) NOT NULL , PRIMARY KEY (`item`(100))) ENGINE = InnoDB COMMENT = 'Table recording items information';
CREATE TABLE `consumption`.`SpendingMoment` ( `id` INT NOT NULL AUTO_INCREMENT , `item_name` VARCHAR(100) NOT NULL , `category` CHAR(1) NOT NULL , `description` TEXT NULL , `expense` DECIMAL(10, 2) NOT NULL , `moment` DATE NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB COMMENT = 'Table logging the moments of expenses';

Insert item

INSERT INTO `UsageDuration` (`id`, `item_name`, `description`, `effective_time`, `check_time`) VALUES (NULL, 'renting room', '9月住了5天', '120:00:00.000000', '2019-09-09');

Query

1. select item_name from item_info
2. select sum(hour(effective_time)) from UsageDuration where item_name="1."
3. select sum(expense) from SpendingMoment where item_name="1."
4. 3./2. where item_name=1.

5. select sum(4.) from

一些SQL技巧

实现列的累加功能

MariaDB [test]> select * from exam;
+-------+-----+
| grade | num |
+-------+-----+
| A     |   2 |
| B     |   4 |
| C     |   2 |
| D     |   6 |
+-------+-----+

MariaDB [test]> select row_number() over(order by grade) row_num,
    -> grade,
    -> sum(num) over(order by grade)
    -> from exam
    -> group by grade;
+---------+-------+-------------------------------+
| row_num | grade | sum(num) over(order by grade) |
+---------+-------+-------------------------------+
|       1 | A     |                             2 |
|       2 | B     |                             6 |
|       3 | C     |                             8 |
|       4 | D     |                            14 |
+---------+-------+-------------------------------+

JDBC

After installing mariadb-jdbc from aur,

sudo ln -s /usr/share/java/mariadb-jdbc/mariadb-java-client.jar /usr/lib/jvm/default-runtime/lib/

Data Analizing

主要绘制一张表和一张图
表是在某个时间点下各 item 与其 单位有效时间花费
图是在某个时间点下各 大类的 总单位有效时间花费

参考文章：

1. （超详细一文看懂）MySQL累计求和问题及窗口函数order by的原理(https://blog.csdn.net/dhr223/article/details/107413344)