(MySQL Math Function) max and min
======= 筆記開始========= ** 每日紀錄 is written by a source which can record-down the history rate for each unique rowdata. /******** 清點每日紀錄資料筆數 OR 將每日紀錄清除 select count(*) OR delete from 每日紀錄 where RPDATE=CURDATE() *****/ /******** 檢查每組 RPDATE 有多少筆 (每天RUN出多少筆) select RPDATE, count(*) from 每日紀錄 group by RPDATE *****/ /******** 擷取符合條件的昨天&&七天前內的兩日資料,確認是否資料 curdate()=9/26 取得 9/19 , 9/25 的資料 ( should be 9/25 - 9/19 ) select RPDATE, count(*) from 每日紀錄 where (TO_DAYS(CURDATE())-TO_DAYS(RPDATE))=7 or(TO_DAYS(CURDATE())-TO_DAYS(RPDATE))=1 group by RPDATE *****/ /******************* 進入正題:抓取符合條件的TOP30 送入 每日紀錄_WEEKLYTOP30 (目前沒有七天以前的資料暫以這兩天資料相比較) 本週TOP30: select ID, TOPIC, max(GRADDING)-min(GRADDING) compGrad, count(*) phNBR from 每日紀錄 where (TO_DAYS(CURDATE())-TO_DAYS(RPDATE))=7 or (TO_DAYS(CURDATE())-TO_DAYS(RPDATE))=1 group by ID having phNBR >1 order by compGrad desc limit 30 next: 另起程式LOOP 把以上符合條件的TOP30 insert into 每日紀錄_WEEKLYTOP30 next: 週排行的source is ready for on-line routine job :確定每日紀...