עזרה בSQL

הפצת אנקי

יש לי טבלת SQL שמכילה נתונים של חזרות על שאלות שנעשו בעבר בתוכנת אנקי.

משמעות השדות שרלוונטיים לעניין:
id- זמן האפוק שבו בוצעה החזרה.
cid- הid של הכרטיס שעליו נעשתה החזרה
ivl- מרווח הזמן של החזרה מהחזרה שרשומה בשדה זה עד למתי שאמורה להתבצע החזרה הבאה (מינוס משמעותו דקות. מספר חיובי משמעותו ימים.)
lastIvl- המרווח מהפעם הקודמת.
type- סוג החזרה:
0=לימוד ראשוני,
1=חזרה,
2=לימוד מחדש,
3=מסונן.
כאן יש תיאור של כל המסד נתונים באנגלית

אחרי כל ההקדמה:
אני מעוניין להוציא מהטבלה הזאת רישום של היסטוריית החזרות לפי ימים דהיינו:
א. תאריך. (נמדד בין שני זמני אפוק. לא חייב להיות 12 בלילה בדווקא אלא יכול להשתנות למשל 4 לפנות בוקר.)
ב. כמות החזרות שהייתה מיועדת להיות ביום הזה.
ג.כמות הכרטיסים שעליהם נעשתה חזרה ביום הזה (יתכנו שתי חזרות על כרטיס אחד באותו יום)
ד. ישנה טבלה נוספת בשם cards שמכילה את כל השאלות שקיימות כרגע במאגר ולא כוללת את אלה שנמחקו. במידה וחלק מהכרטיסים נמחקו, כמה מתוך הכרטיסים שנעשתה עליהם חזרה עדיין קיימים במאגר
ד. אחוז החזרות שנעשו מתוך מה כלל החזרות שהיו אמורות להתבצע באותו יום.

לצורך כך כתב לי מומחה גדול את השאילתא הבאה (כרגע הוא לא יכול להמשיך לעסוק בזה):

with RECURSIVE 

        -- יצירת טווח תאריכים רצוי
        dates as (select '{start}' as date union select date(date,'+1 day')
        from dates where date < '{end}' ),

        -- מעשיר את נתוני הלוג
        dates_sch as (select cid, date_sch,next_date_rep from revlog_data 
        union select  cid, date(date_sch, '+1 day'), next_date_rep 
        from dates_sch where date(date_sch, '+1 day') <= next_date_rep and date(date_sch) <  date(unixepoch() {offset},'unixepoch') ),

        -- מחשב תאריכי תזמון
        revlog_data as (
        select cid, case when next_date_rep < date_sch then next_date_rep else date_sch end date_sch,  next_date_rep
        from         
        (select  cid, 
        date((substr(id ,1,10) {offset}) + (case when ivl < 0 then 0 else ivl * 86400 end),'unixepoch') date_sch,
        coalesce(LEAD(date(substr(id,1,10) {offset},'unixepoch')) over (PARTITION by cid order by date(id  
         + (case when ivl < 0 then 0 else ivl * 86400 end),'unixepoch')),'2070-01-01') next_date_rep
        from revlog {lim} order by id)
        --from revlog order by id)
        ),
        cid_rep_date as (
        select date(substr(id,1,10) {offset},'unixepoch') cid_date , count(distinct cid) cnt_cid from revlog group by date(substr(id,1,10) {offset},'unixepoch')         
        )
        -- מציג נתוני לוג ביחס לטווח התאריכים
        select unixepoch(date)  , 
        (case when date > date() then - cnt else cnt end)  sch, 
        (case when date > date() then - cnt else ans end) ans, 
        (case when date > date() then - cnt else ans * 100.0 / cnt end) p, 
        --unixepoch(date) d
        coalesce(cnt_cid,0) cnt_cid
        from dates left join (select date_sch , sum(case when date_sch = next_date_rep then 1 else 0 end) ans, 
        count(*)  cnt
        --from dates_sch GROUP by date_sch) log on dates.date = log.date_sch
        from dates_sch GROUP by date_sch) log on dates.date = log.date_sch left join cid_rep_date on dates.date = cid_rep_date.cid_date;

הסברים מתוך מה שהבנתי מדבריו:
בsql אין מושג של לוח שנה. לכן בתחילה הוא יוצר טווח תאריכים ואז הוא ממלא אותם יום יום בכל הנתונים. החישוב נעשה לפי כל כרטיס ממתי שהיתה אמורה להתבצע חזרה עליו עד הזמן שבו נעשתה החזרה בפועל.
יש התייחסות גם למצב שבו החזרה הוקדמה לזמן שבו היתה אמורה להיות.
כרטיסים שנמחקו- לעת עתה אין אליהם התייחסות כלשהיא. מכיון שצריך שיוגדר שאם הם לא קיימים לא יחושבו זמני חזרות צפויים מאז החזרה האחרונה שנעשתה עליהם.
חזרה ראשונה- השאילתא סופרת את החזרותרק החל מהחזרה השניה על כל כרטיס אולם החזרה הראשונה לא נספרת מכיון שהיא לא "צפויה"- זה זקוק לפתרון.
ימים ריקים בעתיד- צריך שהשאילתא תחזיר שאין כלום דהיינו 0 ולא כמו עכשיו שהיא מחזירה ערך ריק.
קובץ לדוגמא. הטבלה הרלוונטית היא revlog.

ארי

פוסט זה נמחק!

הפצת אנקי

כותב הנוסחא עדכן אותה מעט אבל עדיין יש בה תקלות.
להלן הקוד

        with RECURSIVE 
        -- שליפה זמנית: רשימת תאריכים
        dates as (select '2020-09-06' as _revlog_date_ union select date(_revlog_date_,'+1 day')
        from dates where _revlog_date_ < '2027-01-20' ),

        -- שליפה זמנית: נתוני לוג
        log as (
        select
        -- מזהה כרטיס 
        cid,
        -- תאריך לוג  
        date(substr(id,1,10)  - 7200,'unixepoch') date_log,
        -- תאריך חזרה 
        date(substr(id,1,10)  - 7200,'unixepoch')  date_ans,
        -- מחזיר את הפעם הבאה שהכרטיס הזה נענה
        LEAD(date(substr(id,1,10),'unixepoch')) over (PARTITION by cid order by date(substr(id,1,10)  - 7200,'unixepoch')) next_ans,
        -- לאיזה תאריך זה תוזמן פחות יום
        date(substr(id,1,10)  - 7200 + (case when ivl < 0 then 0 else ivl * 86400 end),'unixepoch','-1 day') sch_from,
        -- האם חדש
        case when lastivl = -60 then 1 else 0 end new, 
        -- האם חזרה
        case when lastivl <> -60 then 1 else 0 end ans, 
        -- האם מתוזמן
        0 sch
        from revlog WHERE ease != 1 AND cid in (select id from cards WHERE queue IN (2,3) ) 

        -- עבור כל שורה מהשליפה לעיל מוסיף עוד שורת תזמון החל sch_from עד next_ans
        union 
        select cid, 
        date(sch_from, '+1 day') date_log , 
        date_ans,
        sch_from, 
        next_ans, 
        0 new, 
        0 ans, 
        1 sch
        from log
        where date(sch_from, '+1 day') <=  COALESCE(next_ans,CURRENT_DATE)
        ),

        -- שליפה זמנית: כמות כרטיסים ביום
        cid_rep_date as (
                select date(substr(id,1,10)+ 0,'unixepoch') cid_date , count(distinct cid) cnt_cid from revlog group by date(substr(id,1,10)+ 0,'unixepoch')         
        )

        -- עד כאן שלב ההצהרות

        select unixepoch(_revlog_date_), 
        a ans, 
        n new, s sch, (a * 100 / s) p, cnt_cid from
        dates left join
        (
        (select date_log,
        sum(ans) a,
        sum(sch) s,
        sum(new) n
 from log group by date_log) ans_sch
        join cid_rep_date on ans_sch.date_log = cid_rep_date.cid_date) log_data
        on dates._revlog_date_ = log_data.date_log

בקוד הזה שמתי לב לבעיה שאם יש שתי חזרות שנענו באותו יום הקוד הזה מסמן אותן רק כחזרה אחת.

(המשמעות של -7200 היא הפער ביחס לזמן GMT + פער של הזמן שבו מתחלף יום בתוכנה)

תחומים - פורום חרדי מקצועי

עזרה בSQL