Вычислить прочитанные страницы с помощью запроса выбора в таблице хода чтения
У меня есть небольшая программа, которую я использую для отслеживания своего прогресса в чтении книг и таких вещей, как goodreads, чтобы знать, сколько я читаю в день.
Для этого я создал две таблицы: tbl_materials(material_id int, name varchar), tbl_progress(date_of_update timestamp, material_id int external 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-09-22 я прочитал 1 страницу, потом еще 1 страницу, так что всего прочитано за этот день = 2 (только для material_id = 4)
в 2017-09-25 последнее обновление для material_id 4 составляет 34 страницы, что означает, что я прочитал 34-1 = 33 страницы (последнее обновление в этот день 34 - последнее обновление до этой даты 1) = 33
до сих пор все работает хорошо, но когда дело доходит до учета пропущенных страниц, я, например, не мог этого сделать:
в 2017-10-18 последнее количество прочитанных страниц для material_id = 4 было 34 (в 2017-09-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 |
+-----------------------------+
Также какую версию MySQL вы используете? SELECT VERSION()
Ответы 2
Это кажется супер-запутанным способом оценки страниц, прочитанных за день. Рассматривали ли вы возможность немного денормализации ваших данных и сохранения как текущей страницы, так и количества прочитанных страниц?
Текущая страница может быть более удобной для хранения в таблице материалов или в отдельной таблице закладок, например.
bookmark- id, material_id, page_numberreading- id, bookmark_id, pages_complete, was_skiped, 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
Вы можете создать представление, в котором используются те же столбцы прогресса таблицы + другой производный столбец, который использует ту же идею, что и предложенная @Arth (столбец pages_completed)
Этот столбец будет содержать текущие completed_pages - завершенные_страницы с последним обновлением до первых завершенных страниц, что является разницей.
Так, например, если ваша таблица прогресса выглядит так:
+-------------+------------+---------+---------------------+
| material_id | read_pages | skipped | last_update |
+-------------+------------+---------+---------------------+
| 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 |
+-------------+------------+---------+---------------------+
мы добавим еще один производный столбец с именем diff.
где разница read_pages в 2017-10-18 02:33:04 - read_pages непосредственно перед 2017-10-18 02:33:04
+-------------+------------+---------+---------------------+------------------+
| material_id | read_pages | skipped | last_update | Derived_col_diff |
+-------------+------------+---------+---------------------+------------------+
| | 68 | T | 2017-10-18T02:33:04 | 68 - null = 0 |
| 4 | | | | |
+-------------+------------+---------+---------------------+------------------+
| 4 | 72 | | 2017-10-18T03:50:51 | 72 - 68 = 4 |
+-------------+------------+---------+---------------------+------------------+
| 2 | 3 | | 2017-10-18T15:02:46 | 3 - null = 0 |
+-------------+------------+---------+---------------------+------------------+
| 2 | 5 | | 2017-10-18T15:10:46 | 5 - 3 = 2 |
+-------------+------------+---------+---------------------+------------------+
| 4 | 82 | | 2017-10-18T16:18:03 | 82 - 72 = 10 |
+-------------+------------+---------+---------------------+------------------+
примечание: это 68 - null равно нулю, но я поставил 0 для уточнения
Производный столбец здесь — это разница между этими read_pages и read_pages непосредственно перед этими read_pages.
Вот вид
create view v_progesses_with_read_pages as
select s0.*,
completed_pieces - ifnull((select completed_pieces
from progresses s1
where s1.material_id = s0.material_id
and s1.last_update = (
select max(last_update)
FROM progresses s2
where s2.material_id = s1.material_id and s2.last_update < s0.last_update
)), 0) read_pages
from progresses s0;
Затем вы можете выбрать сумму этого производного столбца за день:
select date (last_update) dl, sum(read_pages) totalReadsInThisDay from v_progesses_with_read_pages where skipped = false group by dl;
Что приведет к чему-то вроде этого:
+-------------+-----------------------------+
| material_id | totalReadsInThisDay |
+-------------+-----------------------------+
| 2017-10-18 | 16 |
+-------------+-----------------------------+
| 2017-10-19 | 20 (just for clarification) |
+-------------+-----------------------------+
Обратите внимание, что последняя строка взята из моей головы, лол.
Можете ли вы также сделать текстовую таблицу ascii для ожидаемых результатов? Поскольку это легче проверить, чем текстовое объяснение. Вот это легко сделать инструмент.