计算不同记录的窗口函数

Question

下面的查询基于一个复杂的视图，该视图按我的意愿工作（我不打算包含该视图，因为我认为它不会帮助解决手头的问题）。我无法正确的是 drugCountsinFamilies 列。我需要它来显示每个药物系列的 distinct drugName 的数量。您可以从第一个屏幕截图中看到有三个不同的 H3A 行。 H3A 的 drugCountsInFamilies 应该是 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) 子句添加不同的内容，SSMS 会发疯。可以使用窗口函数来完成吗？

原文由 wootscootinboogie 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

将 count(distinct) 作为 Windows 功能需要一个技巧。实际上，有几个级别的技巧。

因为您的请求实际上非常简单——该值始终为 1，因为 rx.drugClass 在分区子句中——我将做一个假设。假设您要计算每个患者的独特药物类别的数量。

如果是这样，请执行 row_number() 由 patid 和 drugClass 分区。当这是 1 时，在一个 patid 内，一个新的 drugClass 开始了。创建一个标志，在这种情况下为 1，在所有其他情况下为 0。

然后，您可以简单地使用分区子句执行 sum 来获取不同值的数量。

查询（在格式化它以便我可以阅读之后）看起来像：

 select rx.patid, d2.fillDate, d2.scriptEndDate, rx.drugName, rx.drugClass,
       SUM(IsFirstRowInGroup) over (partition by rx.patid) as NumDrugCount
from (select distinct rx.patid, d2.fillDate, d2.scriptEndDate, rx.drugName, rx.drugClass,
             (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 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 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')
     ) t
order by patid

原文由 Gordon Linoff 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

SELECT    MAX(
            dense_rank()
            OVER (
              ORDER BY name DESC
              PARTITION BY family
            )
          )
OVER (
  PARTITION BY family
)

这能行吗？

原文由 WinnieThePooh 发布，翻译遵循 CC BY-SA 4.0 许可协议