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

看板Database (資料庫)作者 (善假狼賺錢中)時間4年前 (2020/10/16 00:29), 編輯推噓0(000)
留言0則, 0人參與, 最新討論串5/8 (看更多)
我從來沒接觸過10萬筆以上資料的案例 所以我本來是想 240萬 join 1600萬 的資料 你應該是會用MOONY135的分批跑排程之類的做法? 不過若是10萬筆以下的資料要跑的話應該還是能一句sql跑完所以來解一下題目 我重新排版一下 先以實際執行確定這個寫法在我這邊是可以跑的 https://i.imgur.com/uX3BOfs.png
※ 引述《Wengboyu ( )》之銘言: : SAS sql的code還是有些不同,我做了一些改寫碰到了一些小問題 所以你的寫法我沒有SAS資料庫的環境來驗證跑起來哪邊有問題 : 我要計算table a每一筆,a.doctor在a.date過去一年內收過多少病人(不重複) 這邊因為時間並不是問題,所以簡化一下先把時間去掉 查詢目標改為「每個醫生不限時間收過多少不重複病人」 先來個測試用資料 醫生的表格是 dr (doctor),有三個醫生,a b c SELECT * FROM dr; +----+-----+ | dr | sid | +----+-----+ | a | A | | b | Z | | c | Z | +----+-----+ 3 rows in set (0.000 sec) 病人沒有表格,有三個病人,x y z 看病的表格是 sv (service),有五筆紀錄,c醫生沒有任何看病紀錄 SELECT * FROM sv; +----+-----+----+ | sv | sid | dr | +----+-----+----+ | 1 | x | a | | 2 | x | b | | 3 | y | b | | 4 | z | b | | 5 | x | b | +----+-----+----+ 5 rows in set (0.000 sec) SELECT dr.*,sv.sid FROM dr LEFT JOIN sv ON dr.dr=sv.dr; +----+-----+------+ | dr | sid | sid | +----+-----+------+ | a | A | x | | b | Z | x | | b | Z | y | | b | Z | z | | b | Z | x | | c | Z | NULL | +----+-----+------+ 6 rows in set (0.000 sec) 醫生表格的 sid 不知道有何作用,但是原本的例子看起來是要撈出來的,所以留著 看病表格的 sid 是病人,撈出來是為了濾掉重複用的 於是這邊的問題就是有兩個欄位都是 sid 如果對這個 join 表格再度查詢一次,則 SELECT * FROM ( SELECT dr.*,sv.sid FROM dr LEFT JOIN sv ON dr.dr=sv.dr )tb; ERROR : Duplicate column name 'sid' 在 mariaDB 中就直接錯誤了 錯誤發生後續就不會繼續跑 所以第一次join出來的表格,把看病的sid重新命名為s SELECT * FROM ( SELECT dr.*,sv.sid s FROM dr LEFT JOIN sv ON dr.dr=sv.dr )tb; +----+-----+------+ | dr | sid | s | +----+-----+------+ | a | A | x | | b | Z | x | | b | Z | y | | b | Z | z | | b | Z | x | | c | Z | NULL | +----+-----+------+ 6 rows in set (0.001 sec) b醫生重複看了病人 x ,重複的過濾掉 SELECT * FROM ( SELECT DISTINCT dr.*,sv.sid s FROM dr LEFT JOIN sv ON dr.dr=sv.dr )tb; +----+-----+------+ | dr | sid | s | +----+-----+------+ | a | A | x | | b | Z | x | | b | Z | y | | b | Z | z | | c | Z | NULL | +----+-----+------+ 5 rows in set (0.000 sec) 這個表格把數量統計起來就是目標:「每個醫生不限時間收過多少不重複病人」 SELECT dr,sid,count(*),count(1),count(s) FROM ( SELECT DISTINCT dr.*,sv.sid s FROM dr LEFT JOIN sv ON dr.dr=sv.dr )tb GROUP BY dr,sid; +----+-----+----------+----------+----------+ | dr | sid | count(*) | count(1) | count(s) | +----+-----+----------+----------+----------+ | a | A | 1 | 1 | 1 | | b | Z | 3 | 3 | 3 | | c | Z | 1 | 1 | 0 | +----+-----+----------+----------+----------+ 3 rows in set (0.001 sec) count(1) 和 count(*) 的結果是一樣的,聽說差別只在效率 count(s) 則是會計算s欄位中非null的數量 因為 c醫生的看病數為 0 ,所以把NULL算成一筆是錯誤的,這邊要用 count(s) 如果看病數為0的醫生完全不打算顯示的話,那一開始第一次的join就不要用 LEFT JOIN SELECT dr.*,sv.sid SELECT dr.*,sv.sid FROM dr LEFT JOIN sv FROM dr JOIN sv ON dr.dr=sv.dr; ON dr.dr=sv.dr; +----+-----+------+ +----+-----+-----+ | dr | sid | sid | | dr | sid | sid | +----+-----+------+ +----+-----+-----+ | a | A | x | | a | A | x | | b | Z | x | | b | Z | x | | b | Z | y | | b | Z | y | | b | Z | z | | b | Z | z | | b | Z | x | | b | Z | x | | c | Z | NULL | +----+-----+-----+ +----+-----+------+ 5 rows in set (0.000 sec) 6 rows in set (0.000 sec) 如果保證沒有null,這樣最後用 count(1),count(*),count(s),count(dr) 都可以 回到目標:「每個醫生不限時間收過多少不重複病人」 SELECT dr,sid,count(s) dr_sv_volumn FROM ( SELECT DISTINCT dr.*,sv.sid s FROM dr LEFT JOIN sv ON dr.dr=sv.dr )tb GROUP BY dr,sid; +----+-----+--------------+ | dr | sid | dr_sv_volumn | +----+-----+--------------+ | a | A | 1 | | b | Z | 3 | | c | Z | 0 | +----+-----+--------------+ 3 rows in set (0.001 sec) 以上是簡化過的目標:不限時間 如果要限定一年之內,就把時間的條件加進去 SELECT date,sid,doctor,count(s) doctor_service_volumn FROM ( SELECT DISTINCT a.date,a.sid,a.doctor,b.sid s FROM a LEFT JOIN b ON a.doctor=b.doctor AND a.date BETWEEN b.date AND DATE_ADD(b.date,INTERVAL 1 YEAR) )tb GROUP BY date,sid,doctor; Proc sql; create table want as select *, count(*) as doctor_service_volume from (select distinct a.*, b.SID from a left join b on a.DoctorID = b.DoctorID && a.date >= b.date && b.date >= intnx('year', a.date, -1, 'same') ) group by date, SID, DoctorID; quit; 比較一下可以看到兩個地方是不一樣的 SELECT date,sid,doctor,count(s) doctor_service_volumn FROM ( SELECT DISTINCT a.date,a.sid,a.doctor,b.sid s FROM a LEFT JOIN b ON a.doctor=b.doctor AND a.date BETWEEN b.date AND DATE_ADD(b.date,INTERVAL 1 YEAR) )tb GROUP BY date,sid,doctor; 其中一個比較讓我意外的是裡面join出來的表格居然外面的可以select 前面我說過會有這個錯誤:ERROR : Duplicate column name 'sid' 在 mariaDB 中就直接錯誤,我覺得是合理的 不然 select sid 到底是要撈哪個 sid ? 而你的 SAS sql 沒報錯,繼續跑出結果,我猜是不是不同資料庫對語法的行為不同? 莫非是因為你是 select * 而不是 select sid, 所以 SAS sql 就把兩個 sid 都撈出來不報錯? 如果是這樣的話,那之後還有 GROUP BY date,sid,doctor 這個 group by sid 的又是哪個 sid ? -- ◣▁▁ ◢ ▄▄▄▄▄▄▄ . * ▁▁ 狼出沒注意! . ˊ . ▅▂ ▂▅ ◢◤ ▄▄▄▄▄▄▄ . ▃▂▁ ◆↗http://chippclass.99k.org/↙◆ ▇▇ ◆↗telnet://bs2.to (P_chippclass)↙◆ ▆▅▂ ψchippclass ▅▂▁ -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 114.136.222.203 (臺灣) ※ 文章網址: https://www.ptt.cc/bbs/Database/M.1602779386.A.CD4.html
文章代碼(AID): #1VY7ZwpK (Database)
文章代碼(AID): #1VY7ZwpK (Database)