时间戳

首页 > 解决方案 > 阅读进度表中通过select查询计算阅读页数

mysql - 阅读进度表中通过select查询计算阅读页数

问题描述

我有一个小程序，我用它来跟踪我在阅读书籍和诸如 goodreads 之类的东西方面的​​进度，以了解我每天阅读了多少。
我为此创建了两个表，tbl_materials(material_id int, name varchar), tbl_progress(date_of_update timestamp, material_id int foreign key, read_pages int, skipped bit)。
每当我阅读一些页面时，我会在 tbl_progress 中插入我已经完成的当前页面，
我可能会在书中多次阅读。如果我跳过了一些页面，我会将它们插入 tbl_progress 并将该位标记skipped为 true。问题是我无法查询 tbl_progress 以了解我每天阅读的内容

我尝试的是在每一天找到每种材料的最后插入进度，例如： +-------------+------------+---------+---------------------+ | material_id | read_pages | skipped | last_update | +-------------+------------+---------+---------------------+ | 4 | 1 | | 2017-09-22 00:56:02 | | 3 | 1 | | 2017-09-22 00:56:14 | | 12 | 1 | | 2017-09-24 20:13:01 | | 4 | 30 | | 2017-09-25 01:56:38 | | 4 | 34 | | 2017-09-25 02:19:47 | | 54 | 1 | | 2017-09-29 04:22:11 | | 59 | 9 | | 2017-10-14 15:25:14 | | 4 | 68 | T | 2017-10-18 02:33:04 | | 4 | 72 | | 2017-10-18 03:50:51 | | 2 | 3 | | 2017-10-18 15:02:46 | | 2 | 5 | | 2017-10-18 15:10:46 | | 4 | 82 | | 2017-10-18 16:18:03 | | 4 | 84 | | 2017-10-20 18:06:40 | | 4 | 87 | | 2017-10-20 19:11:07 | | 4 | 103 | T | 2017-10-21 19:50:29 | | 4 | 104 | | 2017-10-22 19:56:14 | | 4 | 108 | | 2017-10-22 20:08:08 | | 2 | 6 | | 2017-10-23 00:35:45 | | 4 | 111 | | 2017-10-23 02:29:32 | | 4 | 115 | | 2017-10-23 03:06:15 | +-------------+------------+---------+---------------------+ 我计算我每天的总阅读页数 = 这一天的最后一次阅读页 - 在此日期之前的某个日期的最后一次阅读页和这可行，但问题是我无法避免跳过页面。
2017 年 9 月 22 日的第一行我读了 1 页，然后又读了 1 页，因此在 2017 年 9 月 25 日，这一天的总阅读量 = 2（仅材料 ID = 4）
，材料 ID 4 的最后更新是 34 页，这意味着我读了 34-1 = 33 页（今天的最后更新 34 - 此日期之前的最后更新 1）= 33
到现在一切正常，但是在考虑跳过的页面时，我不能这样做，例如：
在 2017 年 10 月 18 日，material_id = 4 的最后阅读页数为 34（2017 年 9 月 25 日），然后我跳过了 34 页，现在当前页为 68，然后阅读了 4 页（2017 年 10 月 18 日 03： 50:51 ）然后是另外 10 页（2017-10-18 16:18:03）所以 material_id = 4 的总数是 14

我创建了一个视图来为每天的每本书选择最近的 last_update

create view v_mostRecentPerDay as
select material_id                                                    id,
       (select title from materials where materials.material_id = id) title,
       completed_pieces,
       last_update,
       date(last_update)                                              dl,
       skipped
from progresses
where last_update = (
    select max(last_update)
    from progresses s2
    where s2.material_id = progresses.material_id
      and date(s2.last_update) = date(progresses.last_update)
      and s2.skipped = false
);

因此，如果一天内一本书有很多更新，则此视图会检索最后一本书（最大为 last_update），它伴随着最大数量的阅读页数，因此对于每本书和另一个视图，以获取每次阅读的总页数天：

create view v_totalReadInDay as
select dl, sum(diff) totalReadsInThisDay
from (
         select dl,
                completed_pieces - ifnull((select completed_pieces
                                           from progresses
                                           where material_id = id
                                             and date(progresses.last_update) < dl
                                           ORDER BY last_update desc
                                           limit 1
                                          ), 0) diff
         from v_mostRecentPerDay
         where skipped = false
     ) omda
group by dl;

但问题是最后一个视图计算跳过的页面。
预期结果：

+------------+------------------+
| day        | total_read_pages |
+------------+------------------+
| 2017-09-22 | 2                |
+------------+------------------+
| 2017-09-24 | 1                |
+------------+------------------+
| 2017-09-25 | 33               |
+------------+------------------+
| 2017-09-29 | 1                |
+------------+------------------+
| 2017-10-14 | 9                |
+------------+------------------+
| 2017-10-18 | 19               |
+------------+------------------+
| 2017-10-20 | 5                |
+------------+------------------+
| 2017-10-21 | 0                |
+------------+------------------+
| 2017-10-22 | 21               |
+------------+------------------+
| 2017-10-23 | 8                |
+------------+------------------+

mysql> SELECT VERSION();
+-----------------------------+
| VERSION()                   |
+-----------------------------+
| 5.7.26-0ubuntu0.16.04.1-log |
+-----------------------------+

标签： mysqlsqldatabase

解决方案

这似乎是评估每天阅读的页面的一种非常复杂的方式。您是否考虑过对数据进行轻微的非规范化并同时存储当前页面和读取的页面数？

将当前页面存储在材料表或单独的书签表中可能更有意义，例如

  • bookmark- id、material_id、page_number
  • reading- id、bookmark_id、pages_complete、was_skipped、end_at

当阅读（或跳过！）会话完成时，可以轻松地从当前页面减去书签中的旧当前页面计算 pages_complete，这可以在您的应用程序逻辑中完成

您每天查询的页面数就变成了

SELECT SUM(pages_complete) pages_read
  FROM reading
 WHERE ended_at >= :day
   AND ended_at < :day + INTERVAL 1 DAY
   AND was_skipped IS NOT TRUE

推荐阅读