下面的查询基于一个复杂的视图,视图按照我的意愿工作(我不会包含视图,因为我认为它不会对手头的问题有所帮助).我无法做到的是drugCountsinFamilies专栏.我需要它来向我展示每个药物家族的不同药物名称的数量.您可以从第一个screencap看到有三个不同的H3A行. H3A的药物家族应该是3(有三种不同的H3A药物.)
您可以从第二个屏幕截图中看到,第一个屏幕截图中的drugCountsInFamilies正在捕获药物名称列出的行数.
以下是我的问题,评论部分不正确
select distinct
rx.patid,d2.fillDate,d2.scriptEndDate,rx.drugName,rx.drugClass
--the line directly below is the one that I can't figure out why it's wrong,COUNT(rx.drugClass) over(partition by rx.patid,rx.drugclass,rx.drugname) as drugCountsInFamilies
from
(
select
ROW_NUMBER() over(partition by d.patid order by d.patid,d.uniquedrugsintimeframe desc) as rn,d.patid,d.fillDate,d.scriptEndDate,d.uniqueDrugsInTimeFrame
from DrugsPerTimeFrame as d
)d2
inner join rx on rx.patid = d2.patid
inner join DrugTable as dt on dt.drugClass=rx.drugClass
where d2.rn=1 and rx.fillDate between d2.fillDate and d2.scriptEndDate
and dt.drugClass in ('h3a','h6h','h4b','h2f','h2s','j7c','h2e')
order by rx.patid
如果我尝试在count(rx.drugClass)子句中添加一个distinct,SSMS就会生气.可以使用窗口函数完成吗？
解决方法
将计数(不同)作为Windows函数需要一个技巧.实际上有几个级别的技巧.
因为您的请求实际上非常简单 – 值始终为1,因为rx.drugClass位于分区子句中 – 我将做出假设.假设您想要计算每个独特药物类别的数量.
如果是这样,请执行由patid和drugClass分区的row_number().当这是1,在一个patid中,然后一个新的drugClass开始.创建一个在这种情况下为1的标志,在所有其他情况下为0.
查询(格式化之后我可以阅读它),如下所示：
select rx.patid,rx.drugClass,SUM(IsFirstRowInGroup) over (partition by rx.patid) as NumDrugCount
from (select distinct rx.patid,(case when 1 = ROW_NUMBER() over (partition by rx.drugClass,rx.patid order by (select NULL))
then 1 else 0
end) as IsFirstRowInGroup
from (select ROW_NUMBER() over(partition by d.patid order by d.patid,d.uniqueDrugsInTimeFrame
from DrugsPerTimeFrame as d
) d2 inner join
rx
on rx.patid = d2.patid inner join
DrugTable dt
on dt.drugClass = rx.drugClass
where d2.rn=1 and rx.fillDate between d2.fillDate and d2.scriptEndDate and
dt.drugClass in ('h3a','h2e')
) t
order by patid
