SQL连接问题 [英] SQL joining question

问题描述

这是我的情况:

我有一个表，其中包含一列包含NDC(标识符)的已售药物清单，已售数量以及该药物是商标名称还是非专利药.我还有另一个表，其中包含处方编号，日期和NDC.

I have one table that contains a list of drugs sold containing the NDC (an identifier), quantity sold, and whether the drug is a brand name or generic. I have another table that contains the prescription numbers, dates, and NDCs.

我需要为前50种非专利药和前50种品牌药生成最新的4个处方编号列表.

I need to generate a list of the most recent 4 prescription numbers for the top 50 generic drugs and the top 50 Brand name drugs.

简化示例:

Drug_list:
NDC   QTY      Type
123   50       Generic
125   47       Brand
128   34       Generic
...
549   1        Brand
294   1        Generic

Claims_list:
NDC  RX_num  Date
123  1234    20081027
123  4194    20090517
594  12598   20091012

我如何编写联接以生成

NDC RX1, RX2, RX3, RX4

NDC是50个最常见的品牌" NDC，而以下RX是最新声明的RX编号?

where NDC are the 50 most common 'Brand' NDCs, and the following RXs are the RX numbers of the most recent claims?

~~~~~~~

到目前为止，我已经知道了:

So far I've got this:

select t.ndc, cl.rx, cl.date from (
select * from (
select * from (
select * from drug_list where brand = 'Generic')
order by qty)
where rownum < 51) t
join claims_list cl on cl.ndc = t.ndc
order by t.ndc, cl.date;

这让我成为其中的一部分.从那里，如何将其缩小到每个NDC仅4个结果?并且，可以通过以下方式获得它:

Which gets me part of the way there. From there, how do I trim it down to only 4 results per NDC? And, is it possible to get it in the following from:

NDC, RX1, RX2, RX3, RX4

如果我必须将其报告为:

If I have to report it as:

NDC1, RX1
NDC1, RX2
NDC1, RX3
NDC1, RX4
NDC2, RX1
NDC2, RX2
NDC2, RX3
NDC2, RX4
NDC3, RX1
... etc

但我希望将其放在一行上.

but I would prefer to have it on one line.

~~~~ (根据注释的要求:为示例表创建表语句):

~~~~ (as requested by a comment: create table statements for example tables):

create table drug_list
(NDC varchar2(15), QTY number, type varchar2(10));

create table claims_list
(NDC varchar2(15), RX_num varchar2(20), "date" date);

推荐答案

您可以结合使用Analytics(分析)(如果您使用的是最新版本的Oracle)和数据透视表来完成此操作.这应该适用于您的数据集.

You can use a combination of Analytics (if you are on a recent enough version of Oracle) and a Pivot Table to do it. This should work with your dataset.

select ndc,
       max(decode(rn, 1, rx_num, null)) rx1,
       max(decode(rn, 2, rx_num, null)) rx2,
       max(decode(rn, 3, rx_num, null)) rx3,
       max(decode(rn, 4, rx_num, null)) rx4
  from (select *
          from (select claims_list.ndc,
                       claims_list.rx_num,
                       row_number() over (partition by claims_list.ndc order by claims_list.date desc) rn
                  from claims_list,
                       (select * 
                          from (select *
                                  from drug_list
                                 where type = 'Generic'
                                order by qty desc
                               )
                         where rownum < 51
                       ) drug_list
                 where drug_list.ndc = claims_list.ndc
               )
         where rn < 5
        order by ndc, rn
       )
group by ndc;

内部查询使用分析功能，根据索赔日期提取每种药物的最新4个rx编号.然后，我们使用枢轴将其从每种药物的4行转移到4列的一行.

The inner query uses analytics to pull the most recent 4 rx numbers for each drug based on the claim date. Then we use a pivot to take it from 4 lines per drug to one line with 4 columns.

这篇关于SQL连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！