作者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/cn.aspx?n=bbs/Database/M.1602779386.A.CD4.html