lemon24
commented
3 years ago My initial idea of how to do this was to calculate the difference between entry publish times, and average that:
Here's a query that doesn't use window functions (they were added to SQLite in 3.25); running it for all the entries in the past year takes 28s. There are also some timings for a window function version; it's much faster.
```sql -- https://stackoverflow.com/a/55779275 with updated_full as ( select coalesce(published, updated, first_updated_epoch) as fu, --first_updated_epoch as fu, id, feed from entries where feed like '%99p%' and fu >= datetime('now', '-12 months') order by fu, id, feed ), updated as ( select count(*) as sequence, current.fu as fu from updated_full as current left join updated_full as others where current.fu >= others.fu group by current.fu, current.id ), updated_skip_one as ( select sequence - 1 as sequence, fu from updated limit -1 offset 1 ), pairs as ( select updated.fu as one, updated_skip_one.fu as two from updated join updated_skip_one using (sequence) ) select avg(strftime('%s', two) - strftime('%s', one)) / (3600 * 24) from pairs; -- whole db 12m (28s): 348.875750942319 kfu (?!), 0.0659692288411146 fu -- 99p 12m (.011s): 6.14506353930461 kfu, 6.28819444444444 fu -- using "select ROW_NUMBER() over () as sequence" in updated instead: -- whole db 12m (~.050s): 0.109319573689652 kfu, 0.0659692288411146 fu -- 99p 12m (.005s): 6.14506353930461 kfu, 6.14540816326531 fu ```Here's a version using a custom aggregate function; timings are comparable to the window function version.
```python from reader import make_reader from datetime import datetime import sqlite3 import time # based on https://github.com/lemon24/reader/blob/0.22/src/reader/core/storage.py#L203-L212 def _datetime_to_s(value): if not value: return None if not isinstance(value, bytes): value = value.encode('utf-8') dt = sqlite3.converters['TIMESTAMP'](value) rv = (dt - datetime(1970, 1, 1)).total_seconds() return rv missing = object() class AvgOfDifferences: def __init__(self): self.prev = missing self.count = 0 self.total = 0 def step(self, value): value = _datetime_to_s(value) if self.prev is missing: self.prev = value return self.count += 1 self.total += (value - self.prev) self.prev = value def finalize(self): return self.total / self.count db = make_reader('db.sqlite')._storage.db db.create_aggregate("avg_of_differences", 1, AvgOfDifferences) start = time.perf_counter() rows = list(db.execute(""" with updated as ( select coalesce(published, updated, first_updated_epoch) as fu --first_updated_epoch as fu from entries where feed like '%99p%' and fu >= datetime('now', '-12 months') order by fu, id, feed ) select avg_of_differences(fu) / (3600 * 24) from updated """)) end = time.perf_counter() print(*rows, end - start) """ -- using python: -- whole db 12m (~.035s): 0.10931957368965244 kfu, 0.06596922992339282 fu -- 99p 12m (.014s): 6.14506353930461 kfu, 6.14540816913313 fu """ ```The custom aggregate version is better, since it's fast, it works now (SQLite 3.15), and it's more flexible (e.g. you can use other statistics function, like mean).
Update: This is overcomplicated. It's way easier to just calculate an average frequency, i.e. occurrences per unit of time; it also solves a few corner cases (e.g. 0 and 1 entries per time window).
I'd like to have a measure of how often a feed gets updated, something like "mean time between updates".
This is useful for e.g. deleting feeds that update too often, or filtering feeds by it.
I'm sure this has been done in other feed readers, it's worth taking a look at how they did it.
Some thoughts:
One corner case we should treat in a useful way is a feed that had weekly updates until a year ago, but then stopped. A way of dealing with this is doing the calculation only for a window of time (e.g. 6 months); this would likely help with less drastic changes in frequency as well, and would prevent bad results for some entries that predate first_updated_epoch (for them, it defaults to 1970).
Another corner case is a feed with exactly 1 entry in the time window.
We could eliminate duplicates from the initial import of entries without published/updated by grouping by first_updated_epoch.
Another useful thing would be to compute this metric for an arbitrary selection of entries, e.g. by tag or a full-text search query (how often do we get "python" entries?).
Presumably, if we allow arbitrary selections, the data would be returned by the
{get,search}_entry_counts(should've called it*_stats).If we don't allow the time window to be configurable, we may want to have 3 different windows (1 month, 6 months, 1 year), a la system load averages. OTOH, YAGNI; we can have just one initially (likely, 1 year), and then alias that attribute to something else later.