原创

[koreader]statistics插件之博客阅读统计模块

前言

去年我打算将koreader的阅读统计模块挪到我的博客站点,为了自己更好的能看到阅读统计,经过3天左右的开发,现在可以汇报下了

截图

架构

编码阶段刚结束,随意添加了几个统计的内容,先凑活着用,后续再调整。

图用了echart.

通过syncthingt(安卓版本)同步koreader目录下的staticstics.sqlite3文件,系统通过采集到本地mysql表中,然后针对表进行具体的查询。

关键查询

最近一个阶段(比如周,月)每天阅读的页数及阅读时间

<select id="getDatesFromAllDetail" parameterType="String" resultMap="datesFromAllDetail">
<!-- '%Y-%m-%d'--> SELECT dates, COUNT(*) AS pages, SUM(sum_duration) AS durations,year,month FROM (SELECT DATE_FORMAT(FROM_UNIXTIME(start_time), '%Y') AS year, DATE_FORMAT(FROM_UNIXTIME(start_time), '%m') AS month, DATE_FORMAT(FROM_UNIXTIME(start_time), #{dateFormat}) AS dates, SUM(duration) AS sum_duration FROM biz_koread_statistics_page_stat WHERE 1=1 <if test="startTime!=null"> AND start_time >= #{startTime} </if> GROUP BY id_book , page , dates,year,month) AS b GROUP BY dates,year,month ORDER BY dates DESC; </select>

获取近一日/近一周/近一月/近一年/总时间 的阅读时间


SELECT
        (select sum(duration) as all_books from biz_koread_statistics_page_stat) AS all_books,
        (select sum(duration) as begin_day from biz_koread_statistics_page_stat where 1=1
        <if test="fromBeginDay!=null">
            and start_time>#{fromBeginDay}
        </if>) AS begin_day,
        (select sum(duration) as begin_week from biz_koread_statistics_page_stat where 1=1
        <if test="fromBeginWeek!=null">
            and start_time>#{fromBeginWeek}
        </if>) AS begin_week,
        (select sum(duration) as begin_month from biz_koread_statistics_page_stat where 1=1
        <if test="fromBeginMonth!=null">
            and start_time>#{fromBeginMonth}
        </if>) AS begin_month,
        (select sum(duration) as begin_year from biz_koread_statistics_page_stat where 1=1
        <if test="fromBeginYear!=null">
            and start_time>#{fromBeginYear}
        </if>) AS begin_year
    FROM DUAL
温馨提示:
本文最后更新于 2023年01月02日,已超过 35 天没有更新。若文章内的图片失效(无法正常加载),请留言反馈或直接联系我
正文到此结束
本文目录