Re: [SQL ] 滾動計算並回傳

看板Database (資料庫)作者linec153 (LINEC)時間4年前 (2020/11/20 18:42)推噓0(0推 0噓 0→)

留言0則, 0人參與討論串7/8 (看更多)

以下僅供參考因為問題敘述還不夠完善，我僅就能理解的部分提供建議這個問題牽涉到大數據與full table scan 所以我會先考慮複雜度的估計我估算的複雜度如下，因為不是很會估，如果有算錯請高手能不吝指正兩個檔互串，有兩個比對變數，又是使用between作範圍式比對我估的比對次數為 240萬*1600萬*1600萬=614400000萬但如果資料是6年併在一起，依照需求會有很多無效比對還有，table a 重點是要知道 date+doctor 的組合，所以我估算每年平均40萬筆資料去重覆假設變30萬筆那麼，估算切一年算一次的次數就是 30萬*(1600萬/6*2)*(1600萬/6*2)*6(年)=51136020萬至少差12倍 ======= 所以，建議至少一年切一次，每年算一次 SAS裡，先把原始資料切年度做成檔案，然後再用SQL執行計算想一句寫完，看起來很帥，但不見得跑得快我寫個範例表示一下我的概念： /*先依照年分切檔案*/ data table_a_2019 table_a_2018; set table_a; if year(prescriptionBeginDate)=2019 then output table_a_2019; if year(prescriptionBeginDate)=2018 then output table_a_2018; keep prescriptionBeginDate DOCTOR; run; proc sort data=table_a_2019 nodup; by DOCTOR prescriptionBeginDate ; proc sort data=table_a_2018 nodup; by DOCTOR prescriptionBeginDate ; run; data table_b_2019 table_b_2018; set table_b; if 2018<=year(prescriptiondate)<=2019 then output table_b_2019; if 2017<=year(prescriptiondate)<=2018 then output table_b_2018; run; /*再用SAS分年度計算*/ proc sql; select distinct a.*, count(distinct b.SID) as service_volume from table_a_2019 as a, table_b_2019 as b where a.Doctor=b.Doctor and b.prescriptiondate between a.prescriptionBeginDate and intnx('year', a.PrescriptionBeginDate, -1, 'same') ; quit; 如果切半年算一次我估的複雜度是 15萬*(1600萬/12*2)*(1600萬/12*2)*12(0.5年)=12832020萬跟原始差47倍，省更多時間也就是要用空間換取時間以上分享 ※ 引述《Wengboyu ( )》之銘言： : 資料庫名稱：SAS SQL : 資料庫版本：9.4 : 內容/問題描述： : 我有table a和b : table a : date SID doctor : 2019/1/1 a jack : 2019/1/2 a jack : 2019/1/3 a jack : 2019/2/1 b ben : 2019/2/2 b ben : 2019/2/3 b ben : ... : 2019/2/15 b mark : 2019/2/16 b mark : 2019/2/17 b mark : table b : date SID doctor : 2018/1/1 a jack : 2018/1/2 b jack : 2018/1/3 c jack : 2018/1/15 a jack : 2018/1/31 a ben : 2018/3/1 b ben : 2018/3/1 c mark : 2018/4/16 d mark : 2018/4/21 c mark : 我要得到下面的結果 : table c : date SID doctor doctor_service_volume : 2019/1/1 a jack 3 : 2019/1/2 a jack 3 : 2019/1/3 a jack 2 : 2019/2/1 b ben 1 : 2019/2/2 b ben 1 : 2019/2/3 b ben 1 : ... : 2019/2/15 b mark 2 : 2019/2/16 b mark 2 : 2019/2/17 b mark 2 : 我要計算table a每一筆，a.doctor在a.date過去一年內收過多少病人(不重複) : table b是處方簽資料 : 例如: : first row in table a : date SID doctor : 2019/1/1 a jack : 我就要從table b中去找docor jack在a.date和(a.date - 1 year)間 : 收了多少不重複的病人 : table b doctor jack 在2018/1/1 ~ 2019/1/1開過處方簽的病人 : date SID doctor : 2018/1/1 a jack : 2018/1/2 b jack : 2018/1/3 c jack : 2018/1/15 a jack (重複) : 所以a.doctor_service_volume = 3 : 我自己寫的code如下 : Proc sql; : create table want as select : a.*, (select count(distinct b.SID) : from : dataset a, dataset b : where : a.DoctorID = b.DoctorID and a.DoctorID is not missing and : b.prescriptiondate between a.prescriptionBeginDate and : intnx('year', a.PrescriptionBeginDate, -1, 'same')) : as service_volume : from : dataset a, dataset b; : quit; : 因為跑很久，我不太確定這樣寫是不是可以得到我要的結果.. : table a 有240萬筆，b有1600萬筆 : 如果大家要測試自己code寫得對不對，會怎麼弄? -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 123.194.244.48 (臺灣) ※ 文章網址: https://www.ptt.cc/bbs/Database/M.1605868945.A.8EC.html