作者chippclass (善假狼賺錢中)
看板Database
標題Re: [SQL ] 滾動計算並回傳
時間Fri Oct 16 00:29:43 2020
我從來沒接觸過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://webptt.com/m.aspx?n=bbs/Database/M.1602779386.A.CD4.html