时间戳

首页 > 解决方案 > 保持更新记录的变化记录

sql - 保持更新记录的变化记录

问题描述

我有一份学生名单和他们的科目：

id | student | subject
---|---------|--------
1  | adam    | math
2  | bob     | english
3  | charlie | math
4  | dan     | english
5  | erik    | math

我从上面的列表中创建了一个统计，汇总了每个学科有多少学生：

id | subject | students
---|---------|--------
1  | math    | 3
2  | english | 2

学生名单将继续扩大，这种汇总将定期进行。

我首先将 Tally 保存在一个单独的表中的原因是因为原始表应该很大（这只是我原始问题的简化），因此查询原始表以获取当前的计数-fly 不够快是不可行的。

无论如何，只要学生不改变他们的主题，聚合就非常简单。

但现在我想添加一个功能，让学生改变他们的主题。

我以前的方法是这样的：在更新 Tally 时，我保留了一个counter变量，直到我已经考虑了哪一行学生。下次我只考虑在该行之后添加的记录。

另外我保留 a 的原因counter是因为学生表很大，我不想每次都扫描整个表，因为它不能很好地扩展。

如果所有学生都是独一无二的并且没有人改变他们的主题，那么它会很好。

但它现在分崩离析，因为我无法再解释计数器之前的行并已更新。

我的第二种方法是使用一个updated_at字段（而不是counter）并以这种方式跟踪新修改的行。

但我仍然不知道如何准确地更新 Tally。

比如说，在上述场景中，Erik 将他的主题从“数学”更改为“英语”。当我运行脚本来更新 Tally 时，虽然它确实找到了新更新的行，但它只是说{"erik": "english"}. 我怎么知道它是从什么改变的？我需要知道这一点才能正确减少 Tally 表中的“数学”，同时增加“英语”。

有没有办法解决这个问题？

再次总结我的问题，我想找到一种方法，能够使用 Student 表中更新/修改的行准确地更新 Tally 表（一个定期运行的过程）。

如果重要的话，我正在使用 NodeJS 和 PostgreSQL。

标签： sqlnode.jsalgorithmdatabase-designaggregate

解决方案

当学生添加主题，删除主题或更改主题时，您为什么不这样做。

  • 学生添加新科目时：增加即可UPDATE tbl_tally SET student = student + 1 WHERE subject = :subject；
  • 当学生删除科目时：减少UPDATE tbl_tally SET student = student - 1 WHERE subject = :subject；
  • 当学生改变科目时：新科目加一，旧科目减一
    • UPDATE tbl_tally SET student = student - 1 WHERE subject = :old_subject;
    • UPDATE tbl_tally SET student = student + 1 WHERE subject = :new_subject;

我不熟悉 PostgreSQL，但在 MySQL 中，您甚至可以使用触发器来完成。我认为 PostgreSQL 也有触发器。

推荐阅读